I have data in SQL that holds a in DateTime format.
startTimeStamp, endTimeStamp, LocationID, AvgPT, NumEmp, InspectionType as my table.
Is there a SQL query that will return the last 10 weeks from today, like this?
Week 1, AvgPT, NumEmp
Week 2, AvgPT, NumEmp
Week 3, AvgPT, NumEmp
So on.
As it stands, I have a C# function that does a loop for each 10 week, and subtracts (i*7) from the current date. I then call a function that opens the database, retrieves my values, and returns then. So yeah.. I make 10 seperate calls to a database. Not
efficient or cost effective.
Is there a native SQL query that will do what I want??
Try this query. It assumes that starttimestamp and endtimestamp corresponds to a single day and not spanning across multiple days.
SELECT 'Week'+ CAST(Datedifference as varchar) , SUM(NumEmp) as NumEmp, SUM(AvgPT) as AvgPT
FROM
(select AvgPT, NumEmp, (DATEDIFF(d,cast(endTimeStamp as date),GETDATE()) / 7) as Datedifference
from temp) as a
GROUP BY Datedifference
Create a table using CTE like below that would generate entries for last 7 weeks. After this use join with your table to get the data weekwise
DECLARE @DATE DATE
SET @DATE = GETDATE()
;WITH WEEKS AS(
SELECT DATEADD(D, -6, @DATE) AS START_DATE, @DATE AS END_DATE, 7 AS WEEKNO
UNION ALL
SELECT DATEADD(D,-7,START_DATE),DATEADD(D,-7,END_DATE), WEEKNO - 1
FROM WEEKS WHERE WEEKNO>1
)
SELECT * FROM WEEKS
Sample code for you reference
DECLARE @TAB TABLE ( DT DATE, CNT INT )
INSERT INTO @TAB
SELECT CAST(GETDATE() - 1 AS DATE), 1
UNION ALL SELECT CAST(GETDATE() - 4 AS DATE), 3
UNION ALL SELECT CAST(GETDATE() - 25 AS DATE), 4
UNION ALL SELECT CAST(GETDATE() - 7 AS DATE), 1
UNION ALL SELECT CAST(GETDATE() - 11 AS DATE), 5
UNION ALL SELECT CAST(GETDATE() - 18 AS DATE), 1
UNION ALL SELECT CAST(GETDATE() - 35 AS DATE), 7
UNION ALL SELECT CAST(GETDATE()- 40 AS DATE) , 4
DECLARE @DATE DATE
SET @DATE = GETDATE()
;WITH WEEKS AS(
SELECT DATEADD(D, -6, @DATE) AS START_DATE, @DATE AS END_DATE, 7 AS WEEKNO
UNION ALL
SELECT DATEADD(D,-7,START_DATE),DATEADD(D,-7,END_DATE), WEEKNO - 1
FROM WEEKS WHERE WEEKNO>1
)
SELECT 'Week ' + CAST(WEEKNO AS VARCHAR) AS WEEK, ISNULL(SUM(CNT),0) AS COUNT
FROM WEEKS W LEFT JOIN @TAB T ON T.DT BETWEEN W.START_DATE AND W.END_DATE
GROUP BY 'Week ' + CAST(WEEKNO AS VARCHAR)
ORDER BY 'Week ' + CAST(WEEKNO AS VARCHAR)
Ah, I like the solution you presented sandeep, do you mind helping me further??
In your solution, I want to know the StartDate and EndDate as an actual date in format mm/dd/yyyy
Result Set should look like "Week #, StartDate, EndDate" for a variable number of weeks. It depends on the number of weeks that the function would receive
--SELECT * FROM dbo.udf_GetWeeks(7)
CREATE FUNCTION udf_GetWeeks(@NoOfWeeks INT)
RETURNS @RET_TAB TABLE (WEEK VARCHAR(10), START_DATE DATE, END_DATE DATE, COUNT INT)
AS
BEGIN
DECLARE @TAB TABLE ( DT DATE, CNT INT )
INSERT INTO @TAB
SELECT CAST(GETDATE() - 1 AS DATE), 1
UNION ALL SELECT CAST(GETDATE() - 4 AS DATE), 3
UNION ALL SELECT CAST(GETDATE() - 25 AS DATE), 4
UNION ALL SELECT CAST(GETDATE() - 7 AS DATE), 1
UNION ALL SELECT CAST(GETDATE() - 11 AS DATE), 5
UNION ALL SELECT CAST(GETDATE() - 18 AS DATE), 1
UNION ALL SELECT CAST(GETDATE() - 35 AS DATE), 7
UNION ALL SELECT CAST(GETDATE()- 40 AS DATE) , 4
DECLARE @DATE DATE
SET @DATE = GETDATE()
;WITH WEEKS AS(
SELECT DATEADD(D, -6, @DATE) AS START_DATE, @DATE AS END_DATE, @NoOfWeeks AS WEEKNO
UNION ALL
SELECT DATEADD(D,-7,START_DATE),DATEADD(D,-7,END_DATE), WEEKNO - 1
FROM WEEKS WHERE WEEKNO>1
)
INSERT INTO @RET_TAB
SELECT 'Week ' + CAST(WEEKNO AS VARCHAR) AS WEEK, START_DATE, END_DATE, ISNULL(SUM(CNT),0) AS COUNT
FROM WEEKS W LEFT JOIN @TAB T ON T.DT BETWEEN W.START_DATE AND W.END_DATE
GROUP BY 'Week ' + CAST(WEEKNO AS VARCHAR), START_DATE, END_DATE
ORDER BY 'Week ' + CAST(WEEKNO AS VARCHAR)
RETURN
END
I know I am getting picky here, and man do I appreciate your help. You do not even want to see my code for how I did it in vb.net... its ugly, and performance is crappy.
Anyway,
Week 1 should be the current week.
And all weeks start on Monday at 12:00am and end at 11:59pm Sunday. Since today is Friday, Week 1 would be 03/16/2012 to 03/12/2012 at 12:00am. If I run this same report tomorrow, Week 1 would be 03/17/2012 to 03/12/2012 at 12:00am.
Anyway, Im sure I can modified what you so generously provided.
What I am trying to accomplish is to have the user tell me the number of weeks they want to pull data. If the user enters 5, then I want SQl to pull the current week, and then 4 additional weeks of data from a table called Report_PT_Master.
I have data in SQL that holds a in DateTime format.
startTimeStamp, endTimeStamp, LocationID, AvgPT, NumEmp, InspectionType as my table.
Is there a SQL query that will return the last 10 weeks from today, like this?
Week 1, AvgPT, NumEmp
Week 2, AvgPT, NumEmp
Week 3, AvgPT, NumEmp
So on.
As it stands, I have a C# function that does a loop for each 10 week, and subtracts (i*7) from the current date. I then call a function that opens the database, retrieves my values, and returns then. So yeah.. I make 10 seperate calls to a database. Not
efficient or cost effective.
Is there a native SQL query that will do what I want??
It might be worth your while to store information about dates in a separate table. In this case, a weekEnding or weekBeginnig field would be useful. Other fields that might be useful are holidays and fiscal year stuff.
It might be worth your while to store information about dates in a separate table. In this case, a weekEnding or weekBeginnig field would be useful. Other fields that might be useful are holidays and fiscal year stuff.
Is this a standard practice? What would a good table look like for that sort of thing?
Is this a standard practice? What would a good table look like for that sort of thing?
The standard practice is to store the data you need to use. We do it for our data warehouse. We also intentionally break normalization rules to increase performance. Also, this is a redbrick database which has date, time, and timestamp ( date and time)
fields. Some of our fields are:
Date date primary key
year_month char (7) example 2012-03
holiday varchar
fiscal_year, month, period, quarter varchar
calendar quarter char (2) example Q2
day_of_week varchar example 1_SUNDAY (used for sorting)
It's useful for us. You have to make your own decisions.
And all weeks start on Monday at 12:00am and end at 11:59pm Sunday. Since today is Friday, Week 1 would be 03/16/2012 to 03/12/2012 at 12:00am. If I run this same report tomorrow, Week 1 would be 03/17/2012 to 03/12/2012 at 12:00am.
Hi,
You could change the WEEK part like this:
declare @setting date;
set @setting = GETDATE()
--select @setting
;WITH WEEKS AS(
SELECT DATEADD(D, - DATEPART (WEEKDAY,@setting)+2, @setting) AS START_DATE, @setting AS END_DATE, 1 AS WEEKNO
UNION ALL
SELECT DATEADD(D,-7,START_DATE),DATEADD(D,-1,START_DATE), WEEKNO + 1
FROM weeks WHERE WEEKNO < 10
)
Thanks.
Please mark the replies as answers if they help or unmark if not.
Feedback to us
Member
17 Points
91 Posts
SQL Query to Get Last 10 Weeks of Data?
Mar 13, 2012 10:57 PM|jalee1011|LINK
Ok, I know I screwed up here.
I have data in SQL that holds a in DateTime format.
startTimeStamp, endTimeStamp, LocationID, AvgPT, NumEmp, InspectionType as my table.
Is there a SQL query that will return the last 10 weeks from today, like this?
Week 1, AvgPT, NumEmp
Week 2, AvgPT, NumEmp
Week 3, AvgPT, NumEmp
So on.
As it stands, I have a C# function that does a loop for each 10 week, and subtracts (i*7) from the current date. I then call a function that opens the database, retrieves my values, and returns then. So yeah.. I make 10 seperate calls to a database. Not efficient or cost effective.
Is there a native SQL query that will do what I want??
Participant
857 Points
450 Posts
Re: SQL Query to Get Last 10 Weeks of Data?
Mar 13, 2012 11:02 PM|Sandeep Shenoy|LINK
HI
Try using stored procedure,you can include all the functionalities in a single stored procedure.
refer link below
http://stackoverflow.com/questions/64977/how-do-you-create-sql-server-2005-stored-procedure-templates-in-sql-server-2005
http://blog.sqlauthority.com/2007/09/03/sql-server-2005-search-stored-procedure-code-search-stored-procedure-text/
Skype : sandeep.d.shenoy
Gmail : sandeepdshenoy@gmail.com
Star
10303 Points
2360 Posts
Re: SQL Query to Get Last 10 Weeks of Data?
Mar 14, 2012 12:58 AM|yrb.yogi|LINK
better to show your talbe structure with some data to get better help.
Sr Technical Lead
Ahmedabad, India
Member
280 Points
86 Posts
Re: SQL Query to Get Last 10 Weeks of Data?
Mar 14, 2012 01:26 AM|jigarbjpatel|LINK
Try this query. It assumes that starttimestamp and endtimestamp corresponds to a single day and not spanning across multiple days.
Participant
1852 Points
915 Posts
Re: SQL Query to Get Last 10 Weeks of Data?
Mar 14, 2012 01:28 AM|Basquiat|LINK
If understand correctly you want to calculate the 1st week as 7 days from today. So your 10th week might be shorter than 7 days?
Once you have done that, what do you want to do with AvgPT? Calculate the average for the week?
And how would you like to aggregate NumEmp? Also a average for the week?
Contributor
5794 Points
1185 Posts
Re: SQL Query to Get Last 10 Weeks of Data?
Mar 14, 2012 01:32 AM|sandeepmittal11|LINK
Create a table using CTE like below that would generate entries for last 7 weeks. After this use join with your table to get the data weekwise
Sample code for you reference
Member
17 Points
91 Posts
Re: SQL Query to Get Last 10 Weeks of Data?
Mar 15, 2012 07:34 PM|jalee1011|LINK
Ah, I like the solution you presented sandeep, do you mind helping me further??
In your solution, I want to know the StartDate and EndDate as an actual date in format mm/dd/yyyy
Result Set should look like "Week #, StartDate, EndDate" for a variable number of weeks. It depends on the number of weeks that the function would receive
Contributor
5794 Points
1185 Posts
Re: SQL Query to Get Last 10 Weeks of Data?
Mar 16, 2012 12:46 AM|sandeepmittal11|LINK
Member
17 Points
91 Posts
Re: SQL Query to Get Last 10 Weeks of Data?
Mar 16, 2012 02:54 PM|jalee1011|LINK
I know I am getting picky here, and man do I appreciate your help. You do not even want to see my code for how I did it in vb.net... its ugly, and performance is crappy.
Anyway,
Week 1 should be the current week.
And all weeks start on Monday at 12:00am and end at 11:59pm Sunday. Since today is Friday, Week 1 would be 03/16/2012 to 03/12/2012 at 12:00am. If I run this same report tomorrow, Week 1 would be 03/17/2012 to 03/12/2012 at 12:00am.
Anyway, Im sure I can modified what you so generously provided.
What I am trying to accomplish is to have the user tell me the number of weeks they want to pull data. If the user enters 5, then I want SQl to pull the current week, and then 4 additional weeks of data from a table called Report_PT_Master.
What I am trying to get to is here:
Week 1 | StartDate | End Date | Area | Score
--------------------------------------------------------------------
Week 1 | 03/12/2012 | 03/16/2012 | Stock Room | 91.56
Week 1 | 03/12/2012 | 03/16/2012 | Restrooms | 75.65
Week 1 | 03/12/2012 | 03/16/2012 | Pantry Area | 88.95
...
Week 2 | 03/05/2012 | 03/11/2012 | Stock Room | 88.95
Week 2 | 03/05/2012 | 03/11/2012 | Restrooms | 96.55
Week 2 | 03/05/2012 | 03/11/2012 | Pantry Area | 71.00
...
The Dates are in a table called ReportMaster (StartDate, EndDate)
Each week it pulls a list of areas From ReportAreas (AreaName)
The values are linked by CategoryID to ReportQScore (PTValue)
Participant
1882 Points
1051 Posts
Re: SQL Query to Get Last 10 Weeks of Data?
Mar 16, 2012 03:01 PM|Dan Bracuk|LINK
It might be worth your while to store information about dates in a separate table. In this case, a weekEnding or weekBeginnig field would be useful. Other fields that might be useful are holidays and fiscal year stuff.
Member
17 Points
91 Posts
Re: SQL Query to Get Last 10 Weeks of Data?
Mar 16, 2012 03:20 PM|jalee1011|LINK
Is this a standard practice? What would a good table look like for that sort of thing?
Participant
1882 Points
1051 Posts
Re: SQL Query to Get Last 10 Weeks of Data?
Mar 16, 2012 03:47 PM|Dan Bracuk|LINK
The standard practice is to store the data you need to use. We do it for our data warehouse. We also intentionally break normalization rules to increase performance. Also, this is a redbrick database which has date, time, and timestamp ( date and time) fields. Some of our fields are:
Date date primary key
year_month char (7) example 2012-03
holiday varchar
fiscal_year, month, period, quarter varchar
calendar quarter char (2) example Q2
day_of_week varchar example 1_SUNDAY (used for sorting)
It's useful for us. You have to make your own decisions.
All-Star
21620 Points
2496 Posts
Re: SQL Query to Get Last 10 Weeks of Data?
Mar 20, 2012 05:13 AM|Chen Yu - MSFT|LINK
Hi,
You could change the WEEK part like this:
declare @setting date; set @setting = GETDATE() --select @setting ;WITH WEEKS AS( SELECT DATEADD(D, - DATEPART (WEEKDAY,@setting)+2, @setting) AS START_DATE, @setting AS END_DATE, 1 AS WEEKNO UNION ALL SELECT DATEADD(D,-7,START_DATE),DATEADD(D,-1,START_DATE), WEEKNO + 1 FROM weeks WHERE WEEKNO < 10 )Thanks.
Feedback to us