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:
KeyDate TransDateSmall TransDateNumeric TransPeriod TransWeekOfYearNumber TransWeekOfYearDescr TransWeekOfMonthNumber TransWeekOfMonthDescr TransMonthDescr TransMonthNumber TransYearNumber
1 NULL NULL NULL NULL NULL 0 NULL NULL NULL
2 01/01/1980 00:00 19800101 1980.01 1 1980 Week 01 1 1980.01 Week 1 January 1 1980
3 02/01/1980 00:00 19800102 1980.01 1 1980 Week 01 1 1980.01 Week 1 January 1 1980
4 03/01/1980 00:00 19800103 1980.01 1 1980 Week 01 1 1980.01 Week 1 January 1 1980
5 04/01/1980 00:00 19800104 1980.01 1 1980 Week 01 1 1980.01 Week 1 January 1 1980
6 05/01/1980 00:00 19800105 1980.01 1 1980 Week 01 1 1980.01 Week 1 January 1 1980
7 06/01/1980 00:00 19800106 1980.01 1 1980 Week 01 1 1980.01 Week 1 January 1 1980
8 07/01/1980 00:00 19800107 1980.01 2 1980 Week 02 2 1980.01 Week 2 January 1 1980
9 08/01/1980 00:00 19800108 1980.01 2 1980 Week 02 2 1980.01 Week 2 January 1 1980
10 09/01/1980 00:00 19800109 1980.01 2 1980 Week 02 2 1980.01 Week 2 January 1 1980
11 10/01/1980 00:00 19800110 1980.01 2 1980 Week 02 2 1980.01 Week 2 January 1 1980
12 11/01/1980 00:00 19800111 1980.01 2 1980 Week 02 2 1980.01 Week 2 January 1 1980
13 12/01/1980 00:00 19800112 1980.01 2 1980 Week 02 2 1980.01 Week 2 January 1 1980
14 13/01/1980 00:00 19800113 1980.01 2 1980 Week 02 2 1980.01 Week 2 January 1 1980
15 14/01/1980 00:00 19800114 1980.01 3 1980 Week 03 3 1980.01 Week 3 January 1 1980
16 15/01/1980 00:00 19800115 1980.01 3 1980 Week 03 3 1980.01 Week 3 January 1 1980
17 16/01/1980 00:00 19800116 1980.01 3 1980 Week 03 3 1980.01 Week 3 January 1 1980
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://qa.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