How to select different dates from one column in DATEDIFF sql

quentin 0
Viewed 42

I'm working on a data warehouse where I have one fact table 'Inventory' and three dimension tables: Product, Location and Time. Each dimension table is linked to the fact table with foreign key on their id.

In my fact table, I have two different dates, each references the primary key idTime in my Time table dimension.

My problem is the following: I want to find the average difference of days between two dates using DATEDIFF statement. Here is my query:

select P.nameProduct, AVG (DATEDIFF(
        (select T.fulldate from Time as T, Inventory as I 
                        WHERE I.idTime_stockout = T.idTime),
        (select T.fulldate from Time as T, Inventory as I 
                        WHERE I.idTime_resupply = T.idTime)
    )) as Time_to_stock_out
FROM Inventory as I, Product as P, Time as T
WHERE I.idProduct = P.idProduct
GROUP BY P.nameProduct;

'fulldate' represent the date column in my Time dimension table. 'idTime_stockout' and 'idTime_stockout' represent two date column in my Fact table which references my Time table

In the average datediff i only want the dates that meet my criteria and not all the dates of the column. So for the first argument I select all the dates of the 'idTime_stockout' column and for the second argument all the dates of the 'idTime_resupply' column. And after I apply my conditions with a WHERE statement.

But I have a problem because the query returns the following error message:

ERROR 1242 (21000): Subquery returns more than 1 row

Can you help me to resolve this problem please ?

Quentin

star-schema datediff data-warehouse mysql sql
1 Answers

maybe,you should using 'limit 1' after the both subquery in the DateDiff field?

select P.nameProduct,
AVG(DATEDIFF((select T.fulldate from Time as T,
Inventory as I WHERE I.idTime_stockout = T.idTime limit 1),
(select T.fulldate from Time as T, 
Inventory as I WHERE I.idTime_resupply = T.idTime limit 1)))
 as Time_to_stock_out
FROM Inventory as I, Product as P WHERE I.idProduct = P.idProduct GROUP BY P.nameProduct;
KomiShao0
Related