October 28, 2014 at 9:44 am
I need to create two scripts and I'm not sure how to do this.
IF
CreateDate = Yesterday
InvoiceDate between 1st and 27th of the next month
Change JournalsDate to the second Saturday of the following month.
IF
CreateDate = Yesterday
InvoiceDate greater than 27 of the next month
Change JournalsDate to second Saturday two months away.
October 28, 2014 at 10:19 am
Do you really need/want two separate scripts? You can do this in one (complex) date computation:
SELECT
*,
DATEADD(DAY, DATEDIFF(DAY, 5, DATEADD(DAY, 19, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) +
CASE WHEN DAY(InvoiceDate) <= 27 THEN 1 ELSE 2 END, 0))) / 7 * 7, 5) AS JournalsDate
FROM (
SELECT GETDATE() - 1 AS CreateDate, CAST('20141101' AS datetime) AS InvoiceDate UNION ALL
SELECT GETDATE() - 1, CAST('20141128' AS datetime)
) AS test_data
WHERE
CreateDate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0) AND
CreateDate < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 28, 2014 at 10:29 am
Scott always tell me not to rely on date settings, but I keep doing it :-D. This is a different way to achieve it with a single script.
CREATE TABLE TestDates(
CreateDate date,
InvoiceDate date,
JournalsDate date)
INSERT INTO TestDates( CreateDate, InvoiceDate) VALUES
('20141027', '20141115'),
('20141027', '20141128')
UPDATE t
SET JournalsDate = DATEADD( dd, 14 - DATEPART(DW, monthStart), monthStart)
FROM TestDates t
CROSS APPLY( SELECT DATEADD(MM, DATEDIFF(MM, 0, InvoiceDate) + CASE WHEN DAY(InvoiceDate) < 28 THEN 1 ELSE 2 END, 0))m(monthStart)
WHERE t.CreateDate = DATEADD( DD, DATEDIFF( DD, 0, GETDATE()), -1)
SELECT *
FROM TestDates
GO
DROP TABLE TestDates
October 28, 2014 at 10:32 am
ScottPletcher - This is great but it's showing the 3rd Saturday of the weekend not the second.
October 28, 2014 at 10:50 am
Thank you both for the responses!! I have been working on this for a while and I was stumped. 🙂
Scott - thanks for the explanation.
October 28, 2014 at 10:50 am
stevemr68 (10/28/2014)
ScottPletcher - This is great but it's showing the 3rd Saturday of the weekend not the second.
D'OH, quite right, sorry. "13" days should be added, not 19.
Btw, the logic behind all this is: the month starts on the first, so adding 13 days to the underlying month computations takes us to the 14th of the month. That is the last possible day that the second Saturday could be. We then back up from there to the last actual Saturday. Day 6 (adding 5 days to the 1st of the month) = 19000106 = "base" Saturday (any earlier, known Saturday will do, but it's most common to use "day 0" (which was a Monday) in SQL Server date calcs, so I use day 6 as a relevant derivative of day 0 in this specific case where we need a starting Saturday).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 5, 2014 at 2:06 pm
I used Luis C's code in my script. After a little tweaking it's working perfectly.
Thanks again to both of you for your help!!
November 5, 2014 at 2:40 pm
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply