CODEWITHSUNDEEP
sql
Florin
Florin

Reputation: 555

Count the sales under a specific value on 3 consecutive days

enter image description here

My data is the column day and sales. The days are ascending but not consecutive.

I create the pattern column with a case statement and my next step is to get the count of the pattern column.

A count of 1 where is just a single 2 in a bucket and so on. I m interested in the buckets of 3.

What I try to achieve in the final is to get the days 3 consecutive days where the sales drop under 500 but for the moment I want this count on the pattern column.

Thank you so much in advance

Upvotes: 0

Views: 562

Answers (1)

mflournoy
mflournoy

Reputation: 76

This answer is for SQL Server.

Given the following table structure:

CREATE TABLE #example (saleDate DATE, sales INT);
INSERT INTO #example (saleDate, sales)
VALUES
  ('2021-12-30', 852),
  ('2021-12-31', 985),
  ('2022-01-01', 460),
  ('2022-01-02', 350),
  ('2022-01-03', 1015),
  ('2022-01-04', 254),
  ('2022-01-05', 352),
  ('2022-01-06', 95),
  ('2022-01-07', 741),
  ('2022-01-08', 365),
  ('2022-01-09', 598),
  ('2022-01-10', 256),
  ('2022-01-11', 411),
  ('2022-01-12', 162);

This query will return date ranges encompassing spans in the table where sales were under 500 for three sequential daily entries, given that there may be gaps in those daily entries.

SELECT 
  (
    SELECT TOP 1 previousDay.saleDate 
    FROM #example AS previousDay 
    WHERE previousDay.saleDate < #example.saleDate 
    ORDER BY previousDay.saleDate DESC
  ) AS startDate, 
  #example.saleDate AS midDate,
  (
    SELECT TOP 1 nextDay.saleDate 
    FROM #example AS nextDay 
    WHERE nextDay.saleDate > #example.saleDate 
    ORDER BY nextDay.saleDate ASC
  ) AS endDate
FROM #example
WHERE #example.sales < 500
AND (
  SELECT TOP 1 previousDay.sales 
  FROM #example AS previousDay 
  WHERE previousDay.saleDate < #example.saleDate 
  ORDER BY previousDay.saleDate DESC
) < 500
AND (
  SELECT TOP 1 nextDay.sales 
  FROM #example AS nextDay 
  WHERE nextDay.saleDate > #example.saleDate 
  ORDER BY nextDay.saleDate ASC
) < 500

Upvotes: 1

Related Questions