La pregunta es : enumere si hay algún modelo que estaba entre los 5 primeros en términos de cantidad, simultáneamente en 2008, 2009 y 2010

Quiero extraer valores que son comunes en 3 consultas diferentes, como se muestra a continuación en una consulta:

Select Top 5 id_model, Count(Quantity) 
From Fact_Transactions
Where Year(date) = 2008
Group by ID_Model
Order by Count(Quantity) DESC

Select Top 5 id_model, Count(Quantity) 
From Fact_Transactions
Where Year(date) = 2009
Group by ID_Model
Order by Count(Quantity) DESC

Select Top 5 id_model, Count(Quantity) 
From Fact_Transactions
Where Year(date) = 2010
Group by ID_Model
Order by Count(Quantity) DESC

Tabla de muestra:

   Id_model         id_cust  id_loc             DATE         Quantity Total_price
    LNVO_A6600P     275262  CA-140858   2007-07-25 00:00:00.000 2   332.00
        Gi_X1       275262  CA-140858   2006-05-06 00:00:00.000 2   492.00
        Gi_SP       275261  CA-104220   2010-08-01 00:00:00.000 3   597.00
        MI_A1       275261  CA-104220   2010-03-20 00:00:00.000 1   144.00
        AC_LX1      275255  CA-120768   2009-05-30 00:00:00.000 5   1160.00
        Gi_SP       275252  US-131149   2009-10-17 00:00:00.000 2   398.00
        AC_LZ6      275250  CA-106075   2003-06-28 00:00:00.000 1   152.00
0
Aman kashyap 23 feb. 2018 a las 08:28

6 respuestas

La mejor respuesta

En lugar de 3, intente esta única consulta

;WITH CTE
AS
(
    SELECT
       RN = ROW_NUMBER() OVER(PARTITION BY YEAR([date]) ORDER BY COUNT(Quantity) DESC),
       id_model,
       MyYear = YEAR([date]),
       Cnt = COUNT(Quantity)
    FROM Fact_Transactions
       WHERE YEAR(date) IN (2008,2009,2010)
       GROUP BY 
          ID_Model,
          YEAR([date])
)
SELECT
    id_model
    FROM CTE
       WHERE RN <6
          GROUP BY id_model
          HAVING COUNT(1)=3
1
Jayasurya Satheesh 23 feb. 2018 a las 05:41

Puede usar la operación INTERSECT:

Select Top 5 id_model,Count(Quantity) from Fact_Transactions
Where Year(date) =2008
Group by ID_Model
Order by Count(Quantity) DESC
INTERSECT
Select Top 5 id_model,Count(Quantity) from Fact_Transactions
Where Year(date) = 2009
Group by ID_Model
Order by Count(Quantity) DESC
INTERSECT
Select Top 5 id_model,Count(Quantity) from Fact_Transactions
Where Year(date) = 2010
Group by ID_Model
Order by Count(Quantity) DESC
1
codeLover 23 feb. 2018 a las 05:35

Obtendrá respuestas correctas al usar esto ...

select * from (SELECT 
    TOP 5 Manufacturer_name
    FROM Fact_Transactions T1
    LEFT JOIN DIM_Model D1 ON T1.IDModel = D1.IDModel
    LEFT JOIN DIM_MANUFACTURER D2  ON D2.IDManufacturer = D1.IDManufacturer
    Where DATEPART(Year,date)='2008' 
    group by Manufacturer_name, Quantity 
    Order by  SUM(Quantity ) DESC  
    intersect
SELECT  TOP 5 Manufacturer_name
    FROM Fact_Transactions T1
    LEFT JOIN DIM_Model D1 ON T1.IDModel = D1.IDModel
    LEFT JOIN DIM_MANUFACTURER D2  ON D2.IDManufacturer = D1.IDManufacturer
    Where DATEPART(Year,date)='2009' 
    group by Manufacturer_name, Quantity 
    Order by  SUM(Quantity ) DESC  
    intersect
SELECT TOP 5 Manufacturer_name
    FROM Fact_Transactions T1
    LEFT JOIN DIM_Model D1 ON T1.IDModel = D1.IDModel
    LEFT JOIN DIM_MANUFACTURER D2  ON D2.IDManufacturer = D1.IDManufacturer
    Where DATEPART(Year,date)='2010' 
    group by Manufacturer_name, Quantity 
    Order by  SUM(Quantity ) DESC)  as A
0
shashvindu 9 dic. 2019 a las 16:29

Seleccione * de (seleccione el top 5 idmodel de FACT_TRANSACTIONS donde año (fecha) = grupo '2008' por idmodel orden por suma (cantidad) desc intersecarse seleccione el top 5 de idmodel de FACT_TRANSACTIONS donde año (fecha) = grupo '2009' por idmodelo, orden por suma (cantidad) desc intersecarse seleccione el top 5 de idmodel de FACT_TRANSACTIONS donde el año (fecha) = '2010' agrupe por idmodel ordene por suma (cantidad) desc) tbl texto fuerte

-1
Parv Sharma 8 may. 2020 a las 04:32

Probar esto

select * from (
Select Top 5 id_model,Count(Quantity) from Fact_Transactions
Where Year(date) =2008
Group by ID_Model
Order by Count(Quantity) DESC
union all
Select Top 5 id_model,Count(Quantity) from Fact_Transactions
Where Year(date) = 2009
Group by ID_Model
Order by Count(Quantity) DESC
union all
Select Top 5 id_model,Count(Quantity) from Fact_Transactions
Where Year(date) = 2010
Group by ID_Model
Order by Count(Quantity) DESC)a
0
Mukesh Kalgude 23 feb. 2018 a las 05:41

Un simple inner join hará:

SELECT id_model
FROM
(
    SELECT TOP 5 id_model, COUNT(Quantity)
    FROM Fact_Transactions
    WHERE YEAR(date) = 2008
    GROUP BY ID_Model
    ORDER BY COUNT(Quantity) DESC
) m2008
INNER JOIN
(
    SELECT TOP 5 id_model, COUNT(Quantity)
    FROM Fact_Transactions
    WHERE YEAR(date) = 2009
    GROUP BY ID_Model
    ORDER BY COUNT(Quantity) DESC
) m2009
    ON m2008.id_model = m2009.id_mode
INNER JOIN
(
    SELECT TOP 5 id_model, COUNT(Quantity)
    FROM Fact_Transactions
    WHERE YEAR(date) = 2010
    GROUP BY ID_Model
    ORDER BY COUNT(Quantity) DESC
) m2010
    ON m2008.id_model = m2010.id_model;
0
Tim Biegeleisen 23 feb. 2018 a las 05:36