none
Help with query - No functions and stored procedures due to lack of permission RRS feed

  • Question

  • I am trying to calculate the following:

    We have group bookings held in our system as blocks. Blocks are not considered reservations until they are picked up and a rooming list entered so what I can see from the query below is the total revenue held on the block per group by arrival and departure date.

    The problem is that the revenue per group is the total revenue shown is that generated during the whole period the bedrooms are blocked for. This would be ok if each group arrived and left in the same month. However, there are groups that arrive in a month and leave on the following month.

    I need to split the revenue for these groups by month. So, for example if a group arrives on 28/06 and leaves on 3/07, I need to know how much of that revenue is generated in June and how much in July.

    I need revenue by days per each group by month.

    The query below is correct but will give me total revenue based on EndDate (check out date), so it will all go to the month the group checks out.

    Select  Year(BeginDate) ArrivalYear,

    DATEDIFF(DAY,BeginDate, EndDate) AS StayNights, 

    DATENAME(month, BeginDate) AS Month,

    GroupRef,

    GADescription AS Decsription,

    GAStatus AS Status,

    SourceSiteId,

    BeginDate AS Arrival,

    EndDate AS Departure,

    CreatedTimestamp,

    DefMarketSegmentCode AS MARKSEG,

    ProjectedRevenueAccomNett AS AccomREV,

    ProjectedRevenueFBNett AS FBRev

    From SyncGroupRoomBlockHeaders

    WHERE 

    CreatedTimestamp < '2019-03-21'

    AND  BeginDate BETWEEN '2019-03-21' AND '2019-12-31'

    or

      CreatedTimestamp < '2018-03-21'

    AND  BeginDate BETWEEN '2018-03-21' AND '2018-12-31'

    Order By Month(BeginDate)

    Monday, April 15, 2019 9:12 AM

Answers

  • I get this

    Msg 207, Level 16, State 1, Line 23
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 43
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 29
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 30
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 31
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 32
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 32
    Invalid column name 'StartDate'.

    My fault. I used SatrtDate instead of BeginDate. Try this:

    DECLARE @Numbers TABLE (
    	Number int
    );
    DECLARE @GroupRef_Numbers TABLE (
    	GroupRef varchar(10),
    	Number int
    );
    
    ;WITH CTE_Numbers AS (
    	SELECT 0 AS Number
    	UNION ALL 
    	SELECT Number + 1 AS Number
    	FROM CTE_Numbers
    	WHERE Number < 99
    )
    
    INSERT INTO @Numbers
    SELECT Number FROM CTE_Numbers --WHERE Number < 100;
    
    INSERT INTO @GroupRef_Numbers
    SELECT s.GroupRef, n.Number 
    FROM @Numbers AS n
    LEFT JOIN SyncGroupRoomBlockHeaders AS s ON n.Number <= DATEDIFF(day, s.BeginDate, s.EndDate)
    WHERE s.GroupRef IS NOT NULL;
    
    SELECT 
    	s.GroupRef, 
    	s.ProjectedRevenueAccomNett / DATEDIFF(DAY, s.BeginDate, s.EndDate) AS Revenue, 
    	DATEADD(day, gn.Number, s.BeginDate) AS Arrival,
    	DATEADD(day, gn.Number + 1, s.BeginDate) AS Departure, 
    	YEAR(s.BeginDate) AS ArrivalYear,
    	LEFT(DATENAME(MONTH, s.BeginDate), 3) + '-' + CAST(YEAR(s.BeginDate) AS char(4)) AS Month,
    	s.GAStatus AS Status,
    	s.SourceSiteId, 
    	s.DefMarketSegmentCode AS MARKSEG,
    	s.GADescription AS Decsription, 
    	s.ProjectedRevenueAccomNett AS AccomREV,
    	s.ProjectedRevenueFBNett AS FBRev
    FROM @GroupRef_Numbers AS gn
    LEFT JOIN SyncGroupRoomBlockHeaders AS s ON gn.GroupRef = s.GroupRef
    WHERE (s.CreatedTimestamp < '2019-03-21' AND s.BeginDate BETWEEN '2019-03-21' AND '2019-12-31'
       OR s.CreatedTimestamp < '2018-03-21' AND s.BeginDate BETWEEN '2018-03-21' AND '2018-12-31')
      AND DATEADD(day, gn.Number + 1, s.BeginDate) <= EndDate
    ORDER BY MONTH(s.BeginDate);


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by Fiotomas Tuesday, April 16, 2019 2:03 PM
    Tuesday, April 16, 2019 12:31 PM

All replies

  • Suppose no any groups book the room for over 99 days. Try this:

    DECLARE @Numbers TABLE (
    	Number int
    );
    DECLARE @GroupRef_Numbers TABLE (
    	GroupRef varchar(10),
    	Number int
    );
    
    ;WITH CTE_Numbers AS (
    	SELECT 0 AS Number
    	UNION ALL 
    	SELECT Number + 1 AS Number
    	FROM CTE_Numbers
    	WHERE Number < 99
    )
    
    INSERT INTO @Numbers
    SELECT Number FROM CTE_Numbers;
    
    INSERT INTO @GroupRef_Numbers
    SELECT s.GroupRef, n.Number 
    FROM @Numbers AS n
    LEFT JOIN SyncGroupRoomBlockHeaders AS s ON n.Number <= DATEDIFF(day, s.StartDate, s.EndDate)
    WHERE s.GroupRef IS NOT NULL;
    
    SELECT 
    	s.GroupRef, 
    	s.ProjectedRevenueAccomNett / DATEDIFF(DAY, s.BeginDate, s.EndDate) AS Revenue, 
    	DATEADD(day, gn.Number, s.StartDate) AS Arrival,
    	DATEADD(day, gn.Number + 1, s.StartDate) AS Departure, 
    	YEAR(a.StartDate) AS ArrivalYear,
    	LEFT(DATENAME(MONTH, a.StartDate), 3) + '-' + CAST(YEAR(a.StartDate) AS char(4)) AS Month,
    	s.GAStatus AS Status,
    	s.SourceSiteId, 
    	s.DefMarketSegmentCode AS MARKSEG,
    	s.GADescription AS Decsription, 
    	s.ProjectedRevenueAccomNett AS AccomREV,
    	s.ProjectedRevenueFBNett AS FBRev
    FROM @GroupRef_Numbers AS gn
    LEFT JOIN SyncGroupRoomBlockHeaders AS s ON gn.GroupRef = s.GroupRef
    WHERE (s.CreatedTimestamp < '2019-03-21' AND s.BeginDate BETWEEN '2019-03-21' AND '2019-12-31'
       OR s.CreatedTimestamp < '2018-03-21' AND s.BeginDate BETWEEN '2018-03-21' AND '2018-12-31')
      AND DATEADD(day, gn.Number + 1, s.StartDate) <= EndDate
    ORDER BY MONTH(s.BeginDate);


    A Fan of SSIS, SSRS and SSAS


    Monday, April 15, 2019 12:17 PM
  • just do a udf like

    https://visakhm.blogspot.com/2010/02/generating-calendar-table.html

    then use query like

    Select  Year(f.[Date]) ArrivalYear,
    
    DATEDIFF(DAY,BeginDate, EndDate) AS StayNights, 
    
    DATENAME(month, f.[Date]) AS Month,
    
    GroupRef,
    
    GADescription AS Decsription,
    
    GAStatus AS Status,
    
    SourceSiteId,
    
    BeginDate AS Arrival,
    
    EndDate AS Departure,
    
    CreatedTimestamp,
    
    DefMarketSegmentCode AS MARKSEG,
    
    ProjectedRevenueAccomNett * COUNT(DISTINCT f.[Date])/(DATEDIFF(dd,BeginDate,EndDate) + 1)  AS AccomREV,
    
    ProjectedRevenueFBNett * COUNT(DISTINCT f.[Date])/(DATEDIFF(dd,BeginDate,EndDate) + 1) 
    AS FBRev
    
    From SyncGroupRoomBlockHeaders s
    CROSS APPLY dbo.CalendarTable(s.BeginDate, s.EndDate,0,0,0)f
    
    WHERE 
    
    (CreatedTimestamp < '2019-03-21'
    
    AND  BeginDate BETWEEN '2019-03-21' AND '2019-12-31')
    
    or
    
     ( CreatedTimestamp < '2018-03-21'
    
    AND  BeginDate BETWEEN '2018-03-21' AND '2018-12-31')
    GROUP BY Year(f.[Date]),
    
    DATEDIFF(DAY,BeginDate, EndDate) , 
    
    DATENAME(month, f.[Date]) ,
    
    GroupRef,
    
    GADescription ,
    
    GAStatus ,
    
    SourceSiteId,
    
    BeginDate,
    
    EndDate ,
    
    CreatedTimestamp,
    
    DefMarketSegmentCode
    
    Order By ArrivalYear,Month



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, April 15, 2019 1:02 PM
  • Hi,

    I get this

    Msg 207, Level 16, State 1, Line 23
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 43
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 29
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 30
    Invalid column name 'StartDate'.
    Msg 4104, Level 16, State 1, Line 31
    The multi-part identifier "a.StartDate" could not be bound.
    Msg 4104, Level 16, State 1, Line 32
    The multi-part identifier "a.StartDate" could not be bound.
    Msg 4104, Level 16, State 1, Line 32
    The multi-part identifier "a.StartDate" could not be bound.

    Monday, April 15, 2019 1:49 PM
  • I don't know how to do a udf
    Monday, April 15, 2019 1:49 PM
  • Hi,

    I get this

    Msg 207, Level 16, State 1, Line 23
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 43
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 29
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 30
    Invalid column name 'StartDate'.
    Msg 4104, Level 16, State 1, Line 31
    The multi-part identifier "a.StartDate" could not be bound.
    Msg 4104, Level 16, State 1, Line 32
    The multi-part identifier "a.StartDate" could not be bound.
    Msg 4104, Level 16, State 1, Line 32
    The multi-part identifier "a.StartDate" could not be bound.

    Sorry, change a.StartDate to s.StartDate. Try this:

    DECLARE @Numbers TABLE (
    	Number int
    );
    DECLARE @GroupRef_Numbers TABLE (
    	GroupRef varchar(10),
    	Number int
    );
    
    ;WITH CTE_Numbers AS (
    	SELECT 0 AS Number
    	UNION ALL 
    	SELECT Number + 1 AS Number
    	FROM CTE_Numbers
    	WHERE Number < 99
    )
    
    INSERT INTO @Numbers
    SELECT Number FROM CTE_Numbers --WHERE Number < 100;
    
    INSERT INTO @GroupRef_Numbers
    SELECT s.GroupRef, n.Number 
    FROM @Numbers AS n
    LEFT JOIN SyncGroupRoomBlockHeaders AS s ON n.Number <= DATEDIFF(day, s.StartDate, s.EndDate)
    WHERE s.GroupRef IS NOT NULL;
    
    SELECT 
    	s.GroupRef, 
    	s.ProjectedRevenueAccomNett / DATEDIFF(DAY, s.BeginDate, s.EndDate) AS Revenue, 
    	DATEADD(day, gn.Number, s.StartDate) AS Arrival,
    	DATEADD(day, gn.Number + 1, s.StartDate) AS Departure, 
    	YEAR(s.StartDate) AS ArrivalYear,
    	LEFT(DATENAME(MONTH, s.StartDate), 3) + '-' + CAST(YEAR(s.StartDate) AS char(4)) AS Month,
    	s.GAStatus AS Status,
    	s.SourceSiteId, 
    	s.DefMarketSegmentCode AS MARKSEG,
    	s.GADescription AS Decsription, 
    	s.ProjectedRevenueAccomNett AS AccomREV,
    	s.ProjectedRevenueFBNett AS FBRev
    FROM @GroupRef_Numbers AS gn
    LEFT JOIN SyncGroupRoomBlockHeaders AS s ON gn.GroupRef = s.GroupRef
    WHERE (s.CreatedTimestamp < '2019-03-21' AND s.BeginDate BETWEEN '2019-03-21' AND '2019-12-31'
       OR s.CreatedTimestamp < '2018-03-21' AND s.BeginDate BETWEEN '2018-03-21' AND '2018-12-31')
      AND DATEADD(day, gn.Number + 1, s.StartDate) <= EndDate
    ORDER BY MONTH(s.BeginDate);


    A Fan of SSIS, SSRS and SSAS


    Monday, April 15, 2019 2:56 PM
  • I don't know how to do a udf

    UDF means User-Defined Functions. Since you do not have the privilege to create the SQL function, you are not able to do it.

    A Fan of SSIS, SSRS and SSAS


    • Edited by Guoxiong Monday, April 15, 2019 3:00 PM
    Monday, April 15, 2019 2:59 PM
  • I get this

    Msg 207, Level 16, State 1, Line 23
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 43
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 29
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 30
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 31
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 32
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 32
    Invalid column name 'StartDate'.

    Tuesday, April 16, 2019 11:32 AM
  • I don't know how to do a udf

    Just run the query in the link and UDF will get created

    Then execute the query given. Thats it


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, April 16, 2019 11:34 AM
  • I get this

    Msg 207, Level 16, State 1, Line 23
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 43
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 29
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 30
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 31
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 32
    Invalid column name 'StartDate'.
    Msg 207, Level 16, State 1, Line 32
    Invalid column name 'StartDate'.

    My fault. I used SatrtDate instead of BeginDate. Try this:

    DECLARE @Numbers TABLE (
    	Number int
    );
    DECLARE @GroupRef_Numbers TABLE (
    	GroupRef varchar(10),
    	Number int
    );
    
    ;WITH CTE_Numbers AS (
    	SELECT 0 AS Number
    	UNION ALL 
    	SELECT Number + 1 AS Number
    	FROM CTE_Numbers
    	WHERE Number < 99
    )
    
    INSERT INTO @Numbers
    SELECT Number FROM CTE_Numbers --WHERE Number < 100;
    
    INSERT INTO @GroupRef_Numbers
    SELECT s.GroupRef, n.Number 
    FROM @Numbers AS n
    LEFT JOIN SyncGroupRoomBlockHeaders AS s ON n.Number <= DATEDIFF(day, s.BeginDate, s.EndDate)
    WHERE s.GroupRef IS NOT NULL;
    
    SELECT 
    	s.GroupRef, 
    	s.ProjectedRevenueAccomNett / DATEDIFF(DAY, s.BeginDate, s.EndDate) AS Revenue, 
    	DATEADD(day, gn.Number, s.BeginDate) AS Arrival,
    	DATEADD(day, gn.Number + 1, s.BeginDate) AS Departure, 
    	YEAR(s.BeginDate) AS ArrivalYear,
    	LEFT(DATENAME(MONTH, s.BeginDate), 3) + '-' + CAST(YEAR(s.BeginDate) AS char(4)) AS Month,
    	s.GAStatus AS Status,
    	s.SourceSiteId, 
    	s.DefMarketSegmentCode AS MARKSEG,
    	s.GADescription AS Decsription, 
    	s.ProjectedRevenueAccomNett AS AccomREV,
    	s.ProjectedRevenueFBNett AS FBRev
    FROM @GroupRef_Numbers AS gn
    LEFT JOIN SyncGroupRoomBlockHeaders AS s ON gn.GroupRef = s.GroupRef
    WHERE (s.CreatedTimestamp < '2019-03-21' AND s.BeginDate BETWEEN '2019-03-21' AND '2019-12-31'
       OR s.CreatedTimestamp < '2018-03-21' AND s.BeginDate BETWEEN '2018-03-21' AND '2018-12-31')
      AND DATEADD(day, gn.Number + 1, s.BeginDate) <= EndDate
    ORDER BY MONTH(s.BeginDate);


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by Fiotomas Tuesday, April 16, 2019 2:03 PM
    Tuesday, April 16, 2019 12:31 PM
  • Thank you,

    I  changed the varchar to 1000 here

    DECLARE @GroupRef_Numbers TABLE (
    	GroupRef varchar(10),

    Got a table with correct results (I am still checking but it looks like they are!) 

    and also the following message

    (100 rows affected)


    (19282 rows affected)

    (10502 rows affected)

    Tuesday, April 16, 2019 1:29 PM
  • Those messages are okay.

    A Fan of SSIS, SSRS and SSAS

    Tuesday, April 16, 2019 1:35 PM
  • Thank you again!
    Tuesday, April 16, 2019 2:03 PM
  • Guoxiong,

    Below the result I got. If you look at the Month column, it says july even in the August rows. If I wanted to filter by month, I would like the rows relevant to each month to go in each month. For example

    row 2 should fall in July while row 3 should fall in August.

    Is this possible? 

        Revenue        Arrival    Departure      ArrivalYear        Month            Status  SourceSiteId
    3540.3392 30/07/2019 31/07/2019 2019 Jul-19 9004 LSEBA
    3540.3392 31/07/2019 01/08/2019 2019 Jul-19 9004 LSEBA
    3540.3392 01/08/2019 02/08/2019 2019 Jul-19 9004 LSEBA
    3540.3392 02/08/2019 03/08/2019 2019 Jul-19 9004 LSEBA

    Tuesday, April 16, 2019 2:33 PM
  • Try this:

    DECLARE @Numbers TABLE (
    	Number int
    );
    DECLARE @GroupRef_Numbers TABLE (
    	GroupRef varchar(10),
    	Number int
    );
    
    ;WITH CTE_Numbers AS (
    	SELECT 0 AS Number
    	UNION ALL 
    	SELECT Number + 1 AS Number
    	FROM CTE_Numbers
    	WHERE Number < 99
    )
    
    INSERT INTO @Numbers
    SELECT Number FROM CTE_Numbers --WHERE Number < 100;
    
    INSERT INTO @GroupRef_Numbers
    SELECT s.GroupRef, n.Number 
    FROM @Numbers AS n
    LEFT JOIN SyncGroupRoomBlockHeaders AS s ON n.Number <= DATEDIFF(day, s.BeginDate, s.EndDate)
    WHERE s.GroupRef IS NOT NULL;
    
    SELECT 
    	s.GroupRef, 
    	s.ProjectedRevenueAccomNett / DATEDIFF(DAY, s.BeginDate, s.EndDate) AS Revenue, 
    	DATEADD(day, gn.Number, s.BeginDate) AS Arrival,
    	DATEADD(day, gn.Number + 1, s.BeginDate) AS Departure, 
    	YEAR(s.BeginDate) AS ArrivalYear,
    	LEFT(DATENAME(MONTH, DATEADD(day, gn.Number, s.BeginDate)), 3) + '-' + CAST(YEAR(DATEADD(day, gn.Number, s.BeginDate)) AS char(4)) AS Month,
    	s.GAStatus AS Status,
    	s.SourceSiteId, 
    	s.DefMarketSegmentCode AS MARKSEG,
    	s.GADescription AS Decsription, 
    	s.ProjectedRevenueAccomNett AS AccomREV,
    	s.ProjectedRevenueFBNett AS FBRev
    FROM @GroupRef_Numbers AS gn
    LEFT JOIN SyncGroupRoomBlockHeaders AS s ON gn.GroupRef = s.GroupRef
    WHERE (s.CreatedTimestamp < '2019-03-21' AND s.BeginDate BETWEEN '2019-03-21' AND '2019-12-31'
       OR s.CreatedTimestamp < '2018-03-21' AND s.BeginDate BETWEEN '2018-03-21' AND '2018-12-31')
      AND DATEADD(day, gn.Number + 1, s.BeginDate) <= EndDate
    ORDER BY MONTH(s.BeginDate);


    A Fan of SSIS, SSRS and SSAS

    Tuesday, April 16, 2019 3:35 PM
  • Hi Guoxiong,

    The above works fine except for one thing.

    If I pull out one group that goes over two months (July and August for example) and select the two months from the Month column, the daily revenue lines will only show the month of arrival.

    This means that if I want to check revenue per month, I can't use the Month column but have to manually calculate line by line. The dates and revenue are correct, it's the month filter that is not working.

    We need to be able to produce a report per each month with total revenue fo the group market segment so that whatever revenue falls in July is counted in July and whatever revenue falls in August is counted in August, even if the group is the same.

    I hope I am being clear enough.

    

    Thursday, May 2, 2019 3:52 PM
  • I think you used 

    LEFT(DATENAME(MONTH, s.BeginDate), 3) + '-' + CAST(YEAR(s.BeginDate) AS char(4)) AS Month,

    instead of

    LEFT(DATENAME(MONTH, DATEADD(day, gn.Number, s.BeginDate)), 3) + '-' + CAST(YEAR(DATEADD(day, gn.Number, s.BeginDate)) AS char(4)) AS Month,


    A Fan of SSIS, SSRS and SSAS

    Thursday, May 2, 2019 4:58 PM
  • Thanks!
    Friday, May 3, 2019 8:17 AM
  • Hi Guoxiong,

    You kindly helped me with the following query:

    DECLARE @Numbers TABLE (

           Number int

    );

    DECLARE @GroupRef_Numbers TABLE (

           GroupRef varchar(1000),

           Number int

    );

    ;WITH CTE_Numbers AS (

           SELECT 0 AS Number

           UNION ALL

           SELECT Number + 1 AS Number

           FROM CTE_Numbers

           WHERE Number < 99

    )

    INSERT INTO @Numbers

    SELECT Number FROM CTE_Numbers --WHERE Number < 100;

    INSERT INTO @GroupRef_Numbers

    SELECT s.GroupRef, n.Number

    FROM @Numbers AS n

    LEFT JOIN SyncGroupRoomBlockHeaders AS s ON n.Number <= DATEDIFF(day, s.BeginDate, s.EndDate)

    WHERE s.GroupRef IS NOT NULL;

    SELECT

           s.GroupRef,

           s.SourceSiteId,

           s.GAStatus AS Status,

           s.GADescription AS Decsription,

           s.DefMarketSegmentCode AS MARKSEG,

           s.ProjectedRevenueAccomNett / DATEDIFF(DAY, s.BeginDate, s.EndDate) AS ACCDailyRevenue,

           s.ProjectedRevenueFBNett / DATEDIFF(DAY, s.BeginDate, s.EndDate) AS FBDailyRevenue,

           DATEADD(day, gn.Number, s.BeginDate) AS Arrival,

           DATEADD(day, gn.Number + 1, s.BeginDate) AS Departure,

           YEAR(s.BeginDate) AS ArrivalYear,

           LEFT(DATENAME(MONTH, DATEADD(day, gn.Number, s.BeginDate)), 3) + '-' + CAST(YEAR(DATEADD(day, gn.Number, s.BeginDate)) AS char(4)) AS Month,

           s.ProjectedRevenueAccomNett AS TOTALAccomREV,

           s.ProjectedRevenueFBNett AS TOTALFBRev

    FROM @GroupRef_Numbers AS gn

    LEFT JOIN SyncGroupRoomBlockHeaders AS s ON gn.GroupRef = s.GroupRef

    WHERE (s.CreatedTimestamp < '2019-05-02' AND s.BeginDate BETWEEN '2019-05-10' AND '2020-12-31'

       OR s.CreatedTimestamp < '2018-05-02' AND s.BeginDate BETWEEN '2018-05-10' AND '2019-12-31')

      AND DATEADD(day, gn.Number + 1, s.BeginDate) <= EndDate

    ORDER BY MONTH(s.BeginDate);

    I am trying to do the same with another table (SyncReservations) which shows individual bookings, not groups. I have changed all the columns to columns included in the new table (SyncReservations) but keep getting the same error message.

    This is the new query I am using:

    DECLARE @Numbers TABLE (
    Number int
    );
    DECLARE @BookRef_Numbers TABLE (
    BookRef varchar(1000),
    Number int
    );

    ;WITH CTE_Numbers AS (
    SELECT 0 AS Number
    UNION ALL 
    SELECT Number + 1 AS Number
    FROM CTE_Numbers
    WHERE Number < 99
    )

    INSERT INTO @Numbers
    SELECT Number FROM CTE_Numbers --WHERE Number < 100;

    INSERT INTO @BookRef_Numbers
    SELECT s.BookRef, n.Number 
    FROM @Numbers AS n
    LEFT JOIN SyncReservations AS s ON n.Number <= DATEDIFF(day, s.DateArrive, s.DateDepart)
    WHERE s.BookRef IS NOT NULL;

    SELECT 
    s.BookRef, 
    s.SourceSiteId, 
    s.BookingStatus AS Status,
    s.PackageCode AS Decsription,
    s.MarketSegment AS MARKSEG,
    s.ChargeTotalNett / DATEDIFF(DAY, s.DateArrive, s.DateDepart) AS ACCDailyRevenue, 
    s.ChargeTotalGross / DATEDIFF(DAY, s.DateArrive, s.DateDepart) AS FBDailyRevenue,
    DateArrive,
    DATEADD(day, gn.Number + 1, s.DateDepart) AS Departure, 
    YEAR(s.DateArrive) AS ArrivalYear,
    LEFT(DATENAME(MONTH, DATEADD(day, gn.Number, s.DateArrive)), 3) + '-' + CAST(YEAR(DATEADD(day, gn.Number, s.DateArrive)) AS char(4)) AS Month,
    s.ChargeTotalNett AS TOTALAccomREV,
    s.ChargeTotalGross AS TOTALFBRev
    FROM @BookRef_Numbers AS gn
    LEFT JOIN SyncReservations AS s ON gn.BookRef = s.BookRef
    WHERE (s.CreatedTimestamp < '2019-05-02' AND s.DateArrive BETWEEN '2019-05-10' AND '2020-12-31'
       OR s.CreatedTimestamp < '2018-05-02' AND s.DateArrive BETWEEN '2018-05-10' AND '2019-12-31')
      AND DATEADD(day, gn.Number + 1, s.DateArrive) <= DateDepart

    ORDER BY MONTH(s.DateArrive);

    This is the error:

    (100 rows affected)

    (1573023 rows affected)
    Msg 8134, Level 16, State 1, Line 26
    Divide by zero error encountered.

    Could you help me understand where I am wrong?

    Thanks.

    Monday, June 3, 2019 9:17 AM
  • Can you post some sample data? Is that possible for both DateArrive and DateDepart are the same?

    A Fan of SSIS, SSRS and SSAS

    Monday, June 3, 2019 1:27 PM
  • HI, it should like this more or less. this is the previous query for groups. The GroupRef column is substituted by the BookRef. Date Arrive and Date depart are arrival and departure so two different things.

    thanks

    Monday, June 3, 2019 2:40 PM
  • … Date Arrive and Date depart are arrival and departure so two different things.

    I mean if both columns might be the same dates? If yes, the error "Divide by zero error encountered" will be caught.

    A Fan of SSIS, SSRS and SSAS

    Monday, June 3, 2019 2:53 PM
  • It shouldn't be the case as one indicates the arrival date and the other the departure of the booking. 

    We have some room types called V_PM who have zero nights, there are about 50 in each property, would they affect the result? RoomTypeCode is the column with the room codes

    Monday, June 3, 2019 3:00 PM
  • Run the following query to check if there are some rows with the same dates in both columns:

    SELECT * FROM SyncReservations WHERE DATEDIFF(DAY, DateArrive, DateDepart) = 0


    A Fan of SSIS, SSRS and SSAS

    Monday, June 3, 2019 4:05 PM
  • Hi, yes, I get 152 lines with 0 nights where arrival and departure are on the same day. Would it be possible then to count all 0 nights as 1 night?
    Tuesday, June 4, 2019 7:53 AM
  • That is why you get the error "Divide by zero error encountered". 

    s.ChargeTotalNett / CASE WHEN DATEDIFF(DAY, s.DateArrive, s.DateDepart) = 0 THEN 1 ELSE DATEDIFF(DAY, s.DateArrive, s.DateDepart) END AS ACCDailyRevenue, 
    s.ChargeTotalGross / CASE WHEN DATEDIFF(DAY, s.DateArrive, s.DateDepart) = 0 THEN 1 ELSE DATEDIFF(DAY, s.DateArrive, s.DateDepart) END AS FBDailyRevenue,


    A Fan of SSIS, SSRS and SSAS

    Tuesday, June 4, 2019 1:05 PM
  • Thanks, seems to be working now. I will check figures. Thanks again.
    Tuesday, June 4, 2019 1:32 PM
  • Hi, this is what I get, as you can see it is not showing me only the revenue for the month selected (June 2018 in this case) as it is including nights in July.

    In the other query it will split the revenue between the months.

    Can you fix it? 

    Tuesday, June 4, 2019 2:25 PM
  • This is the query I am using

    DECLARE @Numbers TABLE (
    Number int
    );
    DECLARE @BookRef_Numbers TABLE (
    BookRef varchar(1000),
    Number int
    );

    ;WITH CTE_Numbers AS (
    SELECT 0 AS Number
    UNION ALL 
    SELECT Number + 1 AS Number
    FROM CTE_Numbers
    WHERE Number < 99
    )

    INSERT INTO @Numbers
    SELECT Number FROM CTE_Numbers --WHERE Number < 100;

    INSERT INTO @BookRef_Numbers
    SELECT s.BookRef, n.Number 
    FROM @Numbers AS n
    LEFT JOIN SyncReservations AS s ON n.Number <= DATEDIFF(day, s.DateArrive, s.DateDepart)
    WHERE s.BookRef IS NOT NULL;

    SELECT 
    s.BookRef, 
    s.SourceSiteId, 
    s.BookingStatus AS Status,
    s.PackageCode AS Decsription,
    s.MarketSegment AS MARKSEG,
    s.ChargeTotalNett / CASE WHEN DATEDIFF(DAY, s.DateArrive, s.DateDepart) = 0 THEN 1 ELSE DATEDIFF(DAY, s.DateArrive, s.DateDepart) END AS ACCDailyRevenueNETT, 
        s.ChargeTotalGross / CASE WHEN DATEDIFF(DAY, s.DateArrive, s.DateDepart) = 0 THEN 1 ELSE DATEDIFF(DAY, s.DateArrive, s.DateDepart) END AS ACCDailyRevenueGROSS,
    DateArrive,
    DATEADD(day, gn.Number + 1, s.DateDepart) AS Departure, 
    YEAR(s.DateArrive) AS ArrivalYear,
    LEFT(DATENAME(MONTH, DATEADD(day, gn.Number, s.DateArrive)), 3) + '-' + CAST(YEAR(DATEADD(day, gn.Number, s.DateArrive)) AS char(4)) AS Month,
    s.ChargeTotalNett AS TOTALAccomNETT,
    s.ChargeTotalGross AS TOTALGross
    FROM @BookRef_Numbers AS gn
    LEFT JOIN SyncReservations AS s ON gn.BookRef = s.BookRef
    WHERE (s.CreatedTimestamp < '2019-06-04' AND s.DateArrive BETWEEN '2019-06-04' AND '2020-12-31'
       OR s.CreatedTimestamp < '2018-06-04' AND s.DateArrive BETWEEN '2018-06-04' AND '2019-12-31')
      AND DATEADD(day, gn.Number + 1, s.DateArrive) <= DateDepart

    ORDER BY MONTH(s.DateArrive);

    Tuesday, June 4, 2019 2:26 PM
  • Hi, did you have a chance to look at the above? tks
    Tuesday, June 11, 2019 10:00 AM
  • Hi, did you have a chance to look at the above? tks

    Did you figure it out? If not, can you tell me the output you expect.

    A Fan of SSIS, SSRS and SSAS

    Thursday, June 13, 2019 2:09 PM
  • Hi, Please look at my message Tuesday, June 4, 2019 2:25 PM

    That is what I get. I select the month of June but get results for July as well. I need the nights to be split between the months just like the report does when i use the SyncGroupRoomBlockheaders table. 

    I don't understand why it won't split the months when it is the same as the query used for the other table.

    I would expect the split to be as below (this is the report I get with the other table).

    So each Booking reference should have a line per night and be split between the months. I hope I am being clear enough. Basically, it is the same as the other query you helped me with only using a different table.

    Thursday, June 13, 2019 3:07 PM
  • Replace 

    DateArrive,
    DATEADD(day, gn.Number + 1, s.DateDepart) AS Departure,

    WITH

    DATEADD(day, gn.Number, s.DateArrive) AS DateArrive,

    DATEADD(day, gn.Number + 1, s.DateArrive) AS Departure,


    A Fan of SSIS, SSRS and SSAS

    Friday, June 14, 2019 4:44 PM
  • Thanks. Seems to be working now.
    Saturday, June 15, 2019 11:29 AM