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

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: 1004392 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)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

"DATEADD(mm,-4,GETDATE())" would be preferable because that way, at.ass_stopdate will still be sargeble and it's index (?) might be used

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

sqlpro Hall of Fame Points: 3859 More actions · Answer 4

Thank u very much wz700 and Jeff for ur posts.

i finally decided to use Jeff's suggestion.

You guys r very helpful