March 22, 2011 at 12:29 pm
The year needs to be 2010 but under these conditions.
here is what I have
where DOS =
CASE 2011
WHEN AD.DM_ConfirmationID = 2 --Agreed
THEN year(isnull(AE.AE_PatientEncounterDate, AD.AD_Date))= 2011
WHEN AD.DM_ConfirmationID = 5 --Confirmed
THEN year(AD.AD_Date) = 2011
ELSE AD.AD_Date
END
March 22, 2011 at 12:57 pm
How about some ddl, sample data and a clear understanding of what you are trying to get for a result?
_______________________________________________________________
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/
March 22, 2011 at 12:59 pm
I just need the syntax to not gove me an error. the ddl is in a temp table
March 22, 2011 at 1:03 pm
There are lots of syntax AND logic errors. in the short snippet you posted.
For syntax errors check case statement BOL entry.
_______________________________________________________________
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/
March 22, 2011 at 1:16 pm
Something like this MIGHT be what you are looking for although it is not totally clear what you are trying to get.
where year(DOS) =
CASE
WHEN AD.DM_ConfirmationID = 2 --Agreed
and year(isnull(AE.AE_PatientEncounterDate, AD.AD_Date))= 2011
then 2011
WHEN AD.DM_ConfirmationID = 5 --Confirmed
and year(AD.AD_Date) = 2011
then 2011
ELSE year(AD.AD_Date)
END
_______________________________________________________________
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/
March 22, 2011 at 1:26 pm
below wont work.
select * from dbo.tblDM_AuditEncounter
where DM_AuditNurseID =
case when 7
then year(AE_AuditDate) = 2011
else
then year(AE_AEncounter date) = 2010
end
March 22, 2011 at 1:30 pm
adam spencer (3/22/2011)
below wont work.select * from dbo.tblDM_AuditEncounter
where DM_AuditNurseID =
case when 7
then year(AE_AuditDate) = 2011
else
then year(AE_AEncounter date) = 2010
end
No it certainly won't. The result portion has to be a result not an assignment.
case when 7
then [must put a result here, not something = something else]
_______________________________________________________________
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/
March 22, 2011 at 1:34 pm
did not your code above will try it!
my boss says i am filtering wrong and needs to be done today. naturally he will only point at the flaws in the results and not look at the date I am using in the code. I am new here but anyway
there are dups in the results but not truly dups there is something distinct about every row, but he says i am filtering wrong.
ahhh
March 22, 2011 at 1:47 pm
"CASE" doesn't quite work that way.
It can have one of two formats. The first is a "simple case".
CASE [Value or Column]
When [Value or Column] Then [Value or Column]
...
Else [Value or Column]
End
For example:
CASE MyColumn
When 1 then 'X'
When 2 then 'Y'
END
In that form, it can only do single-value equality comparisons. Each value after a When is compared with the value of the [Value or Column] after the "CASE" until either an equality is found, or none is found and it hits an ELSE or END operator.
"Complex Case" has this syntax:
CASE
WHEN [Value or Column] [comparison operator] [Value or Column] THEN [Value or Column]
...
ELSE [Value or Column]
END
Again, the ELSE is optional. In this format, you don't have a value or a column name after the CASE, and you can do more complex comparisons, such as comparing if one column is less than another, or between two values, or matches a certain pattern using LIKE.
What you've done is sort of mix the two, while leaving a few things out.
case when 7
then year(AE_AuditDate) = 2011
else
then year(AE_AEncounter date) = 2010
end
You have "case when 7". When what is 7? SQL doesn't know what you're trying to compare to 7, so it gives a syntax error. You either need:
where year(AE_AuditDate) =
case DM_AuditNurseID
when 7
then 2011
else 2010
end
Or something like that. What that will do is check the column DM_AuditNurseID and, if it's 7, then use 2011, and when it's not 7, use 2010. That will then be used in the Where statement to check the year of the AE_AuditDate column.
Generally speaking, if AE_AuditDate is a datetime column, you'll get a better query by using:
WHERE AE_AuditDate >= '1/1/2010' and AE_AuditDate < '1/1/2011' and DM_AuditNurseID = 7
or AE_AuditDate >= '1/1/2011' and AE_AuditDate < '1/1/2012'
Avoiding using parsing functions on columns in your Where clause will allow for better index use (in most cases), plus the code is simpler and easier to read.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 23, 2011 at 6:22 pm
Yes thelast 2 both worked both worked! thanks so much. It seems like no matter have much I learn, there is still so much about tsql I do not know. I am getting there with google, trial and error, and your alls help. Truly. Thanks.
Adam
March 24, 2011 at 6:41 am
You're welcome.
And yeah, half the time, the trick is knowing what to ask. Took years before I could comfortable and efficiently search T-SQL details, because you have to know what words to search for before you can get any sort of results that make sense.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 24, 2011 at 10:30 am
adam spencer (3/22/2011)
The year needs to be 2010 but under these conditions.here is what I have
where DOS =
CASE 2011
WHEN AD.DM_ConfirmationID = 2 --Agreed
THEN year(isnull(AE.AE_PatientEncounterDate, AD.AD_Date))= 2011
WHEN AD.DM_ConfirmationID = 5 --Confirmed
THEN year(AD.AD_Date) = 2011
ELSE AD.AD_Date
END
Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply