Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Find YTD

Posted on 2014-11-13
6
Medium Priority
?
170 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

824 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