Solved

SQL Help This- timing metric

Posted on 2014-10-12
16
174 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Finding a closest match in Excel 7 47
insert wont work in SQL 14 22
Show Results for Latest DateTime in a View 27 25
Query to capture 5 and 9 digit zip code? 4 20
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

777 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