Problem with DATEADD/BETWEEN in SQL

Transact-SQL
1

I am having a problem with the following subquery ( I am self taught on T-SQL). The issue lies with the bolded statement. The output is ignoring the WHERE clause which utilizes DATEADD and BETWEEN. The result is the same result as just using sum(b.qty) which is on the above line.

The rest of the query is functioning exactly how I want it to. Would appreciate the assistance! Query is below

SELECT
max(z.item#) as item#
, sum(b.qty) as Qty
,( select sum(B.qty) WHERE z.item# = ( select max(b.item#)) AND max(b.[date]) BETWEEN (DATEADD(dd, -92, GETDATE())) AND GETDATE() ) as L13
,z.description as description
,z.category as category

FROM

(SELECT
AA.description
,AA.category
,AA.item#

FROM
[adg_icitems(1)] AA
Join [adg_icitem(3)extension] M On aa.item# = m.item#
JOIN [adg_icitems(2)additional] Q ON aa.item# = q.item#
JOIN [EXPORTER_EAST].[dbo].[adg_icitems(1)] X ON aa.item# = x.item#

WHERE
AA.activeitem='A' AND
left(AA.item#, 2) !='CR' AND
left(AA.item#, 2) !='AC' AND
AA.category !='OI' AND
left(AA.item#, 3) !='PAL' AND
left(AA.item#, 2) !='ZZ' ) Z

inner JOIN

(SELECT
max(adg.[date]) as [date]
,d.ComponentItem as item#
,left(d.componentitem, 2) as category
,min(D.QtyNeeded / H.QtyToBuild) * sum(adg.qty) as qty
FROM adg_srtransactions ADG INNER JOIN LWMS.dbo.BomDetail D ON adg.item# = d.MasterItem
INNER JOIN lwms.dbo.BomHeader H ON H.Company = D.Company AND H.Warehouse = D.Warehouse AND H.ID = D.ID AND H.MasterItem = D.MasterItem
WHERE
H.warehouse='2' AND adg.[date] >= '2016.1.1'
GROUP BY item#, d.ComponentItem, [date]

UNION

select
max(Exporter.dbo.[adg_srtransactions].[date]) as [date]
,max(Exporter.dbo.[adg_srtransactions].item#) as item#
,max(Exporter.dbo.[adg_srtransactions].category) as category
,sum(Exporter.dbo.[adg_srtransactions].qty) as qty

FROM Exporter.dbo.[adg_srtransactions]
WHERE
Exporter.dbo.[adg_srtransactions].type = N'L'
AND left(Exporter.dbo.[adg_srtransactions].item# ,2) != N'CR'
AND left(Exporter.dbo.[adg_srtransactions].item# ,2) != N'AC'
AND left(Exporter.dbo.[adg_srtransactions].cust#,2) != N'CR'
AND left(Exporter.dbo.[adg_srtransactions].item#,2) != N'70'
AND Exporter.dbo.[adg_srtransactions].[date] >= '2016.1.1'

GROUP BY

Exporter.dbo.[adg_srtransactions].item#
,Exporter.dbo.[adg_srtransactions].category
,Exporter.dbo.[adg_srtransactions].[date]

UNION

select
max(Exporter_east.dbo.[adg_srtransactions].[date]) as [date]
,max(Exporter_east.dbo.[adg_srtransactions].item#) as item#
,max(Exporter_east.dbo.[adg_srtransactions].category) as category
,sum(Exporter_east.dbo.[adg_srtransactions].qty) as qty

FROM Exporter_east.dbo.[adg_srtransactions]
WHERE
Exporter_east.dbo.[adg_srtransactions].type = N'L'
AND left(Exporter_east.dbo.[adg_srtransactions].item# ,2) != N'CR'
AND left(Exporter_east.dbo.[adg_srtransactions].item# ,2) != N'AC'
AND left(Exporter_east.dbo.[adg_srtransactions].cust#,2) != N'CR'
AND left(Exporter_east.dbo.[adg_srtransactions].item#,2) != N'70'
AND Exporter_east.dbo.[adg_srtransactions].[date] >= '2016.1.1'

GROUP BY

Exporter_east.dbo.[adg_srtransactions].item#
,Exporter_east.dbo.[adg_srtransactions].category
,Exporter_east.dbo.[adg_srtransactions].[date]

) B on Z.item# = b.item#

GROUP BY
z.item#
,z.description
,z.category

HAVING z.item# = ( select max(b.item#) )

ORDER BY item#

2

I've read that four times and not figured out quite what you are trying to do ... definitely can't do it like that though!!

You can do something like this:

,sum(CASE WHEN SomeCondition THEN B.qty ELSE 0 END)  as L13

or

,(
    SELECT sum(CASE WHEN SomeCondition THEN B.qty ELSE 0 END)  
    FROM MyTable 
    WHERE MyConditions ...
 ) as L13
3

Sorry, I should have stated. I'm am trying to aggregate an amount over a period of time. Ie give me sales during the last 13 weeks starting today.