November 8, 2011 at 7:04 am
I'm able to do the following ok, but when I try and calcualte the Date of the Current Week and 4 Previous Weeks (Starting on Sunday) I run into trouble.
Any help would be greatly appreciated.
SELECT CAST(YEAR(DATEADD(yy, -1, GETDATE())) AS VARCHAR(4)) AS 'PrevYear',
CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ' YTD' AS 'YearToDate',
--CAST(LEFT(UPPER(DATENAME(MONTH, GETDATE())),3) AS VARCHAR(3))+ ' ' +
--CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ' MTD' AS 'CurrentMTD',-- OR
CAST(UPPER(DATEADD(mm, 0, GETDATE())) AS VARCHAR(4)) +
CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ' MTD' AS 'Current MTD',
CAST(UPPER(DATEADD(mm, -1, GETDATE())) AS VARCHAR(4)) +
CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ' MTD' AS 'Previous Month',CAST(UPPER(DATEADD (mm, -2, GETDATE())) AS VARCHAR(4)) +
CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ' MTD' AS 'Two Months Prior'
Returns:
PrevYear YearToDate Current MTD Previous Month Two Months Prior
2010 2011 YTD NOV 2011 MTD OCT 2011 MTD SEP 2011 MTD
I'm having Trouble getting the Current Week and 4 Weeks Prior.:w00t:
I need the Weeks Starting with the Current Week Starting on Sunday Where the current Week is:
Week of NOV 6
Previous Weeks are:
Week of OCT 30
Week of OCT 23
Week of OCT 16
Week of OCT 9
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 8, 2011 at 7:35 am
What I was trying to do will not work in SSIS, Tested it, please disregard.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 8, 2011 at 8:26 am
You're probably running into problems, because the standard way to do beginning of time period calculations is to use date 0 ('1900-01-01') as the foundation of the calculations. This is great for most time periods, because date 0 falls on the beginning of the time period, so adding whole time periods to that will result in values that are also at the beginning of that same time period. The problem is that date 0 falls on a Monday, so it doesn't work for beginning of the week calculations for weeks beginning on any other day of the week, such as the Sunday that you are looking for.
The other problem is that DateDiff() ignores the first day of week setting, so you may run into problems with some first day of week settings producing values that are off a week from the expected.
Here is the code to produce the headers that you are looking for.
SELECT 'Week of ' + Convert(varchar(6), DateAdd(wk, DateDiff(wk, -1, GetDate()), -1), 0)
, 'Week of ' + Convert(varchar(6), DateAdd(wk, DateDiff(wk, -1, GetDate()) - 1, -1), 0)
, 'Week of ' + Convert(varchar(6), DateAdd(wk, DateDiff(wk, -1, GetDate()) - 2, -1), 0)
, 'Week of ' + Convert(varchar(6), DateAdd(wk, DateDiff(wk, -1, GetDate()) - 3, -1), 0)
, 'Week of ' + Convert(varchar(6), DateAdd(wk, DateDiff(wk, -1, GetDate()) - 4, -1), 0)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 8, 2011 at 8:32 am
This will work OK no matter what the setting for DATEFIRST is:
select
DATE,
WeekStartDate = dateadd(dd,(datediff(dd,'17530107',a.DATE)/7)*7,'17530107')
from
(
select DATE = getdate() union all
select DATE = getdate()-7 union all
select DATE = getdate()-(7*2) union all
select DATE = getdate()-(7*3) union all
select DATE = getdate()-(7*4)
) a
order by
a.DATE desc
Results:
DATE WeekStartDate
----------------------- -----------------------
2011-11-08 10:29:06.733 2011-11-06 00:00:00.000
2011-11-01 10:29:06.733 2011-10-30 00:00:00.000
2011-10-25 10:29:06.733 2011-10-23 00:00:00.000
2011-10-18 10:29:06.733 2011-10-16 00:00:00.000
2011-10-11 10:29:06.733 2011-10-09 00:00:00.000
November 8, 2011 at 8:35 am
Thanks Drew.
I probably won't be able to use this for the SSIS Excel Worksheet as Dynamic Headers followed by the Output from a CTE.
I have a Data Conversion Task before the Excel OLEDB Destination and I must map the columns to convert to UniCode.
Thanks again.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 8, 2011 at 9:02 am
Thanks Michael.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 8, 2011 at 9:29 am
Welsh Corgi (11/8/2011)
I probably won't be able to use this for the SSIS Excel Worksheet as Dynamic Headers followed by the Output from a CTE.
If SSRS is a possibility, it would be much easier to do this in a report than in SSIS.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 8, 2011 at 9:43 am
drew.allen (11/8/2011)
Welsh Corgi (11/8/2011)
I probably won't be able to use this for the SSIS Excel Worksheet as Dynamic Headers followed by the Output from a CTE.If SSRS is a possibility, it would be much easier to do this in a report than in SSIS.
Drew
I probably should have done this in SSRS.
There are multiple sheets, 8 that I have already loaded. Some are nearly 300K.
Then there are 7 Sheet that are Pivot Tables based on the other sheets
I'm comming down the stretch, I think :w00t:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 24, 2012 at 4:55 am
Hi,
If I have weekstartdate & enddate, week column in my table......can any one tell me how to write sql to display curren week number, previous 9 weeks & next 6 weeks ........by considering 0 as current week........
PFA for your reference.........I need tio get the output in one of my report in COGNOS report studio.....so I am asking help in getting sql or any function for the same...............
Thanks
Gopi
July 24, 2012 at 5:00 am
84gopi (7/24/2012)
Hi,If I have weekstartdate & enddate, week column in my table......can any one tell me how to write sql to display curren week number, previous 9 weeks & next 6 weeks ........by considering 0 as current week........
PFA for your reference.........I need tio get the output in one of my report in COGNOS report studio.....so I am asking help in getting sql or any function for the same...............
Thanks
Gopi
Do not hijack peoples threads, you have your own thread for this here, if you provide the information requested on the original thread we will try to help you.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply