July 20, 2012 at 3:39 am
Case 1.
-----------
select Convert(bigint,RecordID) as RecordId,TRANSACTION_ID,PROPERTY_ID,GeoSource_ID,Segment_ID,Channel_ID,RoomType_ID,STAY_DATE,RESV_NAME_ID,BOOKED_ROOM,CONSUMED_ROOM,ROOM_REVENUE,FOOD_REVENUE,OTHER_REVENUE,TOTAL_REVENUE,MARKET_GROUP,ORIGIN_OF_BOOKING,RATE_CODE,RESV_STATUS,NON_REVENUE,ADULTS,CHILDREN,TRUNC_BEGIN_DATE,TRUNC_END_DATE,PSUEDO_ROOM_YN,CORPORATE_NUMBER,BOOKED_RATE,RESERVATION_DATE,MEMBERSHIP_CARD_NUMBER,CREATED_DATE,MODIFIED_DATE,CREATED_USER,MODIFIED_USER,RECORD_STATUS,SOURCE
into #temp4
from RPM_Pms_Stay_Detail
where property_id = 31
and case When Stay_date between 'Apr 01 2011' and 'Jun 30 2011' then 1
when Stay_date between 'Jul 01 2011' and 'Sep 30 2011' then 1
else 0 end = 1
Case 2.
-----------
select Convert(bigint,RecordID) as RecordId,TRANSACTION_ID,PROPERTY_ID,GeoSource_ID,Segment_ID,Channel_ID,RoomType_ID,STAY_DATE,RESV_NAME_ID,BOOKED_ROOM,CONSUMED_ROOM,ROOM_REVENUE,FOOD_REVENUE,OTHER_REVENUE,TOTAL_REVENUE,MARKET_GROUP,ORIGIN_OF_BOOKING,RATE_CODE,RESV_STATUS,NON_REVENUE,ADULTS,CHILDREN,TRUNC_BEGIN_DATE,TRUNC_END_DATE,PSUEDO_ROOM_YN,CORPORATE_NUMBER,BOOKED_RATE,RESERVATION_DATE,MEMBERSHIP_CARD_NUMBER,CREATED_DATE,MODIFIED_DATE,CREATED_USER,MODIFIED_USER,RECORD_STATUS,SOURCE
into #temp1
from RPM_Pms_Stay_Detail
where property_id = 31
and Stay_date between 'Apr 01 2011' and 'Jun 30 2011'
insert into #temp1(RecordID,TRANSACTION_ID,PROPERTY_ID,GeoSource_ID,Segment_ID,Channel_ID,RoomType_ID,STAY_DATE,RESV_NAME_ID,BOOKED_ROOM,CONSUMED_ROOM,ROOM_REVENUE,FOOD_REVENUE,OTHER_REVENUE,TOTAL_REVENUE,MARKET_GROUP,ORIGIN_OF_BOOKING,RATE_CODE,RESV_STATUS,NON_REVENUE,ADULTS,CHILDREN,TRUNC_BEGIN_DATE,TRUNC_END_DATE,PSUEDO_ROOM_YN,CORPORATE_NUMBER,BOOKED_RATE,RESERVATION_DATE,MEMBERSHIP_CARD_NUMBER,CREATED_DATE,MODIFIED_DATE,CREATED_USER,MODIFIED_USER,RECORD_STATUS,SOURCE)
select Convert(bigint,RecordID) as RecordId,TRANSACTION_ID,PROPERTY_ID,GeoSource_ID,Segment_ID,Channel_ID,RoomType_ID,STAY_DATE,RESV_NAME_ID,BOOKED_ROOM,CONSUMED_ROOM,ROOM_REVENUE,FOOD_REVENUE,OTHER_REVENUE,TOTAL_REVENUE,MARKET_GROUP,ORIGIN_OF_BOOKING,RATE_CODE,RESV_STATUS,NON_REVENUE,ADULTS,CHILDREN,TRUNC_BEGIN_DATE,TRUNC_END_DATE,PSUEDO_ROOM_YN,CORPORATE_NUMBER,BOOKED_RATE,RESERVATION_DATE,MEMBERSHIP_CARD_NUMBER,CREATED_DATE,MODIFIED_DATE,CREATED_USER,MODIFIED_USER,RECORD_STATUS,SOURCE
from RPM_Pms_Stay_Detail
where property_id = 31
and Stay_date between 'Jul 01 2011' and 'Sep 30 2011'
Case 3.
-----------
select Convert(bigint,RecordID) as RecordId,TRANSACTION_ID,PROPERTY_ID,GeoSource_ID,Segment_ID,Channel_ID,RoomType_ID,STAY_DATE,RESV_NAME_ID,BOOKED_ROOM,CONSUMED_ROOM,ROOM_REVENUE,FOOD_REVENUE,OTHER_REVENUE,TOTAL_REVENUE,MARKET_GROUP,ORIGIN_OF_BOOKING,RATE_CODE,RESV_STATUS,NON_REVENUE,ADULTS,CHILDREN,TRUNC_BEGIN_DATE,TRUNC_END_DATE,PSUEDO_ROOM_YN,CORPORATE_NUMBER,BOOKED_RATE,RESERVATION_DATE,MEMBERSHIP_CARD_NUMBER,CREATED_DATE,MODIFIED_DATE,CREATED_USER,MODIFIED_USER,RECORD_STATUS,SOURCE
into #temp3
from RPM_Pms_Stay_Detail
where property_id = 31
and (Stay_date between 'Apr 01 2011' and 'Jun 30 2011'
or Stay_date between 'Jul 01 2011' and 'Sep 30 2011')
July 20, 2012 at 4:48 am
Test all three and see which one does what you want in the most efficient manner.
Probably, I'd look at #3 first, but it really depends on what the execution plan looks like. Another option for an OR is to use a UNION ALL. I would probably shy away from #2 unless, again, testing showed otherwise. I just try to avoid loading up temp tables as a standard part of processing, but it has it's place.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply