Tuesday, July 21, 2015

Copy SQL Table With Child Table Relaionships

dbo.RoomOccupancyRate has many dbo.OccupancyChildRate records.

How to copy them?

Following stored procedure takes care of it. It even has the option to copy only the active items.

ALTER PROCEDURE [dbo].[sp_CopyHotelContractRoomOccupancyRate]
    @NewHotelContractID int,
    @SelectedHotelContractID int,
    @UserName nvarchar(20),
    @ActiveItemsOnly bit,
    @OldDateBandId int,
    @NewDateBandId int,
    @OldRoomOccupancyId int,
    @NewRoomOccupancyId int 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements. 
    SET NOCOUNT ON;

    IF (EXISTS(SELECT TOP 1 cr.[RoomOccupancyRateID]
                 FROM [dbo].[RoomOccupancyRate] cr  
           INNER JOIN [dbo].[HotelRoomOccupancy] rc ON cr.[RoomOccupancyID] = rc.[RoomOccupancyID]
                WHERE rc.[RoomOccupancyID] = @OldRoomOccupancyId)) 
    BEGIN
        PRINT '----[dbo].[RoomOccupancyRate] table should be copied'

        DECLARE @_existingRoomOccupancyRateId int, 
                @_newRoomOccupancyRateId int;

        SELECT @_existingRoomOccupancyRateId = MIN([RoomOccupancyRateID])
          FROM [dbo].[RoomOccupancyRate] 
         WHERE RoomOccupancyID = @OldRoomOccupancyId;

            WHILE (@_existingRoomOccupancyRateId IS NOT NULL)
            BEGIN 
                -- insert new record 
                 INSERT INTO [dbo].[RoomOccupancyRate]
                               ([RoomOccupancyID],[ChargeTypeID] 
                               ,[Active],[LastChangedBy],[LastChangedTime]) 
                        SELECT @NewRoomOccupancyId,[ChargeTypeID],[Active],@UserName,GETDATE() 
                      FROM [dbo].[RoomOccupancyRate]
                     WHERE [RoomOccupancyRateID] = @_existingRoomOccupancyRateId 
                       AND CASE
                             WHEN  @ActiveItemsOnly = 1 AND (Active = 1) THEN 1 
                             WHEN  @ActiveItemsOnly = 0 AND (Active = 1 OR Active = 0) THEN 1
                           END = 1

                -- if room occupancy rate record successfully inserted.
                IF(@@ROWCOUNT > 0)
                BEGIN 
                    SELECT @_newRoomOccupancyRateId = @@IDENTITY;

                    PRINT '     |-----[dbo].[OccupancyChildRate] table should be copied'

                    -- copy occupancy child rate table
                    INSERT INTO [dbo].[OccupancyChildRate]
                               ([RoomOccupancyRateID],[MinAge],[MaxAge],[BuyRate],[Active],[LastChangedBy],[LastChangedTime])
                        SELECT @_newRoomOccupancyRateId,[MinAge],[MaxAge],[BuyRate],[Active],@UserName,GETDATE() 
                         FROM [dbo].[OccupancyChildRate] 
                        WHERE [RoomOccupancyRateID] = @_existingRoomOccupancyRateId  
                            AND CASE
                                 WHEN  @ActiveItemsOnly = 1 AND (Active = 1) THEN 1 
                                 WHEN  @ActiveItemsOnly = 0 AND (Active = 1 OR Active = 0) THEN 1
                               END = 1;  
                END

                SELECT @_existingRoomOccupancyRateId = MIN([RoomOccupancyRateID])
                  FROM [dbo].[RoomOccupancyRate]
                 WHERE RoomOccupancyID = @OldRoomOccupancyId
                   AND [RoomOccupancyRateID] > @_existingRoomOccupancyRateId;
            END  
    END 
END

Monday, July 20, 2015

Conditional Filtering in SQL

Assume I need to select a set of data based on the following criteria.


  • If the @Active-Items-Only flag is set, then select only the active items
  • otherwise select all active and non-active items.
DECLARE @ActiveOnly bit = 1; -- select only active

SELECT [HotelContractID]
      ,[RoomTypeID]
      ,[TravelStart]
      ,[TravelEnd]
      ,[ReleaseTypeID]
      ,[ReleaseValue]
      ,[Active] 
  FROM [dbo].[RoomAllocation]
  WHERE [HotelContractID] = 124 AND
  (CASE
    -- if active only selected, 
    -- check only active = 1 rows in the table and return 1
    WHEN  @ActiveOnly = 1 AND (Active = 1) THEN 1 
    -- if active and inactive both selected, 
    -- check for both active = 1 and active = 0 rows in the table and return 1
    WHEN  @ActiveOnly = 0 AND (Active = 1 OR Active = 0) THEN 1
  END) = 1 -- 1=1 where conditional always true. magic happens in WHEN condition.
Results:

When you set @ActiveOnly = 1, it gives only the active ones.
When you set @ActiveOnly = 0, it gives only the active and inactive both.