lundi 8 juin 2015

use query results for another query sql

I know that there are many topics discussing nested queries, however I am getting errors on my nested query due to the functions I am using. I am using SQL Server 9.

I am looking to query the last 7 DATES with data (may not be the last 7 days).

My query to return the last 7 Dates with data works well.

   -- Set the return record count to the last 7 days
    SET ROWCOUNT 7
--Get the Distinct Dates
  SELECT DISTINCT(CONVERT(VARCHAR, CONVERT(DATETIME,[TestDate]),23)) AS DT

  FROM [SERVER].[dbo].[TABLE]  
  --Get the last 60 days
  WHERE   [TestDate]  BETWEEN (Getdate() - 60) AND Getdate() 
  --Start at the current date and go backwards.
 ORDER BY DT DESC
 --  reset the return record count to prevent issues with further queries.
 SET ROWCOUNT 0

This provides the following result:

DT
2015-05-29
2015-05-27
2015-05-26
2015-05-22
2015-05-19
2015-05-18
2015-05-15

Now, I want to use those 7 entries to pull the data for those dates.

Usually I would do a

SELECT * WHERE [TestDate] >= '2015-05-29' AND [TestDate] <= '2015-05-30' 

for example (cumbersome I know).

A) I get errors with the SET function in a nested query.

B) How to make the proper WHERE statement. One option is to use the first and last result (2015-05-29 and 2015-05-15) from the query

(WHERE [TestDate] >= 'FIRST_RESULT' AND [TestDate] <= 'LAST_RESULT')

Aucun commentaire:

Enregistrer un commentaire