June 27, 2014 at 10:52 am
My current sql uses the YEAR function to get the YEAR portion of a data column. I read an article in wikipedia about this not being a good practice, not sargable
I was wondering if someone could help me improve my query, especially in the WHERE clause: WHERE YEAR(cle.[Posting Date]) IN ('2013', '2014')
What I am trying to do is to use current year and previous year.
SELECT
[Customer No_]
,SUM(CASE YEAR(cle.[Posting Date])
WHEN '2013'
THEN [Sales (LCY)]
ELSE 0
END) AS low_yr
,SUM(CASE YEAR(cle.[Posting Date])
WHEN '2014'
THEN [Sales (LCY)]
ELSE 0
END) AS high_yr
FROM [NAV].[dbo].[CQC$Cust_ Ledger Entry] cle
WHERE YEAR(cle.[Posting Date]) IN ('2013', '2014')
GROUP BY
[Customer No_]
Thank you very much
June 27, 2014 at 11:26 am
First you don't need the quotes for your in, because year returns data type of int. (http://msdn.microsoft.com/en-us/library/ms186313.aspx) Using functions is not recommended in the where clause you could use date > '2013-01-01' and date <= '2014-12-31 23:59:59'. My best recommendation is to try a few things out and check out the execution plans to learn what is indeed the best solution for your situation.
June 27, 2014 at 12:07 pm
Here is a quick rewrite of the query:
SELECT
[Customer No_],
SUM(CASE YEAR(cle.[Posting Date])
WHEN '2013'
THEN [Sales (LCY)]
ELSE 0
END) AS low_yr,
SUM(CASE YEAR(cle.[Posting Date])
WHEN '2014'
THEN [Sales (LCY)]
ELSE 0
END) AS high_yr
FROM
[NAV].[dbo].[CQC$Cust_ Ledger Entry] cle
WHERE
--YEAR(cle.[Posting Date]) IN ('2013', '2014')
cle.[Posting Date] >= '20130101' and
cle.[Posting Date] < '20150101'
GROUP BY
[Customer No_]
June 27, 2014 at 12:10 pm
Sarah Wagner (6/27/2014)
First you don't need the quotes for your in, because year returns data type of int. (http://msdn.microsoft.com/en-us/library/ms186313.aspx) Using functions is not recommended in the where clause you could use date > '2013-01-01' and date <= '2014-12-31 23:59:59'. My best recommendation is to try a few things out and check out the execution plans to learn what is indeed the best solution for your situation.
There is nothing wrong with functions in the where clause. What you need to be careful of is when a column is one of the arguments to the function.
The logic you posted is close but it would be better if it was < '2015-01-01'. That accommodates every possible value. There are valid datetime values > 2014-12-31 23:59:59 that are still part of 2014. 😉
I suspect what the OP is really after is to get rows from the current year and last year. Here is how you can do that so you never have to change your code. It will always be the beginning of last year through the end of this year.
WHERE PostingDate > dateadd(yy, datediff(yy, 0, getdate()) - 1, 0)
and PostingDate < dateadd(yy, datediff(yy, 0, getdate()) + 1, 0)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2014 at 12:26 pm
Lynn Pettis (6/27/2014)
Here is a quick rewrite of the query:
SELECT
[Customer No_],
SUM(CASE YEAR(cle.[Posting Date])
-- WHEN '2013'
WHEN 2013
THEN [Sales (LCY)]
ELSE 0
END) AS low_yr,
SUM(CASE YEAR(cle.[Posting Date])
-- WHEN '2014'
WHEN 2014
THEN [Sales (LCY)]
ELSE 0
END) AS high_yr
FROM
[NAV].[dbo].[CQC$Cust_ Ledger Entry] cle
WHERE
--YEAR(cle.[Posting Date]) IN ('2013', '2014')
cle.[Posting Date] >= '20130101' and
cle.[Posting Date] < '20150101'
GROUP BY
[Customer No_]
A nitpick, but the YEAR function returns an int, so the code should be as above. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 27, 2014 at 1:06 pm
Is there a way that the year part of the case expression in the select can be done without using hard coded values for previous year, and current year?
,SUM(CASE YEAR(cle.[Posting Date])
WHEN 2013
THEN [Sales (LCY)]
ELSE 0
END) AS low_yr
,SUM(CASE YEAR(cle.[Posting Date])
WHEN 2014
THEN [Sales (LCY)]
ELSE 0
END) AS high_yr
I tried this way:
,SUM(CASE YEAR(cle.[Posting Date])
WHEN YEAR(dateadd(yy, datediff(yy, 0, getdate()) - 1, 0))
THEN [Sales (LCY)]
ELSE 0
END) AS low_yr
,SUM(CASE YEAR(cle.[Posting Date])
WHEN YEAR(DATEADD(YY, DATEDIFF(YY, 0, GETDATE()), 0))
THEN [Sales (LCY)]
ELSE 0
END) AS high_yr
But does not seem to work
June 27, 2014 at 1:12 pm
I tried it again:
SELECT
[Customer No_]
,SUM(CASE YEAR(cle.[Posting Date])
WHEN YEAR(dateadd(yy, datediff(yy, 0, getdate()) - 1, 0))
THEN [Sales (LCY)]
ELSE 0
END) AS low_yr
,SUM(CASE YEAR(cle.[Posting Date])
WHEN YEAR(DATEADD(YY, DATEDIFF(YY, 0, GETDATE()), 0))
THEN [Sales (LCY)]
ELSE 0
END) AS high_yr
FROM [NAV].[dbo].[CQC$Cust_ Ledger Entry] cle
WHERE
--YEAR(cle.[Posting Date]) IN ('2013', '2014')
cle.[Posting Date] > dateadd(yy, datediff(yy, 0, getdate()) - 1, 0)
AND
cle.[Posting Date] < dateadd(yy, datediff(yy, 0, getdate()) + 1, 0)
GROUP BY
[Customer No_]
It seems it works this way
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply