Solved

capture data for the week

Posted on 2014-03-05
13
478 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

707 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