query help - is there a good solution to this?

Question

query help - is there a good solution to this?

SQL_NuB

Hall of Fame

Points: 3666
More actions
April 9, 2010 at 12:01 pm

#139742

Ok, here is what I'm running into
I have 2 tables that I have to use.
table 1 has 2 columns that I need
refDate, closedDate (taskName needs to be joined on table 2)
I need to calculate the difference between these 2 dates for each record, So I did this.
select datediff("d", refDate, closedDate) as DayDifference, Task from OpenRecords where [quarter] = 3
which works,
then once I get those totals I need to calculate them against table 2 data which has a field named
daysToComplete (taskName needs to match the task in table 1)
so it would be something like
DayDifference - daysToComplete = total
and then I need to count all of the records that have a total less then 0 (-1, -2, etc)
I tried a subquery, but it gave me an error message:
select
x.RefDate.
x.CloseDate
from (Select
RefDate = ISNULL((Select refDate from OpenRecords where quarter = 3 ),0),
CloseDate = ISNULL((Select closedDate from OpenRecords where quarter = 3),0)
from OpenRecords ) as x
and it gives me this error:
Msg 258, Level 15, State 1, Line 3
Cannot call methods on datetime.
how can I do what I'm trying to accomplish? Why can't I do the dates like I'm doing?

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please provide table definition, sample data and expected result in a ready to use format as described in the first link in my signature.

That would make it easier for us to test our solution.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

SQL_NuB (4/9/2010)
Ok, here is what I'm running into
I have 2 tables that I have to use.
table 1 has 2 columns that I need
refDate, closedDate (taskName needs to be joined on table 2)
I need to calculate the difference between these 2 dates for each record, So I did this.
select datediff("d", refDate, closedDate) as DayDifference, Task from OpenRecords where [quarter] = 3
which works,
then once I get those totals I need to calculate them against table 2 data which has a field named
daysToComplete (taskName needs to match the task in table 1)
so it would be something like
DayDifference - daysToComplete = total
and then I need to count all of the records that have a total less then 0 (-1, -2, etc)
I tried a subquery, but it gave me an error message:
select
x.RefDate.
x.CloseDate
from (Select
RefDate = ISNULL((Select refDate from OpenRecords where quarter = 3 ),0),
CloseDate = ISNULL((Select closedDate from OpenRecords where quarter = 3),0)
from OpenRecords ) as x
and it gives me this error:
Msg 258, Level 15, State 1, Line 3
Cannot call methods on datetime.
how can I do what I'm trying to accomplish? Why can't I do the dates like I'm doing?

Subqueries in a SELECT list must only return 1 value. You're code doesn't do that. Also, you don't need any subqueries according to the code your posted. Try this, instead...

SELECT ISNULL(refDate,0) AS RefDate,

ISNULL(closedDate,0) AS CloseDate

FROM dbo.OpenRecords

WHERE quarter = 3

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)