counting case statement

Question

Hello,

Im trying to get this query to return in a specific way and am not sure if i need to start over or if i can expand on this query. Im in sql management studio 2008, sql server 2005.  This case statement is working for labeling orders in the results correctly but im having trouble counting it properly.

I want it to display like this:

Client	Due Today	Due Tomorrow	Past Due …	Total
client	count of status per client	count of status per client	count of status per client	count of row
client
client

SELECT

COUNT(DeliveryStatus) FROM (SELECT

CASE

WHEN dbo.TruncateDate(CURRENT_TIMESTAMP) = dbo.TruncateDate(oi.RequiredByDate) THEN 'Due Today'

WHEN CURRENT_TIMESTAMP > oi.RequiredByDate THEN 'Past Due'

WHEN DATEADD(dd, DATEDIFF(dd, 0, oi.RequiredByDate), 0) = dateadd(day, datediff(day, '19000101',CURRENT_TIMESTAMP),'19000102') then 'Due Tomorrow'

WHEN DateDiff(day, getdate(), RequiredByDate) BETWEEN 2 and 7 AND DateName(weekday, RequiredByDate) = 'Monday' Then 'Due Monday'

ELSE 'Due Beyond' end

as DeliveryStatus

FROM

OrderItems oi

JOIN

Orders o ON o.OrderID = oi.OrderID

JOIN

Counties c ON c.FIPS = o.FIPS

JOIN

Clients cl ON cl.ClientID = o.ClientID

JOIN

Milestones m ON m.MilestoneID = oi.LastMilestoneID

JOIN

Products p ON p.ProductID = oi.ProductID

JOIN

Vendors v ON v.VendorID = oi.VendorID

LEFT

JOIN ClientBranches clb ON clb.ClientID = o.ClientID

WHERE

QueueID > 0

and cl.Name not like ('Tes%')) AS Total

GROUP BY DeliveryStatus

Answer 1

did some research and figured this out, thanks for anyone that is working on it now

Answer 2

Try:

;with cte as (SELECT cl.ClientID, cl.Name as Client, o.OrderID,

CASE WHEN CURRENT_TIMESTAMP > oi.RequiredByDate THEN 'Past Due'
WHEN DATEADD(dd, DATEDIFF(dd, 0, oi.RequiredByDate), 0) = dateadd(day, datediff(day, '19000101',CURRENT_TIMESTAMP),'19000102') then 'Due Tomorrow'

WHEN DateDiff(day, getdate(), RequiredByDate) BETWEEN 2 and 7 AND DateName(weekday, RequiredByDate) = 'Monday' Then 'Due Monday'

WHEN dbo.TruncateDate(CURRENT_TIMESTAMP) = dbo.TruncateDate(oi.RequiredByDate) THEN 'Due Today'

ELSE 'Due Beyond' end

as DeliveryStatus

FROM

OrderItems oi

JOIN Orders o ON o.OrderID = oi.OrderID

JOIN Counties c ON c.FIPS = o.FIPS

JOIN Clients cl ON cl.ClientID = o.ClientID

JOIN Milestones m ON m.MilestoneID = oi.LastMilestoneID

JOIN Products p ON p.ProductID = oi.ProductID

JOIN Vendors v ON v.VendorID = oi.VendorID

LEFT JOIN ClientBranches clb ON clb.ClientID = o.ClientID

WHERE QueueID > 0 -- which table?

and cl.Name not like 'Tes%')

select *, coalesce([Due Today],0) + coalesce([Past Due],0) + coalesce([Due Tomorrow],0) + 
coalesce([Due Monday],0) + coalesce([Due Beyond],0) as [Total]
 from cte 
PIVOT (COUNT(o.OrderID) FOR [DeliveryStatus] IN ([Due Today],[Past Due], [Due Tomorrow], [Due Monday], [Due Beyond])) pvt

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

Basically you need:

Select Client,

SUM(CASE WHEN dbo.TruncateDate(CURRENT_TIMESTAMP)= dbo.TruncateDate(oi.RequiredByDate) THEN 1 ELSE 0 END) as DueTodayCount,
SUM(CASE WHEN CURRENT_TIMESTAMP > oi.RequiredByDate THEN 1 ELSE 0 END) as PASTDueCount,
SUM(CASE WHEN DATEADD(dd, DATEDIFF(dd, 0, oi.RequiredByDate), 0) = dateadd(day, datediff(day, '19000101',CURRENT_TIMESTAMP),'19000102') then 1 ELSE 0 END) as DueTomorrowCount,
SUM(CASE WHEN dbo.TruncateDate(CURRENT_TIMESTAMP)= dbo.TruncateDate(oi.RequiredByDate) THEN 1
	 WHEN CURRENT_TIMESTAMP > oi.RequiredByDate THEN 1
	 WHEN DATEADD(dd, DATEDIFF(dd, 0, oi.RequiredByDate), 0) = dateadd(day, datediff(day, '19000101',CURRENT_TIMESTAMP),'19000102') then 1
	ELSE 0 END as Total


GROUP BY Client

---

Chuck