SQL Help This- timing metric

Hello All,
There are three tables in SQLServer.
 Weekly_Number – this table lists out the a specified metric numbers for a given condition of account + prodType + entire Week
WeekEndToStart – is a calendar list that gives the initiation / start and end dates for a given week number.
Daily_Number – this table lists out the a specified metric numbers for a given condition of account + prodType + single date

For example – lets say – in [weekly_Number] table –for account Ac1 and prodType (cups34) in W1 was 100. So  now since there are 5 working days in a week and we know the initiation and end dates of all week – we allocate 100/5 (=20) for those 5 working days. We skip the two weekend days. See the Daily_Number for what I am saying. I have attached the sql to create tables in Sql Server and add data to it just like what you see in excel.



Please help as to what SQL I should write…
sql-data-set.xlsx
RayneAsked:
Who is Participating?
 
SharathData EngineerCommented:
Do you want to get the result as shown in DAILY_Number table from [a_WeekToStartEnd] and [a_Weekly_Number] tables?
If so, you can try this query. one data issue in sample data: you have week as W1 for all your weeks. I think it should be W1,W2,W3,W4.
Anyway, try this query.
 
;with a_WeekToStartEnd_CTE as (
select [Week],st,[end] from a_WeekToStartEnd union all
select [Week],dateadd(d,1,st),[end] from a_WeekToStartEnd_CTE where dateadd(d,1,st) <= [end])
select t2.Account,t2.prodType,t1.st Dates_Daily,case when datepart(dw,st) in (1,7) then 0 else Metric/5 end Metric_Daily
  from a_WeekToStartEnd_CTE t1
  join [a_Weekly_Number] t2 on t1.[Week] = t2.[Week]
  order by st

/* result
Account	prodType	Dates_Daily	Metric_Daily
Ac1	cups34	2014-10-01	20.000000
Ac1	cups34	2014-10-02	20.000000
Ac1	cups34	2014-10-03	20.000000
Ac1	cups34	2014-10-04	0.000000
Ac1	cups34	2014-10-05	0.000000
Ac1	cups34	2014-10-06	20.000000
Ac1	cups34	2014-10-07	20.000000
Ac1	cups34	2014-10-08	40.000000
Ac1	cups34	2014-10-09	40.000000
Ac1	cups34	2014-10-10	40.000000
Ac1	cups34	2014-10-11	0.000000
Ac1	cups34	2014-10-12	0.000000
Ac1	cups34	2014-10-13	40.000000
Ac1	cups34	2014-10-14	40.000000

Open in new window

0
 
RayneAuthor Commented:
I am attaching a sql to create the tables and add values to them...here below
CREATE TABLE [myDBName]..[a_Weekly_Number] (
	[Account] [nvarchar](150) NULL
	,[ProdType] [nvarchar](150) NULL
	,[Week] [nvarchar](150) NULL
	,[Metric] [decimal](18, 2) NULL
	) ON [PRIMARY]
GO
insert into [myDBName]..[a_Weekly_Number] values('Ac1','cups34','W1',100)
insert into [myDBName]..[a_Weekly_Number] values('Ac1','cups34','W2',200)
--------------------------------------------------------------------------------------------




CREATE TABLE [myDBName]..[a_DAILY_Number] (
	[Account] [nvarchar](150) NULL
	,[ProdType] [nvarchar](150) NULL
	,[Dates_Daily] date
	,[Metric_Daily] [decimal](18, 2) NULL
	) ON [PRIMARY]
GO
insert into [myDBName]..[a_DAILY_Number] values('Ac1','cups34','10/01/2014',20)
insert into [myDBName]..[a_DAILY_Number] values('Ac1','cups34','10/02/2014',20)
insert into [myDBName]..[a_DAILY_Number] values('Ac1','cups34','10/03/2014',20)
insert into [myDBName]..[a_DAILY_Number] values('Ac1','cups34','10/04/2014',0)
insert into [myDBName]..[a_DAILY_Number] values('Ac1','cups34','10/05/2014',0)
insert into [myDBName]..[a_DAILY_Number] values('Ac1','cups34','10/06/2014',20)
insert into [myDBName]..[a_DAILY_Number] values('Ac1','cups34','10/07/2014',20)
--------------------------------------------------------------------------------------------





CREATE TABLE [myDBName]..[a_WeekToStartEnd] (
	[Week] [nvarchar](150) NULL
	,[st] date
	,[end] date
	) ON [PRIMARY]
GO
insert into [myDBName]..[a_WeekToStartEnd] values('W1','10/01/2014','10/07/2014')
insert into [myDBName]..[a_WeekToStartEnd] values('W1','10/08/2014','10/14/2014')
insert into [myDBName]..[a_WeekToStartEnd] values('W1','10/15/2014','10/21/2014')
insert into [myDBName]..[a_WeekToStartEnd] values('W1','10/22/2014','10/28/2014')

Open in new window

0
 
RayneAuthor Commented:
so how do it distribute the weekly metric into single dated day look?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
RayneAuthor Commented:
I think it makes sense to add a extra column like this?
account      prodType      Dates_Daily      Sunday/Saturday      Metric_Daily
Ac1      cups34      10/1/2014      n      20
Ac1      cups34      10/2/2014      n      20
Ac1      cups34      10/3/2014      n      20
Ac1      cups34      10/4/2014      Y      0
Ac1      cups34      10/5/2014      Y      0
Ac1      cups34      10/6/2014      n      20
Ac1      cups34      10/7/2014      n      20
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The problem you're going to have here is handling holidays where a day Monday-Friday is not a business day.  SQL Server has no function to get US Federal, New York City, Manchester United, or the Rick Springfield Fan Club of East Orange, New Jersey holidays, as they are wildly different and can change.

If you want to expand your question to 'How to I create a calendar table with dates so I can add my own custom holidays', then this article is a demo with code on how to pull that off, complete with logic on dealing with holidays such as 'The third Thursday in November'.

Then, in any given set you can just sum the Business Days column for total business days, count the Business Days column for total days, and the math will be correct.

Good luck
0
 
RayneAuthor Commented:
Hello Jim,

actually i don't need to handle any holidays which are in weekday ...so i am cool with not considering federal/national  holidays. I only need to consider Sunday/Saturday for the holiday consideration...
0
 
RayneAuthor Commented:
Hello Sharath,

This works awesome. Thank you. Can you explain whats happening here? I was not comprehending it deeply...

select [Week],st,[end] from a_WeekToStartEnd union all
select [Week],dateadd(d,1,st),[end] from a_WeekToStartEnd_CTE where dateadd(d,1,st) <= [end])
select t2.Account,t2.prodType,t1.st Dates_Daily,case when datepart(dw,st) in (1,7) then 0 else Metric/5 end Metric_Daily

Thank you
0
 
RayneAuthor Commented:
There is a bug in your code. It si distributing for the entire month when the weekly number for a given combination of account + ProdType + week

Account      prodType      Dates_Daily      Metric_Daily
Ac1      cups34      10/1/2014      20
Ac1      cups34      10/2/2014      20
Ac1      cups34      10/3/2014      20
Ac1      cups34      10/4/2014      0
Ac1      cups34      10/5/2014      0
Ac1      cups34      10/6/2014      20
Ac1      cups34      10/7/2014      20
Ac1      cups34      10/8/2014      20
Ac1      cups34      10/9/2014      20
Ac1      cups34      10/10/2014      20
Ac1      cups34      10/11/2014      0
Ac1      cups34      10/12/2014      0
Ac1      cups34      10/13/2014      20
Ac1      cups34      10/14/2014      20
Ac1      cups34      10/15/2014      20
Ac1      cups34      10/16/2014      20
Ac1      cups34      10/17/2014      20
Ac1      cups34      10/18/2014      0
Ac1      cups34      10/19/2014      0
Ac1      cups34      10/20/2014      20
Ac1      cups34      10/21/2014      20
Ac1      cups34      10/22/2014      20
Ac1      cups34      10/23/2014      20
Ac1      cups34      10/24/2014      20
Ac1      cups34      10/25/2014      0
Ac1      cups34      10/26/2014      0
Ac1      cups34      10/27/2014      20
Ac1      cups34      10/28/2014      20
Ac1      cups35      10/1/2014      40
Ac1      cups35      10/2/2014      40
Ac1      cups35      10/3/2014      40
Ac1      cups35      10/4/2014      0
Ac1      cups35      10/5/2014      0
Ac1      cups35      10/6/2014      40
Ac1      cups35      10/7/2014      40
Ac1      cups35      10/8/2014      40
Ac1      cups35      10/9/2014      40
Ac1      cups35      10/10/2014      40
Ac1      cups35      10/11/2014      0
Ac1      cups35      10/12/2014      0
Ac1      cups35      10/13/2014      40
Ac1      cups35      10/14/2014      40
Ac1      cups35      10/15/2014      40
Ac1      cups35      10/16/2014      40
Ac1      cups35      10/17/2014      40
Ac1      cups35      10/18/2014      0
Ac1      cups35      10/19/2014      0
Ac1      cups35      10/20/2014      40
Ac1      cups35      10/21/2014      40
Ac1      cups35      10/22/2014      40
Ac1      cups35      10/23/2014      40
Ac1      cups35      10/24/2014      40
Ac1      cups35      10/25/2014      0
Ac1      cups35      10/26/2014      0
Ac1      cups35      10/27/2014      40
Ac1      cups35      10/28/2014      40
0
 
RayneAuthor Commented:
Yeah, the actual 300 of weekly table is now totaling to 1200 in the daily table , so that's a issue
0
 
RayneAuthor Commented:
OK, lets scrap the a_weekTostartEnd !

what if I am referencing a calendar table that has all the dates (at a daily level) along with the week start and end date?
Will that make it any easier?

Date      Week      st      End
20141001      W1      10/1/2014      10/7/2014
20141002      W2      10/8/2014      10/14/2014
20141003      W3      10/15/2014      10/21/2014
20141004      W4      10/22/2014      10/28/2014
0
 
SharathData EngineerCommented:
Did you correct the test data in your table and try?

insert into [myDBName]..[a_WeekToStartEnd] values('W1','10/01/2014','10/07/2014')
insert into [myDBName]..[a_WeekToStartEnd] values('W1','10/08/2014','10/14/2014')
insert into [myDBName]..[a_WeekToStartEnd] values('W1','10/15/2014','10/21/2014')
insert into [myDBName]..[a_WeekToStartEnd] values('W1','10/22/2014','10/28/2014')
0
 
RayneAuthor Commented:
Thank you Sharath, you pointed that early but I didn't picked it up. This works like GOLD. Thank you. I will reward you the points for your briliance.
0
 
RayneAuthor Commented:
Sharath,
it would be greatly appreciated if you can explain whats happening here? if you don't mind...I was not sure about the code meaning clearly >>

select [Week],st,[end] from a_WeekToStartEnd union all
select [Week],dateadd(d,1,st),[end] from a_WeekToStartEnd_CTE where dateadd(d,1,st) <= [end])
select t2.Account,t2.prodType,t1.st Dates_Daily,case when datepart(dw,st) in (1,7) then 0 else Metric/5 end Metric_Daily


Thank you
0
 
SharathData EngineerCommented:
Glad I could help.
Sure, will explain when I get some time.
0
 
SharathData EngineerCommented:
;with a_WeekToStartEnd_CTE as (
select [Week],st,[end] from a_WeekToStartEnd union all
select [Week],dateadd(d,1,st),[end]
from a_WeekToStartEnd_CTE
-- a_WeekToStartEnd_CTE is a recursive CTE to generate extra records between Start date and End Date
where dateadd(d,1,st) <= [end]
-- This is the condition to generate the records between Start and End dates. I am incrementing start date by 1 day until it reached end date
)
select t2.Account,t2.prodType,t1.st Dates_Daily,
case when datepart(dw,st) in (1,7) then 0 else Metric/5 end Metric_Daily
-- The CASE condition to keep 0 for weekends and distribute the Metric for remaining 5 days
  from a_WeekToStartEnd_CTE t1
  join [a_Weekly_Number] t2 on t1.[Week] = t2.[Week]
  -- Once we have all dates from CTE, the CTE can be joined to a_Weekly_Number on Week column
  order by st
0
 
RayneAuthor Commented:
Thank you Sharath, this explains it crystal clear. Thank you :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.