March 2, 2010 at 1:01 pm
I have the query below. Everything works right except for the CASE section at the end. I think I really screwed this part up. I am trying to check a temp table to see if it contains a certain year or years.
The temp table(@TempList) is just a one column table containing a year in each row. The table can contain the values 2005-2013, but since it's a temp table, and generated dynamically, it can contain a mix of these years, all, or none of them.
So in my query, I need to filter by fDateTimeStart for every year contained in the temp table. So I figured I could use a CASE statement it's throwing errors.
Does this section even look right?
SELECT *
FROM cElements A
LEFT JOIN sectionList D ON D.sectionID = A.sectionID
LEFT JOIN fList E ON E.fID = D.fID
WHERE 1 = 1
AND (CASE WHEN '2006' IN(SELECT acYears = yearID FROM @TempList) THEN E.fDateTimeStart BETWEEN '2006-01-01' AND '2007-11-15' END
OR CASE WHEN '2007' IN(SELECT acYears = yearID FROM @TempList) THEN E.fDateTimeStart BETWEEN '2007-01-01' AND '2008-11-15' END
OR CASE WHEN '2008' IN(SELECT acYears = yearID FROM @TempList) THEN E.fDateTimeStart BETWEEN '2008-01-01' AND '2009-11-15' END
OR CASE WHEN '2009' IN(SELECT acYears = yearID FROM @TempList) THEN E.fDateTimeStart BETWEEN '2009-01-01' AND '2010-11-15' END
OR CASE WHEN '2010' IN(SELECT acYears = yearID FROM @TempList) THEN E.fDateTimeStart BETWEEN '2010-01-01' AND '2011-11-15' END
OR CASE WHEN '2011' IN(SELECT acYears = yearID FROM @TempList) THEN E.fDateTimeStart BETWEEN '2011-01-01' AND '2012-11-15' END
OR CASE WHEN '2012' IN(SELECT acYears = yearID FROM @TempList) THEN E.fDateTimeStart BETWEEN '2012-01-01' AND '2013-11-15' END
OR CASE WHEN '2013' IN(SELECT acYears = yearID FROM @TempList) THEN E.fDateTimeStart BETWEEN '2013-01-01' AND '2014-11-15' END
)
ORDER BY fDateTimeStart
Thanks!
March 2, 2010 at 1:11 pm
What exactly are you trying to do here?
Your "CASE WHEN '2006'..." (and all other similar case statements) don't really make sense. What variable/column is supposed to have a value of '2006' to make your CASE condition true?
Other than that: I'm sure there's a much easier solution to what you're trying to do.
If you'd explain a little more what you're expected result will be we might be able to help you.
March 2, 2010 at 2:02 pm
It is an ugly query...I know
What I need to do is, for every year in the temp table, I need to add a filter to the query. For example, if the years 2006, 2007 and 2009 are in the temp table, then I need this:
AND (E.fDateTimeStart BETWEEN '2006-01-01' AND '2007-11-15'
OR E.fDateTimeStart BETWEEN '2007-01-01' AND '2008-11-15'
OR E.fDateTimeStart BETWEEN '2009-01-01' AND '2010-11-15'
) --the fDateTimeStart can between this date, or this date, or this date, etc...
That's why I had all the case statements. I was trying to check to see if every possible year is in the temp table.
In English it would be like:
Is 2006 in the temp table? Then add this to the query....
Is 2007 in the temp table? Then also add this to the query...
Is 2008 in the temp table? Then also add this to the query...etc...etc...
Thanks!
March 2, 2010 at 2:24 pm
Magy (3/2/2010)
It is an ugly query...I knowIn English it would be like:
Is 2006 in the temp table? Then add this to the query....
Is 2007 in the temp table? Then also add this to the query...
Is 2008 in the temp table? Then also add this to the query...etc...etc...
Thanks!
In SQL Server it should be like:
Join the temp table to the rest of the query on an expression which creates a match between the two. Something like this:
SELECT *
FROM cElements A
LEFT JOIN sectionList D ON D.sectionID = A.sectionID
LEFT JOIN fList E ON E.fID = D.fID
INNER JOIN @TempList tl
ON E.fDateTimeStart BETWEEN (tl.yearID + '-01-01') AND (CAST(CAST(tl.yearID AS INT)+1 AS CHAR(4))) + '-11-15')
Edit: - which of course would be much more efficient if @TempList contained the upper and lower bound for each year selection, rather than the year alone, to match fDateTimeStart on data type.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 2, 2010 at 2:26 pm
Why not joining the temp table directly to the query?
Something like
SELECT *
FROM cElements A
LEFT JOIN sectionList D ON D.sectionID = A.sectionID
LEFT JOIN fList E ON E.fID = D.fID
INNER JOIN @TempList T ON
E.fDateTimeStart > = CAST(t.col + '0101' AS DATETIME)
AND E.fDateTimeStart < DATEADD(year,1,CAST(t.col + '0101' AS DATETIME))
Side note:
If you'd change the column type of your temp table to DATETIME data type holding
a value like '2010-01-01 00:00:00.000' you wouldn't have to use all those casting stuff...
March 2, 2010 at 3:27 pm
lmu92 (3/2/2010)
Why not joining the temp table directly to the query?Something like
SELECT *FROM cElements A
LEFT JOIN sectionList D ON D.sectionID = A.sectionID
LEFT JOIN fList E ON E.fID = D.fID
INNER JOIN @TempList T ON
E.fDateTimeStart > = CAST(t.col + '0101' AS DATETIME)
AND E.fDateTimeStart < DATEADD(year,1,CAST(t.col + '0101' AS DATETIME))
Side note:
If you'd change the column type of your temp table to DATETIME data type holding
a value like '2010-01-01 00:00:00.000' you wouldn't have to use all those casting stuff...
Lutz' date arithmetic is easier to implement correctly than the method in my post.
How about setting up some sample tables & data for folks to play with? You're far more likely to get an off-the-shelf working solution. See the links at the bottom of the post above.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 2, 2010 at 10:06 pm
Check if this works..
SELECT*
FROM cElements A
LEFT JOIN sectionList D ON D.sectionID = A.sectionID
LEFT JOIN fList E ON E.fID = D.fID
WHERE
1 = CASE
WHEN '2006' IN(SELECT acYears = yearID FROM @TempList) AND E.fDateTimeStart BETWEEN '2006-01-01' AND '2007-11-15'
THEN 1 ELSE 0 END
OR1 = CASE
WHEN '2007' IN(SELECT acYears = yearID FROM @TempList) AND E.fDateTimeStart BETWEEN '2007-01-01' AND '2008-11-15'
THEN 1 ELSE 0 END
OR1 = CASE
WHEN '2008' IN(SELECT acYears = yearID FROM @TempList) AND E.fDateTimeStart BETWEEN '2008-01-01' AND '2009-11-15'
THEN 1 ELSE 0 END
OR1 = CASE
WHEN '2009' IN(SELECT acYears = yearID FROM @TempList) AND E.fDateTimeStart BETWEEN '2009-01-01' AND '2010-11-15'
THEN 1 ELSE 0 END
OR1 = CASE
WHEN '2010' IN(SELECT acYears = yearID FROM @TempList) AND E.fDateTimeStart BETWEEN '2010-01-01' AND '2011-11-15'
THEN 1 ELSE 0 END
OR1 = CASE
WHEN '2011' IN(SELECT acYears = yearID FROM @TempList) AND E.fDateTimeStart BETWEEN '2011-01-01' AND '2012-11-15'
THEN 1 ELSE 0 END
OR1 = CASE
WHEN '2012' IN(SELECT acYears = yearID FROM @TempList) AND E.fDateTimeStart BETWEEN '2012-01-01' AND '2013-11-15'
THEN 1 ELSE 0 END
OR1 = CASE
WHEN '2013' IN(SELECT acYears = yearID FROM @TempList) AND E.fDateTimeStart BETWEEN '2013-01-01' AND '2014-11-15'
THEN 1 ELSE 0 END
ORDER BY fDateTimeStart
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 3, 2010 at 10:20 am
Kingston Dhasian (3/2/2010)
Check if this works..
SELECT*
FROM cElements A
LEFT JOIN sectionList D ON D.sectionID = A.sectionID
LEFT JOIN fList E ON E.fID = D.fID
WHERE
1 = CASE
WHEN '2006' IN(SELECT acYears = yearID FROM @TempList) AND E.fDateTimeStart BETWEEN '2006-01-01' AND '2007-11-15'
THEN 1 ELSE 0 END
OR1 = CASE
WHEN '2007' IN(SELECT acYears = yearID FROM @TempList) AND E.fDateTimeStart BETWEEN '2007-01-01' AND '2008-11-15'
THEN 1 ELSE 0 END
...
ORDER BY fDateTimeStart
it's usually not a good idea to hard code values that are stored in a table, too. Even more in this special case where a temp table is used (that's filled by a method yet unknown though...)
What would happen if due to some modification to the insert logic or the source data for the temp table the year 2005 will be added to the temp table, therewith expecting to be shown in the result set, too?
You'd need to remember to add another CASE statement (if you'd notice that 2005 will suddenly be part of that table, of course). If you don't, your results will be wrong.
Therefore, both solutions (Chris' and mine) are using the values from @TempList only, so our solution will cover all entries made to this table without any change required to the code itself.
March 3, 2010 at 10:38 am
Magy,
In your original, you're looking for values between '2006-01-01' AND '2007-11-15' for 2006. Is there some significance to that?
March 3, 2010 at 11:02 am
Those dates are billing dates...I need to find data between those dates to make sure they fall in that billing period...
Thanks!
March 3, 2010 at 9:54 pm
it's usually not a good idea to hard code values that are stored in a table, too. Even more in this special case where a temp table is used (that's filled by a method yet unknown though...)
What would happen if due to some modification to the insert logic or the source data for the temp table the year 2005 will be added to the temp table, therewith expecting to be shown in the result set, too?
You'd need to remember to add another CASE statement (if you'd notice that 2005 will suddenly be part of that table, of course). If you don't, your results will be wrong.
Therefore, both solutions (Chris' and mine) are using the values from @TempList only, so our solution will cover all entries made to this table without any change required to the code itself.
Agreed, Lutz. But i was just trying to make the CASE statement proper. Your solution is definitely better. And i had come across many situations where a condition had to be checked based on another condition. This example can be useful in those cases.:-)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply