Merging functionality of 2 queries
The way things should work, is that cookies should come out of the oven in just the amount to fill a box, in order. However, due to human error, a cookie of the wrong type may get put in a box that it's not supposed to be in, or the person who is entering the data into the DB at the oven exit may incorrectly classify a cookie, or forget to increment the box number, so the cookie is in the right box in the end, but gets recorded incorrectly in the DB.
So in the 2 queries below, the first gets the box numbers in chronological order for today (which is what I want), but does not account for the fact that the "first" cookie in the box may not be representative of the rest in the box (due to operator error). The second query correctly finds the (statistical) mode of each cookie feature in each box, but does not order the boxes chronologically and may group a box from earlier in the day with a box of the same number later in the day.
WITH CookiePlus AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY OvenExitTime) AS rn FROM CookiesData WHERE OvenExitTime >= CONVERT(date, GETDATE()) ) SELECT cp1.BoxNo, cp1.DoughType, cp1.ChipType, cp1.IsSugarFree FROM CookiePlus AS cp1 LEFT JOIN CookiePlus AS cp2 ON cp2.rn = cp1.rn + 1 WHERE cp2.OvenExitTime >= CONVERT(date, GETDATE()) AND (cp2.BoxNo <> cp1.BoxNo OR cp2.rn IS NULL)
;WITH Ranked AS ( SELECT BoxNo, DoughType, ChipType, IsSugarFree, CookieFreq = COUNT(*), Ranking = DENSE_RANK() OVER (PARTITION BY BoxNo ORDER BY COUNT(*) DESC) FROM CookiesData WHERE OvenExitTime >= CONVERT(date, GETDATE()) GROUP BY BoxNo, DoughType, ChipType, JointType ) SELECT BoxNo, MIN(DoughType) AS DoughType, MIN(ChipType) AS ChipType, MIN(IsSugarFree) AS IsSugarFree FROM Ranked WHERE Ranking = 1 GROUP BY BoxNo