Query ?

[ravi]

Please give me the query for the below?

Given the following populated table:

CREATE TABLE Q1Inventory
(
pno char(2) NOT NULL,
movedate datetime NOT NULL,
qty int NOT NULL
)
GO
INSERT INTO Q1Inventory(pno,movedate,qty) VALUES('P1','05/1/2003',100)
INSERT INTO Q1Inventory(pno,movedate,qty) VALUES('P1','05/2/2003',120)
INSERT INTO Q1Inventory(pno,movedate,qty) VALUES('P1','05/4/2003',-150)
INSERT INTO Q1Inventory(pno,movedate,qty) VALUES('P1','05/5/2003',50)
INSERT INTO Q1Inventory(pno,movedate,qty) VALUES('P1','05/06/2003',-35)
GO

List as many different queries that will produce the following result set containing the original data and a running total of quantity on hand. Use additional sheets of paper if required. For example:

movedate qty qtyonhand
----------------------------------------
2003/05/01 100 100
2003/05/02 120 220
2003/05/04 -150 70
2003/05/05 50 120
2003/05/06 -35 85

[rmiao]

For homework?

[ravi]

Yes

[MAK]

One Way of doing it is


SELECT movedate,qty,(SELECT SUM(qty)
FROM Q1Inventory AS x
WHERE y.movedate >= x.movedate) AS
qtyinhand
FROM Q1Inventory AS y

[MAK]

Another way......

SELECT movedate,qty,(SELECT SUM(qty)
FROM Q1Inventory AS x
WHERE x.movedate <= y.movedate) AS
qtyinhand
FROM Q1Inventory AS y