October 30, 2014 at 1:49 pm
I have a table (we will cal DateTable) with several (20) columns, each being a date type. Another table's (Project) PK is referenced in the DateTable.
I am trying to write a query that will pull all dates for a specific project from the DateTable if they meet certain criteria(i.e. if the date is <= 7 days from now.
My brain may just be fried but I started with a normal select statement selecting each column with a join to the project and then a where clause using
(DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()) OR (DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE())) ... the rest of the columns(all with OR between them).
The problem with this is that because I am using OR once one of the dates meets the criteria it selects all the dates that are associated with the project. I ONLY want the dates that meet the criteria and don't care about the rest. Obviously because I have all the columns in the select statement...
So I need something like
Select ALL Columns
from DateTable d
Join Project p
where p.ProjectID = d.ProjectID AND only dates BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()))
Why am I making this harder than it has to be......... :unsure:
November 3, 2014 at 12:27 pm
I think this is the general structure you need:
SELECT p.*, d_outer.date
FROM Project p
INNER JOIN Date d ON
p.ProjectID = d.ProjectID
CROSS APPLY (
VALUES(d.Date1),(d.Date2),(D.Date3) /*,...,D.Date20*/
) AS d_outer (Date)
WHERE
d_outer.Date BETWEEN GETDATE() AND DATEADD(DAY, 7, GETDATE())
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 3, 2014 at 1:21 pm
perfect this is what I needed! I will need to modify a bit to get the naming correct but this is definitely the direction I needed.
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply