All BookingID shows 0 not exactly matching with time and put 1

Question

Hi @MelissaMa-MSFT

Could you please check all BookingID show 0 and Its same like my previous query visits. BookingID comes in different timings. But in time we don't have any 1's. I have attached DDL for data. Please check the joining part with calender and some of them booking IDs are fine. Is that correct?

Create Table Sessions(  
  SessionID int,SessionDescription varchar(100)  
  );  
  Insert into  Sessions   
  (SessionID,SessionDescription)  
  VALUES  (763,'RPM'),  
   (759,'RPM EXTENDED'),  
   (759,'RPM'),  
   (763,'RPM EXTENDED'),  
   (100,'RPM EXTENDED'),  
   (1833,'RPM SUN 900 AM'),  
   (1833,'RPM');  
Create Table SessionBookings(  
SessionID int,SessionBookingID int, SessionDateTime datetime  
);  
Insert into SessionBookings  
(SessionID,SessionBookingID,SessionDateTime)  
VALUES   
(763,1346731,'2017-12-31 09:30:00.000'),  
(759,1346738,'2017-12-31 08:30:00.000'),  
(759,1346743,'2017-12-31 08:30:00.000'),  
(763,1346761,'2017-12-31 09:30:00.000'),  
(100,1346769,'2017-12-31 09:00:00.000'),  
(1833,1346797,'2017-12-31 09:00:00.000'),  
(1833,1346798,'2017-12-31 09:00:00.000');  
  
IF OBJECT_ID(N'tempdb..#Calender') IS NOT NULL   
BEGIN  
DROP TABLE #Calender  
END  
GO   
  
  
SET DATEFIRST 1 ;    
          
  Declare @beginDate int = 0;   
  Declare @pendDate int = 1;  
  Declare @CurrDate date = '2017-12-31';  
  Declare @PrevDate date = '2017-12-31';  
  
    
 CREATE TABLE #Calendar(WeekStart varchar(15),WeekStartDate varchar(15),[WeekDay] varchar(15))  
   While (@beginDate < 31 and @pendDate < 32)  
      BEGIN  
     insert into #Calendar  
    Select  Datepart(ISO_WEEK,DATEADD(day, -1 * @beginDate , @CurrDate)),  
        DATEADD(day, -1 * @beginDate , @CurrDate),  
  
        DATENAME(DW,Dateadd(day, -1 *@beginDate,@CurrDate))  
      
    SET @beginDate += 1  
    SET @pendDate +=1  
  
  END  
  
  IF OBJECT_ID(N'tempdb..#Test') IS NOT NULL   
BEGIN  
DROP TABLE #Test  
END  
GO   
 CREATE TABLE #Test(WeekStart varchar(15),WeekStartDate date,[WeekDay] varchar(15),SessionTime varchar(15),  
SessionID int,SessionBookingID int,BookingID int,PeopleID int,SessionDescription varchar(100))  
  
  insert Into #Test  
   select A.WeekStart,A.WeekStartDate,A.WeekDay, (cast(DATEPART(HH,B.SessionDateTime) as char(2)))+'-'+   
   rtrim(cast(DATEPART(HH,B.SessionDateTime)+1 as char(2))),B.SessionID,B.SessionBookingID,B.SessionBookingID as BookingID,  
   B.PeopleId,S.SessionDescription  
        From #Calendar A   
  
   Inner Join SessionBookings B  
  ON A.WeekStart = DatePart(ISO_WEEK,B.SessionDateTime) AND   
  A.WeekDay = DATENAME(DW,B.SessionDateTime)    
       And A.WeekStartDate = CAST(B.SessionDateTime AS date)  
    inner Join Sessions S  
    ON B.SessionID = S.SessionID  
  
  
 select *  
 from (  
 select distinct WeekStart,WeekStartDate,WeekDay,SessionTime,SessionBookingID,BookingID,SessionDescription  
 ,PeopleId from #Test   
  
  
   )a  
 PIVOT  
       (  
         count(BookingID)  
       For SessionTime IN ([0-1],[1-2],[2-3],[3-4],[4-5],[5-6],[6-7],[7-8],[8-9],  
            [9-10],[10-11],[11-12],[12-13],[13-14],[14-15],[15-16],[16-17],  
                 [17-18],[18-19],[19-20],[20-21],[21-22],[22-23],[23-24])   
       ) As pvt    
 ORDER BY pvt.WeekStart desc,  
  CASE WHEN WeekDay ='Monday' THEN 1  
         WHEN weekday='Tuesday' THEN 2  
         WHEN weekday='Wednesday' THEN 3  
         WHEN weekday='Thursday' THEN 4  
         WHEN weekday='Friday' THEN 5  
         WHEN weekday='Saturday' THEN 6  
         WHEN weekday='Sunday' THEN 7 END DESC  

  

Answer 1

Hi @RXR ，

Please try to add one rtrim before first cast as below and your issue could be fixed.

   insert Into #Test  
    select A.WeekStart,A.WeekStartDate,A.WeekDay, rtrim(cast(DATEPART(HH,B.SessionDateTime) as char(2)))+'-'+   
    rtrim(cast(DATEPART(HH,B.SessionDateTime)+1 as char(2))),B.SessionID,B.SessionBookingID,B.SessionBookingID as BookingID, B.PeopleId,  
	S.SessionDescription  
         From #Calendar A   
    Inner Join SessionBookings B  
   ON A.WeekStart = DatePart(ISO_WEEK,B.SessionDateTime) AND   
   A.WeekDay = DATENAME(DW,B.SessionDateTime)    
        And A.WeekStartDate = CAST(B.SessionDateTime AS date)  
     inner Join Sessions S  
     ON B.SessionID = S.SessionID  

In below snapshot, your session time is like '8 -9' and '9 -10' which has a space behind the first number while in the pivot part it is [8-9],[9-10] which has no space.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.