CODEWITHSUNDEEP
sqloracle-database
Mvz
Mvz

Reputation: 507

Get count of consecutive days meeting a given criteria

I have the following structure in my Oracle database:

Date          Allocation  id
2015-01-01    Same        200
2015-01-02    Good        200
2015-01-03    Same        200
2015-01-04    Same        200
2015-01-05    Same        200
2015-01-06    Good        200

I would like to have a query that has to check for only the previous consecutive days and get the count where Allocation is "Same".

I want to select by a date, for example 2015-01-05.
Example output: for the date 2015-01-05 the count is 3.

New problem. With the query from Lukas Eder the count is always 1 or 2. but the expected is 3. Why?! 

Date          Allocation  id
2015-01-01    Same        400
2015-01-02    Good        400
2015-01-03    Same        400
2015-01-04    Same        400
2015-01-05    Same        400
2015-01-06    Good        400

Code from Lukas Eder

 SELECT c
    FROM (
      SELECT allocation, d, count(*) OVER (PARTITION BY allocation, part ORDER BY d) AS c
      FROM (
        SELECT allocation, d,
               d - row_number() OVER (PARTITION BY allocation ORDER BY d) AS part
        FROM t
      )
    )
    WHERE d = DATE '2015-01-05';

The expected output is something like this, First_day end Last day not necessary:

id   count    first_day   Last_Day
200  3        2015-01-03  2015-01-05
400  3        2015-01-03  2015-01-05

Upvotes: 4

Views: 4768

Answers (4)

moffeltje
moffeltje

Reputation: 4658

It's easier:

DEMO

SELECT count(*) AS c
FROM t
WHERE d <
TO_DATE (
       '2015-01-05'
        ,'yyyy-mm-dd'
)
AND allocation = 'Same';

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 220762

This query will yield the counts for each row:

SELECT allocation, d, count(*) OVER (PARTITION BY allocation, part ORDER BY d) AS c
FROM (
  SELECT allocation, d,
         d - row_number() OVER (PARTITION BY allocation ORDER BY d) AS part
  FROM t
)
ORDER BY d;

You can then filter on it to find the counts for a given row:

SELECT c
FROM (
  SELECT allocation, d, count(*) OVER (PARTITION BY allocation, part ORDER BY d) AS c
  FROM (
    SELECT allocation, d,
           d - row_number() OVER (PARTITION BY allocation ORDER BY d) AS part
    FROM t
  )
)
WHERE d = DATE '2015-01-05';

Explanation:

The derived table is used to calculate different "partitions" part for each date and allocation:

  SELECT allocation, d,
         d - row_number() OVER (PARTITION BY allocation ORDER BY d) AS part
  FROM t

The result is:

allocation  d           part
--------------------------------
Same        01.01.15    31.12.14
Good        02.01.15    01.01.15
Same        03.01.15    01.01.15
Same        04.01.15    01.01.15
Same        05.01.15    01.01.15
Good        06.01.15    04.01.15

The concrete date produced by part is irrelevant. It's just some date that will be the same for each "group" of dates within an allocation. You can then count the number of identical values of (allocation, part) using the count(*) over(...) window function:

SELECT allocation, d, count(*) OVER (PARTITION BY allocation, part ORDER BY d) AS c
FROM (...)
ORDER BY d;

to produce your wanted result.

Data

I've used the following table for the example:

CREATE TABLE t AS (
  SELECT DATE '2015-01-01' AS d, 'Same' AS allocation FROM dual UNION ALL
  SELECT DATE '2015-01-02' AS d, 'Good' AS allocation FROM dual UNION ALL
  SELECT DATE '2015-01-03' AS d, 'Same' AS allocation FROM dual UNION ALL
  SELECT DATE '2015-01-04' AS d, 'Same' AS allocation FROM dual UNION ALL  
  SELECT DATE '2015-01-05' AS d, 'Same' AS allocation FROM dual UNION ALL
  SELECT DATE '2015-01-06' AS d, 'Good' AS allocation FROM dual
);

Upvotes: 3

Gene
Gene

Reputation: 179

Consider the following query to solve your problem:

SELECT COUNT(*) AS `count` FROM test t
WHERE `date` < '2015-01-05' AND allocation = 'Same';

Let's assume that the given date is '2015-01-05'. The idea here is to select all dates that are less than '2015-01-05' which means its previous days. Since allocation must be 'Same' so it's also included in condition section of statement.

Upvotes: 1

Manashvi Birla
Manashvi Birla

Reputation: 2843

Try this : 

SELECT  count(Allocation) as total_allocation FROM table_name 
WHERE (Date BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE()) 
AND (Allocation='Same');

This will fetch all the records from date before today's date and whose Allocation='Same'

Upvotes: 0

Related Questions