Hello.
I'm trying to do a query to calculate the total entries per hour in a table and show only the top value for the date.
For example, Today model A has results at 6 for 100 pcs, at 7 for 200 pcs and at 8 for 50 pcs. In this case what I need to show is only 200 pcs in a column and the Time (7) in another column.
I have been able to show the top values but I cannot show the time, if I show the time then the top does not work...
Samples:
Including Hours:
Without Top[
^]
Not Including Hours:
WithTop[
^]
I really appreciate your help!
What I have tried:
This is the query that I've tried so far (Without Hour)
SELECT *
FROM
(
select count(barcodenum)as count1, date1, Model_sap, model_customer, linea
from (
SELECT FinishGoods.linea, FinishGoods.Model_sap, model.model_customer,FinishGoods.BarCodeNum,CONVERT(VARCHAR(10), scanprod.fecha, 102) as date1, scanprod.fecha
FROM FinishGoods INNER JOIN model ON FinishGoods.Model_sap = model.model_ext INNER JOIN scanprod ON FinishGoods.BarCodeNum = scanprod.barcodenum
WHERE (FinishGoods.linea <> '0') AND (FinishGoods.linea <> '') AND FinishGoods.scandt is not null AND (scanprod.fecha >= '2016-03-18 06:00:00' AND scanprod.fecha <= '2016-03-22 23:59:59' )
) as tbl1
group by date1, DATEPART(Hour, fecha), Model_sap, model_customer,linea--,hora1
--order by model_sap, linea
) AS HourlySalesData
PIVOT( max(count1) FOR [date1] IN ([2016.03.18],[2016.03.19],[2016.03.20],[2016.03.21],[2016.03.22])) AS DatePivot
order by model_customer
And This one with hour included:
SELECT *
FROM
(
select count(barcodenum)as count1, date1, Model_sap, model_customer, linea, (convert(int, DATEPART(Hour, fecha))) as hour_
from (
SELECT FinishGoods.linea, FinishGoods.Model_sap, model.model_customer,FinishGoods.BarCodeNum,CONVERT(VARCHAR(10), scanprod.fecha, 102) as date1, scanprod.fecha
FROM FinishGoods INNER JOIN model ON FinishGoods.Model_sap = model.model_ext INNER JOIN scanprod ON FinishGoods.BarCodeNum = scanprod.barcodenum
WHERE (FinishGoods.linea <> '0') AND (FinishGoods.linea <> '') AND FinishGoods.scandt is not null AND (scanprod.fecha >= '2016-03-18 06:00:00' AND scanprod.fecha <= '2016-03-22 23:59:59' )
) as tbl1
group by date1, DATEPART(Hour, fecha), Model_sap, model_customer,linea--,hora1
--order by model_sap, linea
) AS HourlySalesData
PIVOT( max(count1) FOR [date1] IN ([2016.03.18],[2016.03.19],[2016.03.20],[2016.03.21],[2016.03.22])) AS DatePivot
order by model_customer