SELECT STATEMENT relating to date, year, etc...
Hi,
I am running "SELECT" statement to output sales order year nad ordered date by quarterly using following statement:
SET DATEFORMAT mdy
SELECT DATEPART(yy, OrderReceivedDate) AS OrderYear,
'Q' + DATENAME(qq, OrderReceivedDate) AS OrderQtr
This results in following output:
1/1/2007 thru 3/31/2007 = (OrderQtr = Q1) and (OrderYear = 2007)
4/1/2007 thru 6/30/2007 = (OrderQtr = Q2) and (OrderYear = 2007)
and so on (In accouting term Quarter is broken by "Physical Year").
However, my client's Q1 does not start on Physical Year (1/1/2007) but rather starts on 10/1/2006. So 4 quarter for 2007 is following:
10/1/2006 - 12/31/2006 = Q1 for year 2007
1/1/2007 - 3/31/2007 = Q2 for year 2007
4/1/2007 - 6/30/2007 = Q3 for year 2007
7/1/2007 - 9/30/2007 = Q4 for year 2007
So my question is how can I run select statement using similar to below statement to accomplish above Calendar year which starts on 10/1/yyyy and ends on 9/30/yyyy?
SET DATEFORMAT mdy
SELECT DATEPART(yy, OrderReceivedDate) AS OrderYear,
'Q' + DATENAME(qq, OrderReceivedDate) AS OrderQtr
Thank you in advance,
CN
I am running "SELECT" statement to output sales order year nad ordered date by quarterly using following statement:
SET DATEFORMAT mdy
SELECT DATEPART(yy, OrderReceivedDate) AS OrderYear,
'Q' + DATENAME(qq, OrderReceivedDate) AS OrderQtr
This results in following output:
1/1/2007 thru 3/31/2007 = (OrderQtr = Q1) and (OrderYear = 2007)
4/1/2007 thru 6/30/2007 = (OrderQtr = Q2) and (OrderYear = 2007)
and so on (In accouting term Quarter is broken by "Physical Year").
However, my client's Q1 does not start on Physical Year (1/1/2007) but rather starts on 10/1/2006. So 4 quarter for 2007 is following:
10/1/2006 - 12/31/2006 = Q1 for year 2007
1/1/2007 - 3/31/2007 = Q2 for year 2007
4/1/2007 - 6/30/2007 = Q3 for year 2007
7/1/2007 - 9/30/2007 = Q4 for year 2007
So my question is how can I run select statement using similar to below statement to accomplish above Calendar year which starts on 10/1/yyyy and ends on 9/30/yyyy?
SET DATEFORMAT mdy
SELECT DATEPART(yy, OrderReceivedDate) AS OrderYear,
'Q' + DATENAME(qq, OrderReceivedDate) AS OrderQtr
Thank you in advance,
CN
sorry, mistake there:
SET DATEFORMAT mdy
SELECT DATEPART(yy, dateadd(month, 3, OrderReceivedDate)) AS OrderYear,
'Q' + DATENAME(qq, dateadd(month, 3, OrderReceivedDate)) AS OrderQtr
SET DATEFORMAT mdy
SELECT DATEPART(yy, dateadd(month, 3, OrderReceivedDate)) AS OrderYear,
'Q' + DATENAME(qq, dateadd(month, 3, OrderReceivedDate)) AS OrderQtr
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT YEAR(OrderReceivedDate) + (CASE WHEN MONTH(OrderReceivedDate) >= 10 THEN 1 ELSE 0 END) AS OrderYear,
'Q' + (CASE MONTH(OrderReceivedDate) WHEN >= 10 THEN 1 WHEN <=3 THEN 2 WHEN <=6 THEN 3 ELSE 4 END) AS OrderQtr
'Q' + (CASE MONTH(OrderReceivedDate) WHEN >= 10 THEN 1 WHEN <=3 THEN 2 WHEN <=6 THEN 3 ELSE 4 END) AS OrderQtr
SELECT DATEPART(yy, OrderReceivedDate) AS OrderYear,
REPLACE(REPLACE(REPLACE(RE
REPLACE(REPLACE(REPLACE(RE
SELECT DATEPART(yy, OrderReceivedDate) AS OrderYear,
'Q' + DATENAME(qq, dateadd(month, 3, OrderReceivedDate)) AS OrderQtr