Subquery resulting duplicates

Question

Sorry for the lengthy query below, with how the database is organized and my little SQL knowledge, it is the only way I can get it to play nice. So the database has a field TicketDate in which I need the MAX value of. However, it is defined as a char(8) variable instead of datetime. So getting the MAX isn't as easy as MAX(TicketDate as DATETIME), it doesn't actually result with the max date.

The query works on its own:

--***SUB QUERY (TimeTicketDet)
        SELECT DISTINCT
            cast(TicketDate as DATE) as MAXtt,
            tt.JobNo
        FROM TimeTicketDet as tt

        ORDER BY
            cast(TicketDate as DATE) DESC OFFSET 0 ROWS

However when added into the main query, I get duplicate rows for each TimeTicketEntry. The query works fine without the last subquery (no duplicates) --***SUB QUERY (TimeTicketDet). JobNo is the unique identifier for the TimeTicketDet table and was used as the link.

DECLARE @now DATETIME
DECLARE @90daysago DATETIME

SET @now = GETDATE()
SET @90daysago = DATEADD(day, -90, @now)


SELECT
    o.PartNo,
    o.JobNo,
    ord.DateEnt,
    por.MAXPONum,
    po.DateEnt,
    tt.MAXtt,
    r.ReceiveDate,

            CASE
            WHEN po.DateEnt >= r.ReceiveDate AND po.DateEnt >= tt.MAXtt THEN cast(po.DateEnt as DATE)
            WHEN r.ReceiveDate >= po.DateEnt AND r.ReceiveDate >= tt.MAXtt THEN cast(r.ReceiveDate as DATE)
            WHEN tt.MAXtt >= po.DateEnt AND tt.MAXtt >= r.ReceiveDate THEN cast(tt.MAXtt as DATE)
            ELSE po.DateEnt
            END AS MostRecentDate,

    POProrate.TotalCost,
    WIPProrate.WIPProrateCost,
    (POProrate.TotalCost+WIPProrate.WIPProrateCost) as ProratedCost,
    (ROUND(cast((o.QtyToMake - o.QtyShipped2Stock) as FLOAT)/o.QtyToMake,3))*(POProrate.TotalCost+WIPProrate.WIPProrateCost) as TotalProratedCost

FROM
--***MAIN QUERY (OrderDet)***
    OrderDet as o

--***SUB QUERY (Order)***
    JOIN
        (
            SELECT
                cast(DateEnt as DATE) as DateEnt,
                OrderNo
            FROM
                Orders
        ) ord
        ON ord.OrderNo = o.OrderNo


--***SUB QUERY (POReleases)***
    JOIN
        (
            SELECT
                MAX(PONum) as MAXPONum,
                JobNo
            FROM
                POReleases
            GROUP BY
                JobNo
        ) por
        ON por.JobNo = o.JobNo

--***SUB QUERY (PO)***
    JOIN
        (
            SELECT
                PONum,
                cast(DateEnt as DATE) as DateEnt
            FROM
                PO
        ) po
        ON po.PONum = por.MAXPONum

--***SUB QUERY (TimeTicketDet)
    JOIN
        (
            SELECT DISTINCT
            cast(TicketDate as DATE) as MAXtt,
            tt.JobNo
        FROM TimeTicketDet as tt

        ORDER BY
            cast(TicketDate as DATE) DESC OFFSET 0 ROWS
        ) tt
        ON tt.JobNo = o.JobNo

--***SUB QUERY (Receiver)
    JOIN
        (
            SELECT
                rd.JobNo,
                cast(MAX(r.ReceiveDate) as DATE) as ReceiveDate
            FROM
                Receiver as r
            INNER JOIN
                ReceiverDet as rd ON r.PONum = rd.PONum
            GROUP BY
                rd.JobNo

        ) r
        ON r.JobNo = o.JobNo

--***SUB QUERY (POProrate)***
    JOIN
        (
            SELECT
                j.JobNo,
                SUM(j.TotalCost) as TotalCost

            FROM
                (   
                    SELECT
                            por.JobNo,
                    CASE
                        WHEN pod.Unit = 'LOT' THEN SUM(pod.UnitCost*1)
                        ELSE SUM(por.Qty*pod.UnitCost)
                    END as TotalCost
                    FROM
                        PODet as pod
                    INNER JOIN
                        POReleases as por ON pod.PONum = por.PONum and pod.partno=por.partno
                    GROUP BY por.JobNo, pod.Unit

                ) j

            GROUP BY j.JobNo
        ) POProrate
        ON o.JobNo = POProrate.JobNo

--***SUB QUERY (WIPProrate)***
    JOIN
        (
            SELECT
                j.JobNo,
                SUM(j.ProratedCost) as WIPProrateCost

            FROM
                (
                    SELECT
                        tt.StepNo,
                        tt.JobNo,
                        tt.ActualPayRate,
                        tt.BurdenRate,
                        tt.CycleTime,
                        tt.SetupTime,
                        tt.CycleTime + tt.SetupTime as TotalTime,
                        (tt.CycleTime + tt.SetupTime) * tt.ActualPayRate as LaborCost,
                        (tt.CycleTime + tt.SetupTime) * tt.BurdenRate as BurdenCost,
                        ((tt.CycleTime + tt.SetupTime) * tt.ActualPayRate) + ((tt.CycleTime + tt.SetupTime) * tt.BurdenRate) as ProratedCost
                    FROM
                        TimeTicketDet as tt
                ) j

            GROUP BY j.JobNo
        ) WIPProrate
        ON WIPProrate.JobNo = o.JobNo

WHERE
    o.Status = 'Open'
    AND o.JobNo <> ''
    AND ord.DateEnt <= @90daysago
    AND po.DateEnt <= @90daysago
    AND tt.MAXtt <= @90daysago
    AND r.ReceiveDate <= @90daysago

The goal of the query is find all relevant dates, <=90 days old, add to the list. All I am missing is the last date, which would pull the correct TicketDate from the TimeTicketDet table and join it on JobNo.

I am happy to add or take away any information requested.

Answer 1

Try changing your subquery to:

SELECT MAX(CAST(TicketDate as DATE)) as MaxTT,
       JobNo
FROM TimeTicketDet as tt
GROUP BY JobNo

I assume your current query must be returning multiple MaxTT dates for any given JobNo