Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

sales between inputs

Status
Not open for further replies.

rotelmak

Programmer
Nov 29, 2004
31
MK
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
 
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top