DATEADD with aliases?

  • 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

  • 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