Solved

SQL Help This- timing metric

Posted on 2014-10-12
16
178 Views
Last Modified: 2014-10-14
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
0
Comment
Question by:Rayne
  • 11
  • 4
16 Comments
 

Author Comment

by:Rayne
ID: 40375526
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
 

Author Comment

by:Rayne
ID: 40375529
so how do it distribute the weekly metric into single dated day look?
0
 

Author Comment

by:Rayne
ID: 40375538
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 65

Expert Comment

by:Jim Horn
ID: 40375758
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
 

Author Comment

by:Rayne
ID: 40375799
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
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 40376143
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
 

Author Comment

by:Rayne
ID: 40376219
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
 

Author Comment

by:Rayne
ID: 40376232
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
 

Author Comment

by:Rayne
ID: 40376237
Yeah, the actual 300 of weekly table is now totaling to 1200 in the daily table , so that's a issue
0
 

Author Comment

by:Rayne
ID: 40376252
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
 
LVL 40

Expert Comment

by:Sharath
ID: 40378931
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
 

Author Comment

by:Rayne
ID: 40379131
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
 

Author Comment

by:Rayne
ID: 40379132
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
 
LVL 40

Expert Comment

by:Sharath
ID: 40379189
Glad I could help.
Sure, will explain when I get some time.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 40379210
;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
 

Author Comment

by:Rayne
ID: 40379275
Thank you Sharath, this explains it crystal clear. Thank you :)
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

838 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