SQL Server 2008 Query: Multiple column values

Question

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

mohan111 · Accepted Answer · 2015-07-15 16:16:59Z

2

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()) )

answered Jul 15, 2015 at 16:16

mohan111

8,8854 gold badges35 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

visevo Over a year ago

I'll try that again - last time I tried it the server locked up. Maybe too much data?

mohan111 Over a year ago

it's a simple query but may be the table structure and data we don't assume.more over you have using left join and Like so it will definitely consume lot of time @visevo

visevo Over a year ago

gotcha. is there a more efficient way to run this query? @mohan111

Collectives™ on Stack Overflow

SQL Server 2008 Query: Multiple column values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related