Merging functionality of 2 queries

Conrad 2015-05-19 20:42:52

I have 2 queries that each do part of what I want, but I am having difficulty merging into one that will handle all my needs. The scenario: there is a cookie factory, with cookies coming out of the oven one at a time. The cookies have orthogonal qualities: e.g. sugar free or not, type of chip, type of dough, etc. Each cookie gets assigned to a box (manually) as it exits the oven. Each box has only one set of cookie characteristics (e.g. all cookies in a box are chocolate chip, not sugar free, and chocolate dough). All boxes are numbered, starting with 1 with the first box of the day, increasing by 1 throughout the day. Box numbering is incremented manually. However, there may be a partial box from the previous day which did not get filled completely, so in that case, that box number (e.g. 40) would be the first box of the day, then going back to number 1 and continuing as usual.

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.

Query 1:

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)

Query 2:

;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