SQL query with 2 nested to queries on same table

Question

This question is an extension capability related to my previous question here Update column with autonumber. Now with only one table this time:

Date        Adds
6/1/18      0
6/5/18      1
6/7/18      0
...+60 records
10/1/18     0

I would like to create a table of Dates, 60 date records (for ex) beyond the Date with a number in the New in Field. Using the previous method, here is what I have:

Select t1.adds,  t1.date from T1 where t1.adds > 0 AND
      (select count(*)+1 from t1 as t2 
      where t2.Date <= t1.Date AND t2.date >= 
               (select date from t1 as t3 where t3.date > t2.date) = 60)

I think everything would work except for the 2nd conditional statement where I need the date to be greater than the corresponding date where Adds > 0. If executed I would expect my table to look like:

Date       Adds
10/1/18    1

I'm thinking I may need to join the table to itself somehow to get a count of 60 where date>6/5/18 AND date<=10/1/18. Just not sure how. — farmpapa, May 29 '18 at 19:16
New is just for "new" addition. Some dates will have multiple "new" additions. It doesn't mean a "new" record, just something added to the inventory — farmpapa, May 29 '18 at 19:28

score 0 · Accepted Answer · answered May 29 '18 at 20:48

I think this works but unsure how efficient it is yet. I just made a tbltemp with Adds and Date where Adds > 0

SELECT q1.adds, t1.Date
FROM T1, tblTemp AS q1
WHERE (select count(*) from T1 as t2 where t2.date <= t1.date AND t2.date > q1.date)=60

I will do a little more testing with more records unless anyone has any better ideas?

SQL query with 2 nested to queries on same table

1 Answers1