November 6, 2008 at 8:01 am
Hi,
I have a table with the following fields
ID INT
D NUMERIC(8,0)
A NUMERIC(8,0)
I need to generate a report as
Q D A W
==========
0 10 8 2 (=Q+D - A)
2 12 8 6
Means W = ((Current row Q + Current row D) - Current row A)
Q = Previous row's W
How to write this query. I have written as
SELECT
CASE WHEN ID = 1 THEN 0 ELSE
(SELECT s1.D - s1.A FROM tab1 WHERE s1.ID = ABS(m.ID-1)),
m.D, m.A,
(
CASE WHEN ID = 1 THEN 0 ELSE
(SELECT s1.D - s1.A FROM tab1 WHERE s1.ID = ABS(m.ID-1)) + m.D
) - m.A AS W,
How to refer the previous row's Q?
thanks in advance
November 6, 2008 at 8:21 am
Hi friends,
I got the query. I did as
SELECT
(
CASE WHEN m.Id = 1 THEN 0 WHEN m.TempId = 2 THEN
(
SELECT s1.D - s1.A FROM #TempResult s1 WHERE s1.Id = ABS(m.Id) - 1
)
WHEN m.Id > 2 THEN
(
SELECT SUM(s1.D) - SUM(s1.A) FROM #TempResult S1 WHERE s1.Id < m.Id
)
END
) AS Q,
m.D, m.A,
(
(m.D +
CASE WHEN m.Id = 1 THEN 0 WHEN m.Id = 2 THEN
(
SELECT s1.D - s1.A FROM #TempResult s1 WHERE s1.Id = ABS(m.Id) - 1
)
WHEN m.Id > 2 THEN
(
SELECT SUM(s1.D) - SUM(s1.A) FROM #TempResult S1 WHERE s1.Id < m.Id
)
END
) - m.A) AS W
FROM #TempResult m
thanks
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply