use query results for another query sql

Question

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.

Sample Data:

Sample TestDate Column:

2015-05-13 13:45:14.000  
2015-05-15 07:33:13.000  
2015-05-18 06:07:11.000  
2015-05-19 02:58:13.000  
2015-05-22 14:08:42.000  
2015-05-26 11:01:29.000  
2015-05-26 11:01:50.000  
2015-05-27 07:19:32.000  
2015-05-15 08:04:28.000  
2015-05-15 10:32:23.000  
2015-05-22 14:11:26.000  
2015-05-27 07:16:57.000  
2015-05-29 08:50:36.000  
2015-05-15 10:38:23.000  
2015-05-19 03:08:53.000  
2015-05-27 13:41:47.000  
2015-05-29 08:47:56.000  
2015-05-15 07:50:04.000  
2015-05-18 06:20:28.000  
2015-05-19 06:32:24.000  
2015-05-26 11:00:58.000  
2015-05-22 14:12:15.000  
2015-05-26 10:57:17.000

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')

EDIT:

So from the table I added above, I would want data from 2015-05-15 - 2015-05-29 (ie the results from the query), but not from the data on date 2015-05-13, since data from the 13 th is the 8 th day.

What is SQL Server 9? Most people reading this question will have no idea what you are talking about. I find your logic hard to follow. Sample data and desired results can really help make a question more clear. — Gordon Linoff, Jun 08 '15 at 11:59
Use Limit not set. Set RowCount does not do what you think it does. It's an instruction to whatever is displaying the the result of the query. — Tony Hopkinson, Jun 08 '15 at 11:59
`SET ROWCOUNT` isn't a SQL statement. If you want the first seven rows, use `TOP 7`. What you are asking though, is how to write subqueries - a SQL tutorial would be best for that — Panagiotis Kanavos, Jun 08 '15 at 12:09

score 0 · Answer 1 · answered Jun 08 '15 at 12:56

0

This would give you the last 7 dates with data without having to do what you've done in your sample code:

SELECT DISTINCT TOP 7
        CAST([TestDate] AS DATE) DT
FROM    YourTable
ORDER BY CAST([TestDate] AS DATE) DESC

I've cast them to DATE to get the date portion.

You can use this to JOIN on to, which will restrict the output to rows with a matching date:

SELECT  *
FROM    YourTable t1
        INNER JOIN ( SELECT DISTINCT TOP 7
                            CAST(TestDate AS DATE) DT
                     FROM   YourTable
                     ORDER BY CAST(TestDate AS DATE) DESC
                   ) dts ON dts.DT = CAST(t1.TestDate AS DATE)

answered Jun 08 '15 at 12:56

Tanner

22,205
9
65
83

I am getting error "Type DATE is not a defined System type." I am using SQL Server Management studio and the "About" dialog says it is 2008 R2. The properties of the SQL server itself say that it is SQL Version 9.00.5000.00. According to this link, I should be able to use the DATE type: http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server – Kenny K. Jun 08 '15 at 13:14
Check the database compatibility level (Management Studio, right click on DB, properties dialog). Your database may have been created in an earlier version of SQL and locked in on that compatibility level – Stan Jun 08 '15 at 13:42
SELECT * FROM [TABLE] WHERE [TestDate] IN ( --Get the Distinct Dates SELECT DISTINCT TOP 7 (CONVERT(VARCHAR, CONVERT(DATETIME,[TestDate]),23)) AS DT FROM [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) This runs OK, but does not return results, because I assume the DT variable is returned by the subquery, and I need to convert it to the TestDate column? – Kenny K. Jun 08 '15 at 14:28

use query results for another query sql

1 Answers1