question

a_car11 avatar image
a_car11 asked

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!
sql serverselectunioncase
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Fatherjack avatar image
Fatherjack answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.