November 13, 2014 at 2:09 am
Hi clever people!
Is there someone that can make this code looks nicer and clearer? It just selects the start of the next month going forward from the current month.
Your help will be greatly appreciated.
Kind regards
Fred
CASE WHEN PlannedRequirement.MIN_keydate IS NULL THEN
CASE WHEN PlannedRequirement.Period1_Total IS NULL THEN
(SELECT MIN(KeyDate) FROM BI1_DW_Dim_CalendarDefinition cd WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT))
WHEN plannedrequirement.Period2_Total IS NULL THEN
(SELECT MIN(KeyDate) FROM BI1_DW_Dim_CalendarDefinition cd WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()+30, 112) AS INT))
WHEN PlannedRequirement.Period3_Total IS NULL THEN
(SELECT MIN(KeyDate) FROM BI1_DW_Dim_CalendarDefinition cd WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()+60, 112) AS INT))
WHEN PlannedRequirement.Period4_Total IS NULL THEN
(SELECT MIN(KeyDate) FROM BI1_DW_Dim_CalendarDefinition cd WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()+90, 112) AS INT))
WHEN PlannedRequirement.Period5_Total IS NULL THEN
(SELECT MIN(KeyDate) FROM BI1_DW_Dim_CalendarDefinition cd WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()+120, 112) AS INT))
WHEN PlannedRequirement.Period6_Total IS NULL THEN
(SELECT MIN(KeyDate) FROM BI1_DW_Dim_CalendarDefinition cd WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()+150, 112) AS INT))
WHEN PlannedRequirement.Period7_Total IS NULL THEN
(SELECT MIN(KeyDate) FROM BI1_DW_Dim_CalendarDefinition cd WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()+180, 112) AS INT))
WHEN PlannedRequirement.Period8_Total IS NULL THEN
(SELECT MIN(KeyDate) FROM BI1_DW_Dim_CalendarDefinition cd WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()+210, 112) AS INT))
WHEN PlannedRequirement.Period9_Total IS NULL THEN
(SELECT MIN(KeyDate) FROM BI1_DW_Dim_CalendarDefinition cd WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()+240, 112) AS INT))
WHEN PlannedRequirement.Period10_Total IS NULL THEN
(SELECT MIN(KeyDate) FROM BI1_DW_Dim_CalendarDefinition cd WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()+270, 112) AS INT))
WHEN PlannedRequirement.Period11_Total IS NULL THEN
(SELECT MIN(KeyDate) FROM BI1_DW_Dim_CalendarDefinition cd WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()+300, 112) AS INT))
WHEN PlannedRequirement.Period12_Total IS NULL THEN
(SELECT MIN(KeyDate) FROM BI1_DW_Dim_CalendarDefinition cd WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()+330, 112) AS INT))
WHEN PlannedRequirement.Period13_Total IS NULL THEN
(SELECT MIN(KeyDate) FROM BI1_DW_Dim_CalendarDefinition cd WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()+360, 112) AS INT))
WHEN PlannedRequirement.Period14_Total IS NULL THEN
(SELECT MIN(KeyDate) FROM BI1_DW_Dim_CalendarDefinition cd WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()+390, 112) AS INT))
WHEN PlannedRequirement.Period15_Total IS NULL THEN
(SELECT MIN(KeyDate) FROM BI1_DW_Dim_CalendarDefinition cd WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()+420, 112) AS INT))
END
ELSE PlannedRequirement.MIN_keydate END AS Keydate
November 13, 2014 at 7:04 am
Not sure that you would just move forwards a month with that code (unless I am miss-understanding something). Does the table BI1_DW_Dim_CalendarDefinition contain 356 / 366 days for each year? If it does, adding 30 days to 31 Jan 2014 gives you 2 Mar 2014, and 30 days on from 1 Oct is still Oct.
Can you supply a bit more info on the tables please and if possible some sample data - the first link in my signature shows a very good way of doing this.
Here is the formatted version fyi - I don't often plug products, but Red Gates' SQL prompt is an absolute boon (in my opinion) when writing / formatting code and did the formatting on this
CASE WHEN PlannedRequirement.MIN_keydate IS NULL
THEN CASE WHEN PlannedRequirement.Period1_Total IS NULL
THEN ( SELECT MIN(KeyDate)
FROM BI1_DW_Dim_CalendarDefinition cd
WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)
)
WHEN plannedrequirement.Period2_Total IS NULL
THEN ( SELECT MIN(KeyDate)
FROM BI1_DW_Dim_CalendarDefinition cd
WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()
+ 30, 112) AS INT)
)
WHEN PlannedRequirement.Period3_Total IS NULL
THEN ( SELECT MIN(KeyDate)
FROM BI1_DW_Dim_CalendarDefinition cd
WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()
+ 60, 112) AS INT)
)
WHEN PlannedRequirement.Period4_Total IS NULL
THEN ( SELECT MIN(KeyDate)
FROM BI1_DW_Dim_CalendarDefinition cd
WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()
+ 90, 112) AS INT)
)
WHEN PlannedRequirement.Period5_Total IS NULL
THEN ( SELECT MIN(KeyDate)
FROM BI1_DW_Dim_CalendarDefinition cd
WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()
+ 120, 112) AS INT)
)
WHEN PlannedRequirement.Period6_Total IS NULL
THEN ( SELECT MIN(KeyDate)
FROM BI1_DW_Dim_CalendarDefinition cd
WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()
+ 150, 112) AS INT)
)
WHEN PlannedRequirement.Period7_Total IS NULL
THEN ( SELECT MIN(KeyDate)
FROM BI1_DW_Dim_CalendarDefinition cd
WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()
+ 180, 112) AS INT)
)
WHEN PlannedRequirement.Period8_Total IS NULL
THEN ( SELECT MIN(KeyDate)
FROM BI1_DW_Dim_CalendarDefinition cd
WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()
+ 210, 112) AS INT)
)
WHEN PlannedRequirement.Period9_Total IS NULL
THEN ( SELECT MIN(KeyDate)
FROM BI1_DW_Dim_CalendarDefinition cd
WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()
+ 240, 112) AS INT)
)
WHEN PlannedRequirement.Period10_Total IS NULL
THEN ( SELECT MIN(KeyDate)
FROM BI1_DW_Dim_CalendarDefinition cd
WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()
+ 270, 112) AS INT)
)
WHEN PlannedRequirement.Period11_Total IS NULL
THEN ( SELECT MIN(KeyDate)
FROM BI1_DW_Dim_CalendarDefinition cd
WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()
+ 300, 112) AS INT)
)
WHEN PlannedRequirement.Period12_Total IS NULL
THEN ( SELECT MIN(KeyDate)
FROM BI1_DW_Dim_CalendarDefinition cd
WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()
+ 330, 112) AS INT)
)
WHEN PlannedRequirement.Period13_Total IS NULL
THEN ( SELECT MIN(KeyDate)
FROM BI1_DW_Dim_CalendarDefinition cd
WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()
+ 360, 112) AS INT)
)
WHEN PlannedRequirement.Period14_Total IS NULL
THEN ( SELECT MIN(KeyDate)
FROM BI1_DW_Dim_CalendarDefinition cd
WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()
+ 390, 112) AS INT)
)
WHEN PlannedRequirement.Period15_Total IS NULL
THEN ( SELECT MIN(KeyDate)
FROM BI1_DW_Dim_CalendarDefinition cd
WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()
+ 420, 112) AS INT)
)
END
ELSE PlannedRequirement.MIN_keydate
END AS Keydate
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
November 13, 2014 at 7:32 am
Hi Stuart
Thanks for replying. Here is some sample data from the calendardefinition table:
KeyDateTransDateSmallTransDateNumericTransPeriodTransWeekOfYearNumberTransWeekOfYearDescrTransWeekOfMonthNumberTransWeekOfMonthDescrTransMonthDescrTransMonthNumberTransYearNumber
1NULLNULLNULLNULLNULL0NULLNULLNULL
201/01/1980 00:00198001011980.0111980 Week 0111980.01 Week 1January11980
302/01/1980 00:00198001021980.0111980 Week 0111980.01 Week 1January11980
403/01/1980 00:00198001031980.0111980 Week 0111980.01 Week 1January11980
504/01/1980 00:00198001041980.0111980 Week 0111980.01 Week 1January11980
605/01/1980 00:00198001051980.0111980 Week 0111980.01 Week 1January11980
706/01/1980 00:00198001061980.0111980 Week 0111980.01 Week 1January11980
807/01/1980 00:00198001071980.0121980 Week 0221980.01 Week 2January11980
908/01/1980 00:00198001081980.0121980 Week 0221980.01 Week 2January11980
1009/01/1980 00:00198001091980.0121980 Week 0221980.01 Week 2January11980
1110/01/1980 00:00198001101980.0121980 Week 0221980.01 Week 2January11980
1211/01/1980 00:00198001111980.0121980 Week 0221980.01 Week 2January11980
1312/01/1980 00:00198001121980.0121980 Week 0221980.01 Week 2January11980
1413/01/1980 00:00198001131980.0121980 Week 0221980.01 Week 2January11980
1514/01/1980 00:00198001141980.0131980 Week 0331980.01 Week 3January11980
1615/01/1980 00:00198001151980.0131980 Week 0331980.01 Week 3January11980
1716/01/1980 00:00198001161980.0131980 Week 0331980.01 Week 3January11980
November 13, 2014 at 7:38 am
Thanks for the data. Please can you submit it as detailed in the first link in my signature or here
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Thanks
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
November 13, 2014 at 7:49 am
How about:
CASE WHEN PlannedRequirement.MIN_keydate IS NULL
THEN SELECT MIN(KeyDate)
FROM BI1_DW_Dim_CalendarDefinition cd
WHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE() +
CASE
WHEN plannedrequirement.Period1_Total IS NULL THEN 0
WHEN plannedrequirement.Period2_Total IS NULL THEN 30
WHEN plannedrequirement.Period3_Total IS NULL THEN 60
WHEN plannedrequirement.Period4_Total IS NULL THEN 90
WHEN plannedrequirement.Period5_Total IS NULL THEN 120
WHEN plannedrequirement.Period6_Total IS NULL THEN 150
WHEN plannedrequirement.Period7_Total IS NULL THEN 180
WHEN plannedrequirement.Period8_Total IS NULL THEN 210
WHEN plannedrequirement.Period9_Total IS NULL THEN 240
WHEN plannedrequirement.Period10_Total IS NULL THEN 270
WHEN plannedrequirement.Period11_Total IS NULL THEN 300
WHEN plannedrequirement.Period12_Total IS NULL THEN 330
WHEN plannedrequirement.Period13_Total IS NULL THEN 360
WHEN plannedrequirement.Period14_Total IS NULL THEN 390
WHEN plannedrequirement.Period15_Total IS NULL THEN 420
, 112) AS INT)
ELSE PlannedRequirement.MIN_keydate
END AS Keydate
Gerald Britton, Pluralsight courses
November 13, 2014 at 7:52 am
That should work - providing the straight + 30 doesn't give a problem with the next month calculation. Re-reading all of this I'm now not sure if Fred just wanted the code formatting as he says it works fine.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
November 13, 2014 at 11:20 pm
Thanks Stuart. You took the ugliness out of the code. 😀
November 14, 2014 at 2:59 am
Try something like
declare @Period1_Total float = null;
declare @Period2_Total float = 85675786781.9;
declare @Period3_Total float = 0;
select n = coalesce(
0 + cast(0*@Period1_Total as int)
,1 + cast(0*@Period2_Total as int)
,2 + cast(0*@Period3_Total as int)) * 30;
as an argument for a single
SELECT MIN(KeyDate) FROM BI1_DW_Dim_CalendarDefinition cdWHERE cd.transdatenumeric = CAST(CONVERT(VARCHAR(8), GETDATE()+n, 112) AS INT))
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply