count :SQL :DB2

Question

I have this table:

CODE	IDNR	NAME	LIMIT
123	80	XXX	2019-05
123	81	XXX	2019-10
124	80	YYY	2019-01
125	80	ZZZ	2019-05
125	81	ZZZ	2019-06
125	80	ZZZ	2019-07
126	80	III	2019-05
126	80	III	2019-09
126	80	III	2019-07

I want to have a new column (Count-LIMIT ) contain how many LIMIT per code, and another contain YES if the limit are continuous and No if not. MY RESULT that I want like:

CODE	IDNR	NAME	LIMIT	Count-Limit	CON
123	80	XXX	2019-05	2	NO
123	81	XXX	2019-10	2	NO
124	80	YYY	2019-01	1	NO
125	80	ZZZ	2019-05	3	YES
125	81	ZZZ	2019-06	3	YES
125	80	ZZZ	2019-07	3	YES
126	80	III	2019-05	3	NO
126	80	III	2019-09	3	NO
126	80	III	2019-07	3	NO

THANKS!

To get what you want, you have to write the SQL query. Want to share what you already have...? See stackoverflow.com/help/how-to-ask — data_henrik
– data_henrik, Commented Dec 21, 2021 at 14:16
SELECT CODE ,IDNR , NAME, LIMIT ,COUNT(LIMIT)OVER (PARTITION BY CODE) AS Count-Limit For rest i know i should use Case men how? — Tick _Tack
– Tick _Tack, Commented Dec 21, 2021 at 15:19
I think you are close to what you want. You just need SELECT DISTINCT — jackic23
– jackic23, Commented Dec 21, 2021 at 15:38

Mark Barinstein · Accepted Answer · 2021-12-22 10:15:46Z

1

Try this:

WITH T (CODE, IDNR, NAME, LIMIT) AS
(
VALUES
  (123, 80, 'XXX', '2019-05')
, (123, 81, 'XXX', '2019-10')
, (124, 80, 'YYY', '2019-01')
, (125, 80, 'ZZZ', '2019-05')
, (125, 81, 'ZZZ', '2019-06')
, (125, 80, 'ZZZ', '2019-07')
, (126, 80, 'III', '2019-05')
, (126, 80, 'III', '2019-09')
, (126, 80, 'III', '2019-07')

, (128, 80, 'AAA', '2021-01')
, (128, 80, 'AAA', '2021-03')
, (128, 80, 'AAA', '2021-05')
, (128, 80, 'AAA', '2021-07')
, (128, 80, 'AAA', '2021-08')
, (128, 80, 'AAA', '2021-09')
)
SELECT 
  T.*
, COUNT (1) OVER (PARTITION BY CODE) AS COUNT_LIMIT
, CASE
    WHEN TO_DATE (LIMIT || '-01', 'YYYY-MM-DD') IN
      (
        LAG  (TO_DATE (LIMIT || '-01', 'YYYY-MM-DD')) OVER (PARTITION BY CODE ORDER BY LIMIT) + 1 MONTH
      , LEAD (TO_DATE (LIMIT || '-01', 'YYYY-MM-DD')) OVER (PARTITION BY CODE ORDER BY LIMIT) - 1 MONTH
      )
    THEN 'YES' 
    ELSE 'NO' 
  END AS CON
FROM T
ORDER BY CODE, LIMIT

The result is:

CODE	IDNR	NAME	LIMIT	COUNT_LIMIT	CON
123	80	XXX	2019-05	2	NO
123	81	XXX	2019-10	2	NO
124	80	YYY	2019-01	1	NO
125	80	ZZZ	2019-05	3	YES
125	81	ZZZ	2019-06	3	YES
125	80	ZZZ	2019-07	3	YES
126	80	III	2019-05	3	NO
126	80	III	2019-07	3	NO
126	80	III	2019-09	3	NO
128	80	AAA	2021-01	6	NO
128	80	AAA	2021-03	6	NO
128	80	AAA	2021-05	6	NO
128	80	AAA	2021-07	6	YES
128	80	AAA	2021-08	6	YES
128	80	AAA	2021-09	6	YES

edited Dec 22, 2021 at 10:15

answered Dec 21, 2021 at 15:38

Mark Barinstein

12.8k2 gold badges11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tick _Tack Over a year ago

thank you very much . but i have problem with this query because (if i have limit 2021-07 , 2021-08 2021-09 i get in the record 2021-07 no and in the 08 ,09 yes . the second problem maybe i have like this

Tick _Tack Over a year ago

LIMIT for the same person (128 ex) have 2021-01 ,2021-03 , 2021-05 ,2021-07 ,2021-08, 2021-09 i want to get yes but result with this no according to query or ???????

Mark Barinstein Over a year ago

Look at the updated answer. If it's again not what you need, then edit the question inserting all the values needed for person 128 into both your table and the result.

Collectives™ on Stack Overflow

count :SQL :DB2

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related