Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DATEADD with aliases? Expand / Collapse
Author
Message
Posted Wednesday, July 30, 2014 2:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:22 AM
Points: 1, Visits: 0
Hello,

I want to subtract 3 months from a date in an alias field but I cannot get it to work. Any ideas if this is possible?

My code so far is:

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)

I want a new field with the date in [Scheduled For] minus 3 months. I've tried many variations but can't get it to work Any ideas please?

J
Post #1597613
Posted Wednesday, July 30, 2014 2:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:52 PM
Points: 13,368, Visits: 11,148
You can't reference an alias in the same query, except in an ORDER BY clause.
You need to use your select statement inside another one (as an inner query) and reference your alias in the outer query, or you just need to reuse the expression that calculates [Scheduled For].




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1597614
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse