Solved

SQL Help This- timing metric

Posted on 2014-10-12
16
163 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
Comment Utility
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
Comment Utility
so how do it distribute the weekly metric into single dated day look?
0
 

Author Comment

by:Rayne
Comment Utility
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
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:Rayne
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Glad I could help.
Sure, will explain when I get some time.
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
;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
Comment Utility
Thank you Sharath, this explains it crystal clear. Thank you :)
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Viewers will learn how the fundamental information of how to create a table.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now