CODEWITHSUNDEEP
sql-server
visevo
visevo

Reputation: 861

SQL Server 2008 Query: Multiple column values

I have a query where I need to select all values where project_status_id = 2 OR project_ship_date has a date in the past year. Not really sure how this is accomplished.

Here's my SQL:

SELECT DISTINCT 
    p.id, p.name, CONVERT(VARCHAR(12), s.ship_date, 109) AS ship_date, 
    sp.name as species, p.quoted_sf
FROM 
    ProjectTracking.dbo.projects AS p
LEFT JOIN 
    ProjectTracking.dbo.shipments AS s ON p.id = s.project_id
LEFT JOIN 
    ProjectTracking.dbo.segments AS seg ON p.id = seg.project_id
LEFT JOIN 
    ProjectTracking.dbo.species AS sp ON seg.specie_id = sp.id
WHERE 
    sp.name LIKE '%maple%' 
    AND (p.project_status_id = 2 OR s.ship_date > "last year?")

Note: p.project_status_id has possible values of 1,2,10,11

Note #2: If p.project_status_id != 2 then s.ship_date must be specified

Upvotes: 0

Views: 24

Answers (1)

mohan111
mohan111

Reputation: 8865

SELECT DISTINCT p.id, p.name, CONVERT(VARCHAR(12),s.ship_date,109) AS ship_date, sp.name as species, p.quoted_sf
FROM ProjectTracking.dbo.projects AS p
LEFT JOIN ProjectTracking.dbo.shipments AS s ON p.id = s.project_id
LEFT JOIN ProjectTracking.dbo.segments AS seg ON p.id = seg.project_id
LEFT JOIN ProjectTracking.dbo.species AS sp ON seg.specie_id = sp.id
WHERE sp.name LIKE '%maple%' AND
(p.project_status_id IN (1,2,10,11) OR s.ship_date > DATEADD(year,-1,GETDATE()) )

Upvotes: 2

Related Questions