July 20, 2012 at 3:39 am
Case 1.
-----------
selectConvert(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
fromRPM_Pms_Stay_Detail
whereproperty_id = 31
andcase 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.
-----------
selectConvert(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
fromRPM_Pms_Stay_Detail
whereproperty_id = 31
andStay_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)
selectConvert(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
fromRPM_Pms_Stay_Detail
whereproperty_id = 31
andStay_date between 'Jul 01 2011' and 'Sep 30 2011'
Case 3.
-----------
selectConvert(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
fromRPM_Pms_Stay_Detail
whereproperty_id = 31
and(Stay_date between 'Apr 01 2011' and 'Jun 30 2011'
orStay_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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply