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 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply