grouping data to a field

Hey
Im doing a report wich is causing me some trouble:
Im doing a dataset selecting all values regarding the company, and then
im mapping the fields to textboxes in my VS designer. This should
result in a list with all my customers and their data.
Now below each customer i would like to place a table holding data from
another dataset, but i only want the data in the table to correspond to
the company just above the table.
I have placed both the customer data and the table in a list item, but
the problem seems to be the data in the table. I either get evrything
each time for all customers, or i only get the first customer data
below each customer.
Can anyone tell me how i can make this work.
Thanks
JimmyHi
I think there are two ways to solve this
first and the easiest :
get all the data in one dataset and use grouping facility in the reporting
services to group the data
second :
use sub report to build the details and pass the company id from the
parent report to the sub report
Joe
"Jimbo" wrote:
> Hey
> Im doing a report wich is causing me some trouble:
> Im doing a dataset selecting all values regarding the company, and then
> im mapping the fields to textboxes in my VS designer. This should
> result in a list with all my customers and their data.
> Now below each customer i would like to place a table holding data from
> another dataset, but i only want the data in the table to correspond to
> the company just above the table.
> I have placed both the customer data and the table in a list item, but
> the problem seems to be the data in the table. I either get evrything
> each time for all customers, or i only get the first customer data
> below each customer.
> Can anyone tell me how i can make this work.
> Thanks
> Jimmy
>|||Hey Joe
Ok, its not optimal, but it got the job done.
Im actually using a combination of the two.
Your posting lead me on the way though :)
Thanks
Jimmy

Grouping COUNT by two date fields in same table

Hi, I'm trying something which I'm sure should be quite simple but I am having a bit of trouble. Basically I have a call logging table which has a PK of CallID and then a Received Date column (RecvdDate) and Closed Date column (ClosedDate).

I need to return a single set of results showing how many calls were received and closed on a particular date. (N.B all records will have a RecvdDate, but not all will have a ClosedDate (i.e if the job has not been completed)).

Now, I can get the information with two seperate queries no problem:

Query 1

SELECT RecvdDate, COUNT(Callid)

FROM CallLog

GROUP BY RecvdDate

ORDER BY RecvdDate

Query 2

SELECT ClosedDate, COUNT(Callid)

FROM CallLog

WHERE NOT ISNULL(ClosedDate, '') = ''

GROUP BY ClosedDate

ORDER BY ClosedDate

The problem is that I can't work out how to get the two counts to show together, grouped by each date, which I need for displaying on a single chart in SSRS. I'm thinking I might need a variable for the date to group by, but then i get lost

The ideal results set would look like this:

Date Total received Total Closed 28/02/2007 54 43 01/03/2007 22 21 02/03/2007 122 104 03/03/2007 33 41 04/03/2007 44 33 05/03/2007 76 56 06/03/2007 34 40 07/03/2007 87 80 08/03/2007 56 45 09/03/2007 42 31 10/03/2007 72 66

Can anybody help with this?

Thanks

Matt

try this one

select convert(varchar(10),RecvdDate,101) as [Date]
, count(RecvdDate) as TotalRecvd
, count(ClosedDate) as TotalClosed
from CallLog
group by
convert(varchar(10),RecvdDate,101)|||disregard my prev post.. try this one instead

select [Date]
, SUM(CASE WHEN Rem = 'Recieved' THEN Calls ELSE 0 END) AS TotalReceived
, SUM(CASE WHEN Rem = 'Closed' THEN Calls ELSE 0 END) AS TotalClosed
FROM (
select convert(varchar(10),RecvdDate,101) as [Date]
, count(RecvdDate) as Calls
, 'Recieved' as Rem
from CallLog
group by
convert(varchar(10),RecvdDate,101)
union all
select convert(varchar(10),ClosedDate,101) as [Date]
, count(ClosedDate) as Calls
, 'Closed' as Rem
from #temp
group by
convert(varchar(10),ClosedDate,101)
) CallLogs
GROUP BY
[Date]|||

Hi, thanks for the reply.

The problem with this solution (I had already tried something similar) is that it returns identical values for both Received and Closed calls, which I know is not the case. Here's the results I got:

04/01/2007 32 32 09/01/2007 62 62 10/01/2007 37 37 12/01/2007 45 45 16/01/2007 55 55 25/01/2007 77 77 02/02/2007 69 69 08/02/2007 106 106 11/02/2007 19 19 17/02/2007 13 13

For example, from manually searching table I know that for 17/02/2007 there were 16 Received calls and 13 Closed calls. However the SELECT statement we've tried doesn't include the three calls which were logged on 17/02/2007 but not closed.

I think the reason for this is because it is being GROUPED by RecvdDate, which doesn't make logical sense as it is possible that a call can be received one day and closed x number of days later.

Do you have any more ideas?

Thanks

Matt

|||

Okay thanks again, have seen your second post now and this solution has worked perfectly. I must admit I don't fully understand it, but it works!

Thanks a lot for your help.

Matt

|||

Matt:

Maybe a 1-pass select like this will work:

Code Snippet

declare @.mockup table
( CallID integer,
RecvdDate datetime,
ClosedDate datetime
)
insert into @.mockup
select 1, '1/1/7', null union all
select 2, '1/1/7', '1/1/7' union all
select 3, '1/5/7', '1/11/7' union all
select 4, '1/19/7', '1/11/7'

select case when dateType = 1 then RecvdDate
else ClosedDate
end as Date,
sum (dateType) as RecvdCount,
sum (case when dateType=0 then 1 else 0 end)
as ClosedCount
from @.mockup a
join (select 0 as dateType union all select 1) b
on dateType = 1 or ClosedDate is not null
group by case when dateType = 1 then RecvdDate
else ClosedDate
end

/*
Date RecvdCount ClosedCount
-- -- --
2007-01-01 00:00:00.000 2 1
2007-01-05 00:00:00.000 1 0
2007-01-11 00:00:00.000 0 2
2007-01-19 00:00:00.000 1 0
*/

|||

Try joining both results by the date.

Code Snippet

select

coalesce(a.d, b.d) as new_d,

isnull(a.cnt, 0) as received,

isnull(b.cnt, 0) as closed

from

(

SELECT RecvdDate as d, COUNT(Callid) as cnt

FROM CallLog

GROUP BY RecvdDate

) as a

full join

(

SELECT ClosedDate as d, COUNT(Callid) as cnt

FROM CallLog

WHERE NOT ISNULL(ClosedDate, '') = ''

GROUP BY ClosedDate

) as b

on a.d = b.d

order by new_d;

AMB

|||Thanks to both hunchback and Kent: both of these solutions also work

group by function not returning expected

Hi,
I am having trouble get the numbers that I need. I have a table that records
positions and the action that happened at that positon and the operator that
caused the action. I need to total the amounts per Operator per Action Code.
I tried to use GROUP BY but it only gave me one operator for each LotID. Thi
s
was the statement I used:
SELECT DISTINCT LotID, UserName, SUM(EncoderpositionDetectionEnd1) AS [End
1], ActionCode
FROM dbo.OperatorData
GROUP BY UserName, ActionCode, LotID
ORDER BY LotID
The following is an example of the data that is stored in the table.
LotID Operator Position Action Code
826O3 Priscilla 923797449 11
826O3 Priscilla 926950347 10
826O3 Priscilla 923797449 11
826O3 Priscilla 926950347 10
A1073 Susan 2946519574 11
826O3 Priscilla 960248867 11
82603 Gloria 885226642 10
82603 Gloria 893352901 11
82603 Angela 924485652 11
82603 Gloria 896646927 10
826O3 Priscilla 960248867 11
A1073 Susan 2946519574 11
82603 Angela 927628980 10
82603 Gloria 885226642 10
82603 Gloria 893352901 11
82603 Gloria 896646927 10
A1073 Carolyn 2915880805 10
82603 Angela 924485652 11Can you clarify this? The columns in your data do not match the columns in
your query. Also, do you need to sum (add stuff up) our count the rows? Also
,
you say you need the amounts per operator per action code but your group by
includes lot id.
"A.B." wrote:

> Hi,
> I am having trouble get the numbers that I need. I have a table that recor
ds
> positions and the action that happened at that positon and the operator th
at
> caused the action. I need to total the amounts per Operator per Action Cod
e.
> I tried to use GROUP BY but it only gave me one operator for each LotID. T
his
> was the statement I used:
> SELECT DISTINCT LotID, UserName, SUM(EncoderpositionDetectionEnd1) AS [End
> 1], ActionCode
> FROM dbo.OperatorData
> GROUP BY UserName, ActionCode, LotID
> ORDER BY LotID
> The following is an example of the data that is stored in the table.
> LotID Operator Position Action Co
de
> 826O3 Priscilla 923797449 11
> 826O3 Priscilla 926950347 10
> 826O3 Priscilla 923797449 11
> 826O3 Priscilla 926950347 10
> A1073 Susan 2946519574 11
> 826O3 Priscilla 960248867 11
> 82603 Gloria 885226642 10
> 82603 Gloria 893352901 11
> 82603 Angela 924485652 11
> 82603 Gloria 896646927 10
> 826O3 Priscilla 960248867 11
> A1073 Susan 2946519574 11
> 82603 Angela 927628980 10
> 82603 Gloria 885226642 10
> 82603 Gloria 893352901 11
> 82603 Gloria 896646927 10
> A1073 Carolyn 2915880805 10
> 82603 Angela 924485652 11
>|||SELECT DISTINCT LotID, UserName 'Operator',
SUM(EncoderpositionDetectionEnd1) AS [End
The LotID in order to connect the results to another query I have that gives
me the Lots that were run last w. Also I need the Sum of the rows.
"Kathi Kellenberger" wrote:
> Can you clarify this? The columns in your data do not match the columns in
> your query. Also, do you need to sum (add stuff up) our count the rows? Al
so,
> you say you need the amounts per operator per action code but your group b
y
> includes lot id.
>
>
> "A.B." wrote:
>|||With your query, you should get a row for every possible combination of
lotID, operator and action code. I'm not sure if that's what you are after.
"A.B." wrote:
> SELECT DISTINCT LotID, UserName 'Operator',
> SUM(EncoderpositionDetectionEnd1) AS [End
> The LotID in order to connect the results to another query I have that giv
es
> me the Lots that were run last w. Also I need the Sum of the rows.
> "Kathi Kellenberger" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. And your narrative is useless.|||That is what i want but this is an example of the results that I am getting:
62324 Pamela 9969832955 10
62324 Pamela 19966076115 11
62332 Susan 9641299760 11
62332 Teresa 9633011910 11
62334 Carolyn 9978382505 10
62334 Carolyn 19983575455 11
62334 Melissa 9719717360 10
I am only getting a one result for certain lots and several for others.
"Kathi Kellenberger" wrote:
> With your query, you should get a row for every possible combination of
> lotID, operator and action code. I'm not sure if that's what you are afte
r.
>
>
> "A.B." wrote:
>|||A.B.,
Kathy says you'll get one row for each combination of lotID, operator,
and action code,
and you say "That is what i want". This is exactly what you are
getting. The combinations
in your results are
(62324, Pamela, 10)
(62324, Pamela, 11)
(62332, Susan, 11)
(62332, Teresa, 11)
(62334, Carolyn, 10)
(62334, Carolyn, 11)
(62334, Melissa, 10)
You have more than one name and/or action code for some lotID values,
so you will get more than one row for those values. For example, for lotID
62334, you have information for Melissa with action code 10, and you have
information for Carolyn with action codes both 10 and 11. If you want only
one row for this lotID, do you want it to say Melissa or Carolyn, and do you
want the action code to be 10 or 11? You need to be more specific about
what your result is supposed to be.
Steve Kass
Drew University
A.B. wrote:
>That is what i want but this is an example of the results that I am getting
:
> 62324 Pamela 9969832955 10
> 62324 Pamela 19966076115 11
> 62332 Susan 9641299760 11
> 62332 Teresa 9633011910 11
> 62334 Carolyn 9978382505 10
> 62334 Carolyn 19983575455 11
> 62334 Melissa 9719717360 10
>I am only getting a one result for certain lots and several for others.
>"Kathi Kellenberger" wrote:
>
>|||No, because I am only getting the operator Pamela for Lot 62324 when actuall
y
there is four or five operators.
"Steve Kass" wrote:

> A.B.,
> Kathy says you'll get one row for each combination of lotID, operator,
> and action code,
> and you say "That is what i want". This is exactly what you are
> getting. The combinations
> in your results are
> (62324, Pamela, 10)
> (62324, Pamela, 11)
> (62332, Susan, 11)
> (62332, Teresa, 11)
> (62334, Carolyn, 10)
> (62334, Carolyn, 11)
> (62334, Melissa, 10)
> You have more than one name and/or action code for some lotID values,
> so you will get more than one row for those values. For example, for lotI
D
> 62334, you have information for Melissa with action code 10, and you have
> information for Carolyn with action codes both 10 and 11. If you want onl
y
> one row for this lotID, do you want it to say Melissa or Carolyn, and do y
ou
> want the action code to be 10 or 11? You need to be more specific about
> what your result is supposed to be.
> Steve Kass
> Drew University
>
> A.B. wrote:
>
>|||Ah. When you said "only one" for some and "several" for others, I
thought the problem was the "several", not the "one". ;)
My guess is that you are not showing us the entire query, since if there
is a row with LotID 62324 and operator <> 'Pamela' in dbo.OperatorData,
the result of
SELECT DISTINCT
LotID,
UserName 'Operator',
SUM(EncoderpositionDetectionEnd1) AS [End 1],
ActionCode
FROM dbo.OperatorData
GROUP BY UserName, ActionCode, LotID
ORDER BY LotID
will definitely include a row showing 62324 with another operator.
Perhaps you
are noting the omission only after this query is used in a larger one, maybe
with an inner join that should be a left join - I can't be sure.
If you are certain that this is your query and that results are missing,
please show us both the results of this query and the result of
SELECT TOP 10
LotID,
UserName, 'Operator',
EncoderpositionDetectionEnd1,
ActionCode
FROM dbo.OperatorData
WHERE LotID = '62324'
AND UserName <> 'Pamela'
-- optionally add ORDER BY something...
SK
A.B. wrote:
>No, because I am only getting the operator Pamela for Lot 62324 when actual
ly
>there is four or five operators.
>"Steve Kass" wrote:
>
>|||I had a date in the where clause to make my results alot smaller and by
taking the date out of the where clause it allowed me to see all of the
operators. I am not sure why this happened but it is working now. Thanks for
your help man.
"Steve Kass" wrote:

> Ah. When you said "only one" for some and "several" for others, I
> thought the problem was the "several", not the "one". ;)
> My guess is that you are not showing us the entire query, since if there
> is a row with LotID 62324 and operator <> 'Pamela' in dbo.OperatorData,
> the result of
> SELECT DISTINCT
> LotID,
> UserName 'Operator',
> SUM(EncoderpositionDetectionEnd1) AS [End 1],
> ActionCode
> FROM dbo.OperatorData
> GROUP BY UserName, ActionCode, LotID
> ORDER BY LotID
> will definitely include a row showing 62324 with another operator.
> Perhaps you
> are noting the omission only after this query is used in a larger one, may
be
> with an inner join that should be a left join - I can't be sure.
> If you are certain that this is your query and that results are missing,
> please show us both the results of this query and the result of
> SELECT TOP 10
> LotID,
> UserName, 'Operator',
> EncoderpositionDetectionEnd1,
> ActionCode
> FROM dbo.OperatorData
> WHERE LotID = '62324'
> AND UserName <> 'Pamela'
> -- optionally add ORDER BY something...
> SK
> A.B. wrote:
>
>