CASE WHEN using > - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

CASE WHEN using >

Author

Topic

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-07-29 : 10:23:31

Hi, how do I get SQL to do CASE WHEN queries with user-defined column headings? I've done 2 MAXs on dates. I want it to say

"CASE WHEN (A) > (b) THEN [A] ELSE (B) END" but it won't let me use the titles I gave each DMAX. The code is (the 2 MAXs are in bold):

SELECT DISTINCT
dbo.Tbl_Families.FamiliesID, dbo.Tbl_Families.ApplicantCode, dbo.Tbl_Families.FamilyCurrentPosition, dbo.Tbl_Families.DateApproved, dbo.Tbl_Families.Withdrawn, dbo.Tbll_SocialWorker.SWSurname, dbo.Tbll_SocialWorker.SWFirstname,
(SELECT MAX(FamiliesActionDate) AS Expr1
FROM dbo.Tbl_FamiliesProgress
WHERE (FamiliesAction = N'panel scheduled for:') AND (FamiliesID = dbo.Tbl_Families.FamiliesID)) AS [Panel Scheduled For:],
(SELECT MAX(FamiliesActionDate) AS Expr1
FROM dbo.Tbl_FamiliesProgress AS Tbl_FamiliesProgress_2
WHERE (FamiliesAction LIKE N'%Review scheduled for: 2nd%') AND (FamiliesID = dbo.Tbl_Families.FamiliesID)) AS [Review Scheduled for]

FROM dbo.Tbl_Families INNER JOIN
dbo.Tbll_SocialWorker ON dbo.Tbl_Families.SocialWorkerID = dbo.Tbll_SocialWorker.SocialWorkerID INNER JOIN
dbo.Tbl_FamiliesProgress AS Tbl_FamiliesProgress_1 ON dbo.Tbl_Families.FamiliesID = Tbl_FamiliesProgress_1.FamiliesID
WHERE (dbo.Tbl_Families.Withdrawn IS NULL) AND (dbo.Tbl_Families.DateApproved IS NOT NULL)

I thought "CASE WHEN [Panel Scheduled For:] > [Review Scheduled for] THEN [Panel Scheduled For:] ELSE [Review Scheduled for] END" but it doesn't work :/

Jim

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-29 : 11:56:44

You can't have an AS on a WHERE clause:


WHERE (FamiliesAction = N'panel scheduled for:') AND (FamiliesID = dbo.Tbl_Families.FamiliesID)) AS [Panel Scheduled For:],

ALso, you can't alias a column and use the alias in the same query until you get to the ORDER BY clause, unless you use a subquery. e.g. this won't work:


select 1 as a, 2 as b
where a = 1

but this will work:


select * from (select 1 as a, 2 as b) sub
where a = 1

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-07-29 : 19:58:15

CROSS APPLY works great for this:



SELECT DISTINCT 
dbo.Tbl_Families.FamiliesID, dbo.Tbl_Families.ApplicantCode, dbo.Tbl_Families.FamilyCurrentPosition, dbo.Tbl_Families.DateApproved, dbo.Tbl_Families.Withdrawn, dbo.Tbll_SocialWorker.SWSurname, dbo.Tbll_SocialWorker.SWFirstname,
CASE WHEN ca1.[Panel Scheduled For:] > ca2.[Review Scheduled for] THEN ca1.[Panel Scheduled For:] ELSE ca2.[Review Scheduled for] END AS [Scheduled For]
--ca1.[Panel Scheduled For:],
--ca2.[Review Scheduled for]

FROM dbo.Tbl_Families INNER JOIN
 dbo.Tbll_SocialWorker ON dbo.Tbl_Families.SocialWorkerID = dbo.Tbll_SocialWorker.SocialWorkerID INNER JOIN
 dbo.Tbl_FamiliesProgress AS Tbl_FamiliesProgress_1 ON dbo.Tbl_Families.FamiliesID = Tbl_FamiliesProgress_1.FamiliesID
CROSS APPLY (
    SELECT MAX(FamiliesActionDate) AS [Panel Scheduled For:]
    FROM dbo.Tbl_FamiliesProgress
    WHERE (FamiliesAction = N'panel scheduled for:') AND (FamiliesID = dbo.Tbl_Families.FamiliesID)
) AS ca1
CROSS APPLY (
    SELECT MAX(FamiliesActionDate) AS [Review Scheduled for]
    FROM dbo.Tbl_FamiliesProgress AS Tbl_FamiliesProgress_2
    WHERE (FamiliesAction LIKE N'%Review scheduled for: 2nd%') AND (FamiliesID = dbo.Tbl_Families.FamiliesID)
) AS ca2

WHERE (dbo.Tbl_Families.Withdrawn IS NULL) AND (dbo.Tbl_Families.DateApproved IS NOT NULL)

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-07-30 : 03:37:54

Thanks to you both, that last bit works a treat. I now need to teach myself about CROSS APPLY as I've never heard of that before :)

Last question, if I want to do a calculation on the date it returns (i.e. I want to minus 3 months from the date returned in the "scheduled for" column, I'm guessing that's not going to be simple as I'd hoped?

J

Jim

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-07-30 : 10:33:14

If I understand correctly, that shouldn't be a problem, something like this:

DATEADD(MONTH, -3, ca1.[Panel Scheduled For:])

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-30 : 11:41:48

quote:
Originally posted by ScottPletcher

If I understand correctly, that shouldn't be a problem, something like this:

DATEADD(MONTH, -3, ca1.[Panel Scheduled For:])

That should work

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-07-30 : 11:56:07

It didn't :/ I tried:

DATEADD(MONTH, -3, ca1.[Panel Scheduled For:])

and

DATEADD(MONTH, -3, ca1.[Panel Scheduled For:]) AS [Paperwork due on:]

It is adding 3 months to the [Panel Scheduled For:] date, not the [Scheduled For] date. I tried changing to the alias and it says "invalid column name"

Jim

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-07-30 : 12:05:22

Using CROSS APPLY, [Panel Scheduled For:] and [Review Scheduled for] are just like any other column names in the query. Thus, you should be able to use DATEADD() or any other function(s) normally on those values.

If the values aren't what you expect, check that the query in the CA is returned the value(s) you expect.

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-07-30 : 14:02:08

But there are 3 fields:
1 - Panel Scheduled for
2 - Review Scheduled for
3 - Scheduled for (this being the date which is greatest of 1 and 2. Adding 3 months to 1 or 2 won't give me my next column which would be [Scheduled for] minus 3 months?

Jim

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-07-31 : 07:49:36

Got this to work now. I had to repeat the case statement for it to work. Thanks to all of you for helping :)

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources