CODEWITHSUNDEEP
sql-server
bl1234
bl1234

Reputation: 163

how to select columns from same table with different where clause criteria

I have a table which is similar to the design below:

Id   Code     Name    short name     Date1       date2       market
1    12A      AAAA1    a1            2012-03-27  2012-03-27  90
2    13A      AAAA2    a2            2012-03-28  2012-03-28  90
3    14A      AAAA3    a3            NULL        NULL        32
4    15A      AAAA4    a4            NULL        NULL        32

I have to build a query such that when I select a code, the codes with market 90, the select should be such a way that the Date1>system date and Date2> Date1 and for other market no criteria is applicable on selection.

I have built the query so far, but I don’t know how to specify the where clause when the market is 90.

SELECT A.Id, B.Name [Name], B.Code [Code], Date1 [Date1], Date2 [Date]2, C.Code[Market]
FROM dbo.fn_idlisting(@System_Date) A 
INNER JOIN code B ON A.Id = B.Id 
LEFT OUTER JOIN Market C ON B.Market_Id = C.Market_Id 
WHERE (Date1 <= @systemDate) AND (Date2 >= Date1)

I feel difficult on how to select other codes with market 32 without any where clause.

Upvotes: 0

Views: 63

Answers (3)

Almas Mahfooz
Almas Mahfooz

Reputation: 1086

What I have understand from your question is your table name is fn_idlisting and you have only one table. Try below.

Note: I have not used equal to as you only mention > and < for date1 and date2, you can use it if wants to change your date range criteria.

select * from fn_idlisting 
where market=90 and date1>cast(SYSDATETIME() as DATE)
and date2>date1
union
select * from fn_idlisting 
where market<>90

Upvotes: 0

JohnS
JohnS

Reputation: 2052

Use a CASE statement to include your Market = 90 logic as follows:

SELECT
  A.Id,
  B.Name [Name],
  B.Code [Code],
  Date1 [Date1],
  Date2 [Date2],
  C.Code [Market]
FROM
  dbo.fn_idlisting(@System_Date) A
  INNER JOIN code B
    ON A.Id = B.Id
  LEFT OUTER JOIN Market C
    ON B.Market_Id = C.Market_Id
WHERE
  Date1 <= CASE WHEN C.Code = 90 THEN @systemDate ELSE Date1 END 
  AND 
  Date2 >= CASE WHEN C.Code = 90 THEN Date1 ELSE Date2 END

Upvotes: 0

Azar
Azar

Reputation: 1867

Can you try this way 

SELECT A.Id, B.Name [Name], B.Code [Code], Date1 [Date1], Date2 [Date]2, C.Code[Market] 
    FROM dbo.fn_idlisting(@System_Date) A 
    INNER JOIN code B ON A.Id = B.Id
    LEFT OUTER JOIN Market C ON B.Market_Id = C.Market_Id 
    WHERE (Date1 > @System_Date and Date2 >Date1 or c.Code != 90)

Upvotes: 1

Related Questions