Join and group

Ignition
1

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?

2

It looks like you are probably getting a GROUP BY problem.

I noticed that EXPR1 is the same as HOUR.

You could try changing that and see if it helps.

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

3

I was able to get a little deeper into this;
I believe the group By line which is supposed to control what rows I am actually supposed to sum is not correct because now I have 2 tables each with its own Summing range…
I thought the cross join was a problem and I replaced with the INNER join and am trying to be more exact in this script but I think the group by line is definitly wrong:

[code]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.Totalestops) AS “Totalestops”,
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) + ‘:00’ as Expir1
FROM OutboundDowntime t
INNER JOIN RapidPackInOutProduction p
ON 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’ =
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) + ‘:00’

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)
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) + ‘:00’,
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) + ‘:00’
ORDER BY Hour
[/code]