[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Find YTD

Posted on 2014-11-13
6
Medium Priority
?
171 Views
Last Modified: 2014-12-03
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)
0
Comment
Question by:VBdotnet2005
6 Comments
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40441708
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.
0
 

Author Comment

by:VBdotnet2005
ID: 40441714
It is numerical field.
0
 

Author Comment

by:VBdotnet2005
ID: 40441721
It is a date type in sql. On my grid view I just display name of month that is all.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
LVL 13

Accepted Solution

by:
Koen Van Wielink earned 2000 total points
ID: 40441735
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.
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 40441840
what is your data (give a sample), and what you need as a result (also post it here)
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 40445831
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)
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question