declare @dataareaid varchar(3)
declare @date datetime

set @dataareaid = 'agl'
set @date = '2012-04-01'

select c.purchid AS [Purchase Order], c.purchname[Name], c.orderaccount AS [Vendor Ac], CONVERT(DECIMAL(12, 2), sum(a.amountmst)) as [Amount]--, c.dimension as [Site]
	from		ledgertrans a
	inner join	(
					SELECT	'Packing' AS Indicator, dataareaid, purchid, internalpackingslipid, ledgervoucher from VENDPACKINGSLIPJOUR 
					UNION ALL
					SELECT 'Invoice', dataareaid, purchid, internalinvoiceid, ledgervoucher from VENDINVOICEJOUR
				)b 
	on			a.voucher = b.ledgervoucher
	inner join	purchtable c
	on			b.purchid = c.purchid
	where	a.accountnum = '34050'
	and		a.dataareaid = @dataareaid
	and		b.dataareaid = @dataareaid
	and		(
				(
					a.voucher like 'GRN-%'
				AND	b.Indicator = 'Packing'
				)
			OR	(
					a.voucher like 'PI-%'
				AND	b.Indicator = 'Invoice'
				)
			)
	and		transdate < @date
--	and		c.purchid = 'PO-C15297'
	group by c.purchid, c.purchname, c.orderaccount--, c.dimension
having sum(amountmst) < 0
order by c.purchid asc

quote:

---

LEDGERTRANS:
ACCOUNTNUM TRANSDATE VOUCHER TXT AMOUNTMST AMOUNTCUR CURRENCYCODE TRANSTYPE DIMENSION DIMENSION2_ DIMENSION3_ DIMENSION4_ DIMENSION5_ DIMENSION6_ DIMENSION7_ QTY DOCUMENTDATE JOURNALNUM JOURNALIZESEQNUM ALLOCATELEVEL POSTING CORRECT CREDITING DOCUMENTNUM PAYMREFERENCE PERIODCODE OPERATIONSTAX THIRDPARTYBANKACCOUNTID COMPANYBANKACCOUNTID PAYMMODE JOURNALIZENUM AMOUNTMSTSECOND EUROTRIANGULATION FURTHERPOSTINGTYPE LEDGERPOSTINGJOURNALID TAXREFID MODIFIEDBY CREATEDDATE CREATEDTRANSACTIONID DATAAREAID RECVERSION RECID HIDETRANS
34050 01/12/2008 GRN-001162 -80 -80 GBP 3 FMT RMT PDO 0 01/12/2008 0 0 83 0 1 13075 1 0 0 0 0 0 wilsn 21/01/2009 5637902769 agl 1 5637350619 0
34050 31/12/2008 PI-01894 276.6 276.6 GBP 3 FMT RMT PDO 0 01/01/1900 0 0 83 0 0 1 0 0 0 0 0 odonc 21/01/2009 5637908324 agl 1 5637352996 0


PURCHTABLE:
PURCHID PURCHNAME ORDERACCOUNT INVOICEACCOUNT FREIGHTZONE EMAIL DELIVERYDATE DELIVERYTYPE ADDRESSREFRECID ADDRESSREFTABLEID INTERCOMPANYORIGINALSALESID INTERCOMPANYORIGINALCUSTACCO12 CURRENCYCODE PAYMENT CASHDISC PURCHPLACER INTERCOMPANYDIRECTDELIVERY VENDGROUP LINEDISC DISCPERCENT DIMENSION DIMENSION2_ DIMENSION3_ DIMENSION4_ DIMENSION5_ DIMENSION6_ DIMENSION7_ PRICEGROUPID MULTILINEDISC ENDDISC INTERCOMPANYCUSTPURCHORDERFO26 DELIVERYADDRESS TAXGROUP DLVTERM DLVMODE PURCHSTATUS MARKUPGROUP PURCHASETYPE URL POSTINGPROFILE TRANSACTIONCODE DELIVERYZIPCODE DLVCOUNTY DLVCOUNTRYREGIONID DLVSTATE SETTLEVOUCHER INTERCOMPANYALLOWINDIRECTCRE46 INTERCOMPANYORIGIN CASHDISCPERCENT DELIVERYNAME COVSTATUS PAYMENTSCHED ONETIMEVENDOR RETURNITEMNUM FREIGHTSLIPTYPE DOCUMENTSTATUS CONTACTPERSONID INVENTLOCATIONID ITEMBUYERGROUPID PROJID PURCHPOOLID VATNUM PORT INCLTAX NUMBERSEQUENCEGROUP LANGUAGEID AUTOSUMMARYMODULETYPE TRANSPORT PRINTMODULETYPE PAYMMODE PAYMSPEC FIXEDDUEDATE DELIVERYCITY DELIVERYSTREET STATPROCID VENDORREF INTERCOMPANYCOMPANYID INTERCOMPANYSALESID INTERCOMPANYORDER PSANOTES PSASUBCONTRACT PSARETAINPERCENT PSARETAINSCHEDULEID COLLECTIONINVENTLOCATIONID COLLECTIONADDRESS CREATEDDATE CREATEDBY DATAAREAID RECVERSION RECID
PO-000416 Steel Craft Engineering STE001 STE001 27/11/2008 0 5637144581 1 GBP 00951 0 UK 0 FMT Northern Ireland UKVAT 3 3 ACP BT75 A2 UK Ty 0 0 0 0 Ag LTD 0 0 0 8 FMT 0 en-gb 0 0 CHEQUE 01/01/1900 Five 127 Road 0 0 0 24/11/2008 nethe agl 1589545165 5637150400


VENDINVOICEJOUR:
VENDGROUP PURCHID ORDERACCOUNT INVOICEACCOUNT INVOICEID INVOICEDATE DUEDATE CASHDISC CASHDISCDATE QTY VOLUME WEIGHT SUMLINEDISC PREPAYMENT SALESBALANCE ENDDISC INVOICEAMOUNT CURRENCYCODE EXCHRATE RETURNITEMNUM TAXROUNDOFF LEDGERVOUCHER DIMENSION DIMENSION2_ DIMENSION3_ DIMENSION4_ DIMENSION5_ DIMENSION6_ DIMENSION7_ TAXPRINTONINVOICE TAXSPECIFYBYLINE DOCUMENTNUM DOCUMENTDATE COUNTRYREGIONID INTRASTATDISPATCH INVOICEROUNDOFF SUMMARKUP PAYMID TAXGROUP CASHDISCCODE PAYMENT POSTINGPROFILE PAYMENTSCHED PURCHASETYPE SUMTAX PARMID EXCHRATESECONDARY TRIANGULATION ITEMBUYERGROUPID VATNUM INTERNALINVOICEID NUMBERSEQUENCEGROUP INCLTAX PAYMDAYID DLVTERM DLVMODE FIXEDDUEDATE PRINTORIGINALS PRINTCOPIES INTERCOMPANYCOMPANYID INTERCOMPANYSALESID INTERCOMPANYLEDGERVOUCHER PROFORMA LANGUAGEID DATAAREAID RECVERSION RECID
UK PO-000079 STE001 STE001 18255 31/12/2008 30/01/2009 0 01/01/1900 16 0 0 0 0 356.6 0 410.09 GBP 100 0 PI-01894 FMT 0 0 01/01/1900 UK 0 0 UKVAT 3 53.49 SYS-1175175 0 0 PI-01894 0 01/01/1900 1 0 0 agl 1361474226 5637165370


VENDINVOICEPURCHLINK:
INVOICEID INVOICEDATE PURCHID ORIGPURCHID INVOICEACCOUNT ORDERACCOUNT PARMID INTERNALINVOICEID DATAAREAID RECVERSION RECID
18255 31/12/2008 PO-000079 PO-000416 STE001 STE001 SYS-1175175 PI-01894 agl 1 5637158847


VENDPACKINGSLIPJOUR:
PURCHID ORDERACCOUNT INVOICEACCOUNT PACKINGSLIPID DELIVERYDATE DELIVERYNAME DELIVERYADDRESS QTY VOLUME WEIGHT PRINTED DIMENSION DIMENSION2_ DIMENSION3_ DIMENSION4_ DIMENSION5_ DIMENSION6_ DIMENSION7_ COUNTRYREGIONID INTRASTATDISPATCH DLVTERM DLVMODE DLVZIPCODE DLVCOUNTY DLVCOUNTRYREGIONID DLVSTATE LEDGERVOUCHER DELIVERYTYPE PURCHASETYPE RETURNITEMNUM FREIGHTSLIPNUM FREIGHTSLIPTYPE PARMID ITEMBUYERGROUPID INTERNALPACKINGSLIPID DELIVERYCITY DELIVERYSTREET PRINTORIGINALS PRINTCOPIES NUMBERSEQUENCEGROUP INTERCOMPANYCOMPANYID INTERCOMPANYSALESID INTERCOMPANYLEDGERVOUCHER LANGUAGEID DATAAREAID RECVERSION RECID
PO-000416 STE001 STE001 13075 01/12/2008 Ag Ltd Northern Ireland 8 0 0 0 UK BT75 A2 UK Ty GRN-001162 0 3 0 SYS-1110933 GRN-001162 Five 127 Road 1 0 en-gb agl 1207811659 5637168912


VENDPACKINGSLIPPURCHLINK:
PACKINGSLIPID DELIVERYDATE PURCHID ORIGPURCHID INVOICEACCOUNT ORDERACCOUNT PARMID INTERNALPACKINGSLIPID DATAAREAID RECVERSION RECID
13075 01/12/2008 PO-000416 PO-000416 STE001 STE001 SYS-1110933 GRN-001162 agl 1 5637168912


VENDPACKINGSLIPTRANS:
PACKINGSLIPID DELIVERYDATE LINENUM INVENTTRANSID DESTCOUNTRYREGIONID ITEMID EXTERNALITEMID NAME ORDERED QTY REMAIN PURCHASER PRICEUNIT DIMENSION DIMENSION2_ DIMENSION3_ DIMENSION4_ DIMENSION5_ DIMENSION6_ DIMENSION7_ VALUEMST PARTDELIVERY INVENTREFID INVENTREFTYPE PURCHID PURCHUNIT TRANSACTIONCODE INVENTREFTRANSID INTERCOMPANYINVENTTRANSID DESTSTATE ORIGPURCHID RETURNACTIONID TRANSPORT INTERNALPACKINGSLIPID INVENTDIMID STATPROCID PORT INVENTDATE NUMBERSEQUENCEGROUP DESTCOUNTY INTRASTATDISPATCHID INVENTQTY DATAAREAID RECVERSION RECID
13075 01/12/2008 0 LOT-021150 UK RAM-ST-NONCON-01 "Nylon bushings 50mm I.D
40mm long 55mm O.D 2.5mm wall" 8 8 0 1 FMT RMT PDO 80 1 0 PO-000416 Each Ty PO-000416 GRN-001162 DIM-000002 01/12/2008 A2 8 agl 724114906 5637169219
13110 11/12/2008 0 LOT-009698 UK RAM-ST-NONCON-01 "Misc - Steel Repairs and Maintenance
Repair port-a power" 1 1 0 1 FMT RMT SPL PRD 0 1 0 PO-000079 Each Ty PO-000079 GRN-001164 DIM-000002 11/12/2008 A2 1 agl 724114906 5637169222
13110 11/12/2008 1 LOT-087477 UK RAM-ST-NONCON-01 "Misc - Steel Repairs and Maintenance
masa ram repair 12/12/08" 3 3 0 1 PIT RMT PDO 0 1 0 PO-000079 Each Ty PO-000079 GRN-001164 DIM-000017 11/12/2008 3 agl 724114906 5637169223

---