Concatenate strings in group column

I have following problem:
I want to display a concatenated string in a group row of a report.
The grouping works fine, i can "sum" numerical values etc. as
expected, but i could not find a way to concatenate strings (from the
rows that make up the group). "Join(...)" does not work (gives me an
error message).
I can use "First(...) & Last(...)" to concatenate the string columns
from the first and last row of the group, but couldn't find a way to
concatenate the string columns from all rows...
I am using SQL Server 2005 / Visual Studio 2005.I use a CLR aggregate directly in SQL Server. Hopes your datasource is SQL
Server 2005.
In reports, I use Replace function to change my separator character.
--
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
"PR" <pr_gg@.egal.de> wrote in message
news:76e4156a-8805-4e71-927a-7f2c848f295d@.q77g2000hsh.googlegroups.com...
>I have following problem:
> I want to display a concatenated string in a group row of a report.
> The grouping works fine, i can "sum" numerical values etc. as
> expected, but i could not find a way to concatenate strings (from the
> rows that make up the group). "Join(...)" does not work (gives me an
> error message).
> I can use "First(...) & Last(...)" to concatenate the string columns
> from the first and last row of the group, but couldn't find a way to
> concatenate the string columns from all rows...
> I am using SQL Server 2005 / Visual Studio 2005.
>
>|||On Jan 17, 8:21 am, "Jean-Pierre Riehl" <jean-pierre.ri...@.bewise.fr>
wrote:
> I use a CLR aggregate directly in SQL Server. Hopes your datasource is SQL
> Server 2005.
> In reports, I use Replace function to change my separator character.
> --
> Jean-Pierre Riehlhttp://blog.djeepy1.nethttp://www.bewise.fr
> "PR" <pr...@.egal.de> wrote in message
> news:76e4156a-8805-4e71-927a-7f2c848f295d@.q77g2000hsh.googlegroups.com...
>
thanks for the idea. I do use SQL Server 2005, however, since this is
a "group" column in a report, i can't use SQL to concatenate the
strings as i deal with a grouped subset of the sql query result...|||On Jan 16, 9:31=A0am, PR <pr...@.egal.de> wrote:
> I have following problem:
> I want to display a concatenated string in a group row of a report.
> The grouping works fine, i can "sum" numerical values etc. as
> expected, but i could not find a way to concatenate strings (from the
> rows that make up the group). "Join(...)" does not work (gives me an
> error message).
> I can use "First(...) & Last(...)" =A0to concatenate the string columns
> from the first and last row of the group, but couldn't find a way to
> concatenate the string columns from all rows...
> I am using SQL Server 2005 / Visual Studio 2005.
I'm having a hard time visualizing what you want to display. Do you
want your concantenation in each detail row or just in a group header?
Can you mock up an example?|||On Jan 21, 4:06 pm, toolman <t...@.infocision.com> wrote:
> I'm having a hard time visualizing what you want to display. Do you
> want your concantenation in each detail row or just in a group header?
> Can you mock up an example?
I want the concatenation in each detail row. I have some group "sum"s
that work fine, and want to do the same for a string. Basically it's
just a normal grouping of columns with group sums etc.. just that i
need to concatenate a string besides building the group sums.|||On Jan 22, 4:23=A0am, PR <pr...@.egal.de> wrote:
> On Jan 21, 4:06 pm, toolman <t...@.infocision.com> wrote:
> > I'm having a hard time visualizing what you want to display. =A0Do you
> > want your concantenation in each detail row or just in a group header?
> > Can you mock up an example?
> I want the concatenation in each detail row. I have some group "sum"s
> that work fine, and want to do the same for a string. Basically it's
> just a normal grouping of columns with group sums etc.. just that i
> need to concatenate a string besides building the group sums.
Is this what you want?
=3D"String Expression " & SUM(Fields!Name.Value)
It would look something like:
String Expression 1234.56
If you're wanting to concantenate actual field values, say like if
you're grouping on a combination of fields then:
=3DFields!GroupField1.Value & ", " & Fields!GroupField2.Value
would give you something like City, State or Company, Division
HTH|||On Jan 22, 8:57 pm, toolman <t...@.infocision.com> wrote:
> Is this what you want?
> ="String Expression " & SUM(Fields!Name.Value)
> It would look something like:
> String Expression 1234.56
> If you're wanting to concantenate actual field values, say like if
> you're grouping on a combination of fields then:
> =Fields!GroupField1.Value & ", " & Fields!GroupField2.Value
> would give you something like City, State or Company, Division
> HTH
Sorry, no, i don't want to concatenate strings with the sum value of
the group.
I have a grouping in the report, where a sum value (i.e. =Sum(Fields!
Name.NumValue)) is written to one textfield of the row.
What i want to do is concatenate a string from the groups result set -
pretty much like the "Sum" sums up the numerical values from the
result set over the rows. If "Join" would work it would look like
=Join(Fields!Name.StringValue) for the other text field.. but
unfortunately that does not work.
Any ideas ?|||On Jan 23, 3:50=A0am, PR <pr...@.egal.de> wrote:
> On Jan 22, 8:57 pm, toolman <t...@.infocision.com> wrote:
>
> > Is this what you want?
> > =3D"String Expression " & SUM(Fields!Name.Value)
> > It would look something like:
> > String Expression 1234.56
> > If you're wanting to concantenate actual field values, say like if
> > you're grouping on a combination of fields then:
> > =3DFields!GroupField1.Value & ", " & Fields!GroupField2.Value
> > would give you something like City, State or Company, Division
> > HTH
> Sorry, no, i don't want to concatenate strings with the sum value of
> the group.
> I have a grouping in the report, where a sum value (i.e. =3DSum(Fields!
> Name.NumValue)) is written to one textfield of the row.
> What i want to do is concatenate a string from the groups result set -
> pretty much like the "Sum" sums up the numerical values from the
> result set over the rows. If "Join" would work it would look like
> =3DJoin(Fields!Name.StringValue) for the other text field.. but
> unfortunately that does not work.
> Any ideas ?
I think if you can get your Fields!Name.StringValue values into an
array, you could then use Join() to get what you want. Unfortunately,
I'm not enough of a VB or .NET guy to guide you through that.
Hopefully someone else can jump at this point. Sorry I can't get you
further.|||On Jan 23, 5:03 pm, toolman <t...@.infocision.com> wrote:
> I think if you can get your Fields!Name.StringValue values into an
> array, you could then use Join() to get what you want. Unfortunately,
> I'm not enough of a VB or .NET guy to guide you through that.
> Hopefully someone else can jump at this point. Sorry I can't get you
> further.
Thanks for your effort. Yes, Join() requires an array.. and i have no
idea how to convert the string values into an array to do that (and
could not find anything about it in the documentation).|||Hello, PR.
Did you find any solution to your problem? I have the same issue and I can't
find any solution for this...
Thank you.
--
Alexandra
"PR" wrote:
> On Jan 23, 5:03 pm, toolman <t...@.infocision.com> wrote:
> >
> > I think if you can get your Fields!Name.StringValue values into an
> > array, you could then use Join() to get what you want. Unfortunately,
> > I'm not enough of a VB or .NET guy to guide you through that.
> > Hopefully someone else can jump at this point. Sorry I can't get you
> > further.
> Thanks for your effort. Yes, Join() requires an array.. and i have no
> idea how to convert the string values into an array to do that (and
> could not find anything about it in the documentation).
>|||On Feb 22, 12:13 pm, Alexandra Ribeiro
<AlexandraRibe...@.discussions.microsoft.com> wrote:
> Hello, PR.
> Did you find any solution to your problem? I have the same issue and I can't
> find any solution for this...
> Thank you.
> --
> Alexandra
>
Hi Alexandra,
no, i haven't found a solution for this. Maybe using a sub-report is
an option, but i haven't tried this yet.sqlsql

Concatenate list of values under a group

I have the following dataset:

State ZIP Homes Schools

WA 98007 2000 4

WA 98052 3000 5

WA 98079 2000 3

Now if I have set the group by expression on State but as display if I want to show it as “[98007, 98052, 98079]” how can I accomplish this.

My report needs to show:

State Homes Schools

[98007, 98052, 98079] 7000 12

Any help will be greatly appreciated

SPJ11, Why dont you do this in SQL itselves? You can also do this in SSRS. At the group level use,

Sum(Fields!Homes.value), Sum(Fields!Schools.value) -- these will retun the counts correctly.

I am not sure as how to concatnate the zip codes. I thought this should work

Join(Fields!Zip.Value) -- but this does not seem to work, I will see if I can come with something else...

|||

Never mind. I could make it work. Thanks to everyone who spent time on this thread

sqlsql

Computing Dates

I'm soliciting any suggestions from the readers of the group on how
best to tackle this task. I need to determine if trouble tickets
opened get closed within 4 hours. Yes, I already know about
datediff(hh, date1, date2, but it's not that simple. The thing I'm
struggle with is that I need to take inaccount business hours (8:00am
to 4:00pm). If date1 is 10/27/05 2:00pm then the 4 hour window ends at
10/28/05 10:00am. Any suggestions are much appreciated.Try,
if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(hh,
@.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112)
and (datediff(hh, @.sd, @.ed) - 8) <= 4) print 'may be in 4 hours'
else
'more than 4 hours'
AMB
"BobD" wrote:

> I'm soliciting any suggestions from the readers of the group on how
> best to tackle this task. I need to determine if trouble tickets
> opened get closed within 4 hours. Yes, I already know about
> datediff(hh, date1, date2, but it's not that simple. The thing I'm
> struggle with is that I need to take inaccount business hours (8:00am
> to 4:00pm). If date1 is 10/27/05 2:00pm then the 4 hour window ends at
> 10/28/05 10:00am. Any suggestions are much appreciated.
>|||Correction:
if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(hh,
@.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112)
and (datediff(hh, @.sd, @.ed) - 20) <= 4)
print 'may be in 4 hours'
else
'more than 4 hours'
go
AMB
"Alejandro Mesa" wrote:
> Try,
> if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(h
h,
> @.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112
)
> and (datediff(hh, @.sd, @.ed) - 8) <= 4) print 'may be in 4 hours'
> else
> 'more than 4 hours'
>
> AMB
> "BobD" wrote:
>|||Correction: (sorry guys)
if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(hh,
@.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112)
and (datediff(hh, @.sd, @.ed) - 16) <= 4)
print 'may be in 4 hours'
else
'more than 4 hours'
go
AMB
"Alejandro Mesa" wrote:
> Correction:
> if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(h
h,
> @.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112
)
> and (datediff(hh, @.sd, @.ed) - 20) <= 4)
> print 'may be in 4 hours'
> else
> 'more than 4 hours'
> go
>
> AMB
> "Alejandro Mesa" wrote:
>|||Bob,
Try this:
SELECT
CASE WHEN cast(convert(varchar(12),date1,114) as datetime) < '08:00'
THEN dateadd(day,datediff(day,0,date1),'12:00
')
WHEN cast(convert(varchar(12),date1,114) as datetime) < '12:00'
THEN dateadd(hour,4,date1)
ELSE dateadd(hour,20,date1) END
FROM yourTable
Steve Kass
Drew University
BobD wrote:

>I'm soliciting any suggestions from the readers of the group on how
>best to tackle this task. I need to determine if trouble tickets
>opened get closed within 4 hours. Yes, I already know about
>datediff(hh, date1, date2, but it's not that simple. The thing I'm
>struggle with is that I need to take inaccount business hours (8:00am
>to 4:00pm). If date1 is 10/27/05 2:00pm then the 4 hour window ends at
>10/28/05 10:00am. Any suggestions are much appreciated.
>
>|||Bod
try this
SELECT CASE WHEN DATE1 = DATE2 THEN DATEDIFF(HOUR, DATE1,DATE2) ELSE
DATEDIFF(HOUR, DATE1,(DATEADD(HOUR,-16,DATE2)))
--
Regards
R.D
--Knowledge gets doubled when shared
"BobD" wrote:

> I'm soliciting any suggestions from the readers of the group on how
> best to tackle this task. I need to determine if trouble tickets
> opened get closed within 4 hours. Yes, I already know about
> datediff(hh, date1, date2, but it's not that simple. The thing I'm
> struggle with is that I need to take inaccount business hours (8:00am
> to 4:00pm). If date1 is 10/27/05 2:00pm then the 4 hour window ends at
> 10/28/05 10:00am. Any suggestions are much appreciated.
>

Compute sum of count(*) with group by

Hi,
Given the following table and test data:
CREATE TABLE test (
recordId numeric(18, 0) NOT NULL,
spId int NOT NULL,
startTime datetime NULL,
endTime datetime NULL )
INSERT INTO test VALUES (1,1,'2005-01-01 12:00','2005-01-01 14:33')
INSERT INTO test VALUES (2,2,'2005-01-01 12:26','2005-01-01 14:00')
INSERT INTO test VALUES (3,1,'2005-01-01 14:00','2005-01-01 14:33')
INSERT INTO test VALUES (4,2,'2005-01-01 14:00','2005-01-01 15:15')
INSERT INTO test VALUES (5,1,'2005-01-01 15:15','2005-01-01 15:20')
INSERT INTO test VALUES (6,2,'2005-01-01 15:15','2005-01-01 16:00')
INSERT INTO test VALUES (7,3,'2005-01-01 12:00','2005-01-01 14:30')
the following query lists only the spid's with non-unique spid's and
their respective counts:
SELECT spid, count(*) AS 'Count'
FROM test
GROUP BY spid
HAVING count(*) > 1 ORDER BY spid
I'm new to SQL and am having difficulty with a couple of things:
1. Modify the above query to compute the grand total for the Count, or
indeed a separate SQL statement to return just the grand total (= 6 in
this example).
2. This is the big challenge :). Taking the grouping returned by the
above query, write a query/stored procedure which looks for records
with identical spId's and the endtime of one spid equal to the
startTime of another. With the above test data, recordIds 3, 5, and 2,
4, 6 match this criteria.
Thanks very much for any help with this.1. Use a derived table construct:
SELECT SUM( total )
FROM ( SELECT spid, COUNT(*)
FROM tbl
GROUP BY spid
HAVING COUNT(*) > 1 ) D ( spid, total ) ;
2. Not sure if your requirements are clear since . Something like:
SELECT recordId, spId, ...
( SELECT MIN( t2.startTime )
FROM tbl t2 WHERE t2.spId = t1.spId
AND t2.startTime >= t1.endtime )
FROM tbl t1
ORDER BY t1.spid, startTime ;
If, not please post the sample resultset for the dataset you posted.
Anith|||Anith Sen wrote:
> 1. Use a derived table construct:
> SELECT SUM( total )
> FROM ( SELECT spid, COUNT(*)
> FROM tbl
> GROUP BY spid
> HAVING COUNT(*) > 1 ) D ( spid, total ) ;
>
Thanks. What does the 'D' mean above?

> 2. Not sure if your requirements are clear since . Something like:
> SELECT recordId, spId, ...
> ( SELECT MIN( t2.startTime )
> FROM tbl t2 WHERE t2.spId = t1.spId
> AND t2.startTime >= t1.endtime )
> FROM tbl t1
> ORDER BY t1.spid, startTime ;
> If, not please post the sample resultset for the dataset you posted.
CREATE TABLE test (
recordId numeric(18, 0) NOT NULL,
spId int NOT NULL,
startTime datetime NULL,
endTime datetime NULL )
INSERT INTO test VALUES (1,1,'2005-01-01 12:00','2005-01-01 14:33')
INSERT INTO test VALUES (3,1,'2005-01-01 14:00','2005-01-01 14:33')
INSERT INTO test VALUES (5,1,'2005-01-01 14:33','2005-01-01 15:20')
INSERT INTO test VALUES (2,2,'2005-01-01 12:26','2005-01-01 14:00')
INSERT INTO test VALUES (4,2,'2005-01-01 14:00','2005-01-01 15:15')
INSERT INTO test VALUES (6,2,'2005-01-01 15:15','2005-01-01 16:00')
INSERT INTO test VALUES (7,3,'2005-01-01 12:00','2005-01-01 14:30')
(Sorry, no wonder it wasn't clear as there was mistake in my original
test data. I've corrected the data above and put records with the same
spId together to make the grouping more obvious.)
So, from the above test data the expected results contain 2 sets of
matching data:
1. recordIds 3 and 5 because they have the same spId (1) and the
endTime of recordId 3 is the same as the startTime of recordId 5.
2. recordIds 2, 4 and 6 because they have the same spId (2) and the
endTime of recordId 2 is the same as the startTime of recordId 4; the
endTime of 4 is the same as the startTime of 6.
I hope that makes sense now. cheers,|||On 11 Nov 2005 09:30:25 -0800, "J Williams"
<johnwilliams_esquire@.hotmail.com> wrote:
>SELECT spid, count(*) AS 'Count'
>FROM test
>GROUP BY spid
WITH ROLLUP
>HAVING count(*) > 1 ORDER BY spid
If that does the job, great, otherwise you can always store the
results of the first query in a table an do further summations against
it.
J.|||>SELECT spid, count(*) AS 'Count'
>FROM test
>GROUP BY spid
WITH ROLLUP
>HAVING count(*) > 1 ORDER BY spid
Thanks, but that doesn't give the expected result. The basic SELECT:
SELECT spid, count(*) AS 'Count'
FROM test
GROUP BY spid
HAVING count(*) > 1 ORDER BY spid
returns:
spid Count
1 3
2 3
The grand total of Count in the above resultset is 6 and the SQL posted
earlier by Anith Sen gives this result:
SELECT SUM( total )
FROM ( SELECT spid, COUNT(*)
FROM tbl
GROUP BY spid
HAVING COUNT(*) > 1 ) D ( spid, total )|||>> What does the 'D' mean above?
D in the query stands for an alias for the derived table ( some folks
explicitly use AS keyword before the alias as well. )
Can you post the sample resultset here ( as you'd want to see on the QA
results pane ).
Anith|||Anith Sen wrote:

> Can you post the sample resultset here ( as you'd want to see on the QA
> results pane ).
First recordId, Second recordId, spId, endTime, startTime
3 5 1 2005-01-01 14:33 2005-01-01 14:33
2 4 2 2005-01-01 14:00 2005-01-01 14:00
4 6 2 2005-01-01 15:15 2005-01-01 15:15
The resultset shows pairs of 'matching' records, which is slightly
different (and better) to how I first envisioned it.
Thanks.|||This is one way of getting it:
SELECT MAX( t1.recordid ),
t2.recordid, t1.spid, t1.endtime
FROM test t1
INNER JOIN test t2
ON t1.spId = t2.spId
AND t1.endTime = t2.starttime
GROUP BY t1.spid, t2.recordid, t1.endtime ;
Anith|||That's excellent, thanks.

Complex SQL query

Hi,

I'm doing a report with a group of queries but right now is very slow, so I need to do it faster. These are not the real tables but will help:

The report needs to show the total products for every combination of ADDRESS and PRODUCT_TYPE. Assume these are the tables:

ADDRESS: ADDRESS_ID, ADDRESS_NAME
PRODUCT_TYPE: PRODUCT_TYPE_CODE, PRODUCT_TYPE_NAME
ORDER: ORDER_ID, DATE, ORDER_STATUS
ORDER_LINE: ORDER_ID, ORDER_PRODUCT_TYPE, PRODUCT_TOTAL
(This is an special table to handle the stock)
STOCK_INFO: STOCK_ACTUAL, DATE_UPDATED

This is what I'm doing in code (asp):

1. Retrieve all the address (and put it in array)
2. Retrieve all the product types (and put it in array)
3. Using double "FOR" I build the query for every combination of Address and ProductType

This is still slow (and is even better than before) and I would like to put everything in just 1 query and get this data ready to show in HTML

Address Product Type 1 Product Type2 Product Type3
Address1 TotProdType11 TotProdType21 TotProdType31
Address2 TotProdType12 TotProdType22 TotProdType32
.....

I'll really appreciate any help. And also any better idea to do these is welcome (is just I don't have to much knowledge in very complex queries)

Thanks in advance

Moving to Transact-SQL forum...|||I don't see any relationship between Address table and the Product_Type at all. How is it related ?|||

use a CROSS JOIN in SQL server if you want a combination of all products and addresses.

eg

select a.address, Address p.Product Type from address a cross join product_type p

Note that if you are wanting a cartesian product here, you should specify no join criteria, as you want every address and product combination. That should be much quicker than doing it in client side code. However, you will need some kind of join to get the totals for each product, as a cross joins blindly combines all rows from 1 table to all the rows from another. I need further clarification here.

You will then have to turn the results into a pivot table. In SQL 2005, use the PIVOT function, in SQL 2003 and earlier, you will need to use a case statement:

SELECT a.address,

CASE

WHEN p.Product_type = 'Product A' -- whatever first product type is

THEN ..... -- your code, I think from your example you want a sum() here

WHEN p.Product_type = 'Product A' --

THEN

etc

END

from.......

Hope that helps

from address a cross join product_type p

GROUP BY a.address

complex report

have the following data from a query
age sex
39 M
48 F
14 M
etc...
need to be displayed as
Age Group Number of Male Number of Female Total in Age Group
0 -2 2 3
5
3 - 9 24 33
57
....
70 and above 12 11
23
Total Males ##
Total Females ##
Total
##
Any suggestions would be helpful.This question is better geared towards a SQL forum rather then reporting
services.
Search online for the SQL syntax of Case. You'll want to create a select
statment where you do something similar to this.
SELECT '0-2' as Age Group, SUM(CASE age>0 AND age < 2 AND sex = "M", 1, 0
END) as Number of Male, SUM(CASE age>0 AND age < 2 AND sex = "F", 1, 0 END)
as Number of Female, SUM(CASE age > 0 AND age < 2) as Total in Age Group
UNION
SELECT '3-9' as Age Group, SUM(CASE age>3 AND age < 9 AND sex = "M", 1, 0
END) as Number of Male, SUM(CASE age>3 AND age < 9 AND sex = "F", 1, 0 END)
as Number of Female, SUM(CASE age > 3 AND age < 9) as Total in Age Group
And so on and so forth for each of the age/sex ranges.
-Michael Abair
Programmer Analyst
Chicos FAS Inc
"Chop" <Chop@.discussions.microsoft.com> wrote in message
news:0D4FB1BC-D77D-4E6B-A728-B798B6EAF6E4@.microsoft.com...
> have the following data from a query
> age sex
> 39 M
> 48 F
> 14 M
> etc...
> need to be displayed as
> Age Group Number of Male Number of Female Total in Age Group
> 0 -2 2 3
> 5
> 3 - 9 24 33
> 57
> ....
> 70 and above 12 11
> 23
> Total Males ##
> Total Females ##
> Total
> ##
> Any suggestions would be helpful.|||You also have the option of creating fields within RS; for the values
use:
iif(fields!age.value >=0 and fields!age.value < 3, "0 - 2",
iif(fields!age.value < 10, "3-9", ...))
Then use a matrix item and use the calculated field as your row group,
sex as your column group.
Chop wrote:
> have the following data from a query
> age sex
> 39 M
> 48 F
> 14 M
> etc...
> need to be displayed as
> Age Group Number of Male Number of Female Total in Age Group
> 0 -2 2 3
> 5
> 3 - 9 24 33
> 57
> ....
> 70 and above 12 11
> 23
> Total Males ##
> Total Females ##
> Total
> ##
> Any suggestions would be helpful.

Complex Query Help.. Please :)

Hello group,

First time post..

I've designed an online evaluation script (php/mssql) and need help either writing a query to get the posted data out in a way that is useable to those collecting the evaluations..

Here are the tables.

evaluations - holds a record for each evauation that is created: evalID, evalName

fields - holds a record for each potential field that could be added to the evaluation: fieldID, fieldTitle, fieldDescription, and fieldTypeID (types are in a seperate table)

fields_evals - contains the link between the evaluations table and the fields table. this one essentially creates teh evaluation from the eval name and the list of fields. it also contains some switches that specify how the data should be reported (i.e. averaged and/or grouped by) and values that determine if the fields are required and what position they are to be listed in the evaluation form.. (hope that made sense) : evalID, FieldID, position, required, groupby, average

fields_custom - one of the field types is a custom field where the user can create a field with a list of options (i.e. Jan, Feb, Mar, April... or whatever they like) : optionID, optionText, fieldID

submitted - holds data for each individual submitted eval..: subID, timestamp, and evalID

eval_data - this is the biggie.. this is the table that all the responses are written to. it has these fields... data_ID, subID, fieldID, data

Here's the problem..
I want the report page to be able to summraize the data by grouping by the data set in the eval_fields table.. (a row for each value submitted) and on that row have all of the averaged fields (again indicated from the eval_fields table) listed with their averages.. all of the data will come from the eval_data table.. possibly including (and i think this may be the main problem) an id value that relates to the optionID in teh custom_fields table.

an example of a posted evaluation will create:
1 row in the submitted table..
and a row in the eval_data table for each field submitted..as well as

with any luck this explanation makes sense and the problem will be easy to solve..

:rolleyes:

any replys are greatly appreciated..

Thanks
Willok.. here is some clarification..

I started thinking about the first post and decided that much of that information was probably not needed..

Here is the sql i'm workin with so far..

SELECT AVG(CAST(d.chData AS float)) AS average , f.chEvalFieldText
FROM eval_eval_data AS d
INNER JOIN fields_evaluation as fe ON fe.intField_ID = d.intField_ID
INNER JOIN fields as f ON f.intField_ID = d.intField_ID
INNER JOIN fields_custom as c ON f.intField_ID = c.intFieldID
WHERE fe.intAverage = '1'
AND c.intOption_ID = '26'
Group By f.chEvalFieldText

my question boils down to this..
regarding the rows in the data table..

can i group by a value in a liked table (f),
and average values in the data table
while limiting by values in teh data table as well.

anybody?

Am i on the wrong track?

Am i totally lost?

have i lost everybody else??

thanks
ws
-|||You are right on track, good job.
:D|||me again...

I'm fairly confident that there is a query that will get me what i need.. but i'm having a helluva time getting it to work..

here is some example data from the data table..

intSub intField_ID chData
1 1 1
1 2 1
1 3 4
1 4 2
1 26 27
2 1 1
2 2 2
2 3 3
2 4 5
2 26 28

intField_ID is a key that relates to fields in the evaluation
chData is the value that was submitted and intSubmission indicates which submission the data came from.
In this case the field_ids 1-4 ratings 1- 5 that will be averaged
the average will be grouped by the field ID no biggie.. here's where i'm stumped.
I Also have to average and group those values when the fieldID 26 (which is a field that was created within the application) equals a certian value.

make sense?
there is also a table that connects the fields to the evaluation and indicates which fields will be averaged..
for the data above .. i need to

Average the values for the indicated fields grouped by the field id..
but only where the value for fieldID = 26 are equal..

any help is greatly appreciated.
If you need more info.. please let me know..

thanks
will

Compatibility_52_409_30003

Hi
Sorry to those caught in this cross post, but the setup group is a bit
dead...you guys are usually spot on.
I am trying to set up a hot spare server. The production database is
set up with server collation Compatibility_52_409_30003 (and for teh
database too).
I am having a problem with trying to create a hot spare server with
this collation, or even to get a master rebuild to use it. Its not in
the options given.
Anyone have any idea how I can get this working?To let anyone who has this problem know: record an unattended install
file. Modify this to use the collation you need, and then run it.
Check BOL for syntax.