I have 2 tables that both have their own time stamps but I want to join them and look at the hourly sums of the columns and group them accordindly.
This is the query I have now:
SELECT SUM(t.Oubound90Jam) AS Outbound90Jam, SUM(t.OuboundMergeJam) AS "OutboundMergeJam",SUM(t.OuboundCurveAJam) AS "OutboundCurveAJam",SUM(t.OuboundCurveBJam) AS "OutboundCurveBJam", SUM(t.OuboundEStops) AS "OutboundEStops",
CAST(DATEPART(month, t.t_stamp) AS varchar) + '-' + CAST(DATEPART(day, t.t_stamp) AS varchar) + ' ' + RIGHT('0' + CAST(DATEPART(hour, t.t_stamp) AS varchar), 2) + ':00' AS Hour,
SUM(p.OutboundWest180CurveJams) AS "OutboundWest180CurveJams", SUM(p.OutboundEast180CurveJams) AS "OutboundEast180CurveJams", SUM(p.OutboundMDR90CurveJams) AS "OutboundMDR90CurveJams",
SUM(p.OutboundMergeJams) AS "OutboundMergeJams",CAST(DATEPART(month, t.t_stamp) AS varchar) + '-' + CAST(DATEPART(day, t.t_stamp) AS varchar) + ' ' + RIGHT('0' + CAST(DATEPART(hour, t.t_stamp) AS varchar), 2) + ':00' AS Expr1
FROM dbo.OutboundDowntime AS t CROSS JOIN
dbo.RapidPackInOutProduction AS p
WHERE t.t_stamp BETWEEN Cast('{Root Container.OutboundCartonFaults.StartDate} {Root Container.OutboundCartonFaults.Start}' as datetime)
and Cast('{Root Container.OutboundCartonFaults.EndDate} {Root Container.OutboundCartonFaults.End}' as datetime) AND
CAST(DATEPART(month, t.t_stamp) AS varchar) + '-' + CAST(DATEPART(day, t.t_stamp) AS varchar) + ' ' + RIGHT('0' + CAST(DATEPART(hour, t.t_stamp) AS varchar), 2) =
CAST(DATEPART(month, p.t_stamp) AS varchar) + '-' + CAST(DATEPART(day, p.t_stamp) AS varchar) + ' ' + RIGHT('0' + CAST(DATEPART(hour, p.t_stamp) AS varchar), 2)
GROUP BY CAST(DATEPART(month, t.t_stamp) AS varchar) + '-' + CAST(DATEPART(day, t.t_stamp) AS varchar) + ' ' + RIGHT('0' + CAST(DATEPART(hour, t.t_stamp) AS varchar), 2),
CAST(DATEPART(month, p.t_stamp) AS varchar) + '-' + CAST(DATEPART(day, p.t_stamp) AS varchar) + ' ' + RIGHT('0' + CAST(DATEPART(hour, p.t_stamp) AS varchar), 2)
ORDER BY Hour
I believe the problem is the cross join. I am not producing the correct results, my values seem to be adding too many times.
I am producing 1 row per minute in each table.
Does anyone have any suggestions or can see the problems with the code?