Database

busNumberSQL[halfhoursSQL] = myReader->get_Item("<code>field_name_here</code>")->ToString();

--Store the first and last day of the month (July 2001)
DECLARE @StartMonth smalldatetime, @EndMonth smalldatetime
 
SELECT 
    --First of the month
    @StartMonth = CAST('1 July 2001' As smalldatetime), 
    --Day before the first of the next month
    @EndMonth = DATEADD(day, -1, DATEADD(month, 1, @StartMonth)) 
 
SELECT
    --Hotel details
    HOTEL.Hotel_No,
    HOTEL.Hotel_Name,
    HOTEL.Address,
 
    --Total income per hotel
    SUM(Bookings.Income) As Income
 
FROM
    HOTEL INNER JOIN
    (
        --Calculate the income as Room price (per night) * # nights
        SELECT
            CASE
                --Booking started before the month
                WHEN Date_From < @StartMonth THEN
                    DATEDIFF(day, @StartMonth, Date_To) * ROOM.Price
 
                --Booking finished after the month
                WHEN Date_To > @EndMonth THEN
                    DATEDIFF(day, Date_From, @EndMonth) * ROOM.Price

                --Booking was entirely within the month
                ELSE
                    DATEDIFF(day, Date_From, Date_To) * ROOM.Price
            END As Income
 
         FROM
            BOOKING INNER JOIN ROOM
            ON ROOM.Room_No = BOOKING.Room_No
 
        WHERE
        --Only want bookings that intersect the month
            Date_From < @EndMonth
        AND
            Date_To > @StartMonth
    )
    As Bookings
 
WHERE
--Only want bookings for the Sheridan hotels
    Hotel_Name = 'Sheridan Hotel'
 
GROUP BY
    HOTEL.Hotel_No,
    HOTEL.Hotel_Name,
    HOTEL.Address

--Store the first and last day of the month (July 2001)
DECLARE @StartMonth smalldatetime, @EndMonth smalldatetime 
SELECT
     --First of the month
    @StartMonth = CAST('1 July 2001' As smalldatetime),
     --Day before the first of the next month
    @EndMonth = DATEADD(day, -1, DATEADD(month, 1, @StartMonth))
 
SELECT
    --Hotel details
    HOTEL.Hotel_No,
    HOTEL.Hotel_Name,
    HOTEL.Address,
 
    --Total income per hotel
    SUM(Bookings.Income) As Income
 
FROM
    HOTEL INNER JOIN
    (
        --Calculate the income as Room price (per night) * # nights
        SELECT
            ROOM.Hotel_No,
            CASE
                --Booking started before the month
                WHEN Date_From < @StartMonth THEN
                    DATEDIFF(day, @StartMonth, Date_To) * ROOM.Price
 
                --Booking finished after the month
                WHEN Date_To > @EndMonth THEN
                    DATEDIFF(day, Date_From, @EndMonth) * ROOM.Price
 
                --Booking was entirely within the month
                ELSE
                    DATEDIFF(day, Date_From, Date_To) * ROOM.Price
            END As Income
  
        FROM
            BOOKING INNER JOIN ROOM
            ON ROOM.Room_No = BOOKING.Room_No
 
        WHERE
        --Only want bookings that intersect the month
            Date_From < @EndMonth
        AND
            Date_To > @StartMonth
    )
    As Bookings
 
    ON Bookings.Hotel_No = HOTEL.Hotel_No
 
WHERE
--Only want bookings for the Sheridan hotels
    Hotel_Name = 'Sheridan Hotel'
 
GROUP BY
    HOTEL.Hotel_No,
    HOTEL.Hotel_Name,
    HOTEL.Address

DECLARE @temp TABLE (
    MASTER_CODE int not null, 
    PART_CODE int not null, 
    INC_COUNT int not null DEFAULT 0
)
 
INSERT INTO 
    @temp (MASTER_CODE, PART_CODE)
SELECT 
    pm.portfolio_code, pp.portfolio_code 
FROM 
    portfolio As pm INNER JOIN portfolio As pp
    ON pp.master_account = pm.portfolio_code
WHERE 
    pm.master_account_indicator = 1
ORDER BY 
    pm.portfolio_code, pp.portfolio_code
 
DECLARE @Count int, @Previous int
SELECT @Count = 1, @Previous = Null
 
UPDATE @temp
SET
    @Count = INC_COUNT = CASE
        WHEN MASTER_CODE = @Previous THEN @Count + 1
        ELSE 1
    END,
    @Previous = MASTER_CODE
 
SELECT
    MASTER_CODE,
    PART_CODE,
    INC_COUNT
FROM
    @temp
ORDER BY
    MASTER_CODE,
    PART_CODE

select  pm.portfolio_code master_code
       ,pp.portfolio_code part_code
       ,( select count(c.portfolio_code) from portfolio c
          where c.portfolio_code <= pp.portfolio_code
            and c.master_account = pm.portfolio_code
        ) inc_count
from  portfolio pm
     ,portfolio pp
where pm.master_account_indicator = 1
  and pp.master_account = pm.portfolio_code
order by pm.portfolio_code, pp.portfolio_code