-3

I have a table as shown below:

CAR NAME    INSERT DATE
MERCEDES    2018-01-01
SEAT        2018-01-01
MERCEDES    2018-01-02
BMW         2018-01-02
MERCEDES    2018-01-03
MERCEDES    2018-01-04
MERCEDES    2018-01-05
BMW         2018-01-05
BMW         2018-01-06
SEAT        2018-01-07
BMW         2018-01-08
AUDI        2018-01-08
BMW         2018-01-09  
BMW         2018-01-10
NULL        2018-01-12
SEAT        2018-01-12
SEAT        2018-01-14
SEAT        2018-01-16
BMW         2018-01-17
NULL        2018-01-19 
MERCEDES    2018-01-21
MERCEDES    2018-01-22
MERCEDES    2018-01-23

I would like to know how many consecutive times the same CAR NAME was inserted into the table, when ordered by INSERT DATE, as well as the first and last INSERT DATE. For the purposes of this query, results of one consecutive CAR NAME should be ignored.

For example:

name  count  first     last
mercedes 3 2018-01-03 2018-01-05
bmw      2 2018-01-05 2018-01-06
bmw      2 2018-01-09 2018-01-10
seat     3 2018-01-12 2018-01-16
mercedes 3 2018-01-21 2018-01-23

I have a problem with implementing this, maybe someone could help.

This is for SQL Server. Unfortunately I have only two columns at my disposal.

4

Your question is not clearly formed, but considering your examples - Phil's comment was correct:

Your example only makes sense if there is another column that defines the order of the data (which is the order you have presented the data as in your question).

Unless you have an additional column with the order of rows - there is no solution to your problem.

Why? Because SQL is based on the theory of relations and in this concept the data itself have no order. So unless you provide an additional column with order of rows (most commonly this would be Id column with incrementing numbers), there would be no way of telling the order of data and thus - your question could not be answered. Without a column with order numbers if you perform SELECT from the database, the SQL officially does not make any guarantees that you will receive rows in the same order every time (and in many cases you won't).

Solution Add another column, e.g. Id as integer and have each row have an incrementing value like this:

Id    NAME         DATE
1   MERCEDES    2018-01-01
2   SEAT        2018-02-01
3   MERCEDES    2018-04-01
4   BMW         2018-01-01
5   MERCEDES    2018-01-01
6   MERCEDES    2018-01-05
7   MERCEDES    2018-01-09

I had much fun figuring out how you could actually query to get the results you wanted, but here it is (sorry I didn't put much time into formatting):

;WITH First (id, car, d, is_first, rn) AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn FROM (
        SELECT
            id
            ,car
            ,d
            ,CASE WHEN ((LEAD(car,1) OVER (ORDER BY id) = car) AND (LAG(car,1) OVER (ORDER BY id) <> car OR LAG(car,1) OVER (ORDER BY id) IS NULL)) THEN 1 ELSE 0 END is_first
        FROM 
            dbo.cars
    ) t
    WHERE t.is_first = 1
),
Last (id, car, d, is_last, rn) AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn FROM (
        SELECT
            id
            ,car
            ,d
            , CASE 
                WHEN (LEAD(car,1) OVER (ORDER BY id) <> car OR LEAD(car,1) OVER (ORDER BY id) IS NULL) AND (LAG(car,1) OVER (ORDER BY id) = car ) THEN 1 ELSE 0 END is_last
        FROM 
            dbo.cars
    ) t
    WHERE t.is_last = 1
)
SELECT
    c.car, COUNT(*) cnt, f.d min_date, l.d max_date
FROM
    First f
    LEFT JOIN Last l ON f.rn = l.rn
    LEFT JOIN cars c ON c.car = f.car AND c.id BETWEEN f.id AND l.id
GROUP BY 
    c.car, f.d, l.d, f.rn
ORDER BY 
    f.rn

The main idea was to find first and last items of ranges (using windowing function LAG and LEAD) and then pair first items with last items using ROW_NUMBER() as keys. And last but not least join these pairs with the original table again just to get COUNT(*)s. Et voila:

fun query

  • 2
    Nice. You may want to add that the problem is in the category of "gaps-and-islands" problems. – ypercubeᵀᴹ Jun 30 '18 at 6:39
1

As explained in comments your question is not clear, but if you want, per name, the count, min and max, you can do:

SELECT name, COUNT(*), min(date), max(date) FROM atable GROUP BY name

Try it yourself: http://sqlfiddle.com/#!15/50fcb/5/0

It does not produce the output you show because your example is not clear/complete (example: you list a count of 3 for seat where you have 4 lines of it...)

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.