Explain [T-SQL Code]

Question

Hi,

can some one explain this code step by step.

SELECT OrderID
FROM (
SELECT s1.OrderID,
MIN(s1.Items) AS Items,
SUM(CASE WHEN s1.OrderID = s2.OrderID THEN 0 ELSE 1 END) AS Hits,
CASE
WHEN MIN(s1.Items) < MIN(s2.Items) THEN MIN(s2.Items)
ELSE MIN(s1.Items)
END AS Cases
FROM (
SELECT OrderID,
COUNT(*) OVER (PARTITION BY OrderID) AS Items,
SKU
FROM @Sample
) AS s1
INNER JOIN (
SELECT OrderID,
COUNT(*) OVER (PARTITION BY OrderID) AS Items,
SKU
FROM @Sample
) AS s2 ON s2.SKU = s1.SKU
GROUP BY s1.OrderID,
s2.OrderID
) AS d
GROUP BY OrderID
ORDER BY MAX(1.0 * Hits / Cases) DESC

Answer 1

It would be nice if you use the code block to get the query in some sort of shape:

SELECT OrderID
FROM   ( SELECT s1.OrderID
         ,      MIN(s1.Items) AS Items
         ,      SUM( CASE WHEN s1.OrderID = s2.OrderID
                       THEN 0
                       ELSE 1
                     END
                   ) AS Hits
         ,      CASE WHEN MIN(s1.Items) < MIN(s2.Items)
                  THEN MIN(s2.Items)
                  ELSE MIN(s1.Items)
                END AS Cases
         FROM   ( SELECT OrderID
                  , COUNT(*) OVER (PARTITION BY OrderID) AS Items
                  , SKU
                  FROM @Sample
                ) AS s1
         INNER JOIN
               ( SELECT OrderID
                 ,      COUNT(*) OVER (PARTITION BY OrderID) AS Items
                 ,      SKU
                 FROM  @Sample
               ) AS s2
         ON s2.SKU = s1.SKU
         GROUP BY s1.OrderID, s2.OrderID
       ) AS d
GROUP  BY OrderID
ORDER  BY MAX(1.0 * Hits / Cases) DESC

Answer 2

SELECT OrderID
FROM 
(		SELECT 
			s1.OrderID,
			MIN(s1.Items) AS Items, -- min of items 
			SUM( CASE WHEN s1.OrderID = s2.OrderID THEN 0 ELSE 1 END) AS Hits,
			CASE	WHEN MIN(s1.Items) < MIN(s2.Items) THEN MIN(s2.Items) -- first case condition
					ELSE MIN(s1.Items)
					END AS Cases
		FROM (	SELECT OrderID, COUNT(*) OVER (PARTITION BY OrderID) AS Items, SKU FROM @Sample) AS s1 -- this is the s1 table
		INNER JOIN (	SELECT OrderID, COUNT(*) OVER (PARTITION BY OrderID) AS Items, SKU FROM @Sample ) AS s2 -- this is s2 table
				ON s2.SKU = s1.SKU -- inner join between sq, s2 table based on SKU field
		GROUP BY s1.OrderID, s2.OrderID -- since MIN, SUm aggregate functions are used in select query, the OrferId needs to be grouped
) AS d -- this is the final table after calculation within sub queries
GROUP BY OrderID
ORDER BY MAX(1.0 * Hits / Cases) DESC -- sorting based on this calculation

case : http://msdn.microsoft.com/en-us/library/ms181765.aspx

group by : http://msdn.microsoft.com/en-us/library/ms177673.aspx

regards

joon

Answer 3

I'm to laze to do a step by step explanation. I think you should learn to read T-SQL yourself.

Here are some pointers:

The construction "FROM (...) AS <Alias>" is called a derived table or inline view: http://www.sqlatoms.com/sql-server-2/how-to-create-and-use-a-derived-table-in-sql-server/

This is how the OVER clause works: http://msdn.microsoft.com/en-us/library/ms189461.aspx

And here is some reading on the GROUP BY clause: http://msdn.microsoft.com/en-us/library/ms177673.aspx