How to customize sql table when retrieve data?

Question

This is the table I can get from Select * from table1

CREATE TABLE Table1 (

    "StayDate" date         NOT NULL,
    "Type"     char(3)      NOT NULL,
    "Rate"     numeric(7,2) NOT NULL,
    "CityNo"   int          NOT NULL,
    "RoomNo"   int          NOT NULL,

    CONSTRAINT PK_Table1 PRIMARY KEY ( "StayDate", "CityNo", "RoomNo" )
);

INSERT INTO Table1 ( "StayDate", "Type", "Rate", "CityNo", "RoomNo" )
VALUES
( '2022-10-03', 'ALL',  10, 5001,        0 ),
( '2022-10-04', 'ALL',  10, 5001,        0 ),
( '2022-10-05', 'ALL', 101, 5001,        0 ),
( '2022-10-06', 'ALL', 101, 5001,        0 ),
( '2022-10-07', 'ALL', 101, 5001,        0 ),
( '2022-10-08', 'ALL', 101, 5001,        0 ),
( '2022-10-09', 'ALL', 101, 5001,        0 ),
( '2022-10-10', 'ALL', 101, 5001,        0 ),
( '2022-10-10', 'ALL', 101, 5001, 10000001 ),
( '2022-10-11', 'ALL',  12, 5001, 10000001 ),
( '2022-10-11', 'ALL',  10, 5001,        0 ),
( '2022-10-12', 'ALL',  12, 5001, 10000001 ),
( '2022-10-12', 'ALL',  10, 5001,        0 ),
( '2022-10-13', 'ALL',  10, 5001,        0 ),
( '2022-10-14', 'ALL',  10, 5001,        0 ),
( '2022-10-15', 'ALL',  10, 5001,        0 );

I want to retrieve data like below

I tried with below sql query:

SELECT
    MIN( "StayDate" ) AS "From Date",
    MAX( "StayDate" ) AS "To Date",
    "Type",
    "Rate",
    "CityNo",
    "RoomNo"
             
FROM
    (
        SELECT
            *,
            COUNT("chn") OVER( ORDER BY "StayDate" ) AS grp
        FROM
            (
                SELECT
                    *,
                    CASE WHEN "Rate" != LAG("Rate") OVER( ORDER BY "StayDate" ) THEN 1 END AS chn
                FROM
                    table1
            ) AS t
    ) AS t
--WHERE
--  "CityNo" = 5001
--  AND
--  "StayDate" BETWEEN '2022-10-03' AND '2022-10-15'

GROUP BY
    "grp",
    "Type",
    "Rate",
    "CityNo",
    "RoomNo"

ORDER BY
    "From Date";

But its not show the out put as I expected:

Can anyone help me to do this?

...don't you want an `ALTER TABLE` query? If not, then you aren't "customizing a table", you're just defining a query. — Dai, Oct 03 '22 at 02:06
@Dai No I want to retrieve data like that type, No need to edit database table. Can you help me? — Nirodha Wickramarathna, Oct 03 '22 at 02:26
That's the pgAdmin version. What do you get when you run `SELECT VERSION();`? — Dai, Oct 03 '22 at 02:41
@Dai It is PostgreSQL 9.5.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit — Nirodha Wickramarathna, Oct 03 '22 at 02:43
PostgresSQL 9 is from **2010** - why are you running such an ancient version? — Dai, Oct 03 '22 at 02:44
This project already built from someone, I am doing the maintenance part that is the issue :( — Nirodha Wickramarathna, Oct 03 '22 at 02:47
I ran your query in DBFiddle and the output looks correct - your expected results are wrong: they don't include the `10000001` row on `2022-10-12`. — Dai, Oct 03 '22 at 03:04
See here https://dbfiddle.uk/tSvZZDX- (my query is the 2nd query, your query is the 3rd one down) — Dai, Oct 03 '22 at 03:10
[Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) — Ken White, Oct 03 '22 at 03:23

score 0 · Answer 1 · answered Oct 03 '22 at 10:43

demo
basically imitate this post: Count events with a cool-down period after each instance and Select first row in each GROUP BY group?

In an group/partition, you want capture first value and last value, you can use recursive query. Desc order can capture the last value. Finally glue these two recursive query together using cross join. To simplify, I use view. For (rate,cityno,roomno) = (10, 5001,0) there should be two partition, cross join will produce 2*2=4 rows, So in the end, you need use distinct on or other way to capture first value in a group.

create temp view part1 as(
WITH RECURSIVE hit AS (
   (
   SELECT staydate, type, rate,cityno,roomno
   FROM   Table1
   ORDER  BY type, rate,cityno,roomno,staydate
   LIMIT  1
   )
   UNION ALL
   SELECT t.*
   FROM   hit h
   CROSS  JOIN LATERAL (
      SELECT t.staydate, t.type,t.rate,t.cityno,t.roomno
      FROM   Table1 t
      WHERE  ( t.rate,t.roomno, t.cityno,t.staydate) >  (h.rate,h.roomno, h.cityno,h.staydate + interval '7 day') 
      ORDER  BY t.type,t.rate,t.cityno,t.roomno ,staydate
      LIMIT  1
      ) t
   )
SELECT * FROM hit);

part2 will be desc order.

create temp view part2 as(
WITH RECURSIVE hit AS (
   (
   SELECT staydate, type, rate,cityno,roomno
   FROM   Table1
   ORDER  BY type desc,rate desc,cityno desc,roomno desc,staydate desc
   LIMIT  1
   )
   UNION ALL
   SELECT t.*
   FROM   hit h
   CROSS  JOIN LATERAL (
      SELECT t.staydate, t.type,t.rate,t.cityno,t.roomno
      FROM   Table1 t
      WHERE  ( t.rate,t.roomno, t.cityno,t.staydate) <  (h.rate,h.roomno, h.cityno,h.staydate - interval '7 day') 
      ORDER  BY t.type desc,t.rate desc,t.cityno desc,t.roomno desc,t.staydate desc
      LIMIT  1
      ) t
   )
SELECT * FROM hit);

Then in the end.

SELECT DISTINCT ON (1, 3, 4, 5, 6)
    p1.staydate AS from_date,
    p2.staydate AS to_date,
    p1.type,
    p1.rate,
    p1.cityno,
    p1.roomno
FROM
    part1 p1
    CROSS JOIN part2 p2
WHERE
    p1.rate = p2.rate
    AND p2.roomno = p1.roomno
    AND p2.staydate >= p1.staydate
ORDER BY
    1,3,4,5,6,2;

How to customize sql table when retrieve data?

1 Answers1

Linked