Filtering data based on criteria.

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)

Filtering data based on criteria.

Author

Topic

acar11
Starting Member

7 Posts

Posted - 2012-08-31 : 09:55:51

I am trying to do a select before inserting the results into another table, but I need to set some rules on the SQL select, which is to only select the "date_entered" if the date is greater or equal to today's date minus 180 days, also the "entered_by" only gets selected if it is greater or equal to today's date minus 180 days, here is what I have so far and I am wondering if this should be good enough or if there is a better way of doing this:

select account, email as personal_email,

case when date_entered >= getdate() - 180 then convert(varchar(10), date_entered, 101) else ' ' end as date_entered,

case when date_entered >= getdate() - 180 then entered_by else ' ' end as entered_by

from my_table where account = '12345'

Thanks for looking!!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-31 : 10:48:37

What you have posted seems fine to me - if I were writing it from scratch, that is probably what I would write as well.

The only comment I would have is about the date_entered column. I usually prefer to send the data with the correct data type - i.e., instead of converting it to varchar(10), I would send it as datetime, and a null for those rows where I don't want to send the date_entered .


....
case when date_entered >= getdate() - 180 then date_entered else end as date_entered,
....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 11:28:51

does date_entered , entered_by etc have timepart?

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

acar11
Starting Member

7 Posts

Posted - 2012-08-31 : 11:32:48

OK, but if a change this line which date_entered doesnt show on the query results:
...
case when date_entered >= getdate() - 180 then convert(varchar(10), date_entered, 101) else ' ' end as date_entered,
...

with:
...
case when date_entered >= getdate() - 180 then date_entered else '' end as date_entered,
....

the date_entered shows on the result

acar11
Starting Member

7 Posts

Posted - 2012-08-31 : 11:42:29

I tested the query with some changes based in what I am reading here and the results were correct, here is the new one, do you see any advantages over the original one?

select account, email as personal_email,

case when date_entered >= getdate() - 180 then ' ' end as date_entered,

case when date_entered >= getdate() - 180 then ' ' end as entered_by

from my_table where account = '12345'

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 11:45:03

you didnt answer me yet

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

acar11
Starting Member

7 Posts

Posted - 2012-08-31 : 11:51:25

Sorry, yes.

date_entered:
2011-11-23 14:14:55.0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 11:54:05

then to consider full day the logic should be tweaked as


select account, email as personal_email,

case when date_entered >= dateadd(dd,datediff(dd,0,getdate()), - 180) then ' ' end as date_entered,

case when date_entered >= dateadd(dd,datediff(dd,0,getdate()), - 180) then ' ' end as entered_by

from my_table where account = '12345'

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

acar11
Starting Member

7 Posts

Posted - 2012-08-31 : 13:00:22

I liked it, I guess there is no need to use "convert". Need to read more on this:
"dateadd(dd,datediff(dd,0,getdate()),"

I am thankful for all the great info!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-31 : 13:51:15

It is a way of removing the time part from a datetime value that has date and time in it. There are a few different ways to do that - see Madhivanan's blog here: http://beyondrelational.com/modules/2/blogs/70/posts/17535/different-ways-to-remove-time-part-from-datetime-values.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 15:01:44

quote:
Originally posted by acar11

I liked it, I guess there is no need to use "convert". Need to read more on this:
"dateadd(dd,datediff(dd,0,getdate()),"

I am thankful for all the great info!

see basis for logic here

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

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

Jared Drake
Starting Member

4 Posts

Posted - 2012-09-01 : 22:09:57

Using convert is actually appropriate here... visakh16++

I am a writer for the http://www.afterhoursprogramming.com/index.php?article=167 (SQL section) on afterhoursprogramming.com

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-09-02 : 10:54:36

quote:
Originally posted by Jared Drake

Using convert is actually appropriate here... visakh16++

I am a writer for the http://www.afterhoursprogramming.com/index.php?article=167 (SQL section) on afterhoursprogramming.com

Why is convert appropriate? Convert will return a character string representing a date - which will not allow any other processes to use that column as a datetime column. If binding to a report - for example an SSRS report - all date functions in SSRS would be useless for this column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-02 : 12:17:55

quote:
Originally posted by jeffw8713

quote:
Originally posted by Jared Drake

Using convert is actually appropriate here... visakh16++

I am a writer for the http://www.afterhoursprogramming.com/index.php?article=167 (SQL section) on afterhoursprogramming.com

Why is convert appropriate? Convert will return a character string representing a date - which will not allow any other processes to use that column as a datetime column. If binding to a report - for example an SSRS report - all date functions in SSRS would be useless for this column.

yep...exactly which I've experienced as well! (I've been ssrs developer for quite a while)
Which is why I always prefer dateadd datediff logics over convert to varchar method

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

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-09-02 : 13:57:38

quote:
Originally posted by visakh16
yep...exactly which I've experienced as well! (I've been ssrs developer for quite a while)
Which is why I always prefer dateadd datediff logics over convert to varchar method

Which invariably leads to the question - how do I format my 'date' column in SSRS as 'some other format' because the standard formatting doesn't work. ;)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-02 : 15:09:47

quote:
Originally posted by jeffw8713

quote:
Originally posted by visakh16
yep...exactly which I've experienced as well! (I've been ssrs developer for quite a while)
Which is why I always prefer dateadd datediff logics over convert to varchar method

Which invariably leads to the question - how do I format my 'date' column in SSRS as 'some other format' because the standard formatting doesn't work. ;)

yep..but thats where we make use of standard formats available inside SSRS cell properties. In case requirement is a kind of non standard one we can make use of SSRS functions like Format(),FormatDateTime()

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

acar11
Starting Member

7 Posts

Posted - 2012-09-05 : 07:52:39

Tried this code but it doesn't works, it doesn't show anything, it ignore the days less 180 days:

select account, email as personal_email,

case when date_entered >= dateadd(dd,datediff(dd,0,getdate()), - 180) then ' ' end as date_entered,

case when date_entered >= dateadd(dd,datediff(dd,0,getdate()), - 180) then ' ' end as entered_by

from my_table where account = '123456'

But this one works, why?

select account, email as personal_email,

case when date_entered >= getdate() - 180 then convert(varchar(10), date_entered, 101) else ' ' end as date_entered,

case when date_entered >= getdate() - 180 then entered_by else ' ' end as entered_by

from my_table where account = '12345'

Thanks

acar11
Starting Member

7 Posts

Posted - 2012-09-05 : 08:44:47

Shouldn't it be like:

select account, email as personal_email,

case when date_entered >= dateadd(dd,datediff(dd,0,getdate()), - 180) then convert(varchar(10),date_entered, 101) else ' ' end as date_entered,

case when date_entered >= dateadd(dd,datediff(dd,0,getdate()), - 180) then entered_by else ' ' end as entered_by

from my_table where account = '123456'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-05 : 09:38:32

quote:
Originally posted by acar11

Shouldn't it be like:

select account, email as personal_email,

case when date_entered >= dateadd(dd,datediff(dd,0,getdate()), - 180) then convert(varchar(10),date_entered, 101) else ' ' end as date_entered,

case when date_entered >= dateadd(dd,datediff(dd,0,getdate()), - 180) then entered_by else ' ' end as entered_by

from my_table where account = '123456'

Yes, I would think so. In the previous post where you indicated that it was not working, you did not have an else clause at all, so it was picking only part of the date range.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources