Solved

Find YTD

Posted on 2014-11-13
6
159 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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 53

Expert Comment

by:Huseyin KAHRAMAN
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
databases from multiple applications - same instance 6 40
When are cursors useful? 8 62
VM SQL server license. 1 67
relocating SQL 2000 18 39
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

710 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