July 1, 2015 at 10:24 pm
Hey i have this query in a proc
declare @bu_id INT,
@CurCaptureDate DATETIME,
@user_id INT,
@col_name VARCHAR(100),
@sort_order VARCHAR(4),
@CityPair_ID INT=NULL,
@Departure_Code VARCHAR(50)=NULL,
@Departure_Date DATETIME=NULL,
@Departure_Date_To DATETIME=NULL,
@Days_Left INT=NULL,
@AnalystName VARCHAR(200)=NULL,
@Reasons VARCHAR(4000)=NULL,
@SearchOperator VARCHAR(50)=NULL,
@casesApproved BIT=NULL,
@Departure_Group_ID INT=NULL,
@Optimization_Settings_ID INT=NULL,
@depTimeFrom INT=NULL,
@depTimeTo INT=NULL,
@massAdj INT=NULL
SELECT Case_Id, Case_Number, Case_Primary_Analyst_Id_fk, ( Users.Last_Name + ' ' + Users.First_Name ) AS AnalystName ,
Case_Departure_Id_fk, D.Departure_Code, Departure_OriginalDate , Case_CityPair_Id_fk, CityPair_Name, LEFT(Datename(DW,getdate()) ,3) AS DOW ,
DCCC.Departure_Detail_Capture_Days_Left AS Days_left , Rev.Adjusted_Revenue, Rev.Adjusted_Revenue_Gain , Rev.Recommended_Revenue ,
Rev.Recommended_Revenue_Gain , cd.Case_Importance, df.Case_Followup_Days_Left, d.Departure_Active, cd.Case_Reminder, cd.Case_Approve ,
CD.Case_Creation_Date, ISNULL(cd.Case_Reminder, 0) , DCCC.Booked_Percent, DCCC.Departure_Detail_Capture_Equipment_Id_fk ,
dd.Departure_Detail_Departure_OriginalTime , CD.NegativeAuth , CSS.Case_Status, Crs.Case_Reason
FROM Case_Details(nolock) CD JOIN PO_CUR_CAP_DEPARTURES(nolock) D
ON CD.Case_Departure_Id_fk =d.Departure_Id
JOIN Routes (NOLOCK)
ON Routes.Route_Id =D.Departure_RouteId_fk
AND Routes.Route_CityPair_fk =CD.Case_CityPair_Id_fk
JOIN PO_CUR_CAP_DEP_DETAILS(NOLOCK)DD
ON D.Departure_Id = DD.Departure_Detail_Departure_Id_fk
AND Departure_Detail_Leg_Seg_Type = 'Leg'
JOIN PO_CUR_CAP_DEP_DET_CAPTURES (NOLOCK) DCCC
ON DCCC.Departure_Detail_Capture_Depature_Details_Id_fk= DD.Departure_Detail_Id
JOIN City_Pairs cp (NOLOCK)
ON cd.Case_CityPair_Id_fk= CP.CityPair_Id
JOIN USERS (nolock)
ON USERS.User_Count_Id= Cd.Case_Primary_Analyst_Id_fk
AND USERS.STATUS = 1
LEFT JOIN Case_Status_Summary CSS
ON css.Case_Id_Fk= cd.Case_Id
LEFT JOIN Case_Reasons_Summary CrS
ON CrS.Case_Id_Fk= cd.Case_Id
LEFT JOIN Revenue (nolock) Rev
on Rev.Revenue_Id = cd.Case_Revenue_Id_fk
LEFTJOIN Departure_Followups Df
ON CD.Case_Id = Df.Case_Id_fk
where CD.CaptureDate = @CurCaptureDate
AND CD.IS_MassAdjustment IN(0,2)
AND (Case_Primary_Analyst_Id_fk = ISNULL(@user_id,Case_Primary_Analyst_Id_fk) OR Case_Secondary_Analyst_ID_fk= ISNULL(@user_id,Case_Secondary_Analyst_ID_fk))
AND CD.Case_CityPair_Id_fk = ISNULL(@CityPair_ID,CD.Case_CityPair_Id_fk)
AND D.Departure_Code = ISNULL(@Departure_Code,D.Departure_Code)
AND CD.Case_Approve = ISNULL(@casesApproved,CD.Case_Approve)
AND D.Departure_OriginalDate Between ISNULL(@Departure_Date,'2000-01-01') AND ISNULL(@Departure_Date_To ,'2099-12-31')
AND DCCC.Departure_Detail_Capture_Days_Left = ISNULL(@Days_Left,DCCC.Departure_Detail_Capture_Days_Left)
AND Crs.Case_Reason = ISNULL(@Reasons,Crs.Case_Reason)
AND Users.Last_Name = ISNULL(@AnalystName,Users.Last_Name)
AND CD.IS_MassAdjustment = ISNULL(@massAdj,CD.IS_MassAdjustment)
AND Rev.Recommended_Revenue_Gain >=(SELECT Threshold_Revenue FROM SYSTEM_DEFAULT_SETTINGS(NOLOCK))
where @reasons and @departure_code can be multiple.. can you please help me write a good query for this.
July 1, 2015 at 11:24 pm
Please if anyone can help me optimizing this where clause where i need not use these multiple isnull checks
July 2, 2015 at 1:49 am
This is one of the few cases where dynamic SQL should be considered.
Take a look at this [/url]article on the subject, particularly the 'dynamic SQL' bit.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply