CODEWITHSUNDEEP
sqlsql-servert-sql
user3115933
user3115933

Reputation: 4443

How to query a table with dates so that the output is in this specific summarized form?

I am using SQL Server 2012 and I have the following table (called StayDateInfo) in my SQL Server database. The table contains around 2 million records.

ResaID   StayDate    RoomCat
 100     2018-03-01    STD
 100     2018-03-02    STD
 150     2018-04-10    STD
 150     2018-04-11    STD
 150     2018-04-12    STD
 150     2018-04-13    DLX

I need my T-Sql query to provide the following output:

 ResaID   RoomCat    StartDate    EndDate      Length
 100      STD        2018-03-01   2018-03-02      2
 150      STD        2018-04-10   2018-04-12      3
 150      DLX        2018-04-13   2018-04-13      1

The Length column is basically the count of number of days between the StartDate and the EndDate.

How to write the T-SQl query to achieve this output?

Upvotes: 1

Views: 51

Answers (3)

jean
jean

Reputation: 4350

You do need just aggregate by the two columns unique indentifiques each row to get the start and end dates. To get the lenght just get the DATEDIFF from both dates.

CREATE TABLE [dbo].[StayDateInfo]
(
    ResaID int not null
   ,StayDate date not null
   ,RoomCat char(3) not null
)

GO

insert into [dbo].[StayDateInfo]
(ResaID, StayDate, RoomCat)
values
 (100,     '2018-03-01',    'STD')
,(100,     '2018-03-02',    'STD')
,(150,     '2018-04-10',    'STD')
,(150,     '2018-04-11',    'STD')
,(150,     '2018-04-12',    'STD')
,(150,     '2018-04-13',    'DLX')
GO

select * from [dbo].[StayDateInfo]

select sdi.ResaID, sdi.RoomCat
      , min(sdi.StayDate) as [StartDate]
      , max(sdi.StayDate) as [EndDate]
      , DATEDIFF(DAY, min(sdi.StayDate), max(sdi.StayDate)) + 1 as [Length]
from [dbo].[StayDateInfo] sdi
group by sdi.ResaID, sdi.RoomCat

The output is exactly what you are asking for.

Upvotes: 0

Aswani Madhavan
Aswani Madhavan

Reputation: 816

The following query works

SELECT ResaID,RoomCat,MIN(StayDate),MAX(StayDate),datediff(D,MIN(StayDate),MAX(StayDate)) +1 
FROM #TEMP_TABLE 
GROUP BY ResaID,RoomCat

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use group by clause

select ResaID, RoomCat,
       min(StayDate) as StartDate,
       max(StayDate) as EndDate,
       datediff(day, min(StayDate), max(StayDate))+1 as Length
from StayDateInfo s
group by ResaID, RoomCat

Upvotes: 2

Related Questions