I have a report that's 99% complete, it does everything I need it to, but I need to add one more feature to it. I have a separate snippet of code that is the missing piece to completing this report, but I am not sure how to incorporate it into my current code, the snippet if a full query with JOINS, a WHERE statement etc.
Any help is greatly appreciated.
Thanks
From a philosophical perspective, we can discuss your needs in general but I am sure you would like more concrete advice and it sure helps if you can post table DDL and the SQL for what you are proposing.
Please provide:
Thanks for the replies.
Here's the query that is compete and working. This is basically returning lines that have a ship date past their promised ship date, then filters and case statements, but at it core a ship date past their promised ship date report.
select
soitem.fac as [Fac],
somast.fcompany as [Customer],
shmast.fccompany as [End Customer],
soitem.fsono as [SO No],
somast.fsorev as [SO Rev],
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.fdesc as [Desc],
soitem.fprodcl as [Prod Cls],
shmast.fshipdate as [Ship Dt],
SHITEM.FITEMNO AS [Ship Ln Item],
soitem_ext.field3 as [Prom Dt],
soitem_ext.field9 as [Reason],
--jomast.fstatus as [Status],
CASE
WHEN soitem.fprodcl = '10' then 'SUB'
WHEN soitem.fprodcl = '11' then 'PDU'
WHEN soitem.fprodcl = '12' then 'PDU'
WHEN soitem.fprodcl = '13' then 'PDU'
WHEN soitem.fprodcl = '14' then 'PDU'
WHEN soitem.fprodcl = '15' then 'X'
WHEN soitem.fprodcl = '16' then 'X'
WHEN soitem.fprodcl = '17' then 'X'
WHEN soitem.fprodcl = '18' then 'RPP'
WHEN soitem.fprodcl = '19' then 'X'
WHEN soitem.fprodcl = '1a' then 'RPP'
WHEN soitem.fprodcl = '1b' then 'RPP'
WHEN soitem.fprodcl = '1c' then 'PDU'
WHEN soitem.fprodcl = '1d' then 'X'
WHEN soitem.fprodcl = '1e' then 'RPP'
WHEN soitem.fprodcl = '1f' then 'BCMS'
WHEN soitem.fprodcl = '1g' then 'X'
WHEN soitem.fprodcl = '1h' then 'X'
WHEN soitem.fprodcl = '1J' then 'PDU'
WHEN soitem.fprodcl = '1K' then 'TRANS'
WHEN soitem.fprodcl = '20' then 'X'
WHEN soitem.fprodcl = '21' then 'PDU'
WHEN soitem.fprodcl = '22' then 'X'
WHEN soitem.fprodcl = '23' then 'PDU'
WHEN soitem.fprodcl = '24' then 'X'
WHEN soitem.fprodcl = '25' then 'HCS'
WHEN soitem.fprodcl = '26' then 'HCS'
WHEN soitem.fprodcl = '27' then 'HCS'
WHEN soitem.fprodcl = '30' then 'RPS'
WHEN soitem.fprodcl = '31' then 'LC'
WHEN soitem.fprodcl = '32' then 'X'
WHEN soitem.fprodcl = '35' then 'RPS'
WHEN soitem.fprodcl = '36' then 'RPS'
WHEN soitem.fprodcl = '37' then 'RPS'
WHEN soitem.fprodcl = '3a' then 'X'
WHEN soitem.fprodcl = '3b' then 'X'
WHEN soitem.fprodcl = '40' then 'STS - Sub'
WHEN soitem.fprodcl = '41' then 'X'
WHEN soitem.fprodcl = '42' then 'X'
WHEN soitem.fprodcl = '43' then 'X'
WHEN soitem.fprodcl = '44' then 'STS'
WHEN soitem.fprodcl = '45' then 'STS'
WHEN soitem.fprodcl = '46' then 'STS'
WHEN soitem.fprodcl = '47' then 'STS'
WHEN soitem.fprodcl = '4a' then 'STS'
WHEN soitem.fprodcl = '4b' then 'STS?'
WHEN soitem.fprodcl = '4c' then 'X'
WHEN soitem.fprodcl = '4z' then 'X'
WHEN soitem.fprodcl = '50' then 'X'
WHEN soitem.fprodcl = '51' then 'X'
WHEN soitem.fprodcl = '52' then 'X'
WHEN soitem.fprodcl = '53' then 'X'
WHEN soitem.fprodcl = '56' then 'BCMS'
WHEN soitem.fprodcl = '57' then 'X'
WHEN soitem.fprodcl = '58' then 'X'
WHEN soitem.fprodcl = '59' then 'X'
WHEN soitem.fprodcl = '60' then 'X'
WHEN soitem.fprodcl = '61' then 'X'
WHEN soitem.fprodcl = '62' then 'X'
WHEN soitem.fprodcl = '63' then 'X'
WHEN soitem.fprodcl = '64' then 'X'
WHEN soitem.fprodcl = '65' then 'X'
WHEN soitem.fprodcl = '66' then 'X'
WHEN soitem.fprodcl = '67' then 'X'
WHEN soitem.fprodcl = '68' then 'X'
WHEN soitem.fprodcl = '69' then 'X'
WHEN soitem.fprodcl = '6a' then 'SERV'
WHEN soitem.fprodcl = '6b' then 'X'
WHEN soitem.fprodcl = '6c' then 'X'
WHEN soitem.fprodcl = '6d' then 'SERV'
WHEN soitem.fprodcl = '6E' then 'SERV'
WHEN soitem.fprodcl = '6F' then 'SERV'
WHEN soitem.fprodcl = '6I' then 'X'
WHEN soitem.fprodcl = '6J' then 'X'
WHEN soitem.fprodcl = '6K' then 'X'
WHEN soitem.fprodcl = '6L' then 'X'
WHEN soitem.fprodcl = '6M' then 'X'
WHEN soitem.fprodcl = '6N' then 'X'
WHEN soitem.fprodcl = '6R' then 'X'
WHEN soitem.fprodcl = '74' then 'SERV'
WHEN soitem.fprodcl = '75' then 'SERV'
WHEN soitem.fprodcl = '80' then 'TRANS'
WHEN soitem.fprodcl = '81' then 'TRANS'
WHEN soitem.fprodcl = '82' then 'TRANS'
WHEN soitem.fprodcl = '83' then 'TRANS'
WHEN soitem.fprodcl = '84' then 'TRANS'
WHEN soitem.fprodcl = '85' then 'TRANS'
WHEN soitem.fprodcl = '8A' then 'TRANS'
WHEN soitem.fprodcl = '8B' then 'TRANS'
WHEN soitem.fprodcl = '90' then 'X'
WHEN soitem.fprodcl = '91' then 'X'
WHEN soitem.fprodcl = '93' then 'X'
WHEN soitem.fprodcl = '98' then 'X'
WHEN soitem.fprodcl = '99' then 'X'
WHEN soitem.fprodcl = '9a' then 'X'
WHEN soitem.fprodcl = '9b' then 'X'
WHEN soitem.fprodcl = '9C' then 'X'
WHEN soitem.fprodcl = 'A1' then 'SHT'
WHEN soitem.fprodcl = 'A2' then 'SHT'
WHEN soitem.fprodcl = 'A3' then 'SHT'
WHEN soitem.fprodcl = 'A4' then 'SHT'
WHEN soitem.fprodcl = 'A5' then 'BUS'
WHEN soitem.fprodcl = 'B1' then 'X'
WHEN soitem.fprodcl = 'B3' then 'X'
WHEN soitem.fprodcl = 'B4' then 'X'
WHEN soitem.fprodcl = 'CN' then 'X'
WHEN soitem.fprodcl = 'ON' then 'X'
WHEN soitem.fprodcl = 'PI' then 'X'
END AS Prod_Cls1,
CASE
** when shmast.fshipdate > SOITEM_EXT.FIELD3 then '0' --1 equals on time, 0 equals late**
** else '1' **
** end as [On_Time],**
**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]
from soitem
LEFT JOIN soitem_ext
on soitem.identity_column = soitem_ext.fkey_id
**LEFT JOIN somast **
**ON soitem.fsono = somast.fsono **
**JOIN shmast **
ON (somast.fsono + somast.fsorev) = (shmast.fcsono + shmast.fcsorev)
**LEFT JOIN SORELS **
ON (SOITEM.FSONO + SOITEM.FPARTNO + SOITEM.FINUMBER + SOITEM.FSONO) = (SORELS.FSONO + SORELS.FPARTNO + SORELS.FINUMBER + SORELS.FSONO)
**LEFT JOIN SHITEM **
ON ((SHMAST.FSHIPNO + SOITEM.FPARTNO + SOITEM.FAC) + (SORELS.FSONO + SORELS.FINUMBER + SORELS.FRELEASE)) = ((SHITEM.FSHIPNO + SHITEM.FPARTNO + SHITEM.FAC) + (SHITEM.FSOKEY))
**--JOIN jomast **
--ON shmast.fcsono = jomast.fsono
**where **
shitem.fshipqty = shitem.forderqty
and
shmast.fshipdate > SOITEM_EXT.FIELD3
and
shitem.fshipqty <> '0'
and
SOITEM_EXT.FIELD3 <> '1/1/1900 12:00:00 AM'
**AND **
(soitem.fprodcl <> '54'
and
soitem.fprodcl <> '55'
and
soitem.fprodcl <> '70'
and
soitem.fprodcl <> '71'
and
soitem.fprodcl <> '72'
and
soitem.fprodcl <> '73'
and
soitem.fprodcl <> '76'
and
soitem.fprodcl <> '77'
and
soitem.fprodcl <> '78'
and
soitem.fprodcl <> '79'
and
soitem.fprodcl <> '7A'
and
soitem.fprodcl <> 'FR'
and
soitem.fprodcl <> 'SH')
order by soitem.fsono ASC
one final feature I need is to only bring in lines where there are no more items left to ship on the order. There is no field in the tables that flags the order as complete or the line item as the last one in the order, so I have this query that will show me that info. This is the query I desperately need to incorporate into the first attached code.
**SELECT shmast.fcsono, shitem.fenumber, Sum(shitem.fshipqty) AS SumOffshipqty, **
sorels.forderqty, Max(shmast.fshipdate) AS MaxOffshipdate
**FROM sorels **
**INNER JOIN (shmast INNER JOIN shitem ON shmast.fshipno = shitem.fshipno) **
ON (sorels.fsono = shmast.fcsono) AND (sorels.fenumber = shitem.fenumber)
Thanks again for your time. I hope I've explained this properly.
Since table description, sample data and expected output wasn't provided, I can only guess the following query will work. I have rewritten the query, the way I would have written it. I really question the way you have joined the tables sorels and shitem (this is why table definition, sample data and expected output was requested).
Anyway - here goes:
select soi.fac as [Fac]
,som.fcompany as [Customer]
,shm.fccompany as [End Customer]
,soi.fsono as [SO No]
,som.fsorev as [SO Rev]
,soi.fenumber as [Line Item]
,soi.fquantity as [SO Qty]
,shi.fshipqty as [Ship Qty]
,sor.fnetprice as [Sales Price]
,soi.fpartno as [Part No]
,soi.fpartrev as [Rev]
,soi.fdesc as [Desc]
,soi.fprodcl as [Prod Cls]
,shm.fshipdate as [Ship Dt]
,shi.fitenno as [Ship Ln Item]
,soie.field3 as [Prom Dt]
,soie.field9 as [Reason]
,jom.fstatus as [Status]
,case soi.fprodcl
when '10' then 'SUB'
when '11' then 'PDU'
when '12' then 'PDU'
when '13' then 'PDU'
when '14' then 'PDU'
when '15' then 'X'
when '16' then 'X'
when '17' then 'X'
when '18' then 'RPP'
when '19' then 'X'
when '1a' then 'RPP'
when '1b' then 'RPP'
when '1c' then 'PDU'
when '1d' then 'X'
when '1e' then 'RPP'
when '1f' then 'BCMS'
when '1g' then 'X'
when '1h' then 'X'
when '1J' then 'PDU'
when '1K' then 'TRANS'
when '20' then 'X'
when '21' then 'PDU'
when '22' then 'X'
when '23' then 'PDU'
when '24' then 'X'
when '25' then 'HCS'
when '26' then 'HCS'
when '27' then 'HCS'
when '30' then 'RPS'
when '31' then 'LC'
when '32' then 'X'
when '35' then 'RPS'
when '36' then 'RPS'
when '37' then 'RPS'
when '3a' then 'X'
when '3b' then 'X'
when '40' then 'STS - Sub'
when '41' then 'X'
when '42' then 'X'
when '43' then 'X'
when '44' then 'STS'
when '45' then 'STS'
when '46' then 'STS'
when '47' then 'STS'
when '4a' then 'STS'
when '4b' then 'STS?'
when '4c' then 'X'
when '4z' then 'X'
when '50' then 'X'
when '51' then 'X'
when '52' then 'X'
when '53' then 'X'
when '56' then 'BCMS'
when '57' then 'X'
when '58' then 'X'
when '59' then 'X'
when '60' then 'X'
when '61' then 'X'
when '62' then 'X'
when '63' then 'X'
when '64' then 'X'
when '65' then 'X'
when '66' then 'X'
when '67' then 'X'
when '68' then 'X'
when '69' then 'X'
when '6a' then 'SERV'
when '6b' then 'X'
when '6c' then 'X'
when '6d' then 'SERV'
when '6E' then 'SERV'
when '6F' then 'SERV'
when '6I' then 'X'
when '6J' then 'X'
when '6K' then 'X'
when '6L' then 'X'
when '6M' then 'X'
when '6N' then 'X'
when '6R' then 'X'
when '74' then 'SERV'
when '75' then 'SERV'
when '80' then 'TRANS'
when '81' then 'TRANS'
when '82' then 'TRANS'
when '83' then 'TRANS'
when '84' then 'TRANS'
when '85' then 'TRANS'
when '8A' then 'TRANS'
when '8B' then 'TRANS'
when '90' then 'X'
when '91' then 'X'
when '93' then 'X'
when '98' then 'X'
when '99' then 'X'
when '9a' then 'X'
when '9b' then 'X'
when '9C' then 'X'
when 'A1' then 'SHT'
when 'A2' then 'SHT'
when 'A3' then 'SHT'
when 'A4' then 'SHT'
when 'A5' then 'BUS'
when 'B1' then 'X'
when 'B3' then 'X'
when 'B4' then 'X'
when 'CN' then 'X'
when 'ON' then 'X'
when 'PI' then 'X'
end as ProdCls1
,case
when shm.fshipdate>soie.field3
then '0' /* 1 equals on time, 0 equals late */
else '1'
end as [OnTime]
,case
when shm.fshipdate>=cast('2015-07-31 00:00:00' as datetime)
and shm.fshipdate< cast('2015-08-29 00:00:00' as datetime) then '1'
when shm.fshipdate>=cast('2015-08-30 00:00:00' as datetime)
and shm.fshipdate< cast('2015-09-27 00:00:00' as datetime) then '2'
when shm.fshipdate>=cast('2015-09-27 00:00:00' as datetime)
and shm.fshipdate< cast('2015-10-31 00:00:00' as datetime) then '3'
when shm.fshipdate>=cast('2015-11-01 00:00:00' as datetime)
and shm.fshipdate< cast('2015-11-28 00:00:00' as datetime) then '4'
when shm.fshipdate>=cast('2015-11-29 00:00:00' as datetime)
and shm.fshipdate< cast('2015-12-26 00:00:00' as datetime) then '5'
when shm.fshipdate>=cast('2015-12-27 00:00:00' as datetime)
and shm.fshipdate< cast('2016-01-30 00:00:00' as datetime) then '6'
when shm.fshipdate>=cast('2016-02-01 00:00:00' as datetime)
and shm.fshipdate< cast('2016-02-27 00:00:00' as datetime) then '7'
when shm.fshipdate>=cast('2016-02-28 00:00:00' as datetime)
and shm.fshipdate< cast('2016-03-26 00:00:00' as datetime) then '8'
when shm.fshipdate>=cast('2016-03-27 00:00:00' as datetime)
and shm.fshipdate< cast('2016-04-30 00:00:00' as datetime) then '9'
when shm.fshipdate>=cast('2016-05-01 00:00:00' as datetime)
and shm.fshipdate< cast('2016-05-28 00:00:00' as datetime) then '10'
when shm.fshipdate>=cast('2016-05-29 00:00:00' as datetime)
and shm.fshipdate< cast('2016-06-25 00:00:00' as datetime) then '11'
when shm.fshipdate>=cast('2016-06-26 00:00:00' as datetime)
and shm.fshipdate< cast('2016-08-01 00:00:00' as datetime) then '12'
else 'X'
end as FM
,case
when shm.fshipdate>=cast('2014-08-01 00:00:00' as datetime)
and shm.fshipdate< cast('2015-08-01 00:00:00' as datetime) then '2015'
when shm.fshipdate>=cast('2015-08-01 00:00:00' as datetime)
and shm.fshipdate< cast('2016-07-31 00:00:00' as datetime) then '2016'
else 'X'
end as FY
,datediff(day,shm.fshipdate,soie.field3) as [Days Late]
from soitem as si
inner join soitemext as soie
on soie.fkey_id=soi.identitycolumn
and soie.field3<>cast('1900-01-01 00:00:00' as datetime)
inner join somast as som
on som.fsono=soi.fsono
inner join shmast as shm
on shm.fcsono=som.fsono
and shm.fcsorev=som.fsorev
and shm.fshipdate>soie.field3
inner join sorels as sor
on sor.fsono=soi.fsono
and sor.fpartno=soi.fpartno
and sor.finumber=soi.finumber
inner join shitem as shi
on shi.fshipno=shm.fshipno
and shi.fpartno=soi.fpartno
and shi.fac=soi.fac
and shi.fsokey=sor.fsono
+sor.finumber
+sor.frelease
and shi.fshipqty=shi.forderqty
and shi.fshipqty<>0
inner join jomast as jom
on shm.fcsono=jom.fsono
where soi.fprodcl not in ('54','55','70','71','72','73','76','77','78','79','7A','FR','SH')
and sor.forderqty=(select sum(shi2.fshipqty)
from shitem as shi2
where shi2.fshipno=shm.fshipno
and shi2.fenumber=sor.fenumber
)
order by soi.fsono
;
What about anything dated 29th?
Do you not have a "Accounting Periods" table, or somesuch, that you can associate with the query, rather than hard-wiring the dates? With dates hardwired (probably in multiple reports) if anything changes, or is wrong, its a lot of work to fix and also a high probability that one, or several, of them will be subtly different and thus silently report the wrong figures.