Can you make this code better?

Question

Hello,

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
FROM(
SELECT
CASE
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
,CASE 
WHEN DueToClient >= DateCompleted
THEN 1
ELSE 0
END AS WithinSLA
,CASE 
WHEN DueToClient < DateCompleted
THEN 1
ELSE 0
END AS NOTWithinSLA
FROM(
select
orderid
,duetoclient
,datecompleted
,datepart(ww, DateCompleted) as week
FROM(
SELECT 
OrderID
,CASE
WHEN datename(dw, orderdate) in ('Thursday','Friday')
and productid <> 60 
   or datename(dw, clientduedate) in ('Saturday','sunday')
  THEN dateadd(dd, 2, clientduedate) 
ELSE clientduedate 
END as DueToClient
,CASE
WHEN clientid = 56156
THEN completeddate
ELSE DeliveredDate
END AS DateCompleted

from(
SELECT
oi.OrderID
,cast(oi.orderid as varchar(max))+'.'+cast(oi.orderitemid as varchar(max)) as OrderNumber
,CASE 
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
,o.orderdate
,oi.CompletedDate
,oi.productid
,o.clientid

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

)x)x)x)x

Group By Week1
Order By datepart(ww, week1) desc

Answer 1

p.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.

Answer 2

try this,

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.

---

ANK HIT

Answer 3

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

SELECT
CASE
when week= 1 then '1/2/2012'
when week= 2 then '1/9/2012'
when week= 3 then '1/16/2012'

Use:

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

Thanks,
Sam Lester (MSFT)

---

My Blog

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.

Answer 4

thanks 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.

Answer 5

Your 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.