ReWrite SQL statement to simplify
-
Looking for a SQL junkie to rewrite the following SQL statement ~ This statement renders the LAST DAY of the QTR from 2 QTR's ago (time = 00:00:00.000) ---- IT works, but we're looking to simplify this - modeling it after the SQL listed at BOTTOM here.
SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,DATEADD(DAY,-DAY(MIN(GETDATE())),DATEADD(MONTH,-3,DATEADD(MONTH,-((MONTH(MIN(GETDATE()))-1)%3),MIN(GETDATE())))))))
BOTTOM: Here is a suggested starting point- this next statement renders the LAST DAY of the Prior QTR (w/ time = 23:59:59.997) ()
SELECT DATEADD(ms,-3,DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0))
(AGAIN, I need the last day from "2 QTR's AGO" w/ TIME = 00:00:00.000)
BT -
OK, maybe I'm being simplistic, but if your statement gives you the last day of the prior period, with an input of getdate, why not use that as the input to the statement, instead of getdate, to get the last last day of the prior period for the last day of the prior period?
-
Or you could use this:
SELECT
Dateadd(qq,-1,(DATEADD(ms,-3,DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0))))
-
Bill - Why do you keep asking the same question in different threads?
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=267409
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=267685
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=267396
There might even be more.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply