September 25, 2017 at 8:06 am
My task is to get a cumulative weekly status report, for a period of time, i.e. last 12-month. The source data looks like below:
ProductId Status UpdatedOn
264525 Initiated 12/28/2016
264525 Ordered 12/29/2016
264525 Completed 12/31/2016
264526 Initiated 1/28/2017
264526 Ordered 1/29/2017
264526 Completed 1/30/2017
and the report should be formatted like this, starts from Thu. and end at Wed.:
BeginOfWeek(Thu) EndOfWeek(Wed) Initiated Ordered Completed
12/29/2016 1/4/2017 3 5 4
1/5/2017 1/11/2017 2 3 6
9/14/2017 9/20/2017 3 4 1
I have a stored procedure to get a weekly status, and need to figure out a way to run it cumulatively for last 52-weeks.
Please advise. Thank you in advance.
September 25, 2017 at 8:17 am
Create a calendar table with a single day in each row, and a column for the week name or number. You can then join to that table and use the week name or number to group your data and produce the counts you're looking for.
For more detailed help, please provide table DDL in the form of CREATE TABLE statement(s) and sample data in the form of INSERT statement(s) and show us what you've tried so far.
John
September 25, 2017 at 9:30 am
John Mitchell-245523 - Monday, September 25, 2017 8:17 AMCreate a calendar table with a single day in each row, and a column for the week name or number. You can then join to that table and use the week name or number to group your data and produce the counts you're looking for.For more detailed help, please provide table DDL in the form of CREATE TABLE statement(s) and sample data in the form of INSERT statement(s) and show us what you've tried so far.
John
Thanks for your reply, John. Below is the code for my tables. However, I'm still struggling to get grouped weekly data per row.
-- Calendar Table
If (OBJECT_ID('tempdb..#tbCalendar') is not null) Drop Table #tbCalendar
Create Table #tbCalendar (CalendarDate datetime, WD_Num int)
Declare @StartDate datetime = (Select Convert(datetime, '12/1/2016'))
, @EndDate datetime = (Select Convert(datetime, '12/31/2017'))
While @StartDate <= @EndDate
begin
Insert Into #tbCalendar (CalendarDate, WD_Num)
Select @StartDate, DATEPART(DW, @StartDate)
Set @StartDate = DATEADD(dd, 1, @StartDate)
end
-- Product Table
If (OBJECT_ID('tempdb..#tbProducts') is not null) Drop Table #tbProducts
Create Table #tbProducts (ProductId int, Prod_Status varchar(25), Updatedon datetime)
Insert Into #tbProducts (ProductId, Prod_Status, Updatedon)
Values (10001, 'Initiated', Convert(datetime, '12/28/2016'))
, (10001, 'Ordered', Convert(datetime, '12/29/2016'))
, (10001, 'Completed', Convert(datetime, '12/31/2016'))
, (10002, 'Initiated', Convert(datetime, '1/12/2017'))
, (10002, 'Ordered', Convert(datetime, '1/14/2017'))
, (10002, 'Completed', Convert(datetime, '1/16/2017'))
, (10003, 'Initiated', Convert(datetime, '2/1/2017'))
, (10003, 'Ordered', Convert(datetime, '2/3/2017'))
, (10003, 'Completed', Convert(datetime, '2/9/2017'))
, (10004, 'Initiated', Convert(datetime, '3/28/2017'))
, (10004, 'Ordered', Convert(datetime, '3/30/2017'))
-- join two tables, with initiated product status as an example
Select Count(*) as InitiatedProd
From #tbProducts p
Inner Join #tbCalendar c on c.CalendarDate = p.Updatedon
Where p.Prod_Status = 'Initiated'
--??
-- Drop Table #tbCalendar
-- Drop Table #Products
September 25, 2017 at 9:53 am
In your Calendar table, you need to number your weeks, not the day of the week. So 2017-09-14 (Wednesday) would be week 20 (say), so would 2017-09-15, so would 2017-09-16, and so on, then 2017-09-21 would be week 21, and so on. And don't forget to put a GROUP BY clause in your SELECT statement, otherwise you'll just get a count of all the rows returned by the SELECT.
John
September 25, 2017 at 9:55 am
getting one step closer to the solution:
Select Count(*) as InitiatedProd, c.Wk_number
From #tbProducts p
Inner Join (Select CalendarDate, WD_Num, datediff(WEEK, '12/31/2017', CalendarDate) as Wk_number From #tbCalendar)
c on c.CalendarDate = p.Updatedon
Where p.Prod_Status = 'Initiated'
Group by Wk_number
September 28, 2017 at 7:54 am
jay-125866 - Monday, September 25, 2017 8:06 AMMy task is to get a cumulative weekly status report, for a period of time, i.e. last 12-month. The source data looks like below:
ProductId Status UpdatedOn
264525 Initiated 12/28/2016
264525 Ordered 12/29/2016
264525 Completed 12/31/2016
264526 Initiated 1/28/2017
264526 Ordered 1/29/2017
264526 Completed 1/30/2017and the report should be formatted like this, starts from Thu. and end at Wed.:
BeginOfWeek(Thu) EndOfWeek(Wed) Initiated Ordered Completed
12/29/2016 1/4/2017 3 5 4
1/5/2017 1/11/2017 2 3 6
9/14/2017 9/20/2017 3 4 1I have a stored procedure to get a weekly status, and need to figure out a way to run it cumulatively for last 52-weeks.
Please advise. Thank you in advance.
Hey You can use the datefirst. By default its set to 7 i.e saturday. below us the solution
Source data
pid status updatedon
1 initiated 2017-09-28
2 initiated 2017-09-28
3 Completed 2017-09-28
4 ordered 2017-09-28
5 initiated 2017-09-28
6 initiated 2017-09-22
7 Completed 2017-09-21
8 ordered 2017-09-26
9 initiated 2017-09-24
set datefirst 4 --set thursday as your week startday
declare @d date ='2017-9-10'
select DATEADD(D,DATEPART(WEEKDAY,@d)-7,@d) as week_start_date,DATEADD(D,7-DATEPART(WEEKDAY,@d),@d) as week_end_date
create table dbo.tbl
(
pid int,
status varchar(10),
updatedon date
)
insert into dbo.tbl
select 6,'initiated',GETDATE()-6
union all
select 7,'Completed',GETDATE()-7
union all
select 8,'ordered',GETDATE()-2
union all
select 9,'initiated',GETDATE()-4
select *,DATEADD(D,DATEPART(WEEKDAY,updatedon)-7,updatedon) as week_start_date,DATEADD(D,7-DATEPART(WEEKDAY,updatedon),updatedon) as week_end_date
from dbo.tbl
Output
pid status updatedon week_start_date week_end_date
1 initiated 2017-09-28 2017-09-22 2017-10-04
2 initiated 2017-09-28 2017-09-22 2017-10-04
3 Completed 2017-09-28 2017-09-22 2017-10-04
4 ordered 2017-09-28 2017-09-22 2017-10-04
5 initiated 2017-09-28 2017-09-22 2017-10-04
6 initiated 2017-09-22 2017-09-17 2017-09-27
7 Completed 2017-09-21 2017-09-15 2017-09-27
8 ordered 2017-09-26 2017-09-25 2017-09-27
9 initiated 2017-09-24 2017-09-21 2017-09-27
Pivot the below table and you will get the desired output.
select *
from dbo.tbl
pivot(count(pid) for status in([initiated],[Completed])) as p;
First solve the problem then write the code !
September 28, 2017 at 10:11 am
abatra002 - Thursday, September 28, 2017 7:54 AMjay-125866 - Monday, September 25, 2017 8:06 AMMy task is to get a cumulative weekly status report, for a period of time, i.e. last 12-month. The source data looks like below:
ProductId Status UpdatedOn
264525 Initiated 12/28/2016
264525 Ordered 12/29/2016
264525 Completed 12/31/2016
264526 Initiated 1/28/2017
264526 Ordered 1/29/2017
264526 Completed 1/30/2017and the report should be formatted like this, starts from Thu. and end at Wed.:
BeginOfWeek(Thu) EndOfWeek(Wed) Initiated Ordered Completed
12/29/2016 1/4/2017 3 5 4
1/5/2017 1/11/2017 2 3 6
9/14/2017 9/20/2017 3 4 1I have a stored procedure to get a weekly status, and need to figure out a way to run it cumulatively for last 52-weeks.
Please advise. Thank you in advance.Hey You can use the datefirst. By default its set to 7 i.e saturday. below us the solution
Source data
pid status updatedon
1 initiated 2017-09-28
2 initiated 2017-09-28
3 Completed 2017-09-28
4 ordered 2017-09-28
5 initiated 2017-09-28
6 initiated 2017-09-22
7 Completed 2017-09-21
8 ordered 2017-09-26
9 initiated 2017-09-24
set datefirst 4 --set thursday as your week startday
declare @d date ='2017-9-10'
select DATEADD(D,DATEPART(WEEKDAY,@d)-7,@d) as week_start_date,DATEADD(D,7-DATEPART(WEEKDAY,@d),@d) as week_end_date
create table dbo.tbl
(
pid int,
status varchar(10),
updatedon date
)insert into dbo.tbl
select 6,'initiated',GETDATE()-6
union all
select 7,'Completed',GETDATE()-7
union all
select 8,'ordered',GETDATE()-2
union all
select 9,'initiated',GETDATE()-4select *,DATEADD(D,DATEPART(WEEKDAY,updatedon)-7,updatedon) as week_start_date,DATEADD(D,7-DATEPART(WEEKDAY,updatedon),updatedon) as week_end_date
from dbo.tblOutput
pid status updatedon week_start_date week_end_date
1 initiated 2017-09-28 2017-09-22 2017-10-04
2 initiated 2017-09-28 2017-09-22 2017-10-04
3 Completed 2017-09-28 2017-09-22 2017-10-04
4 ordered 2017-09-28 2017-09-22 2017-10-04
5 initiated 2017-09-28 2017-09-22 2017-10-04
6 initiated 2017-09-22 2017-09-17 2017-09-27
7 Completed 2017-09-21 2017-09-15 2017-09-27
8 ordered 2017-09-26 2017-09-25 2017-09-27
9 initiated 2017-09-24 2017-09-21 2017-09-27Pivot the below table and you will get the desired output.
select *
from dbo.tbl
pivot(count(pid) for status in([initiated],[Completed])) as p;
This is great, thank you very much!
September 28, 2017 at 9:46 pm
jay-125866 - Thursday, September 28, 2017 10:11 AMabatra002 - Thursday, September 28, 2017 7:54 AMjay-125866 - Monday, September 25, 2017 8:06 AMMy task is to get a cumulative weekly status report, for a period of time, i.e. last 12-month. The source data looks like below:
ProductId Status UpdatedOn
264525 Initiated 12/28/2016
264525 Ordered 12/29/2016
264525 Completed 12/31/2016
264526 Initiated 1/28/2017
264526 Ordered 1/29/2017
264526 Completed 1/30/2017and the report should be formatted like this, starts from Thu. and end at Wed.:
BeginOfWeek(Thu) EndOfWeek(Wed) Initiated Ordered Completed
12/29/2016 1/4/2017 3 5 4
1/5/2017 1/11/2017 2 3 6
9/14/2017 9/20/2017 3 4 1I have a stored procedure to get a weekly status, and need to figure out a way to run it cumulatively for last 52-weeks.
Please advise. Thank you in advance.Hey You can use the datefirst. By default its set to 7 i.e saturday. below us the solution
Source data
pid status updatedon
1 initiated 2017-09-28
2 initiated 2017-09-28
3 Completed 2017-09-28
4 ordered 2017-09-28
5 initiated 2017-09-28
6 initiated 2017-09-22
7 Completed 2017-09-21
8 ordered 2017-09-26
9 initiated 2017-09-24
set datefirst 4 --set thursday as your week startday
declare @d date ='2017-9-10'
select DATEADD(D,DATEPART(WEEKDAY,@d)-7,@d) as week_start_date,DATEADD(D,7-DATEPART(WEEKDAY,@d),@d) as week_end_date
create table dbo.tbl
(
pid int,
status varchar(10),
updatedon date
)insert into dbo.tbl
select 6,'initiated',GETDATE()-6
union all
select 7,'Completed',GETDATE()-7
union all
select 8,'ordered',GETDATE()-2
union all
select 9,'initiated',GETDATE()-4select *,DATEADD(D,DATEPART(WEEKDAY,updatedon)-7,updatedon) as week_start_date,DATEADD(D,7-DATEPART(WEEKDAY,updatedon),updatedon) as week_end_date
from dbo.tblOutput
pid status updatedon week_start_date week_end_date
1 initiated 2017-09-28 2017-09-22 2017-10-04
2 initiated 2017-09-28 2017-09-22 2017-10-04
3 Completed 2017-09-28 2017-09-22 2017-10-04
4 ordered 2017-09-28 2017-09-22 2017-10-04
5 initiated 2017-09-28 2017-09-22 2017-10-04
6 initiated 2017-09-22 2017-09-17 2017-09-27
7 Completed 2017-09-21 2017-09-15 2017-09-27
8 ordered 2017-09-26 2017-09-25 2017-09-27
9 initiated 2017-09-24 2017-09-21 2017-09-27Pivot the below table and you will get the desired output.
select *
from dbo.tbl
pivot(count(pid) for status in([initiated],[Completed])) as p;This is great, thank you very much!
Make sure u use this approach when your source table has 100s or 1000s of rows. If there are millions of them; u should use the dummy calendar table(create it the same way setting the datefirst to 4 and loop it to dates). Because calling a function in select makes the approach ROW-based rather than SET based.
First solve the problem then write the code !
September 29, 2017 at 2:08 am
TheCTEGuy - Thursday, September 28, 2017 9:46 PMBecause calling a function in select makes the approach ROW-based rather than SET based.
Only if the function is a user-defined function. A system function won't do any harm in the SELECT clause. In the WHERE clause, it could make the predicate non-sargable, thereby forcing index seeks instead of scans.
John
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply