Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- i.MillerAd8,i.AdYear4,(i.MillerAd8+i.AdYear4) AS FullAdNum,
- i.PubNumber,i.PubName,i.ClientNum,i.InvoiceNumber,i.InsertDates,i.FirstInsDate,
- i.LineType,i.RecordType,i.PriorInvNumber,i.InvoiceDate,i.InvoiceType,i.GrossAmount,a.CheckNumber,
- h.NoBill,p.PrepayType,p.ChargeAmount,p.ChargeDate,p.FromAP,
- a.CheckDate,a.PaidAmount,c.ClientName,
- (SELECT SUM(z.GrossAmount) FROM "invhistd" z WHERE (z.RecordType='S') AND
- (z.LineType='O') AND
- (z.InvoiceNumber=i.InvoiceNumber) AND
- (z.MillerAdNumber=FullAdNum) AND
- NOT(z.AncillaryType IN (0,2,4,5,10,12,15))) AS MiscTotal
- FROM "invhistd" i
- LEFT OUTER JOIN "apdata" a ON ((i.MillerAd8=a.MillerAdNumber) AND (i.AdYear4=a.EntryYear))
- LEFT OUTER JOIN "adheader" h ON ((i.MillerAd8=h.AdNumber) AND (i.AdYear4=h.EntryYear))
- LEFT OUTER JOIN "ccads" p ON ((i.MillerAd8=p.AdNumber) AND (i.AdYear4=p.EntryYear))
- LEFT OUTER JOIN "client" c ON (i.ClientNum=c.ClientNumber)
- WHERE (i.invoicedate>=42005) AND (i.invoicedate<=42290) AND
- (i.LineType='S') AND (i.RecordType='S') AND (a.CheckNumber IS NULL)
- AND NOT(i.PubNumber IN ('0001','0002','0004','0005','0007','0011','0828','8160','8450','8509'))
- ORDER BY i.PubNumber,i.ClientNum,i.FirstInsDate
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement