Best way to get Year To Date/Quarter to Date data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Best way to get Year To Date/Quarter to Date data

Author

Topic

saad.ahnad@gmail.com
Starting Member

15 Posts

Posted - 2007-10-12 : 13:22:54

Hello !!

Environment: SQLServer 2005 SP2

What is the best way to get YTD and QTD type of data in SQLServer 2005. I am using "cross apply" syntax - Is that the best (fastest) approach.

Data.
month, customer, sale
1/1/2007, CST1, 10
1/1/2007, CST1, 20
2/1/2007, CST1, 30
3/1/2007, CST2, 40
4/1/2007, CST2, 30
5/1/2007, CST1, 10

Output
month, customer, month_sale, YTD_sale
1/1/2007, CST1, 30, 30
2/1/2007, CST1, 30, 60
3/1/2007, CST2, 40, 40
4/1/2007, CST2, 30, 70
5/1/2007, CST1, 10, 100

select x.*, ca.ytd
from
(
select month, customer, sum(sale)
from
sale_date d
group by month, customer
) x
cross apply ( select ytd = sum(d1.sale)
from sale_date d1
where year(d1.month) = year(x.month)
) ca

DDLS for Reference
drop table saad_temp
select *
into saad_temp
from (
select cast('1/1/2006' as datetime) sale_date, 'CST1' cstnum, 50 sale
union all
select cast('2/1/2006' as datetime) sale_date, 'CST1' cstnum, 20 sale
union all
select cast('4/1/2006' as datetime) sale_date, 'CST2' cstnum, 20 sale
union all
select cast('1/1/2007' as datetime) sale_date, 'CST1' cstnum, 10 sale
union all
select cast('1/1/2007' as datetime) sale_date, 'CST1' cstnum, 20 sale
union all
select cast('2/1/2007' as datetime) sale_date, 'CST1' cstnum, 30 sale
union all
select cast('3/1/2007' as datetime) sale_date, 'CST2' cstnum, 40 sale
union all
select cast('4/1/2007' as datetime) sale_date, 'CST2' cstnum, 30 sale
union all
select cast('5/1/2007' as datetime) sale_date, 'CST1' cstnum, 10 sale
) x

--
Saad Ahmad
saad.ahmad@gmail.com

Kristen
Test

22859 Posts

Posted - 2007-10-12 : 13:25:16

I don't know if it specifically covers this, but MVJ has most date-wise things covered in his script here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-12 : 14:08:47

It is always the most efficient to do running totals at your client; where are outputting these results?

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources