CODEWITHSUNDEEP
sql-servert-sqlconditional-statementswhere-clause
user3115933
user3115933

Reputation: 4443

How to write this WHERE filter in my T-SQL Query that will cater for 2 conditions to be met?

I am using SQL Server 2014 and I have a table called ResStayDate which lists all reservations made on a property by date. Here is an extract of the table:

 ResaID    StayDate      PkgPlanCode      RateAmt
 61200     2015-01-11      COGB              0.00
 61200     2015-01-11      G1A810          152.00
 61200     2015-01-11      G1A810          152.00
 61200     2015-01-11      G1A810          152.00
 63500     2015-02-04      R2AI            125.00
 63500     2015-02-05      R2AI            125.00
 73850     2015-05-10      COGB              0.00
 73850     2015-05-10      G2450           169.00
 68901     2015-05-15      COFR              0.00
 68901     2015-05-15      COFR              0.00

I want a query that will output all the ResaIDs (plus the remaining columns of that table) which contain at least one StayDate with the PkgPlanCode "CO..." AND its corresponding RateAmt = 0. If all StayDates of a particular ResaID have PkgPlanCodes starting with "CO..." and their corresponding RateAmt = 0,then these ResaIDs should be excluded in the output.

In other words, my output should look like this:

ResaID    StayDate      PkgPlanCode      RateAmt
 61200     2015-01-11      COGB              0.00
 61200     2015-01-11      G1A810          152.00
 61200     2015-01-11      G1A810          152.00
 61200     2015-01-11      G1A810          152.00
 73850     2015-05-10      COGB              0.00
 73850     2015-05-10      G2450           169.00

The query should exclude ResaID 63500 (since it does not contain any PkgPlanCode starting with "CO..." and having RateAmt = 0) and also exclude ResaID 68901 (as the latter has all of its StayDate with PkgPlanCode starting with "CO..." and RateAmt = 0)

My query stands as follows as at now (as I am stuck as to how to handle the condition in the query):

SELECT *

FROM ResStayDate

WHERE.......

Upvotes: 2

Views: 115

Answers (3)

Joel Coehoorn
Joel Coehoorn

Reputation: 415810

WITH COPkgs As 
(   -- IDs for reservations with a CO* Package and 0 rate
    SELECT DISTINCT ResaID FROM ResStayDate Where PkgPlanCode LIKE 'CO%' AND RateAmt = 0
),
NonCOPkgs As
(   -- IDs for reservartions with a non-CO package
    SELECT DISTINCT ResaID FROM ResStayDate Where PkgPlanCode NOT LIKE 'CO%'
)
-- Reservation records that match IDs from BOTH previous sets
SELECT r.*
FROM ResStayDate r
INNER JOIN COPkgs c ON c.ResaID = r.ResaID 
INNER JOIN NonCOPkgs n on n.ResaID = r.ResaID

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

We can use a couple of CTEs to assess the conditions required and then use windowed aggregates to determine if the condition is sometimes true across ResaID values:

declare @t table (ResaID int,StayDate date,PkgPlanCode varchar(17),RateAmt decimal(13,2))
insert into @t(ResaID,StayDate,PkgPlanCode,RateAmt) values
 (61200,'20150111','COGB'  ,  0.00),
 (61200,'20150111','G1A810',152.00),
 (61200,'20150111','G1A810',152.00),
 (61200,'20150111','G1A810',152.00),
 (63500,'20150204','R2AI'  ,125.00),
 (63500,'20150205','R2AI'  ,125.00),
 (73850,'20150510','COGB'  ,  0.00),
 (73850,'20150510','G2450' ,169.00),
 (68901,'20150515','COFR'  ,  0.00),
 (68901,'20150515','COFR'  ,  0.00)

 ;With Assessment as (
    select ResaID,StayDate,PkgPlanCode,RateAmt,
         CASE WHEN PkgPlanCode like 'CO%' and RateAmt=0.0 THEN 1 ELSE 0 END as Cond
    from @t
), Groups as (
    select *,
        MIN(Cond) OVER (PARTITION BY ResaID) as MinCond,
        MAX(Cond) OVER (PARTITION BY ResaID) as MaxCond
    from
        Assessment
)
select * from Groups where MinCond < MaxCond

Result:

ResaID      StayDate   PkgPlanCode       RateAmt    Cond        MinCond     MaxCond
----------- ---------- ----------------- ---------- ----------- ----------- -----------
61200       2015-01-11 COGB              0.00       1           0           1
61200       2015-01-11 G1A810            152.00     0           0           1
61200       2015-01-11 G1A810            152.00     0           0           1
61200       2015-01-11 G1A810            152.00     0           0           1
73850       2015-05-10 COGB              0.00       1           0           1
73850       2015-05-10 G2450             169.00     0           0           1

Upvotes: 2

Rahul
Rahul

Reputation: 77876

Make it a composite WHERE condition like

WHERE PkgPlanCode LIKE 'CO%'
AND RateAmt = 0.00

Upvotes: 0

Related Questions