Pivot status of booking by date

user_13961966 0
Viewed 41

A package can be booked between 2 dates, and the status of the booking gets updated by 2 users (0 as unconfirmed, 1 as pending confirmation, 2 as confirmed). I am trying to display the status of a package over a period of time.

We have the below tables:

CREATE TABLE [dbo].[Packages](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
 CONSTRAINT [PK_Packages] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Users](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Bookings](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [PackageID] [int] NULL,
    [fromDate] [datetime] NULL,
    [toDate] [datetime] NULL,
    [status] [int] NULL,
    [user1] [int] NULL,
    [user2] [int] NULL,
 CONSTRAINT [PK_Bookings] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Some test data:

insert into Packages(Name) values ('pack1')
insert into Packages(Name) values ('pack2')
insert into Packages(Name) values ('pack3')
insert into Packages(Name) values ('pack4')
insert into Packages(Name) values ('pack5')

insert into users(name) values('operator1')
insert into users(name) values('operator2')
insert into users(name) values('admin1')
insert into users(name) values('admin2')

insert into bookings(PackageID, fromDate, toDate, status, user1, user2) values (1,'7/1/2020','7/11/2020',0,1,null)
insert into bookings(PackageID, fromDate, toDate, status, user1, user2) values (1,'7/15/2000','7/18/2020',1,2,3)
insert into bookings(PackageID, fromDate, toDate, status, user1, user2) values (2,'7/6/2020','7/10/2020',1,1,4)
insert into bookings(PackageID, fromDate, toDate, status, user1, user2) values (2,'7/20/2000','7/25/2020',0,2,null)
insert into bookings(PackageID, fromDate, toDate, status, user1, user2) values (3,'7/13/2020','7/13/2020',2,1,3)
insert into bookings(PackageID, fromDate, toDate, status, user1, user2) values (3,'7/24/2020','7/30/2020',2,2,3)
insert into bookings(PackageID, fromDate, toDate, status, user1, user2) values (4,'7/5/2020','7/16/2020',2,1,4)
insert into bookings(PackageID, fromDate, toDate, status, user1, user2) values (5,'7/2/2020','7/8/2020',1,1,3)
insert into bookings(PackageID, fromDate, toDate, status, user1, user2) values (5,'7/22/2020','7/30/2020',1,2,4)

I am trying to achieve something like this: displaying the booking status for July 2020 

Packages - 7/1/2020 - 7/2/2020 - 7/3/2020 - 7/4/2020 - 7/5/2020 - 7/6/2020 - 7/7/2020 - 7/8/2020 ... 
1        - 0        - 0        - 0        - 0        - 0        - 0        - 0        - 0        ... 
2        -          -          -          -          -          - 1        - 1        - 1        ... 
3        -          -          -          -          -          -          -          -          ... 
4        -          -          -          -          - 2        - 2        - 2        - 2        ... 
5        -          - 1        - 1        - 1        - 1        - 1        - 1        - 1        ...
recursive-query pivot join sql-server sql
1 Answers

I would probably be simpler to put the dates in rows rather than in columns. If that's an acceptable solution for you, here is one approach that uses a recursive query to generate the dates, then joins. This assumes that there are not overlapping bookings for a given package:

with dates as (
    select '20207101' dt
    union all
    select dateadd(day, 1, dt) from dates where dateadd(day, 1, dt) < '20200801'
)
select p.id, d.dt, b.status
from dates d
cross join packages p 
left join bookings b 
    on  b.packageID = p.id 
    and b.fromDate >= d.dt
    and b.toDate   <  d.dt

You might want to adjust the inequalities on the dates according to the exact meaning of fromDateand toDate (I assumed an half-open interval).

If you actually want a pivoted resultset, then a method other than dynamic SQL is to use conditional aggregation over each day of the month, like so:

with dates as (
    select '20207101' dt
    union all
    select dateadd(day, 1, dt) from dates where dateadd(day, 1, dt) < '20200801'
)
select 
    p.id, 
    max(case when day(d.dt) =  1 then b.status end) [2020701],
    max(case when day(d.dt) =  2 then b.status end) [2020702],
    max(case when day(d.dt) =  3 then b.status end) [2020703],
    ...
    max(case when day(d.dt) = 31 then b.status end) [2020731]
from dates d
cross join packages p 
left join bookings b 
    on  b.packageID = p.id 
    and b.fromDate >= d.dt
    and b.toDate   <  d.dt
group by p.id
user_106767160
Related