help needed writing this logic

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

General SQL Server Forums

New to SQL Server Programming

help needed writing this logic

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-02-25 : 05:57:27

Hi,

The following are my columns

SubmitterId int
TimeatFileReceived DataTime
FileID char(10)
Filetype char(20)

So i need to get the submitterId where the Submitter is not sending file for "N" number days. N will be an parameter

how do we get those details here? pelase give me sample query

raky
Aged Yak Warrior

767 Posts

Posted - 2010-02-25 : 06:10:45

Is this is what you want ?


SELECT
 SubmitterId 
FROM tbl
WHERE TimeatFileReceived NOT BETWEEN DATEADD(D,-@N,GETDATE()) AND GETDATE()

Note: @N is the parameter representing the Number of days

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-02-25 : 06:13:55

hi raky,

Thanks for the reply. could you please give me bit exp on what the following statement does

BETWEEN DATEADD(D,-@N,GETDATE()) AND GETDATE().

thanks in advance.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-25 : 06:14:59

It can be rewrite like the below

SELECT
SubmitterId
FROM tbl
WHERE DATEDIFF(D, TimeatFileReceived, GETDATE()) < @N

Vabhav T

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-25 : 06:16:48

Sorry that should be like

SELECT
SubmitterId
FROM tbl
WHERE DATEDIFF(D, TimeatFileReceived, GETDATE()) > @N

Vabhav T

raky
Aged Yak Warrior

767 Posts

Posted - 2010-02-25 : 06:18:57

quote:
Originally posted by vaibhavktiwari83

It can be rewrite like the below

SELECT
SubmitterId
FROM tbl
WHERE DATEDIFF(D, TimeatFileReceived, GETDATE()) < @N

Vabhav T

It think he want this

WHERE DATEDIFF(D, TimeatFileReceived, GETDATE()) > @N

raky
Aged Yak Warrior

767 Posts

Posted - 2010-02-25 : 06:19:42

hmmmm page not refreshed..

raky
Aged Yak Warrior

767 Posts

Posted - 2010-02-25 : 06:21:59

quote:
Originally posted by sqllover

hi raky,

Thanks for the reply. could you please give me bit exp on what the following statement does

BETWEEN DATEADD(D,-@N,GETDATE()) AND GETDATE().

thanks in advance.

TimeatFileReceived NOT BETWEEN DATEADD(D,-@N,GETDATE()) AND GETDATE()
from this statement what i mean is

the number of days difference between TimeatFileReceived and current date should be greater than @n (Parameter value )

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-02-25 : 06:26:14

Thanks guys for the help

raky
Aged Yak Warrior

767 Posts

Posted - 2010-02-25 : 06:29:41

quote:
Originally posted by sqllover

Thanks guys for the help

Welcome...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 09:19:00

quote:
Originally posted by vaibhavktiwari83

Sorry that should be like

SELECT
SubmitterId
FROM tbl
WHERE DATEDIFF(D, TimeatFileReceived, GETDATE()) > @N

Vabhav T

this wont use an existing index on TimeatFileReceived column. better to use


WHERE TimeatFileReceived< DATEADD(d,DATEADD(d,0,GETDATE())-@N,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources