sales between inputs

[rotelmak]

I have table with this data(sql 2000):

product_id date_c input sales
001 01.09.2008 100
001 01.09.2008 30
001 10.09.2008 40
001 10.09.2008 10
001 11.09.2008 50
001 11.09.2008 20

How can I got this:

product_id date_c input last_date_s total_sales
001 01.09.2008 100 10.09.2008 80
001 11.09.2008 50 11.09.2008 20

Thanks
Vidoevski

 

[ArunPrithviraj]

Hi Vidoevski,

Not sure whether this will help. But in case u have time please try this and do let me know.


SELECT
OUTERTABLE.PRODUCT_ID,
(SELECT MIN(INNERTABLE.DATE_C)
FROM
TABLE1 INNERTABLE
WHERE INNERTABLE.INPUT IS NOT NULL
AND
INNERTABLE.PRODUCT_ID = OUTERTABLE.PRODUCT_ID
) AS DATE_C,
OUTERTABLE.INPUT,
(SELECT MAX(INNERTABLE.DATE_C)
FROM TABLE1 INNERTABLE
WHERE INNERTABLE.PRODUCT_ID = OUTERTABLE.PRODUCT_ID
AND
INNERTABLE.INPUT IS NULL
) AS LAST_DATE_S,
(
SELECT SUM(INNERTABLE.SALES) FROM TABLE1 INNERTABLE
WHERE INNERTABLE.PRODUCT_ID = OUTERTABLE.PRODUCT_ID
)
FROM
TABLE1 OUTERTABLE
WHERE
OUTERTABLE.INPUT IS NOT NULL
AND OUTERTABLE.SALES IS NULL

Thanks,
AP

 

[rotelmak]

Thanks AP, but I got this wrong result

product_id date_c input last_date_s total_sales
001 01.09.2008 100 11.09.2008 100
001 01.09.2008 50 11.09.2008 100

Result must be

product_id date_c input last_date_s total_sales
001 01.09.2008 100 10.09.2008 80
001 11.09.2008 50 11.09.2008 20

Vidoevski

 

[SQLSister]

Look at it this way, first you have to get input and sales on the same record. So write a query that does that by doing a self join.

Now use this query as a derived table to get the min date for each product id. Then do a union to get the max date for each product id using the same thing as a derived table.

In the future do not design a table to work this way. If you will have one sales and one input per product per date, they should be inthe same record form the start.

"NOTHING is more important in a database than integrity." ESquared

 

[simian101]

I dare not say it, but I will... You could do it with a cursor.

Simi