1. USE AjitKhadka
2. GO
3.  
4.  
5. --1a
6. ALTER PROCEDURE SpAccountingDateSel
7.  @Json nvarchar(MAX)
8. AS
9. BEGIN
10. BEGIN Try
11.  BEGIN TRANSACTION
12.  DECLARE @StartDate DATE
13.  DECLARE @EndDate DATE
14.  
15.  --parse json into @startDate and @EndDate
16.  SELECT @StartDate = json_value(@Json, '$.StartDate'),
17.         @EndDate = json_value(@Json, '$.EndDate')
18.  
19.   --
20.   DECLARE @AccountingTable TABLE(
21.     AccountingDate DATE,
22.  [DAY] VARCHAR(10),
23.  [Week] INT,
24.  [MONTH] VARCHAR(10),
25.  [Quarter] INT);
26.  
27.  
28.  
29.   while @StartDate <= @EndDate
30.     BEGIN
31.    INSERT INTO @AccountingTable VALUES(
32.      @StartDate,
33.   DATENAME(WEEKDAY,@StartDate),
34.   DATEPART(WEEK,@StartDate),
35.   DATENAME(MONTH, @StartDate),
36.   DATEPART(QUARTER,@StartDate));
37.   SET @StartDate = DATEADD(DAY, 1, @StartDate);
38.  END
39.  
40.   SELECT * FROM @AccountingTable
41.  
42.  Commit TRANSACTION
43.  END Try
44.  BEGIN Catch
45.  ROLLBACK TRANSACTION
46.  Print 'Operaton failed'
47.  END Catch
48.  
49. END
50.  
51.  
52. DECLARE @Json nvarchar(MAX);
53.  SET @Js "EndDate":"2024-04-27"}'
54. Exec SpAccountingDateSel @Json;
55.  
56. --1b
57.  
58.  
59. select * from room
60.  
61. Alter Procedure SpAvailableRooms
62. @Json nvarchar(max)
63. As
64. Begin
65.  Begin Try
66.  Begin Transaction
67.  
68.  Declare @StartDate Date
69.  Declare @EndDate Date
70.  Declare @totalRooms int
71.  Declare @BookedRooms int
72.  Declare @RoomId int
73.  
74. --parse json into @startDate and @EndDate
75. Select @StartDate = json_value(@Json, '$.StartDate'),
76.        @EndDate = json_value(@Json, '$.EndDate')
77.  
78.  Select @totalRooms = COUNT(*)
79.    from Room;
80.  Select @BookedRooms = COUNT(DISTINCT RoomID) from Reservation
81.  
82.  
83.  Select RoomId from Room where RoomId in (
84.  Select Distinct RoomId  From Reservation
85.  Where CheckOutDate < @EndDate) And status = 'OPEN'
86.  
87.  
88.  Commit Transaction
89.  End Try
90.  Begin Catch
91.  Rollback Transaction
92.  Print ''
93.  End Catch
94. End
95.  
96. Declare @Json nvarchar(max);
97. Set @Js "EndDate":"2024-05-01"}'
98. EXEC SpAvailableRooms @Json;
99.  
100.