Edit Question - KnowledgeHub

Title Body

Today, for the first time in 10 years of development with sql server I used a cross join in a production query. I needed to pad a result set to a report and found that a cross join between two tables with a creative where clause was a good solution. I was wondering what use has anyone found in production code for the cross join? Update: the code posted by Tony Andrews is very close to what I used the cross join for. Believe me, I understand the implications of using a cross join and would not do so lightly. I was excited to have finally used it (I'm such a nerd) - sort of like the time I first used a full outer join. Thanks to everyone for the answers! Here's how I used the cross join: SELECT CLASS, [Trans-Date] as Trans_Date, SUM(CASE TRANS WHEN 'SCR' THEN [Std-Labor-Value] WHEN 'S+' THEN [Std-Labor-Value] WHEN 'S-' THEN [Std-Labor-Value] WHEN 'SAL' THEN [Std-Labor-Value] WHEN 'OUT' THEN [Std-Labor-Value] ELSE 0 END) AS [LABOR SCRAP], SUM(CASE TRANS WHEN 'SCR' THEN [Std-Material-Value] WHEN 'S+' THEN [Std-Material-Value] WHEN 'S-' THEN [Std-Material-Value] WHEN 'SAL' THEN [Std-Material-Value] ELSE 0 END) AS [MATERIAL SCRAP], SUM(CASE TRANS WHEN 'RWK' THEN [Act-Labor-Value] ELSE 0 END) AS [LABOR REWORK], SUM(CASE TRANS WHEN 'PRD' THEN [Act-Labor-Value] WHEN 'TRN' THEN [Act-Labor-Value] WHEN 'RWK' THEN [Act-Labor-Value] ELSE 0 END) AS [ACTUAL LABOR], SUM(CASE TRANS WHEN 'PRD' THEN [Std-Labor-Value] WHEN 'TRN' THEN [Std-Labor-Value] ELSE 0 END) AS [STANDARD LABOR], SUM(CASE TRANS WHEN 'PRD' THEN [Act-Labor-Value] - [Std-Labor-Value] WHEN 'TRN' THEN [Act-Labor-Value] - [Std-Labor-Value] --WHEN 'RWK' THEN [Act-Labor-Value] ELSE 0 END) -- - SUM([Std-Labor-Value]) -- - SUM(CASE TRANS WHEN 'RWK' THEN [Act-Labor-Value] ELSE 0 END) AS [LABOR VARIANCE] FROM v_Labor_Dist_Detail where [Trans-Date] b

Tags (comma-separated) Cancel