Year to Date Query ... By month?

Question

Year to Date Query ... By month?

cs_source

Default port

Points: 1459
More actions
August 20, 2013 at 7:44 am

#276298

Hi,
I have a table which currently has a month and a number by month representing lets say car sales,
Table Car
Month - Date
Sales - Int
I would like to get a Year To Date formula going which i understand how to do for one value for the most recent month
Sum(Sales) from Car Where Month >= '2012-04-01'
however its getting complicated because i need to show the year to date data by month, so i need to create some sort of loop to go though every month?
Example:
April 2012 - #=5 YTD=5
May 2012 - #=10 YTD=15
June 2012 - #=2 YTD=17
any help is greatly appreciated

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Nope you don't need a loop for this at all. You will need a tally table. If you can post ddl and some sample data I will be happy to help. Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

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/

ScottPletcher SSC Guru Points: 101046 More actions · Answer 2

Here's one way:

SELECT

DATEADD(MONTH, DATEDIFF(MONTH, 0, c.month), 0) AS Month,

SUM(c.sales) AS Month_Sales

INTO #Monthly_Totals

FROM dbo.Car c

WHERE

c.month >= '20120401' AND --chg as needed

c.month < '20130101' --chg as needed

GROUP BY

DATEADD(MONTH, DATEDIFF(MONTH, 0, c.month), 0)

SELECT

DATENAME(MONTH, Month) + SPACE(1) + DATENAME(YEAR, Month) AS Month,

Month_Sales,

(SELECT SUM(month_sales) FROM #Monthly_Totals mt2 WHERE mt2.Month <= mt.Month) AS YTD_Sales

FROM #Monthly_Totals mt

ORDER BY

mt.Month

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Luis Cazares SSC Guru Points: 183701 More actions · Answer 3

No need for a loop, you can change your condition to your columns instead of the whole query. Here's one example, but to get it working you need to provide more information as Sean said previously.

SELECT Sum(CASE WHEN Month >= '2012-04-01' THEN Sales ELSE 0 END)

from Car

Where Month BETWEEN @StartDate AND @EndDate

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

cs_source Default port Points: 1459 More actions · Answer 4

Hey all thank you for all the replies, i used the following code to automate this query going forward, in the mean time for historical data i needed to though and run each month.

declare @start as date

declare @end as date

set @start = '2013-04-01'

set @end = (select MAX(date) from CAR)

update CAR set Year_To_Date =

(select SUM(Sales) from CAR

where date >= @start ) where CAR.Date=@end

i was originally thinking id need to join one of my dimension tables and count back from each month until the start of a quarter etc etc but this works great thanks guys! 😎