| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
03-29-08, 09:49
|
|
Registered User
|
|
Join Date: Mar 2008
Posts: 3
|
|
list use products
|
|
hi
I have 2 tables that are,
Quote:
Create table products (idproduct int, nameproduct varchar(80), primary key (idproduct))
Create table use(product int, dateuse date, primary key(dateuse,product))
|
what i want to do is, a select that show if a product was uses in that day, and if was, show a 1 ou X, if not dont show anything, and in the last collum show the sum.
the date can be for a week, 2008/1/1, 2008/1/2, 2008/1/3, 2008/1/4, 2008/1/5, 2008/1/6, 2008/1/7.
what i wanna do is like this.
Quote:
Prod_|_2008/1/1_|_2008/1/2___|_2008/1/2__|_2008/1/3_|_2008/1/3_| SUM___|
---------------------------------------------------------------------------|
_1__|_________|_____X_____|_____X____|_________|__ ___X____|____3__|
_2__|_________|_____X_____|_____X____|_________|__ ___X____|____3__|
_3__|____X____|___________|__________|_________|__ ___X____|____2__|
_4__|____X____|___________|_____X____|____X____|__ ___X____|____4__|
_5__|_________|_____X_____|_____X____|_________|__ ___X____|____3__|
_6__|____X____|___________|__________|_________|__ ___X____|____2__|
_7__|_________|____X______|_____X____|____X____|__ ___X____|____4__|
_8__|____X____|____X______|_____X____|_________|__ ___X____|____4__|
|
hope you can help me
cya
|
Last edited by jonasp; 03-29-08 at 12:17.
|
03-29-08, 11:04
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
Code:
SELECT product
, dateuse
FROM use
WHERE dateuse BETWEEN '2008-01-01' AND '2008-01-07'
ORDER
BY product
, dateuse
and then format the results with your application code
SQL should not be misused for cosmetic rearrangement, especially when this means the SQL becomes bloated, complex, and difficult to maintain
|
|
03-29-08, 11:33
|
|
Registered User
|
|
Join Date: Mar 2008
Posts: 3
|
|
|
|
hi
i have something like this, almost like i want, but dont show the X
Quote:
select idproduct as 'Product'
,sum(CASE used.dateused WHEN '2008-01-01' THEN "X" ELSE "" END)
,sum(CASE used.dateused WHEN '2008-01-02' THEN "X" ELSE "" END)
,sum(CASE used.dateused WHEN '2008-01-03'THEN "X" ELSE "" END)
,sum(CASE used.dateused WHEN '2008-01-04'THEN "X" ELSE "" END)
,sum(CASE used.dateused WHEN '2008-01-05'THEN "X" ELSE "" END)
,sum(CASE used.dateused WHEN '2008-01-06'THEN "X" ELSE "" END)
,sum(CASE used.dateused WHEN '2008-01-07'THEN "X" ELSE "" END)
,count(products.idproduct) as 'Total Used'
from products, used
where products.idproduct =used.product
group by products.idproduct;
|
|
|
03-29-08, 11:38
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
use MIN instead of SUM, and use ELSE NULL instead of ELSE ''
remove the Total Used column for the time being (i will help you put it back in a moment)
also, i don't think you need the products table -- could you confirm?
|
|
03-29-08, 12:10
|
|
Registered User
|
|
Join Date: Mar 2008
Posts: 3
|
|
Quote:
|
Originally Posted by r937
use MIN instead of SUM, and use ELSE NULL instead of ELSE ''
remove the Total Used column for the time being (i will help you put it back in a moment)
also, i don't think you need the products table -- could you confirm?
|
no i have to use it
now show me the X, but the days that i have 0 in the total, never been used, dont show me
right now i have
Code:
select products.idproduct as 'Product'
,CASE used.dateused WHEN '2008-01-01' THEN"X" ELSE " " END "01/01/2008"
,CASE used.dateused WHEN '2008-01-02' THEN"X" ELSE " " END "02/01/2008"
,CASE used.dateused WHEN '2008-01-03' THEN"X" ELSE " " END "03/01/2008"
,CASE used.dateused WHEN '2008-01-04' THEN"X" ELSE " " END "04/01/2008"
,CASE used.dateused WHEN '2008-01-05' THEN"X" ELSE " " END "05/01/2008"
,CASE used.dateused WHEN '2008-01-06' THEN"X" ELSE " " END "06/01/2008"
,CASE used.dateused WHEN '2008-01-07' THEN"X" ELSE " " END "07/01/2008"
,count(used.product) 'Total Used'
from products, used
where products.idproduct =used.product
group by products.idproduct;
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
list use products
