Solved

Find YTD

Posted on 2014-11-13
6
162 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 13

Accepted Solution

by:
Koen Van Wielink earned 500 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 56

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.

626 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