SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Filtering data based on criteria.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

acar11
Starting Member

7 Posts

Posted - 08/31/2012 :  09:55:51  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 08/31/2012 :  10:48:37  Show Profile  Reply with Quote
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,
....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/31/2012 :  11:28:51  Show Profile  Reply with Quote
does date_entered , entered_by etc have timepart?


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

Go to Top of Page

acar11
Starting Member

7 Posts

Posted - 08/31/2012 :  11:32:48  Show Profile  Reply with Quote
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
Go to Top of Page

acar11
Starting Member

7 Posts

Posted - 08/31/2012 :  11:42:29  Show Profile  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/31/2012 :  11:45:03  Show Profile  Reply with Quote
you didnt answer me yet

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

Go to Top of Page

acar11
Starting Member

7 Posts

Posted - 08/31/2012 :  11:51:25  Show Profile  Reply with Quote
Sorry, yes.

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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/31/2012 :  11:54:05  Show Profile  Reply with Quote
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/

Go to Top of Page

acar11
Starting Member

7 Posts

Posted - 08/31/2012 :  13:00:22  Show Profile  Reply with Quote
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!
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 08/31/2012 :  13:51:15  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/31/2012 :  15:01:44  Show Profile  Reply with Quote
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/

Go to Top of Page

Jared Drake
Starting Member

4 Posts

Posted - 09/01/2012 :  22:09:57  Show Profile  Reply with Quote
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
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
798 Posts

Posted - 09/02/2012 :  10:54:36  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/02/2012 :  12:17:55  Show Profile  Reply with Quote
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/

Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
798 Posts

Posted - 09/02/2012 :  13:57:38  Show Profile  Reply with Quote
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. ;)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/02/2012 :  15:09:47  Show Profile  Reply with Quote
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/

Go to Top of Page

acar11
Starting Member

7 Posts

Posted - 09/05/2012 :  07:52:39  Show Profile  Reply with Quote
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
Go to Top of Page

acar11
Starting Member

7 Posts

Posted - 09/05/2012 :  08:44:47  Show Profile  Reply with Quote
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'

Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/05/2012 :  09:38:32  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000