x

Filtering data based on criteria.

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!
more ▼

asked Aug 31, 2012 at 01:45 PM in Default

a_car11 gravatar image

a_car11
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I think you might need to re-read your question and possibly make some changes. As it is, it is a little ambiguous in what you are asking.

Do you mean that you want to move a few rows from a table with many into a different table, or that you want to move all rows from a table into another?

Recordsets (the results of a SELECT) are reduced in a number of ways. The main method is by specifying values that certain columns have to contain in order to be included. This is done with a WHERE clause....

SELECT Column1
FROM myTABLE
WHERE Column1 = 'SQL Server'

Here, any records in the table myTable will be returned providing they have are storing the value 'SQL Server'.

If the table holds 100 records but only 3 have the value we want then only 3 records will be in our recordset. This means there will only be 3 rows to insert into the other table.

If, however, you want to move all 100 records from the table to the new location then you wont want to do that. you would need to remove the WHERE so that you get all the records. In that case, you then need to control how the Column1 value is transferred.

SELECT 
CASE WHEN Column1 = 'SQL Server' THEN 'SQL Server'
     ELSE 'Not SQL Server'
     END as ColVal
FROM myTABLE
Here, the value in Column1 is being tested for every record in myTABLE and where it is 'SQL Server' then 'SQL Server' is the value in our recordset. If it isn't then the recordset contains 'Not SQL Server'. If this query is used to INSERT data into a different table then the records put into that table will have 'SQL Server' or 'Not SQL Server' in that column.
more ▼

answered Aug 31, 2012 at 03:32 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x109
x57
x19
x16

asked: Aug 31, 2012 at 01:45 PM

Seen: 885 times

Last Updated: Aug 31, 2012 at 03:32 PM