November 20, 2014 at 1:50 am
Hi Clever People
I have the following codes with hard-coded dates as per below. I have will have to run this code in a procedure every month, which means I will have to change the dates every month. Does anybody have an idea how I can do this without having to change the dates all the time? The months will always be fifteen months ahead.
Kind regards
Fred
WHEN PlannedRelease.TransDateNumeric BETWEEN 20140601 AND 20140630 THEN 1
WHEN PlannedRelease.TransDateNumeric BETWEEN 20140701 AND 20140731 THEN 2
WHEN PlannedRelease.TransDateNumeric BETWEEN 20140801 AND 20140831 THEN 3
WHEN PlannedRelease.TransDateNumeric BETWEEN 20140901 AND 20140930 THEN 4
WHEN PlannedRelease.TransDateNumeric BETWEEN 20141001 AND 20141030 THEN 5
WHEN PlannedRelease.TransDateNumeric BETWEEN 20141101 AND 20141130 THEN 6
WHEN PlannedRelease.TransDateNumeric BETWEEN 20141201 AND 20141231 THEN 7
WHEN PlannedRelease.TransDateNumeric BETWEEN 20150101 AND 20150131 THEN 8
WHEN PlannedRelease.TransDateNumeric BETWEEN 20150201 AND 20150228 THEN 9
WHEN PlannedRelease.TransDateNumeric BETWEEN 20150301 AND 20150331 THEN 10
WHEN PlannedRelease.TransDateNumeric BETWEEN 20150401 AND 20150430 THEN 11
WHEN PlannedRelease.TransDateNumeric BETWEEN 20150501 AND 20150531 THEN 12
WHEN PlannedRelease.TransDateNumeric BETWEEN 20150601 AND 20150130 THEN 13
WHEN PlannedRelease.TransDateNumeric BETWEEN 20150701 AND 20150731 THEN 14
WHEN PlannedRelease.TransDateNumeric BETWEEN 20150801 AND 20150831 THEN 15
WHEN PlannedRelease.TransDateNumeric BETWEEN 20140601 AND 20150831 THEN 99
November 20, 2014 at 2:36 am
Your condition for 99 is never met, check your logic.
Here's a method to calculate the dates dynamically:
DECLARE @startDate int = 20140601;
WITH SampleData AS (
SELECT *
FROM (
VALUES
(20140601),
(20140701),
(20140801),
(20140901),
(20141001),
(20141101),
(20141201),
(20150101),
(20150201),
(20150301),
(20150401),
(20150501),
(20150601),
(20150701),
(20150801),
(20140601)
) AS data (TransDateNumeric)
)
SELECT TransDateNumeric,
result = ISNULL((
SELECT i + 1
FROM (
VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)
) AS data(i)
WHERE CONVERT(date,CAST(TransDateNumeric AS char(8)),112)
BETWEENDATEADD(month,i,CONVERT(date,CAST(@startDate AS char(8)),112))
AND DATEADD(day,-1,DATEADD(month,i+1,CONVERT(date,CAST(@startDate AS char(8)),112)))
),99)
FROM SampleData AS PlannedRelease
Hope this helps
Gianluca
-- Gianluca Sartori
November 20, 2014 at 10:50 am
I wasn't not sure what '20140601' was relative to the run date. Is it the current month, the next month, 6 months ago?
Therefore, I used @month_1 to hold the first month. Set that value however you need to. Then the calculation should be what you want.
DECLARE @month_1 datetime;
SET @month_1 = '20140601';
SELECT
PlannedRelease.TransDateNumeric
, DATEDIFF(MONTH, @month_1, CAST(PlannedRelease.TransDateNumeric AS varchar(8))) + 1
FROM (
SELECT 20140605 AS TransDateNumeric UNION ALL
SELECT 20140714 AS TransDateNumeric UNION ALL
SELECT 20140811 UNION ALL
SELECT 20140922 UNION ALL
SELECT 20141002 UNION ALL
SELECT 20141121 UNION ALL
SELECT 20141217 UNION ALL
SELECT 20150116 UNION ALL
SELECT 20150207 UNION ALL
SELECT 20150308 UNION ALL
SELECT 20150409 UNION ALL
SELECT 20150503 UNION ALL
SELECT 20150617 UNION ALL
SELECT 20150726 UNION ALL
SELECT 20150830
) AS PlannedRelease
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 20, 2014 at 11:16 pm
Thank you both for your reply but the integer dates are still hard-coded in your answers. And that's the problem that I have. Instead of 20141106 (hard-coded) I want an alternative where I do not hard-code it. Write it away to a temp-table perhaps and use what is in the temp-table - not what is hard-coded as is now.
November 21, 2014 at 1:38 am
frdrckmitchell7 (11/20/2014)
Thank you both for your reply but the integer dates are still hard-coded in your answers. And that's the problem that I have. Instead of 20141106 (hard-coded) I want an alternative where I do not hard-code it. Write it away to a temp-table perhaps and use what is in the temp-table - not what is hard-coded as is now.
It's not hardcoded, it's a variable.
You can assign whatever you want to that variable. It could be a stored procedure parameter, a value you read from a table... whatever.
-- Gianluca Sartori
November 21, 2014 at 1:50 am
Oh, ok. Thanks. It worked!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply