Use AjitKhadka
Go


--1a
Alter procedure SpAccountingDateSel 
 @Json nvarchar(max)
As
Begin
Begin Try
 Begin Transaction
 Declare @StartDate Date
 Declare @EndDate Date
 
 --parse json into @startDate and @EndDate
 Select @StartDate = json_value(@Json, '$.StartDate'),
        @EndDate = json_value(@Json, '$.EndDate')
  
  --
  declare @AccountingTable table(
    AccountingDate date, 
 [Day] varchar(10), 
 [Week] int,
 [Month] varchar(10), 
 [Quarter] int);



  while @StartDate <= @EndDate
    Begin
   INSERT INTO @AccountingTable VALUES(
     @StartDate, 
  DATENAME(WEEKDAY,@StartDate), 
  DATEPART(WEEK,@StartDate), 
  DATENAME(MONTH, @StartDate), 
  DATEPART(QUARTER,@StartDate));
  SET @StartDate = DATEADD(DAY, 1, @StartDate);
 End

  Select * from @AccountingTable

 Commit Transaction
 End Try
 Begin Catch
 Rollback Transaction
 Print 'Operaton failed'
 End Catch

End


Declare @Json nvarchar(max);
 Set @Js "EndDate":"2024-04-27"}'
Exec SpAccountingDateSel @Json;

--1b


select * from room

Alter Procedure SpAvailableRooms
 @Json nvarchar(max)
As
Begin
  Begin Try
  Begin Transaction

  Declare @StartDate Date
  Declare @EndDate Date
  Declare @totalRooms int
  Declare @BookedRooms int
  Declare @RoomId int
 
 --parse json into @startDate and @EndDate
 Select @StartDate = json_value(@Json, '$.StartDate'),
        @EndDate = json_value(@Json, '$.EndDate')

  Select @totalRooms = COUNT(*)
    from Room;
  Select @BookedRooms = COUNT(DISTINCT RoomID) from Reservation

  
  Select RoomId from Room where RoomId in (
  Select Distinct RoomId  From Reservation
  Where CheckOutDate < @EndDate) And status = 'Open'

  
  Commit Transaction
  End Try
  Begin Catch
  Rollback Transaction
  Print ''
  End Catch
End

Declare @Json nvarchar(max);
 Set @Js "EndDate":"2024-05-01"}'
Exec SpAvailableRooms @Json;