Solved

# capture data for the week

Posted on 2014-03-05
457 Views
Hello experts,

I need to help with the following syntax.  I need to capture sales for the current and sales for the same week last year.

Current calender  week: ?

Current calender   week Last year: ?
0
Question by:tips54
• 7
• 6

LVL 4

Expert Comment

find current calendar week
select DATEPART(WEEK,GETDATE())
0

LVL 4

Expert Comment

Here is full solution:

DECLARE @datecol datetime = GETDATE();
DECLARE @WeekNum INT
, @YearNum char(4);

SELECT @WeekNum = DATEPART(WK, @datecol)
, @YearNum = CAST(DATEPART(YY, @datecol) AS CHAR(4));

-- once you have the @WeekNum and @YearNum set, the following calculates the date range.
SELECT DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @YearNum) + (@WeekNum-1), 6) AS StartOfWeek;
SELECT DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + @YearNum) + (@WeekNum-1), 5) AS EndOfWeek;

--
SET @YearNum= CAST(DATEPART(YY, @NextDate) AS CHAR(4));
SELECT DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @YearNum) + (@WeekNum-1), 6) AS StartOfWeek;
SELECT DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + @YearNum) + (@WeekNum-1), 5) AS EndOfWeek;
0

Author Comment

Hi Ravi,

Your code returns the current week number for the year. (10)

what I'm after is something that returns : 3/2/2014 - 3/8/2014

And 3/2/2013  -  3/8/2013
0

LVL 4

Expert Comment

3/2/2014 - 3/8/2014 is the date range of current year of the 10 th week.

and date of 10th week of last year was 3/2/2013  -  3/8/2013
0

Author Comment

If I have a table with multiple invoices with dates,  how would the syntax look for sum of invoices for just week 10?
0

LVL 4

Expert Comment

DECLARE @FirstDate as datetime = DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @YearNum) + (@WeekNum-1), 6) --AS StartOfWeek;
DECLARE @SecondDate as datetime = DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + @YearNum) + (@WeekNum-1), 5) --AS EndOfWeek;

Select sum(Amount) from Invoices Where InvDate > = @FirstDate and InvDate <=@SecondDate
0

LVL 4

Accepted Solution

ravikantninave earned 400 total points
Same for last year
Declare @ThirdDate as datetime =DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @YearNum) + (@WeekNum-1), 6) --AS StartOfWeek;
Declare @ForthDate as Datetime =DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + @YearNum) + (@WeekNum-1), 5) --AS EndOfWeek;

Select sum(Amount) from Invoices Where InvDate > = @ThirdDate and InvDate <=@ForthDate
0

Author Comment

where does it differentiate Current and Last year?

I get this error when I run the code:
Cannot assign a default value to a local variable.
0

Author Comment

Thanks ravikantninave,

I got it.  I had to set those values on the next line. This might be a Sql 2005 issue.
Thanks again.

What I Used:

/*
DECLARE @datecol datetime
set @datecol = GETDATE();
DECLARE @WeekNum INT
set @WeekNum = DATEPART(week,GETDATE());
DECLARE    @YearNum char(4);
set @YearNum =DATEPART(year,GETDATE());

Declare @NextDate datetime
DECLARE    @YearNum2 char(4);
SET @YearNum2= CAST(DATEPART(YY, @NextDate) AS CHAR(4));

-- once you have the @WeekNum and @YearNum set, the following calculates the date range.
SELECT DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @YearNum) + (@WeekNum-1), 6) AS StartOfWeek;
SELECT DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + @YearNum) + (@WeekNum-1), 5) AS EndOfWeek;

SELECT DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @YearNum2) + (@WeekNum-1), 6) AS StartOfWeek;
SELECT DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + @YearNum2) + (@WeekNum-1), 5) AS EndOfWeek;

*/
0

Author Comment

How would I get the sum for the quarter?
0

Author Comment

I would like to capture the same data quarterly compare to last years quarter.
0

LVL 4

Expert Comment

first three months of the year? or what? Find quarter :

SELECT DATEADD(mm, (quarter - 1) * 3, year_date) StartDate,
quarter QuarterNo
FROM
(
SELECT '2013-01-01' year_date
) s CROSS JOIN
(
SELECT 1 quarter UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
) q
0

Author Closing Comment

Thank you.

the second question was answered here:
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28381957.html
0

## Featured Post

### Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even withinâ€¦
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.