[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

Best practice on "CASE" script

The script i have works but very long and i have run into a "CASE" statement limit so i can not go as far back in history as i would like.  Here is a example of what i am currently using.
SELECT ITEM_NO, 
	CASE 
		WHEN datediff(DD, [BUS_DAT], GETDATE()) <= 7 THEN 'Week_01' 
		WHEN datediff(DD, [BUS_DAT], GETDATE()) <= 14 THEN 'Week_02' 
		WHEN datediff(DD, [BUS_DAT], GETDATE()) <= 21 THEN 'Week_03' 
		WHEN datediff(DD, [BUS_DAT], GETDATE()) <= 28 THEN 'Week_04' 
		WHEN datediff(DD, [BUS_DAT], GETDATE()) <= 35 THEN 'Week_05'
		WHEN datediff(DD, [BUS_DAT], GETDATE()) <= 42 THEN 'Week_06'
		WHEN datediff(DD, [BUS_DAT], GETDATE()) <= 49 THEN 'Week_07' 
                WHEN datediff(DD, [BUS_DAT], GETDATE()) <= 56 THEN 'Week_08' 
                WHEN datediff(DD, [BUS_DAT], GETDATE()) <= 63 THEN 'Week_09' 
                WHEN datediff(DD, [BUS_DAT], GETDATE()) <= 70 THEN 'Week_10' 
		ELSE NULL 
	END AS SALE_WEEK, QTY_SOLD
FROM dbo.PS_TKT_HIST_LIN

Open in new window


What this is doing is labeling each line according to what week the sale falls in to by counting back 7 day from the current date to create week one and so on and so forth.  I'm looking to a way to not use so many when clauses and i can look back 3 or 4 years if i want.

Thank you for your help and let me know if you have any further questions.
0
Littlet5621
Asked:
Littlet5621
1 Solution
 
Brian CroweCommented:
SELECT ITEM_NO,
   'Week_' + RIGHT('0' + CAST(DATEDIFF(DAY, BUS_DAT, GETDATE()) / 7 + 1 AS VARCHAR), 2),
   ...
0
 
David ToddSenior DBACommented:
Hi,

select dateadd( week, (datediff( week, 0, getdate()), 0 ) should give the date at the start of the week.

You may need to change the 0 to -1 or something as the base datetime. Depends on what you want as the starting day for the week.

HTH
  David
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now