Get count of timeslots from table in SQL Server

Hi pandeygolu4200,

Please refer the sample query below.

SQL

CREATE TABLE #temp1
(
    EventlocationId VARCHAR(50)                      
    ,[EventId]         VARCHAR(50)                    
    ,[locationName]    VARCHAR(50)                       
    ,[locationAddress] VARCHAR(50)             
    ,[locationCity]    VARCHAR(50)
    ,[RoomNo]       VARCHAR(50)
    ,[EventDate]       VARCHAR(50)       
    ,[ClinicStarttime] VARCHAR(50)
    ,[ClinicEndtime]   VARCHAR(50)
    ,[locationContactPerson] VARCHAR(50)
    ,[locationContactEmail] VARCHAR(50) 
    ,[SpecialInstructions] VARCHAR(50)
    ,[CreatedBy] VARCHAR(50)
    ,[CreatedOn] VARCHAR(50)
    ,[UpdatedBy] VARCHAR(50)
    ,[UpdatedOn] VARCHAR(50)
    ,[locationId] VARCHAR(50)
)

INSERT INTO #temp1 VALUES('3622EE68-1E84-4B5A-85A8-8A02BECC532E','6FCBA375-562E-418E-8F03-705B3CEDB5C9','Firstlacation','Dadar','Mumbai','23 217','2023-01-20 08:19:19.340','09:00 AM','09:00 PM','firstconatperson','firstcontactemail@yopmail.com','NA','NULL','2023-01-10 13:31:02.020 ','NULL','NULL','2A011DF6-6627-443E-A34E-C958AE0A73CE')
INSERT INTO #temp1 VALUES('A69722F0-4F53-4E36-90F4-E2B5DF3B6E59','6FCBA375-562E-418E-8F03-705B3CEDB5C9','Secondlocation','DAndheri','Mumbai','23 219','2023-01-15 08:19:19.340','08:00 AM','08:00 PM','secondconatct','secondconta@yopmail.com','NA','NULL', '2023-01-10 13:31:02.020',' NULL','NULL',' CD500780-91CF-4B24-9FA7-15A90B506C34')
INSERT INTO #temp1 VALUES('59C6A50A-C88D-4F2E-9BFF-D533284F67FC','13C1C037-257F-4BC4-BA1C-3CDF58F221CB','Center for spectrum healthcare service','3589 Big Ridge Road SpecsorPort','NY 14559','23 208','2023-01-18 13:37:07.893','10:00 AM','10:00 PM','brideg','bridheuser@yopmail.com','Emergency','NULL','2023-01-10 13:42:13.730','NULL','NULL','750F90B9-11CD-490D-8DA9-6FF2446A8B83')
INSERT INTO #temp1 VALUES('FE743FC5-5B91-4307-AD6D-3BA1A55B0FAE','F7F893C4-E192-44CB-A0FB-55FBFBD31A14','Center for spectrum healthcare service','3589 Big Ridge Road SpecsorPort','NY 14559','23 208','2023-01-01 13:37:07.893','10:00 AM','10:00 PM','brideg','bridheusers@yopmail.com','Emergency','NULL','2023-01-10 13:45:26.133','NULL','NULL','750F90B9-11CD-490D-8DA9-6FF2446A8B83')
INSERT INTO #temp1 VALUES('111646BC-77D4-490D-9C07-8BBA74872012','C0831C95-177E-4B68-9FBD-41922C397646','LA Steet','23, LA Street, Digama Raod','NY 400068','23  405','2023-02-02 08:02:31.530','09:00 AM','06:00 PM','Michael','michael@yopmail.com ','Emergency serices','9B31094C-1AD6-43A2-9DBD-6A67F0AFE838','2023-01-16 08:13:37.167','NULL','NULL','73D5644C-B14D-43E3-8985-3481CC0A60CE')
INSERT INTO #temp1 VALUES('D180C31A-8F2A-42EC-B0AB-89784C276530','C0831C95-177E-4B68-9FBD-41922C397646','Divine Street',' 24, Divine Street, Digama Raod','NY 400068','23  411','2023-02-10 08:02:31.530','09:00 AM','05:00 PM','John','john@yopmail.com','NA','9B31094C-1AD6-43A2-9DBD-6A67F0AFE838','2023-01-16 08:13:37.167','NULL','NULL','F3510748-56E4-44FF-BE52-1F618869D238')

CREATE TABLE #temp2
(
     [ServicemapId] VARCHAR(50)                      
    ,[EventId] VARCHAR(50)                    
    ,[ServiceId] VARCHAR(50)                       
    ,[NoofNurse] INT            
    ,[AppointmentSessiontime] VARCHAR(50)
    ,[CreatedBy] VARCHAR(50)
    ,[CreatedOn] VARCHAR(50)
    ,[UpdatedBy] VARCHAR(50)
    ,[UpdatedOn] VARCHAR(50)
    ,[locationId] VARCHAR(50)
)

INSERT INTO #temp2 VALUES('908FD5C5-5FAF-49CF-991A-CCF09748C610','6FCBA375-562E-418E-8F03-705B3CEDB5C9','7B65DBD7-4ED1-47EF-93E6-B151BD6D9995','2','30','NULL','2023-01-10 13:31:02.053','NULL','NULL','2A011DF6-6627-443E-A34E-C958AE0A73CE')
INSERT INTO #temp2 VALUES('EA6E1540-376D-47C9-8635-878B03B08ADD','6FCBA375-562E-418E-8F03-705B3CEDB5C9','7B65DBD7-4ED1-47EF-93E6-B151BD6D9995','1','15','NULL','2023-01-10 13:31:02.053','NULL','NULL','CD500780-91CF-4B24-9FA7-15A90B506C34')
INSERT INTO #temp2 VALUES('EE12432E-3074-4E84-BDB1-68F3E57C3EA8','13C1C037-257F-4BC4-BA1C-3CDF58F221CB','7B65DBD7-4ED1-47EF-93E6-B151BD6D9995','1','30','NULL','2023-01-10 13:42:13.753','NULL','NULL','750F90B9-11CD-490D-8DA9-6FF2446A8B83')
INSERT INTO #temp2 VALUES('F79B80CF-CDF3-4FDB-94FB-0A12139AED77','F7F893C4-E192-44CB-A0FB-55FBFBD31A14','7B65DBD7-4ED1-47EF-93E6-B151BD6D9995','1','30','NULL','2023-01-10 13:45:26.153','NULL','NULL','750F90B9-11CD-490D-8DA9-6FF2446A8B83')
INSERT INTO #temp2 VALUES('A2CD1644-5189-4735-B141-9F1A58C36F0C','C0831C95-177E-4B68-9FBD-41922C397646','7B65DBD7-4ED1-47EF-93E6-B151BD6D9995','2','30','9B31094C-1AD6-43A2-9DBD-6A67F0AFE838','2023-01-16 08:13:37.193','NULL','NULL','73D5644C-B14D-43E3-8985-3481CC0A60CE')
INSERT INTO #temp2 VALUES('5D61B0F3-3BC6-4FDB-AA5D-64FE3414404D','C0831C95-177E-4B68-9FBD-41922C397646','48589747-1104-400F-B448-617B44CDE477','1','30','9B31094C-1AD6-43A2-9DBD-6A67F0AFE838','2023-01-16 08:13:37.193','NULL','NULL','F3510748-56E4-44FF-BE52-1F618869D238')

SELECT * FROM #temp1 t1
SELECT * FROM #temp2 t2
SELECT * FROM #temp3 t3

SELECT t1.locationId,t1.locationName,t1.ClinicStartTime,t1.ClinicEndTime,DATEDIFF(mi,(CONVERT(DATETIME,t1.ClinicStartTime)),(CONVERT(DATETIME,t1.ClinicEndTime)))AS 'TimeDifference' INTO #temp3  FROM #temp1 t1
SELECT DISTINCT(t3.locationId),t3.locationName,t3.ClinicStartTime,t3.ClinicEndTime,t2.serviceId,t2.AppointmentSessiontime,(t3.TimeDifference/t2.AppointmentSessiontime)AS AppointmentAvailbility FROM #temp3 t3, #temp2 t2 WHERE t3.locationId = t2.locationId

Output