?
Solved

capture data for the week

Posted on 2014-03-05
13
Medium Priority
?
482 Views
Last Modified: 2014-03-07
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
Comment
Question by:tips54
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 4

Expert Comment

by:ravikantninave
ID: 39907600
find current calendar week
select DATEPART(WEEK,GETDATE())
0
 
LVL 4

Expert Comment

by:ravikantninave
ID: 39907652
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;

--
Declare @NextDate datetime= DATEADD(YEAR,-1, getdate())
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

by:tips54
ID: 39907677
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 4

Expert Comment

by:ravikantninave
ID: 39907684
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

by:tips54
ID: 39907721
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

by:ravikantninave
ID: 39907734
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

by:
ravikantninave earned 1200 total points
ID: 39907740
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

by:tips54
ID: 39907796
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

by:tips54
ID: 39907920
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
SET @NextDate= DATEADD(YEAR,-1, getdate())
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

by:tips54
ID: 39907978
How would I get the sum for the quarter?
0
 

Author Comment

by:tips54
ID: 39909662
I would like to capture the same data quarterly compare to last years quarter.
0
 
LVL 4

Expert Comment

by:ravikantninave
ID: 39911897
first three months of the year? or what? Find quarter :

SELECT DATEADD(mm, (quarter - 1) * 3, year_date) StartDate,
       DATEADD(dd, -1, DATEADD(mm, quarter * 3, year_date)) EndDate,
       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

by:tips54
ID: 39912380
Thank you.

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

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question