last 4 months data

Question

Post reply

last 4 months data

sqlpro

Hall of Fame

Points: 3859
More actions
May 2, 2004 at 8:31 pm

#108173

Hi friends

I've 2 tables Task and Assignments .task is PK table assignment is fk table

example data:

task table data

taskid,status,complete,buildver

416 80 1 6.57a

1024 70 1 6.64b

1124 60 1

this assignment table data

fk_staffid,fk_taskid,ass_startdate,ass_stopdate

NITIN 416 2003-05-01 00:00:00.000 NULL

CHRIS 1024 2003-11-10 00:00:00.000 NULL

rk 1124 2003-12-12 23:49:00.000 2003-12-15 13:25:00.000

how can i write query which returns data

Where task was completed > 4 months ago (there is field complete bit in task table if it .t. means task completed else not)

and assignment table assignment has assignment stop date(ass_stopdate)

Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

wz700 SSCertifiable Points: 7133 More actions · Answer 1

wz700

SSCertifiable

Points: 7133

May 3, 2004 at 5:16 am

#504958

select taskid from task a

inner join assigment b on a.taskid=b.fk_taskid

where a.status = 1 and datediff(mm, b.ass_stopdate, getdate())>4

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

Haven't tested this but should work just fine. Notice how DATEADD was used to subtract 4 months from the current date... not a job for DATEDIFF as many would think...

SELECT at.*
FROM AssignmentTable at
INNER JOIN TaskTable tt
ON at.TaskID = tt.TaskID
WHERE tt.Complete = 1
AND at.Ass_StopDate < DATEADD(mm,-4,GETDATE())

--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)
Intro to Tally Tables and Functions