Conditional Query help... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Conditional Query help...

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2009-02-16 : 08:41:32

I'm trying to return the result of Tbl1 ID base on the Tbl2 conditional. The code below return part of it, however if any existing conditional exists, I do no want it as result. Do I need to add If exist statement on the subquery?

thanks


SELECT   T1.ID,
         MAX(T2.REVIEWDATE)  AS RDATE,
         MAX(T2.REVIEW_BY)   AS RNAME,
         MAX(T2.SALE)        AS SALE
FROM     TBL1 T1
         LEFT JOIN TBL2 T2
           ON T1.ID = T2.ID
WHERE    T1.STATUS = 'V'
         AND (T2.MISSINGINFO = 0
               OR (T2.MISSINGINFO = 1
                   AND T2.INFOOVERRIDE = 1))
         AND (T2.BADPRICE = 0
               OR (T2.BADPRICE = 1
                   AND T2.BADPRICEOVERRIDE = 1))
GROUP BY T1.ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 08:53:07

can you elaborate your reqmnt with some sample data. your current explanation doesnt make much sense

hai
Yak Posting Veteran

84 Posts

Posted - 2009-02-16 : 09:24:48

Hi,

From the information below...ID 4 should not return, because it contain bad price, but the override not set.

thanks


CREATE TABLE TBL1 (ID INT IDENTITY ( 1,1 ), STATUS CHAR(1))

CREATE TABLE TBL2 (
  ID               INT,
  REVIEWDATE       DATETIME,
  REVIEW_BY        VARCHAR(25),
  SALE             MONEY,
  MISSINGINFO      BIT,
  BADPRICE         BIT,
  INFOVERRIDE      BIT,
  BADPRICEOVERRIDE BIT)

INSERT INTO TBL1 (STATUS)
SELECT 'V' UNION ALL
SELECT 'C' UNION ALL
SELECT 'V' UNION ALL
SELECT 'V'
       
INSERT INTO TBL2(ID,REVIEWDATE,REVIEW_BY,SALE,MISSINGINFO,BADPRICE,INFOVERRIDE,BADPRICEOVERRIDE)
select 1, '01/02/09', 'HT', 25,0,0,0,0 union all
select 1, '01/02/09', 'HT', 25,0,0,0,0 union all
select 1, '01/02/09', 'HT', 25,0,0,0,0 union all
select 2, '01/02/09', 'HT', 25,0,0,0,0 union all
select 3, '01/02/09', 'HT', 25,0,0,0,0 union all
select 3, '01/02/09', 'HT', 25,0,1,0,1 union all
select 4, '01/02/09', 'HT', 25,0,0,0,0 union all
select 4, '01/02/09', 'HT', 25,0,1,0,0 
       
SELECT   T1.ID,
         MAX(T2.REVIEWDATE)   AS RDATE,
         MAX(T2.REVIEW_BY)    AS RNAME,
         MAX(T2.SALE)         AS SALE
FROM     TBL1 T1
         LEFT JOIN TBL2 T2
           ON T1.ID = T2.ID
WHERE    T1.STATUS = 'V'
         AND (T2.MISSINGINFO = 0
               OR (T2.MISSINGINFO = 1
                   AND T2.INFOVERRIDE = 1))
         AND (T2.BADPRICE = 0
               OR (T2.BADPRICE = 1
                   AND T2.BADPRICEOVERRIDE = 1))
GROUP BY T1.ID
         
DROP TABLE TBL1
DROP TABLE TBL2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 09:51:21

did you mean this?


SELECT t1.ID,
MAX(t2.REVIEWDATE)  AS RDATE,
MAX(t2.REVIEW_BY)   AS RNAME,
MAX(t2.SALE)        AS SALE
FROM TBL1 t1
INNER JOIN TBL2 t2
ON t1.ID = t2.ID
WHERE t1.STATUS='V'
GROUP BY t1.ID
HAVING SUM(CASE WHEN BADPRICE=1 AND BADPRICEOVERRIDE=0 THEN 1 ELSE 0 END) =0

hai
Yak Posting Veteran

84 Posts

Posted - 2009-02-16 : 10:27:36

Hi visakh16,

You solution almost work, but this will not work if I can the ID 3 to one of the conditions. Now, only ID 1 should return.

thanks


INSERT INTO TBL2(ID,REVIEWDATE,REVIEW_BY,SALE,MISSINGINFO,BADPRICE,INFOVERRIDE,BADPRICEOVERRIDE)
select 1, '01/02/09', 'HT', 25,0,0,0,0 union all
select 1, '01/02/09', 'HT', 25,0,0,0,0 union all
select 1, '01/02/09', 'HT', 25,0,0,0,0 union all
select 2, '01/02/09', 'HT', 25,0,0,0,0 union all
select 3, '01/02/09', 'HT', 25,1,0,0,0 union all
select 3, '01/02/09', 'HT', 25,0,1,0,1 union all
select 4, '01/02/09', 'HT', 25,0,0,0,0 union all
select 4, '01/02/09', 'HT', 25,0,1,0,0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-16 : 11:13:16

[code]SELECT ID,
ReviewDate,
Review_By,
Sale,
MissingInfo,
BadPrice,
InfoVerride,
BadPriceOverride
FROM (
SELECT t2.ID,
t2.ReviewDate,
t2.Review_By,
t2.Sale,
t2.MissingInfo,
t2.BadPrice,
t2.InfoVerride,
t2.BadPriceOverride,
ROW_NUMBER() OVER (PARTITION BY t2.ID ORDER BY (t2.BadPrice ^ t2.BadPriceOverride) | (t2.MissingInfo ^ t2.InfoVerride) DESC) AS recID
FROM Tbl2 AS t2
INNER JOIN Tbl1 AS t1 ON t1.ID = t2.ID
AND t1.Status = 'V'
) AS d
WHERE recID = 1
AND (BadPrice ^ BadPriceOverride) | (MissingInfo ^ InfoVerride) = 0[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources