get records back between a date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

get records back between a date

Author

Topic

tech_1
Posting Yak Master

129 Posts

Posted - 2010-10-25 : 05:14:39

This is a tricky one. well for me.

I have 2 main tables. one has a criticalDate field, the other has a noteCreatedOn field.

a record can contain more than 1 notes.

I want to find all records so we can flag them as "archive".

the criteria is that:

get all records where the criticalDate has been more than 4 months from the current Date AND no note has been created for that record for over the past month from the present Date.

SELECT t1.field1, t1.field2, t1.criticalDate, t2.field1, t2.noteCreatedON

FROM tableA t1
INNER JOIN tableB t2 ON
t2.tableAID = t1.AID

WHERE DATEDIFF(mm, t1.criticalDate, GETDATE()) >= 4
AND DATEDIFF(mm, t2.noteCreatedOn, GETDATE()) > 1

this works without doing the join on the notes table (t2)

but otherwise, it still brings back records that the critical date is say, next month from today (so november 2010)

any ideas?

tech_1
Posting Yak Master

129 Posts

Posted - 2010-10-25 : 05:18:25

ignore. it was a mistake on my part. I was also doing a string condition match and didnt include that in brackets.

SELECT t1.field1, t1.field2, t1.criticalDate, t2.field1, t2.noteCreatedON

FROM tableA t1
INNER JOIN tableB t2 ON
t2.tableAID = t1.AID

WHERE t1.fieldA LIKE '%...%' OR t1.fieldA = '...'

AND DATEDIFF(mm, t1.criticalDate, GETDATE()) >= 4
AND DATEDIFF(mm, t2.noteCreatedOn, GETDATE()) > 1

the WHERE clause and the AND clause should have been in brackets

tech_1
Posting Yak Master

129 Posts

Posted - 2010-10-25 : 05:34:21

follow up question:

how do we get back just the unique record ID's (lets say t1.fieldID) rather than bringing back the records which are joined to another table which contain different notes?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources