Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 TSQL to join two tables to calculate the total based on the date range Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, October 15, 2012 11:18 AM
 SSC Journeyman Group: General Forum Members Last Login: Tuesday, October 22, 2013 9:55 AM Points: 78, Visits: 279
 Hi I have 2 tables Rates and Records. RATES tablecountry fromdate todate costpersec=======================================================================USA 2012-10-10 00:00:00.000 2012-10-14 23:59:59.000 0.01USA 2012-10-15 00:00:00.000 NULL 0.02RECORDS tableCountry date duration(sec)=========================================================USA 2012-10-14 10:00:00.000 10USA 2012-10-15 20:00:00.000 10Country TotalCost===================USA 0.3
Post #1372841
 Posted Monday, October 15, 2012 11:27 AM
 UDP Broadcaster Group: General Forum Members Last Login: Yesterday @ 10:01 AM Points: 1,475, Visits: 1,011
 What exactly are you trying to do here?Can you please post better data, DDL and DML statements. You'll get more help if you follow the suggestions in this Post www.sqlservercentral.com/articles/Best+Practices.Also Post what you have tried, and we can go from there
Post #1372846
 Posted Monday, October 15, 2012 11:28 AM
 SSC Journeyman Group: General Forum Members Last Login: Tuesday, October 22, 2013 9:55 AM Points: 78, Visits: 279
 Hi AllI need your help Im not much good in Tsql.I have 2 tables Rates and Records, I need to calculate the total cost for each country picking the rates based on the from and to dates on the RATE table. RATES tablecountry fromdate todate costpersec=======================================================================USA 2012-10-10 00:00:00.000 2012-10-14 23:59:59.000 0.01USA 2012-10-15 00:00:00.000 NULL 0.02UK 2012-10-13 00:00:00.000 NULL 0.02RECORDS tableCountry date duration(sec)===========================================USA 2012-10-14 10:00:00.000 10USA 2012-10-15 20:00:00.000 10UK 2012-10-13 20:00:00.000 10I want to calculate the TotalCost between '2012-10-10 00:00:00.000' and '2012-10-16 00:00:00.000 'So that picks up 0.01 rate between 10th and 14th and form 15th it should pick 0.02 and calculate the total.Country TotalCost===================USA 0.3UK 0.2Thanks a lot in advance.
Post #1372847
 Posted Monday, October 15, 2012 12:36 PM
 SSChampion Group: General Forum Members Last Login: Today @ 3:16 PM Points: 10,876, Visits: 10,037
 Unfortunately just re-posting the same "table" is not much help. We need to able to run queries against this stuff to help. It seems you did not read the article that was previously suggested.I think your ddl and dml might look like this:`create table #Rates( Country varchar(3), FromDate datetime, ToDate datetime, CostPerSec numeric(5,2))create table #Records( Country varchar(3), Date datetime, Duration int)insert #Ratesselect 'USA', '2012-10-10 00:00:00.000', '2012-10-14 23:59:59.000', 0.01 union allselect 'USA', '2012-10-15 00:00:00.000', NULL, 0.02 union allselect 'UK', '2012-10-13 00:00:00.000', NULL, 0.02insert #Recordsselect 'USA', '2012-10-14 10:00:00.000', 10 union allselect 'USA', '2012-10-15 20:00:00.000', 10 union allselect 'UK', '2012-10-13 20:00:00.000', 10select * from #Ratesselect * from #Recordsdrop table #Ratesdrop table #Records`The bigger challenge is that it is totally unclear what you want for desired output.I want to calculate the TotalCost between '2012-10-10 00:00:00.000' and '2012-10-16 00:00:00.000 'So that picks up 0.01 rate between 10th and 14th and form 15th it should pick 0.02 and calculate the total.Country TotalCost===================USA 0.3UK 0.2What does that mean? Is the total cost supposed to be the sum of all CostPerSec values that are valid during any of the Date values in #Records?You do seem to have some major issues in your tables. You have nothing that can be a primary key and your naming convention is a bit too vague. A table called Records or Rates is unclear. Hopefully your real table names have better names. Also, you should avoid reserved words as object names. Date is not a good name for a column. Not only is it a reserved word it gives no indication what it is. If you can answer the question behind the logic this should be pretty simple. _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1372889
 Posted Monday, October 15, 2012 2:27 PM
 SSC Journeyman Group: General Forum Members Last Login: Tuesday, October 22, 2013 9:55 AM Points: 78, Visits: 279
 HI Sean LangeSorry - I didnt check the post before. Thanks for your advise. I will follow it hereafter and these are not real table names and are with fake values.I need to join the 2 tables on country field and calculate the total cost.im using this below query which is wrong, I need to pass 2 dates from and to date in the query, so it will calculate the actual cost based on the date range.On the RatesTable the when the ToDate field has NULL entry - its refers till date.select y.country , SUM(x.costpersec * y.duration) as TotalCostfrom rates x join records y on x.country = y.countryand y.date between x.FromDate and y.todateGROUP BY y.countryI need to calculate the total cost grouping by countries. Eg, If i want to know the total cost for USA between Oct 12 and Oct 16 then It should process (10*0.01) (14th) + (10 *0.02 ) (15th) = 0.3I hope i made it clear now, Please let me Thanks
Post #1372938
 Posted Monday, October 15, 2012 2:35 PM
 SSChampion Group: General Forum Members Last Login: Today @ 3:16 PM Points: 10,876, Visits: 10,037
 xXShanXx (10/15/2012)HI Sean LangeSorry - I didnt check the post before. Thanks for your advise. I will follow it hereafter and these are not real table names and are with fake values.I need to join the 2 tables on country field and calculate the total cost.im using this below query which is wrong, I need to pass 2 dates from and to date in the query, so it will calculate the actual cost based on the date range.On the RatesTable the when the ToDate field has NULL entry - its refers till date.select y.country , SUM(x.costpersec * y.duration) as TotalCostfrom rates x join records y on x.country = y.countryand y.date between x.FromDate and y.todateGROUP BY y.countryI need to calculate the total cost grouping by countries. Eg, If i want to know the total cost for USA between Oct 12 and Oct 16 then It should process (10*0.01) (14th) + (10 *0.02 ) (15th) = 0.3I hope i made it clear now, Please let me ThanksOh good. Glad they are fake names and tables to protect the innocent. Thanks for the clarification. This works at least for your sample data.`select r.Country, SUM(CostPerSec)from #Rates rjoin #Records rc on rc.Date >= r.FromDate and rc.Date <= isnull(r.ToDate, rc.Date) and r.Country = rc.Countrygroup by r.Country` _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1372941
 Posted Monday, October 15, 2012 3:44 PM
 SSC Journeyman Group: General Forum Members Last Login: Tuesday, October 22, 2013 9:55 AM Points: 78, Visits: 279
 Hi I will have many rows on the records table.What if if i want to pass 2 dates ie like between two dates.
Post #1372974
 Posted Monday, October 15, 2012 3:49 PM
 SSChampion Group: General Forum Members Last Login: Today @ 3:16 PM Points: 10,876, Visits: 10,037
 xXShanXx (10/15/2012)Hi I will have many rows on the records table.What if if i want to pass 2 dates ie like between two dates.Just use your parameters in the condition instead of the row values.`declare @FromDate datetime = '20121013', @ToDate datetime = '20121015'select r.Country, SUM(CostPerSec)from #Rates rjoin #Records rc on rc.Date >= @FromDate and rc.Date <= isnull(@ToDate, rc.Date) and r.Country = rc.Countrygroup by r.Country` _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1372976
 Posted Monday, October 15, 2012 4:56 PM
 SSCommitted Group: General Forum Members Last Login: Today @ 6:00 PM Points: 1,896, Visits: 4,199
 Sean, for what I understood, he might need to use the original query and filter the rows with a where clause.`DECLARE @FromDate datetime = '20121013', @ToDate datetime = '20121015'SELECT rc.Country, SUM(r.CostPerSec*rc.Duration)FROM #Rates rJOIN #Records rc ON rc.Date >= r.FromDate AND rc.Date <= isnull(r.ToDate, rc.Date) AND r.Country = rc.CountryWHERE rc.Date >= @FromDate AND rc.Date <=@ToDateGROUP BY rc.Country` Luis C.Please don't trust me, test the solutions I give you before using them.Forum Etiquette: How to post data/code on a forum to get the best help
Post #1372991
 Posted Monday, October 15, 2012 8:47 PM
 SSChampion Group: General Forum Members Last Login: Today @ 3:16 PM Points: 10,876, Visits: 10,037
 Luis Cazares (10/15/2012)Sean, for what I understood, he might need to use the original query and filter the rows with a where clause.`DECLARE @FromDate datetime = '20121013', @ToDate datetime = '20121015'SELECT rc.Country, SUM(r.CostPerSec*rc.Duration)FROM #Rates rJOIN #Records rc ON rc.Date >= r.FromDate AND rc.Date <= isnull(r.ToDate, rc.Date) AND r.Country = rc.CountryWHERE rc.Date >= @FromDate AND rc.Date <=@ToDateGROUP BY r.Country`Unless I am misreading your code, that would actually return almost the same thing, except it won't handle the null for r.ToDate. It is the same because the date checks are in the join condition. _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1373016

 Permissions