Solved

capture data for the week

Posted on 2014-03-05
13
471 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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 400 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

740 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