Year to Today sales Figure

Question

Post reply

Year to Today sales Figure

Mathew M.Varghese

SSCommitted

Points: 1929
More actions
November 1, 2010 at 11:59 pm

#388143

Hi All,
I need to calcualte the Year to Today sales details from last 4 years
Is any simple sql statement help me to do it.
Expected output like this way
2007 1500
2008 1300
2009 1699
2010 1400
I use following sql stmts:
select YEAR(Sale_date),count(*) from Sale_Fact
where month("Sale_Fact "."Sale_date") <= month(getdate())
and day("Sale_Fact "."Sale_date") <= day(getdate())
Group by YEAR(Sale_date)
Any method to get all days total from the start year to today in all years?

Viewing 7 posts - 1 through 6 (of 6 total)

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

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 1

You mean some kind of running total?

N 56°04'39.16"
E 12°55'05.25"

Mathew M.Varghese SSCommitted Points: 1929 More actions · Answer 2

hi,

Running total from Jan 1 to Today date( jan 1 to Nov 2).

Mathew

WayneS SSC Guru Points: 95410 More actions · Answer 3

Is this what you're talking about?

-- declare and initialize variables

DECLARE @StartDate datetime,

@EndDate datetime;

-- get first of year three years ago

SET @StartDate = dateadd(year, DateDiff(year, 0, GetDate())-3, 0)

-- get tomorrows date

SET @EndDate = DateAdd(day, DateDiff(day, 0, GetDate()), 1)

SELECT [year] = YEAR(Sale_date),

Qty = count(*)

FROM Sale_Fact

WHERE Sale_Fact.Sale_date >= @StartDate

AND Sale_Fact.Sale_Date < @EndDate

GROUP BY YEAR(Sale_date)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Mathew M.Varghese SSCommitted Points: 1929 More actions · Answer 4

Hi,

I need every year Jan to Nov 2

Like 2007 = Jan to Nov2

2008 = Jan to Nov2

2009= Jan to Nov2

2010 = Jan to Nov2

Now it returned all the record from pervious years and record from this year jan to today.

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 5

SELECTDATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - d.Delta, 0) AS StartOfYear,

DATEADD(MONTH, 12 * DATEPART(YEAR, GETDATE()) - 22790 - 12 * d.Delta, 1) AS EndOfYear

FROM(

SELECT0 UNION ALL

SELECT1 UNION ALL

SELECT2 UNION ALL

SELECT3

) AS d(delta)

ORDER BYd.Delta DESC

N 56°04'39.16"
E 12°55'05.25"

tfifield SSCrazy Eights Points: 9655 More actions · Answer 6

Mathew,

I would use a calendar table to do all of the heavy lifting on this type of query - especially if you have to do this each month. I have nothing against being able to use complicated date functions in SQL Server and you should be able to do them. But just because you can doesn't mean you should.

Here is my first article on calendar tables:

http://www.sqlservercentral.com/articles/T-SQL/70482/

Create the calendar table from the article. I would actually add another column to the table:

YearNum INT

and populate it with the year.

Then you could use a query like:

SELECT CM.YearNum, CM.MonthNum, CM.MonthDescr

, COUNT(*) AS NumSales, SUM(SF.SaleAmt) AS Sales

FROM Sale_Fact SF

INNER JOIN CalMonth CM ON

SF.Sale_date BETWEEN CM.MonthStart AND CM.MonthEnd

WHERE CM.YearNum BETWEEN 2007 AND 2010

AND CM.MonthNum BETWEEN 1 AND 11

GROUP BY CM.YearNum, CM.MonthNum, CM.MonthDescr

ORDER BY CM.YearNum, CM.MonthNum

I had to assume you wanted some sale amount as well as the number of sales.

You could also do a pivot that would return 1 row for each year and the number of sales and sales amount for each month. Something like

SELECT CM.YearNum

, SUM(CASE WHEN CM.MonthNum = 1 THEN 1 ELSE 0 END) AS JanOrders

, SUM(CASE WHEN CM.MonthNum = 1 THEN SF.SaleAmt

ELSE 0 END) AS JanSales

, SUM(CASE WHEN CM.MonthNum = 2 THEN 1 ELSE 0 END) AS FebOrders

, SUM(CASE WHEN CM.MonthNum = 2 THEN SF.SaleAmt

ELSE 0 END) AS FebSales

FROM Sale_Fact SF

INNER JOIN CalMonth CM ON

SF.Sale_date BETWEEN CM.MonthStart AND CM.MonthEnd

WHERE CM.YearNum BETWEEN 2007 AND 2010

AND CM.MonthNum BETWEEN 1 AND 11

GROUP BY CM.YearNum

ORDER BY CM.YearNum

Something like this should make it easier to keep on adding another month when it needs to be run.

Todd Fifield