i****a
发帖数: 36252 | 1 SQL 2005, Have the following data
seq ID month type amount
1 1234 1 R 421
2 1234 1 Cr 410
3 1234 1 Inv 430
1 1234 2 Cr 625
2 1234 2 Inv 650
1 1234 3 Inv 916
1 1234 4 Inv 882
How to get the rows of Inv and Cr, only when Cr exists for that month, and
the difference between Cr and Inv
example result:
1234, 1, Cr, 410
1234, 1, Inv, 430, difference 20
1234, 2, Cr, 625
1234, 2, Inv, 650, difference 25 | i****a
发帖数: 36252 | 2 found this example. can do calculation from the sub query
CREATE TABLE Sales (DayCount smallint, Sales money)
CREATE CLUSTERED INDEX ndx_DayCount ON Sales(DayCount)
go
INSERT Sales VALUES (1,120)
INSERT Sales VALUES (2,60)
INSERT Sales VALUES (3,125)
INSERT Sales VALUES (4,40)
select *
from Sales
SELECT DayCount,
Sales,
coalesce((SELECT Sales
FROM Sales b
WHERE b.DayCount = a.DayCount+1), 0)
AS NextDaySales
FROM S | s*******6
发帖数: 3 | 3 select k.ID,k.mon,k.type,k.amount,
( select case k.amount-amount
when 0 then NULL
else k.amount-amount
end
from table where ID=k.id and MON=k.mon and TYPE='CR') as difference
from table as k,
(select ID,MON,COUNT (*) as test from table
where TYPE='Cr' or TYPE='Inv'
group by ID,MON having COUNT(*)>1) as s
where k.id=s.id
and k.mon=s.mon
and (k.TYPE='Cr' or k.TYPE='Inv') | i****a
发帖数: 36252 | 4 thanks!
I followed the example on 2nd post and got the result.
【在 s*******6 的大作中提到】
: select k.ID,k.mon,k.type,k.amount,
: ( select case k.amount-amount
: when 0 then NULL
: else k.amount-amount
: end
:
: from table where ID=k.id and MON=k.mon and TYPE='CR') as difference
: from table as k,
: (select ID,MON,COUNT (*) as test from table
: where TYPE='Cr' or TYPE='Inv'
|
|
