Find YTD
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)
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)
Koen Van Wielink
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.
ASKER
It is numerical field.
ASKER
It is a date type in sql. On my grid view I just display name of month that is all.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
what is your data (give a sample), and what you need as a result (also post it here)
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)
//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)