March 16, 2005 at 7:39 am
hello guys,
I have tabel with following data
TENANCYID SUPPLIERID EMPLOYEEID invAmout AMOUNT itemtype
----------- ----------- ----------- ----------- --------------------- -----------
852317 NULL NULL 0 -15.0000 5
852317 NULL NULL 0 96.3600 12
852317 NULL NULL 0 91.0200 12
852317 NULL NULL 0 73.6800 12
852317 NULL NULL 0 -2.8000 5
i need to write a select statement which retuen a result as
TENANCYID SUPPLIERID EMPLOYEEID invAmout openingBalance payment
----------- ----------- ----------- ----------- ---------------- ----------- --------
852317 NULL NULL 0 261.0600 -17.8000
openingBalance is the sum of teh amount with itemtype=12 and payment is itemtype=5
Thanks
Vinu
March 16, 2005 at 7:42 am
The business rules for how you arrive at those numbers are necessary for anyone to provide a solution that isn't a time-wasting guessing game.
March 16, 2005 at 2:25 pm
Try somthing like this...
SELECT TENANCYID=Max(TENANCYID),
SUPPLIERID,
EMPLOYEEID,
InvAmount,
openingBalance = (SELECT Sum(openingBalance) FROM mytablename WHERE itemtype=12 GROUP BY itemtype) ,
payment= (SELECT sum(openingbalance) FROM mytablename WHERE itemtype<>12 GROUP BY itemtype)
FROM mytablename
GROUP BY SUPPLIERID, EMPLOYEEID, InvAmount
March 17, 2005 at 3:13 am
Or this
SELECT TENANCYID, SUPPLIERID, EMPLOYEEID, invAmout,
SUM(CASE WHEN itemtype=12 THEN AMOUNT ELSE 0. END) as [openingBalance],
SUM(CASE WHEN itemtype=5 THEN AMOUNT ELSE 0. END) as [payment]
FROM
GROUP BY TENANCYID, SUPPLIERID, EMPLOYEEID, invAmout
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply