Find YTD

VBdotnet2005
VBdotnet2005 used Ask the Experts™
on
How can I find out Year to Date of Quantiy and SalesTotal in SQL for current year and 2013? I am a sample below .
It should returns like

YTD, YTD of 2013, YTD 2014 VS YTD 2013


Data sample
Sales & quantity for 2013

Month  Quantity   Sales
Jan             5           $100
Feb          20          $2000
Mar         15           $1500
etc...

Sales & quantity for 2014

Month  Quantity   Sales
Jan          10           $200
Feb          20          $400
Mar         30           $600
etc... up to today's date (Nov 13, 2014)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Koen Van WielinkBusiness Intelligence Specialist

Commented:
Is your month column in this string format, or is it a numerical field in the database? Please provide your table definitions if possible, data types are important with these things.

Author

Commented:
It is numerical field.

Author

Commented:
It is a date type in sql. On my grid view I just display name of month that is all.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Business Intelligence Specialist
Commented:
Then next time please post accordingly. We can help you most effectively if we know what raw data you're working with.
Try this:

Create table #sales
(	SaleDate date
,	Qty	int
,	Sales numeric(10,2)
)

insert into #sales

values	('20130101', 5, 100)
	,	('20130201', 20, 2000)
	,	('20130301', 15, 1500)
	,	('20131201', 25, 2500)
	,	('20140101', 5, 100)
	,	('20140201', 10, 1000)
	,	('20140301', 7, 7000)
	,	('20141101', 8, 8000)

select		SUM(case
				when YEAR(SaleDate) = YEAR(getdate()) - 1
				and (MONTH(saleDate) < MONTH(getdate())
				or	(MONTH(saleDate) = MONTH (GETDATE())
				and DAY(saleDate) <= DAY(getdate())))
				Then Qty
				else 0
			end) as 'Qty_last_year_YTD'
		,	SUM(case
				when YEAR(SaleDate) = YEAR(getdate()) - 1
				and (MONTH(saleDate) < MONTH(getdate())
				or	(MONTH(saleDate) = MONTH (GETDATE())
				and DAY(saleDate) <= DAY(getdate())))
				Then sales
				else 0
			end) as 'Sales_last_year_YTD'
		,	SUM(case
				when YEAR(SaleDate) = YEAR(getdate())
				and (MONTH(saleDate) < MONTH(getdate())
				or	(MONTH(saleDate) = MONTH (GETDATE())
				and DAY(saleDate) <= DAY(getdate())))
				Then Qty
				else 0
			end) as 'Qty_current_year_YTD'
		,	SUM(case
				when YEAR(SaleDate) = YEAR(getdate())
				and (MONTH(saleDate) < MONTH(getdate())
				or	(MONTH(saleDate) = MONTH (GETDATE())
				and DAY(saleDate) <= DAY(getdate())))
				Then Sales
				else 0
			end) as 'Sales_current_year_YTD'
		,	SUM(case
				when YEAR(SaleDate) = YEAR(getdate()) - 1
				Then Qty
				else 0
			end) as 'Qty_last_year'
		,	SUM(case
				when YEAR(SaleDate) = YEAR(getdate()) - 1
				Then Sales
				else 0
			end) as 'Sales_last_year'
from	#sales

drop table #sales

Open in new window


I've made it a bit more generic, using last year and current year rather than hardcoding 2013 and 2014. That way it'll still be valid come January 1st.
HainKurtSr. System Analyst

Commented:
what is your data (give a sample), and what you need as a result (also post it here)
Valliappan ANSenior Tech Consultant

Commented:
declare @now smalldatetime

//set @now = getdate()  -- for 2014 ytd

set @now = dateadd(yy,-1,getdate())  --for 2013 ytd

select @now, dateadd(yy, DATEDIFF(yy, 0, @now), 0)

SELECT SUM(sales) FROM SalesTable
WHERE salesdate BETWEEN dateadd(yy, DATEDIFF(yy, 0, @now), 0) AND @now

HTH.

(refer: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c589041e-8971-4f64-93bc-ac370f14b2c0/year-to-date-quarter-to-date-and-months-to-date-sum-in-single-query?forum=transactsql)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial