July 30, 2014 at 2:25 am
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
July 30, 2014 at 2:28 am
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].
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply