October 15, 2012 at 11:18 am
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
October 15, 2012 at 11:27 am
October 15, 2012 at 11:28 am
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.
October 15, 2012 at 12:36 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 15, 2012 at 2:27 pm
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
October 15, 2012 at 2:35 pm
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 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 15, 2012 at 3:44 pm
Hi
I will have many rows on the records table.
What if if i want to pass 2 dates ie like between two dates.
October 15, 2012 at 3:49 pm
xXShanXx (10/15/2012)
HiI 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 15, 2012 at 4:56 pm
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
October 15, 2012 at 8:47 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 15, 2012 at 9:59 pm
But it gives different results, Sean. Because your query will affect how the join is made and will generate duplicates. That's why you need to use separate conditions for the joins and the filter. I made a mistake in the GROUP BY, but I will correct it right now.
You can check it yourself with the sample data you posted, but I hope Shan tests it as well.
October 16, 2012 at 7:20 am
Luis Cazares (10/15/2012)
But it gives different results, Sean. Because your query will affect how the join is made and will generate duplicates. That's why you need to use separate conditions for the joins and the filter. I made a mistake in the GROUP BY, but I will correct it right now.You can check it yourself with the sample data you posted, but I hope Shan tests it as well.
I see that you added a calculation but the code I provided does not produce duplicates. There are two rows for USA and he wants both of them because of the date range. I guess it really boils down to clarification about what the OP really wants. 🙂
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 16, 2012 at 7:24 am
Sean Lange (10/16/2012)
Luis Cazares (10/15/2012)
But it gives different results, Sean. Because your query will affect how the join is made and will generate duplicates. That's why you need to use separate conditions for the joins and the filter. I made a mistake in the GROUP BY, but I will correct it right now.You can check it yourself with the sample data you posted, but I hope Shan tests it as well.
I see that you added a calculation but the code I provided does not produce duplicates. There are two rows for USA and he wants both of them because of the date range. I guess it really boils down to clarification about what the OP really wants. 🙂
But with the date range you established in the parameters, only one row should be used. Check the hours.;-)
October 16, 2012 at 7:32 am
Hi Luis and Sean
Thanks a lot for your advise.
I tested Luis code it was working fine and I get the expected results.
Sorry I don't understand what duplicates will i be getting. I used this below query and it works fine.
But it runs for a long time, because the Records table has around 500K records per day.
Can anyone advise me is there any way to speed up the process.
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
October 16, 2012 at 7:53 am
This might work, but an index might help even more (if you don't have one). I won't give my advice on the index because I'm learning as well about the best way to design them.:-D Maybe a more experienced one can help you.
DECLARE @FromDate datetime = '20121013', @ToDate datetime = '20121015'
SELECT rc.Country, SUM(r.CostPerSec*rc.Duration)
FROM #Rates r
JOIN (SELECT rec.Date, rec.country, rec.Duration
FROM #Records rec
WHERE rc.Date >= @FromDate
AND rc.Date <=@ToDate) rc ON rc.Date >= r.FromDate
AND rc.Date <= isnull(r.ToDate, rc.Date)
AND r.Country = rc.Country
GROUP BY rc.Country
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply