2

I have a query where I JOIN 4 weekly "summary" tables, and SELECT a MIN and a MAX of units FROM each table. This would give me the highest/lowest total units sold for, and I'm pulling an average from another table.

    SELECT
            L4.TotalUnits / 4   AS AvgMvmt,
            MIN(Ago.TotalUnits) AS LowMvmt,
            MAX(Ago.TotalUnits) AS HighMvmt
    FROM

            RTG_LOOKUP_LAST4 AS L4 INNER JOIN
            (
                    SELECT  UCC12UPCCode, TotalUnits, Date
                    FROM    [CACTUS].[dbo].[RTG_Lookup2_Last1]

                    UNION

                    SELECT  UCC12UPCCode, TotalUnits, Date
                    FROM    [CACTUS].[dbo].[RTG_Lookup2_2_Ago]

                    UNION

                    SELECT  UCC12UPCCode, TotalUnits, Date
                    FROM    [CACTUS].[dbo].[RTG_Lookup2_3_Ago]

                    UNION

                    SELECT  UCC12UPCCode, TotalUnits, Date
                    FROM    [CACTUS].[dbo].[RTG_Lookup2_4_Ago]
            ) AS Ago
                    ON Ago.UCC12UPCCode = L4.UCC12UPCCode
    WHERE
            L4.UCC12UPCCode = '01254601144'
    GROUP BY
            L4.TotalUnits

Now I need to add two more columns, HighDate and LowDate. These columns represent the date for the week that sold the highest/lowest units.

The Ago table looks like this:

UCC12UPCCode  TotalUnits  Date
------------- ----------- ----------
01254601144   90          2018-04-14
01254601144   98          2018-05-05
01254601144   107         2018-04-21
01254601144   132         2018-04-28

How would pull the Date column from the correct table so my end results looks like below:

LowMvmt     HighMvmt    LowDate    HighDate
----------- ----------- ---------- ----------
90          132         2018-04-14 2018-04-28

E: Fiddle https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=749c59e1f11bc2146a605072f1ef7d98

  • I mean, just use MIN(Date) and MAX(Date)?. (you also need to stop grouping by the TotalUnits) – Lamak May 8 '18 at 18:13
  • MIN/MAX(Date) will return just that, min/max date, not the date that corresponds to MIN(Ago.TotalUnits) (if the first 2 total units, 90 and 98 were swapped, MIN(date) will not be correct.). I also have to group by unless i remove L4.TotalUnits / 4 AS AvgMvmt, which i need – lizzy91 May 8 '18 at 18:18
  • 2
    You could try providing an SQL Fiddle with sample data and your desired result - your question is quite complex -- help us to help you! :-) – Vérace May 8 '18 at 18:49
  • @Vérace see edit – lizzy91 May 8 '18 at 18:59
1

As a another option

WITH Ago AS (
    SELECT  UCC12UPCCode, TotalUnits, Date
    FROM    [CACTUS].[dbo].[RTG_Lookup2_Last1]

    UNION

    SELECT  UCC12UPCCode, TotalUnits, Date
    FROM    [CACTUS].[dbo].[RTG_Lookup2_2_Ago]

    UNION

    SELECT  UCC12UPCCode, TotalUnits, Date
    FROM    [CACTUS].[dbo].[RTG_Lookup2_3_Ago]

    UNION

    SELECT  UCC12UPCCode, TotalUnits, Date
    FROM    [CACTUS].[dbo].[RTG_Lookup2_4_Ago]
    ),
    Ranked AS (
    SELECT *, 
        ROW_NUMBER() OVER (PARTITION BY UPC ORDER BY TotalUnits, Date) LowUnitRank, 
        ROW_NUMBER() OVER (PARTITION BY UPC ORDER BY TotalUnits DESC, Date) HighUnitRank
    FROM Ago
    )
SELECT L4.UCC12UPCCode,
    MAX(CASE WHEN LowUnitRank = 1 THEN TotalUnits END) LowTotalUnits,
    MAX(CASE WHEN HighUnitRank = 1 THEN TotalUnits END) HighTotalUnits,
    MAX(CASE WHEN LowUnitRank = 1 THEN Date END) LowUnitDate,
    MAX(CASE WHEN HighUnitRank = 1 THEN Date END) HighUnitDate
FROM RTG_LOOKUP_LAST4 L4
    INNER JOIN Ranked r ON L4.UCC12UPCCode = r.UCC12UPCCode
GROUP BY L4.UCC12UPCCode;

I've done this using a CTE query as I find it easier to follow and explain.

The first CTE is just your Ago data.

The second CTE ranks each row of the Ago data using ROW_NUMBER based on the ordering of the TotalUnits. I have include the Date in the order so that if there are duplicate TotalUnits the first occurrence will be picked.

In the final query we are using CASE statements to pivot the data and aggregating the result for each UCC12UPCCode with MAX to create a single row for each UCC12UPCCode.

This query can be run over multiple UCC12UPCCode's

1

A similar solution as @MickyT:

select upc
     , max(case when asc_order = 1 then totalunits end) as min_units
     , max(case when desc_order = 1 then totalunits end) as max_units
     , max(case when asc_order = 1 then date end) as min_date
     , max(case when desc_order = 1 then date end) as max_date
from (
    SELECT row_number() over (order by TotalUnits) as asc_order
         , row_number() over (order by TotalUnits desc) as desc_order
         , upc
         , TotalUnits
         , Date
    FROM (
        SELECT  *   FROM    #RTG_Lookup2_4_Ago
        UNION
        SELECT  *   FROM    #RTG_Lookup2_3_Ago
        UNION
        SELECT  *       FROM    #RTG_Lookup2_2_Ago
        UNION
        SELECT  *       FROM    #RTG_Lookup2_Last1
    ) AS Ago
) as x
where 1 in (asc_order, desc_order)
group by upc;
1

I took a different approach - thanks for the fiddle BTW. I think that you should consider changing your schema - you appear to be doing UNIONs between a series of tables with different dates. This leads to a strange result

Low   High      Low date     High Date
 90    132    2018-04-14    2018-04-28

To me, it makes no sense to combine the low sale and date with the high sale and date - it makes much more sense in a relational theory to have this

Sale          Date        Volume
  90         2018-04-14      Low
 132         2018-04-28     High

To tackle this I did the following (see my own fiddle here):

CREATE TABLE movement (share_sale INTEGER, share_code VARCHAR(20), mvt_date DATETIME);

INSERT INTO movement VALUES (100, '01254601144', '2018-05-08');
INSERT INTO movement VALUES (150, '01254601144', '2018-05-08');
INSERT INTO movement VALUES (137, '01254601144', '2018-05-08');
INSERT INTO movement VALUES (112, '01254601144', '2018-05-08');
INSERT INTO movement VALUES (190, '01254601144', '2018-05-07');
INSERT INTO movement VALUES (260, '01254601144', '2018-05-07');
INSERT INTO movement VALUES (121, '01254601144', '2018-05-07');
INSERT INTO movement VALUES (165, '01254601144', '2018-05-07');
INSERT INTO movement VALUES ( 95, '01254601144', '2018-05-06');
INSERT INTO movement VALUES (239, '01254601144', '2018-05-06');
INSERT INTO movement VALUES (120, '01254601144', '2018-05-06');
INSERT INTO movement VALUES (111, '01254601144', '2018-05-06');
INSERT INTO movement VALUES (222, '01254601144', '2018-05-06');
INSERT INTO movement VALUES ( 86, '01254601144', '2018-05-06');
INSERT INTO movement VALUES ( 75, '01254601144', '2018-05-05');
INSERT INTO movement VALUES ( 45, '01254601144', '2018-05-05');
INSERT INTO movement VALUES ( 98, '01254601144', '2018-05-05');
INSERT INTO movement VALUES (120, '01254601144', '2018-05-05');
INSERT INTO movement VALUES ( 67, '01254601144', '2018-05-05');

And then I ran this query:

WITH cte1 AS
(
  SELECT MIN(share_sale) AS min_sale, mvt_date AS min_mvt_date
  FROM movement
  GROUP BY mvt_date
),
cte2 AS 
(
  SELECT MAX(share_sale) AS max_sale, mvt_date AS max_mvt_date
  FROM movement
  GROUP BY mvt_date
),
cte3 AS
(
SELECT * FROM cte1
JOIN
(
  SELECT * FROM cte2
) AS tab
ON cte1.min_mvt_date = tab.max_mvt_date
)
SELECT min_sale, max_sale, max_mvt_date AS mvt_date FROM cte3

Result:

min_sale  max_sale     date:
      45       120     05/05/2018 00:00:00
      86       239     06/05/2018 00:00:00
     121       260     07/05/2018 00:00:00
     100       150     08/05/2018 00:00:00

Now - on a given day, I give you (with my simulated data) the lowest sale and the highest sale - to me, this makes more sense than putting two different dates in with 2 different figures. If there isn't a sale every day, then maybe something would have to be done with NULLs. Just a few thoughts.

  • If it helps, this is sales data. we cant "predict" which week will sell more/less. the 4 tables are weekly summaries of that weeks sales. In this situation, i need to know which of the 4 weeks sold the most/least and what that date is. The column Volume wouldnt help in this case (or atleast i dont think it would unless we update that column prior to running the query, correct me if im wrong) – lizzy91 May 8 '18 at 20:56
  • Have a week_ending column so. The actual metric doesn't matter that much as long as you're comparing like with like on a weekly basis. But I don't see why you have tables for the last 4 weeks. Tables these days can easily hold hundreds of millions of records - I'd amalgamate if I were you - unless you're Walmart :-) – Vérace May 8 '18 at 21:16
0

I think you can use corelated subquery to get your desired result

SELECT
    MIN(Ago.TotalUnits) AS LowMvmt,
    MAX(Ago.TotalUnits) AS HighMvmt,
    (select Date
     from #TempAgo as Ago2
     where min(Ago.totalunits) = ago2.totalunits) as lowdate,
    (select Date
     from #TempAgo as Ago2
     where max(Ago.totalunits) = ago2.totalunits) as highdate
FROM 
    #TempAgo as Ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.