Thanks for your help Tara!
Not sure how to provide sample data but here's the whole query:
select
soitem.fac as [Fac],
somast.fcompany as [Customer],
shmast.fccompany as [End Customer],
soitem.fsono as [SO No],
soitem.fenumber as [Line Item],
soitem.fquantity as [SO Qty],
shitem.fshipqty as [Ship Qty],
sorels.fnetprice as [Sales Price],
soitem.fpartno as [Part No],
soitem.fpartrev as [Rev],
soitem.fprodcl as [Prod Cls],
soitem.fdesc as [Desc],
shmast.fshipdate as [Ship Dt],
SHITEM.FITEMNO AS [Ship Ln Item],
soitem_ext.field3 as [Prom Dt],
soitem_ext.field9 as [Reason],
CASE
WHEN shmast.fshipdate >= '20150731'
AND shmast.fshipdate < '20150829' THEN '1'
WHEN shmast.fshipdate >= '20150830'
AND shmast.fshipdate < '20150927' THEN '2'
WHEN shmast.fshipdate >= '20150927'
AND shmast.fshipdate < '20151031' THEN '3'
WHEN shmast.fshipdate >= '20151101'
AND shmast.fshipdate < '20151128' THEN '4'
WHEN shmast.fshipdate >= '20151129'
AND shmast.fshipdate < '20151226' THEN '5'
WHEN shmast.fshipdate >= '20151227'
AND shmast.fshipdate < '20160130' THEN '6'
WHEN shmast.fshipdate >= '20160201'
AND shmast.fshipdate < '20160227' THEN '7'
WHEN shmast.fshipdate >= '20160228'
AND shmast.fshipdate < '20160326' THEN '8'
WHEN shmast.fshipdate >= '20160327'
AND shmast.fshipdate < '20160430' THEN '9'
WHEN shmast.fshipdate >= '20160501'
AND shmast.fshipdate < '20160528' THEN '10'
WHEN shmast.fshipdate >= '20160529'
AND shmast.fshipdate < '20160625' THEN '11'
WHEN shmast.fshipdate >= '20160626'
AND shmast.fshipdate < '20160801' THEN '12'
ELSE 'X'
END AS FM,
CASE
WHEN shmast.fshipdate >= '20140801'
AND shmast.fshipdate < '20150801' THEN '2015'
WHEN shmast.fshipdate >= '20150801'
AND shmast.fshipdate < '20160731' THEN '2016'
ELSE 'X'
END AS FY,
DATEDIFF(DD, shmast.fshipdate,SOITEM_EXT.FIELD3) AS [Days Late],
/CHANGES*****************/
CASE
when shmast.fshipdate > SOITEM_EXT.FIELD3 then '1'
else '0'
end as [Days_Early_Count]*/
/END CHANGES********/
from soitem
JOIN soitem_ext--l
on soitem_ext.fkey_id=soitem.identity_column
--and soitem_ext.field3<>cast('1900-01-01 00:00:00' as datetime)
LEFT JOIN somast--l
on somast.fsono=soitem.fsono
JOIN shmast
on shmast.fcsono=somast.fsono
and shmast.fcsorev=somast.fsorev
--and shmast.fshipdate>soitem_ext.field3
LEFT JOIN sorels--l
on sorels.fsono=soitem.fsono
and sorels.fpartno=soitem.fpartno
and sorels.finumber=soitem.finumber
JOIN shitem ON
shmast.fshipno = shitem.fshipno
and
sorels.fsono = shmast.fcsono
and
sorels.fenumber = shitem.fenumber
and
soitem.fenumber = shitem.fenumber
JOIN (
SELECT
Sum(shitem.fshipqty) AS fshipqty
FROM sorels
INNER JOIN shmast ON sorels.fsono = shmast.fcsono
INNER JOIN shitem ON shmast.fshipno = shitem.fshipno AND sorels.fenumber = shitem.fenumber
) t ON shitem.forderqty=t.fshipqty
where
soitem.fprodcl not in ('54','55','70','71','72','73','76','77','78','79','7A','FR','SH')
and
shmast.fshipdate BETWEEN '4/16/2016' AND '4/24/2016'
order by soitem.fsono ASC