יום חמישי 09 אוגוסט 2012 15:14
Below is a query i made for work where i had to seperate out a weeks worth of orders and summarize their Service Level (SLA) to our clients. This report runs a bit slow and im trying to learn better ways to write this kind of thing. I wrote this because i was unable to find anything better online so i just made it up in a case statement. I know some of the code wont make sense to everyone but im simply looking for a better way to parse my results into weeks so i dont have to write 60 lines of SQL again. Thanks for taking a look.SELECT
top 10(Week1) as Week1
,left(CAST(sum(withinsla) as float)/isnull((sum(withinsla) + sum(notwithinsla)), 0) * 100, 5) as Percentage
when week = 1 then '1/2/2012'
when week = 2 then '1/9/2012'
when week = 3 then '1/16/2012'
when week = 4 then '1/23/2012'
when week = 5 then '1/30/2012'
when week = 6 then '2/6/2012'
when week = 7 then '2/13/2012'
when week = 8 then '2/20/2012'
when week = 9 then '2/27/2012'
when week = 10 then '3/5/2012'
when week = 11 then '3/12/2012'
when week = 12 then '3/19/2012'
when week = 13 then '3/26/2012'
when week = 14 then '4/2/2012'
when week = 15 then '4/9/2012'
when week = 16 then '4/16/2012'
when week = 17 then '4/23/2012'
when week = 18 then '4/30/2012'
when week = 19 then '5/7/2012'
when week = 20 then '5/14/2012'
when week = 21 then '5/21/2012'
when week = 22 then '6/4/2012'
when week = 23 then '6/11/2012'
when week = 24 then '6/18/2012'
when week = 25 then '6/25/2012'
when week = 27 then '7/2/2012'
when week = 28 then '7/9/2012'
when week = 29 then '7/16/2012'
when week = 30 then '7/23/2012'
when week = 31 then '7/30/2012'
when week = 32 then '8/6/2012'
when week = 33 then '8/13/2012'
when week = 34 then '8/20/2012'
when week = 35 then '8/27/2012'
when week = 36 then '9/3/2012'
when week = 37 then '9/10/2012'
when week = 38 then '9/17/2012'
when week = 39 then '9/24/2012'
when week = 40 then '10/1/2012'
when week = 41 then '10/8/2012'
when week = 42 then '10/15/2012'
when week = 43 then '10/22/2012'
when week = 44 then '10/29/2012'
when week = 45 then '11/5/2012'
when week = 46 then '11/12/2012'
when week = 47 then '11/19/2012'
when week = 48 then '11/26/2012'
when week = 49 then '12/3/2012'
when week = 50 then '12/10/2012'
when week = 51 then '12/17/2012'
when week = 52 then '12/24/2012'
when week = 53 then '12/31/2012'
END AS Week1
WHEN DueToClient >= DateCompleted
END AS WithinSLA
WHEN DueToClient < DateCompleted
END AS NOTWithinSLA
,datepart(ww, DateCompleted) as week
WHEN datename(dw, orderdate) in ('Thursday','Friday')
and productid <> 60
or datename(dw, clientduedate) in ('Saturday','sunday')
THEN dateadd(dd, 2, clientduedate)
END as DueToClient
WHEN clientid = 56156
END AS DateCompleted
,cast(oi.orderid as varchar(max))+'.'+cast(oi.orderitemid as varchar(max)) as OrderNumber
WHEN p.productID in (16,6,9) THEN dateadd(dw, 6 ,o.OrderDate)
WHEN p.productID in (62,63,54,55,56,52,17,47,48,49,50,43,31,38,27,7,10,2,3,4,12,90,91,7,69,70,71,72,92)
THEN dateadd(dw, 5, o.orderdate)
WHEN p.ProductID in (13,14,18,19,20,8,42,46,51,53,61,77,83,84,64,65,76,78,79,80,81,85,87,66,67,68,75,82,86,93)
THEN dateadd(dw, 4, o.orderdate)
WHEN p.productid in (11) THEN dateadd(dw, 3, o.orderdate)
WHEN p.productId in (5,15,21,23,25,26,28,29,41,44,57,58,59,73,74,88) THEN dateadd(dw, 2, o.orderdate)
WHEN p.ProductID in (24,45,60) THEN dateadd(dw, 1, o.orderdate)
end as ClientDueDate
,dbo.GetEarliestMilestoneDate(oi.orderid, oi.orderitemid, 130) as DeliveredDate
FROM OrderItems oi
JOIN Orders o ON o.OrderID = oi.OrderID
JOIN Products p on p.productid = oi.productid
where oi.lastmilestoneid not in (140,150)
and queueid = 0
Group By Week1
Order By datepart(ww, week1) desc
יום חמישי 09 אוגוסט 2012 15:15p.s. my SQL knowledge is limited, but i do think using 4 derived tables is a little obscene but i did this in a time crunch.
יום חמישי 09 אוגוסט 2012 15:47
select dateadd (week, @wk, dateadd (year, @yr-1900, 0)) + 1 -
datepart(dw, dateadd (week, @wk, dateadd (year, @yr-1900, 0)) ),
inside the CTE first so you can get rid of all those case statements.
יום חמישי 09 אוגוסט 2012 15:50מנחה דיון
Can you post the create table statements to make it easier for us to try to help out with the table joins?
One quick idea to cut out the repetitive case statement is to replace it with the DATEADD function.
Ex: Instead of
when week= 1 then '1/2/2012'
when week= 2 then '1/9/2012'
when week= 3 then '1/16/2012'
select dateadd(week, @w, '1/2/2012') as Week1 -- @w is the week count, the 1st param "week" is the date part to add weeks
Sam Lester (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.
יום שישי 17 אוגוסט 2012 17:37thanks im a bit busy at work today but ill see if i can post an example later, this definitly helps though. thanks to both of you for your help.
יום שישי 17 אוגוסט 2012 17:44Your usage of the many IN (id-list) predicates indicate that these products have a certain property. This property should/must be stored somewhere in a table. Then the selection should be easier. When this information is not stored, then create a table to join it, e.g. ProductID, DWOffset.