Select query - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Select query

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2006-07-26 : 11:09:56

Can you please help me under select in where condition at this line:
And isdate(A.DueDate) = 1

i want to get record which has closest A.duedate against getdate() and also which are overdue records too.

if the duedate of a record is 072206, which is over due. i want to get those and also if a record has a A.duedate which is close to getdate().

for example: if there are three record:

1st record has duedate: 072006
2nd record has duedate: 072706
3rd record has duedate: 073106

So 1st and 2nd record's to be fetched, 1st record is overdue and the 2nd record is close to todays date(getdate())


SELECT S.SUNumber, S.SUTitle, A.Description, S.PDSupervisor, S.Suid, 
FROM TAB_ccsNetSU AS S INNER JOIN TAB_ccsNetActions A ON S.SUID = A.ModuleRecordID 
WHERE A.ModuleName = 'SU'
AND isdate(A.PDToContractorDate) = 0
And isdate(A.DueDate) = 1
GROUP BY S.SUNumber, S.SUTitle, S.PDSupervisor, S.SUID, A.description

Thank you very much for the information.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-26 : 11:42:18

Is the DueDate a datetime?
something like

And A.DueDate > getdate() - 2

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

reddymade
Posting Yak Master

165 Posts

Posted - 2006-07-26 : 12:08:36

Hello Nr: Duedate is a date field
But what does this do: And A.DueDate > getdate() - 2

It seems like, it is fixed for 2 days going back, i want to show all record which are overdue and also the nearest future duedate record too.
the resultset could get any number of records.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-26 : 12:18:41

>> duedate which is close to getdate() ?
>> overdue and also the nearest future duedate record too.

What is "close to" mean ?
What is Overdue ?

In the following context :
>> the resultset could get any number of records.

Srinika

reddymade
Posting Yak Master

165 Posts

Posted - 2006-07-26 : 12:28:06

Hello Srini,

for Example I have 5 records in the table there duedates are:

1.) 073106
2.) 072906
3.) 072506
4.) 080206
4.) 081406

from the above records one is overdue which is 072506

and the next closest due record is 072906

and rest all records has little far duedate which are not close to getdate()

Please let me know. if i am not clear.
Thank you very much Srini.

quote:
Originally posted by Srinika

>> duedate which is close to getdate() ?
>> overdue and also the nearest future duedate record too.

What is "close to" mean ?
What is Overdue ?

In the following context :
>> the resultset could get any number of records.

Srinika

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-26 : 12:30:20

And A.DueDate < getdate() + 1

That will give everything that is overdue or within 1 day of being overdue.

SELECT S.SUNumber, S.SUTitle, A.Description, S.PDSupervisor, S.Suid,
FROM TAB_ccsNetSU AS S INNER JOIN TAB_ccsNetActions A ON S.SUID = A.ModuleRecordID
WHERE A.ModuleName = 'SU'
AND isdate(A.PDToContractorDate) = 0
And A.DueDate < getdate()
GROUP BY S.SUNumber, S.SUTitle, S.PDSupervisor, S.SUID, A.description

That will give everything overdue
(suspect your isdate(A.PDToContractorDate) = 0 is also incorrect).
To get the next one that will be overdue is not so simple

SELECT S.SUNumber, S.SUTitle, A.Description, S.PDSupervisor, S.Suid,
FROM TAB_ccsNetSU AS S INNER JOIN TAB_ccsNetActions A ON S.SUID = A.ModuleRecordID
WHERE A.ModuleName = 'SU'
AND isdate(A.PDToContractorDate) = 0
And A.DueDate <= (select min(DueDate) TAB_ccsNetActions A2 where A2.ModuleRecordID = A.ModuleRecordID and A2.ModuleName = 'SU' AND isdate(A.PDToContractorDate) = 0
and DueDate >= getdate())
GROUP BY S.SUNumber, S.SUTitle, S.PDSupervisor, S.SUID, A.description

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources