June 9, 2009 at 10:32 am
I have a SQL report with a table in it that is grouped by a specific field. For each grouping I need to count the total number of fields that have a field ="IN" and then also a separate counter for the same field where the field="OUT" I am pretty sure I need to use an IIF statement with a count statement inside. Can anyone help guide me in the correct direction. Any help would be greatly appreciated!!!
June 9, 2009 at 10:53 am
Maybe you need something like a CASE .. WHEN expression, bu I suggest you take a look at this article:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
maybe we can help you better with some sample code.
Regards
Gianluca
-- Gianluca Sartori
June 9, 2009 at 11:00 am
SELECT A.ItemCode AS [Container ID], Cust.company AS Account, Dept.departmentname AS Department, A.ref1 AS [Alternate ID], A.fromdate AS [From Date],
A.todate AS [To Date], A.ref2 AS [Sequence Begin], A.ref3 AS [Sequence End],
CASE WHEN A.destroyed = 0 AND
A.Retrieved = 0 THEN 'IN' WHEN A.destroyed = 0 AND
A.Retrieved = 1 THEN 'OUT' WHEN A.destroyed = 1 THEN 'PERMOUT' WHEN A.destroyed = 2 THEN 'DESTROY' WHEN A.destroyed = 3 THEN 'RETRIEVED'
END AS Status,
A.effectdate AS [Add Date], A.expiredate1 AS [Destroy Date], A.retrieveddate AS [Retrieved Date], A.location AS Location,
A.ref10 AS Description
FROM trItem AS A LEFT OUTER JOIN
trCustType AS CType WITH (NOLOCK) ON A.trTypeID = CType.trcusttypeid LEFT OUTER JOIN
trCustomer AS Cust WITH (NOLOCK) ON A.trCustomerID = Cust.trcustomerid LEFT OUTER JOIN
trDepartment AS Dept WITH (NOLOCK) ON A.trDepartmentID = Dept.trdepartmentid
WHERE (Cust.company IN (@Company)) AND (A.Destroyed IN (@Status)) AND (CType.category IN (@Type))
GROUP BY Cust.company, Dept.departmentname, A.ref1, A.fromdate, A.todate, A.ref2, A.ref3, A.expiredate1, A.retrieveddate, A.location, A.ref10, A.ItemCode,
A.Destroyed, A.retrieved, A.effectdate
June 9, 2009 at 11:00 am
I need to get the count from the status field. The count has to be by department, and also a total count
June 9, 2009 at 11:08 am
add these columns to your SQL:
SUM(CASE WHEN A.destroyed = 0 AND A.Retrieved = 0 THEN 1 ELSE 0 END) AS INCOUNT,
SUM(CASE WHEN A.destroyed = 0 AND A.Retrieved = 1 THEN 1 ELSE 0 END) AS OUTCOUNT,
SUM(CASE WHEN A.destroyed = 1 THEN 1 ELSE 0 END) AS PERMOUTCOUNT,
SUM(CASE WHEN A.destroyed = 2 THEN 1 ELSE 0 END) AS DESTROYCOUNT,
SUM(CASE WHEN A.destroyed = 3 THEN 1 ELSE 0 END) AS RETRIEVEDCOUNT,
note your existing case statemnt has a logic hole:
it will NEVER find the second condition "OUT", because the first case will catch all A.Destroyed=0
you will want to change it so the two case statements that are checking CASE WHEN A.destroyed = 0 AND A.Retrieved = 1 ' THEN 'OUT' ,
SUM(CASE WHEN A.destroyed = 0 AND A.Retrieved = 0 THEN 1 ELSE 0 END) AS INCOUNT,
SUM(CASE WHEN A.destroyed = 1 THEN 1 ELSE 0 END) AS PERMOUTCOUNT,
SUM(CASE WHEN A.destroyed = 2 THEN 1 ELSE 0 END) AS DESTROYCOUNT,
SUM(CASE WHEN A.destroyed = 3 THEN 1 ELSE 0 END) AS RETRIEVEDCOUNT, also explicitylu test A.Retrieved as well.
Lowell
June 9, 2009 at 11:09 am
This should do the trick:
SELECT Department, Status, COUNT(*)
FROM (
SELECT A.ItemCode AS [Container ID],
Cust.company AS Account,
Dept.departmentname AS Department,
A.ref1 AS [Alternate ID],
A.fromdate AS [From Date],
A.todate AS [To Date],
A.ref2 AS [Sequence Begin],
A.ref3 AS [Sequence End],
CASE WHEN A.destroyed = 0
AND A.Retrieved = 0 THEN 'IN'
WHEN A.destroyed = 0
AND A.Retrieved = 1 THEN 'OUT'
WHEN A.destroyed = 1 THEN 'PERMOUT'
WHEN A.destroyed = 2 THEN 'DESTROY'
WHEN A.destroyed = 3 THEN 'RETRIEVED'
END AS Status,
A.effectdate AS [Add Date],
A.expiredate1 AS [Destroy Date],
A.retrieveddate AS [Retrieved Date],
A.location AS Location,
A.ref10 AS Description
FROM trItem AS A
LEFT OUTER JOIN trCustType AS CType WITH ( NOLOCK )
ON A.trTypeID = CType.trcusttypeid
LEFT OUTER JOIN trCustomer AS Cust WITH ( NOLOCK )
ON A.trCustomerID = Cust.trcustomerid
LEFT OUTER JOIN trDepartment AS Dept WITH ( NOLOCK )
ON A.trDepartmentID = Dept.trdepartmentid
WHERE ( Cust.company IN ( @Company ) )
AND ( A.Destroyed IN ( @status ) )
AND ( CType.category IN ( @Type ) )
GROUP BY Cust.company,
Dept.departmentname,
A.ref1,
A.fromdate,
A.todate,
A.ref2,
A.ref3,
A.expiredate1,
A.retrieveddate,
A.location,
A.ref10,
A.ItemCode,
A.Destroyed,
A.retrieved,
A.effectdate
) AS qry
GROUP BY Department, Status
WITH ROLLUP
-- Gianluca Sartori
June 9, 2009 at 11:12 am
sorry bumble-read your post...my dyslexia kicked in and i thought i only saw CASE WHEN A.destroyed = 0 as the first case condition.
Lowell
June 9, 2009 at 11:13 am
I am not sure if that will really work. I still need to display all the fields that I was originally.
June 9, 2009 at 11:18 am
jacobostop (6/9/2009)
I am not sure if that will really work. I still need to display all the fields that I was originally.
This changes things a bit. Try this way:
SELECT [Container ID],
Account,
Department,
[Alternate ID],
[From Date],
[To Date],
[Sequence Begin],
[Sequence End],
Status,
[Add Date],
[Destroy Date],
[Retrieved Date],
Location,
Description,
COUNT(*)
FROM ( SELECT A.ItemCode AS [Container ID],
Cust.company AS Account,
Dept.departmentname AS Department,
A.ref1 AS [Alternate ID],
A.fromdate AS [From Date],
A.todate AS [To Date],
A.ref2 AS [Sequence Begin],
A.ref3 AS [Sequence End],
CASE WHEN A.destroyed = 0
AND A.Retrieved = 0 THEN 'IN'
WHEN A.destroyed = 0
AND A.Retrieved = 1 THEN 'OUT'
WHEN A.destroyed = 1 THEN 'PERMOUT'
WHEN A.destroyed = 2 THEN 'DESTROY'
WHEN A.destroyed = 3 THEN 'RETRIEVED'
END AS Status,
A.effectdate AS [Add Date],
A.expiredate1 AS [Destroy Date],
A.retrieveddate AS [Retrieved Date],
A.location AS Location,
A.ref10 AS Description
FROM trItem AS A
LEFT OUTER JOIN trCustType AS CType WITH ( NOLOCK )
ON A.trTypeID = CType.trcusttypeid
LEFT OUTER JOIN trCustomer AS Cust WITH ( NOLOCK )
ON A.trCustomerID = Cust.trcustomerid
LEFT OUTER JOIN trDepartment AS Dept WITH ( NOLOCK )
ON A.trDepartmentID = Dept.trdepartmentid
WHERE ( Cust.company IN ( @Company ) )
AND ( A.Destroyed IN ( @status ) )
AND ( CType.category IN ( @Type ) )
) AS qry
GROUP BY [Container ID],
Account,
Department,
[Alternate ID],
[From Date],
[To Date],
[Sequence Begin],
[Sequence End],
Status,
[Add Date],
[Destroy Date],
[Retrieved Date],
Location,
Description
WITH ROLLUP
-- Gianluca Sartori
June 9, 2009 at 11:19 am
Lowell When I run the code you give me those values end up only being 1 and 0
Do i need to do something within my report to get the actual count to show up
June 9, 2009 at 11:25 am
Gianluca,
I dont see how this is going to provide me with a count for each different status type within each group...
June 9, 2009 at 12:12 pm
this is just a WAG, but every A.ItemCode would only have one status, right?
don't you need a rollup by Customer of all the status?
something like the query below, that you could then add as another LEFT OUTER JOIN so you can grab the totals toy your existing SQL?
SELECT
Cust.company AS Account,
Dept.departmentname
SUM(CASE WHEN A.destroyed = 0 AND A.Retrieved = 0 THEN 1 ELSE 0 END) AS INCOUNT,
SUM(CASE WHEN A.destroyed = 0 AND A.Retrieved = 1 THEN 1 ELSE 0 END) AS OUTCOUNT,
SUM(CASE WHEN A.destroyed = 1 THEN 1 ELSE 0 END) AS PERMOUTCOUNT,
SUM(CASE WHEN A.destroyed = 2 THEN 1 ELSE 0 END) AS DESTROYCOUNT,
SUM(CASE WHEN A.destroyed = 3 THEN 1 ELSE 0 END) AS RETRIEVEDCOUNT
FROM trItem AS A
LEFT OUTER JOIN trCustType AS CType WITH (NOLOCK)
ON A.trTypeID = CType.trcusttypeid
LEFT OUTER JOIN trCustomer AS Cust WITH (NOLOCK)
ON A.trCustomerID = Cust.trcustomerid
LEFT OUTER JOIN trDepartment AS Dept WITH (NOLOCK)
ON A.trDepartmentID = Dept.trdepartmentid
WHERE (Cust.company IN (@Company))
AND (A.Destroyed IN (@Status))
AND (CType.category IN (@Type))
GROUP BY Cust.company,
Dept.departmentname
Lowell
June 9, 2009 at 12:19 pm
I actually think I may have gotten it. I am checking against some data now.. I will let you know either way... Thanks for all the help!
June 10, 2009 at 1:10 am
jacobostop (6/9/2009)
Gianluca,I dont see how this is going to provide me with a count for each different status type within each group...
Looks like I did not understand what you need.
If you think you got it, post the solution here, so that I can understand (and maybe pretend I'm not totally clueless :-D)
-- Gianluca Sartori
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply