Solved

capture data for the week

Posted on 2014-03-05
13
468 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
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

789 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