Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

TSQL to join two tables to calculate the total based on the date range Expand / Collapse
Author
Message
Posted Monday, October 15, 2012 11:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 18, 2014 5:44 AM
Points: 118, Visits: 478
Hi

I have 2 tables Rates and Records.

RATES table

country fromdate todate costpersec
=======================================================================
USA 2012-10-10 00:00:00.000 2012-10-14 23:59:59.000 0.01
USA 2012-10-15 00:00:00.000 NULL 0.02

RECORDS table

Country date duration(sec)
=========================================================

USA 2012-10-14 10:00:00.000 10
USA 2012-10-15 20:00:00.000 10



Country TotalCost
===================
USA 0.3
Post #1372841
Posted Monday, October 15, 2012 11:27 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 8:50 AM
Points: 1,486, Visits: 1,037
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-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 18, 2014 5:44 AM
Points: 118, Visits: 478
Hi All

I 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 table

country fromdate todate costpersec
=======================================================================
USA 2012-10-10 00:00:00.000 2012-10-14 23:59:59.000 0.01
USA 2012-10-15 00:00:00.000 NULL 0.02
UK 2012-10-13 00:00:00.000 NULL 0.02

RECORDS table

Country date duration(sec)
===========================================

USA 2012-10-14 10:00:00.000 10
USA 2012-10-15 20:00:00.000 10
UK 2012-10-13 20:00:00.000 10


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.3
UK 0.2


Thanks a lot in advance.
Post #1372847
Posted Monday, October 15, 2012 12:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
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 #Rates
select 'USA', '2012-10-10 00:00:00.000', '2012-10-14 23:59:59.000', 0.01 union all
select 'USA', '2012-10-15 00:00:00.000', NULL, 0.02 union all
select 'UK', '2012-10-13 00:00:00.000', NULL, 0.02

insert #Records
select 'USA', '2012-10-14 10:00:00.000', 10 union all
select 'USA', '2012-10-15 20:00:00.000', 10 union all
select 'UK', '2012-10-13 20:00:00.000', 10


select * from #Rates
select * from #Records


drop table #Rates
drop 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.3
UK 0.2


What 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-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 18, 2014 5:44 AM
Points: 118, Visits: 478
HI Sean Lange

Sorry - 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 TotalCost
from rates x join records y on x.country = y.country
and y.date between x.FromDate and y.todate
GROUP BY y.country

I 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.3

I hope i made it clear now, Please let me

Thanks
Post #1372938
Posted Monday, October 15, 2012 2:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
xXShanXx (10/15/2012)
HI Sean Lange

Sorry - 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 TotalCost
from rates x join records y on x.country = y.country
and y.date between x.FromDate and y.todate
GROUP BY y.country

I 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.3

I hope i made it clear now, Please let me

Thanks


Oh 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 r
join #Records rc on rc.Date >= r.FromDate and rc.Date <= isnull(r.ToDate, rc.Date) and r.Country = rc.Country
group 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-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 18, 2014 5:44 AM
Points: 118, Visits: 478
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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
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 r
join #Records rc on rc.Date >= @FromDate and rc.Date <= isnull(@ToDate, rc.Date) and r.Country = rc.Country
group 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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:23 PM
Points: 4,049, Visits: 9,214
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 r
JOIN #Records rc ON rc.Date >= r.FromDate
AND rc.Date <= isnull(r.ToDate, rc.Date)
AND r.Country = rc.Country
WHERE rc.Date >= @FromDate
AND rc.Date <=@ToDate
GROUP BY rc.Country




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
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 r
JOIN #Records rc ON rc.Date >= r.FromDate
AND rc.Date <= isnull(r.ToDate, rc.Date)
AND r.Country = rc.Country
WHERE rc.Date >= @FromDate
AND rc.Date <=@ToDate
GROUP 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
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse