SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TSQL to join two tables to calculate the total based on the date range


TSQL to join two tables to calculate the total based on the date range

Author
Message
xXShanXx
xXShanXx
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 601
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
Ray M
Ray M
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2187 Visits: 1076
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
xXShanXx
xXShanXx
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 601
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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26323 Visits: 17553
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 Modens 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)
xXShanXx
xXShanXx
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 601
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26323 Visits: 17553
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. :-P

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 Modens 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)
xXShanXx
xXShanXx
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 601
Hi

I will have many rows on the records table.
What if if i want to pass 2 dates ie like between two dates.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26323 Visits: 17553
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 Modens 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)
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16715 Visits: 19109
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26323 Visits: 17553
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 Modens 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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search