?
Solved

SQL Help This- timing metric

Posted on 2014-10-12
16
Medium Priority
?
197 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 66

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 41

Accepted Solution

by:
Sharath earned 2000 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 41

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 41

Expert Comment

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

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
New style of hardware planning for Microsoft Exchange server.
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.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

764 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