title: Multiple select queries using while loop in a single table? Is it Possible?
body: <p>I have 2 tables. Table A has Date, ISBN (for Book), Demand(demand for that date). Table B has Date, ISBN (for Book), and SalesRank.</p>
  
  <p>The sample data is as follows:
  The DailyBookFile has 150k records for each date, from year 2010 (i.e. 150k * 365 days * 8 years) rows. Same goes with SalesRank Table having about 500k records for each date</p>
  
  <pre><code>DailyBookFile       
  Date        Isbn13         CurrentModifiedDemandTotal
  20180122    9780955153075   13
  20180122    9780805863567   9
  20180122    9781138779396   1
  20180122    9780029001516   9
  20180122    9780470614150   42
  
  SalesRank       
  importdate  ISBN13          SalesRank
  20180122    9780029001516   69499
  20180122    9780470614150   52879
  20180122    9780805863567   832429
  20180122    9780955153075   44528
  20180122    9781138779396   926435
  
  Required Output     
  Date        Avg_Rank    Book_Group
  20180122    385154  Elite
  20180121    351545  Elite
  20180120    201545  Elite
  </code></pre>
  
  <p>I want to get the Top 200 CurrentModifiedDemand for each day, and take the average Rank. </p>
  
  <p>I am unable to work out a solution as I am new to SQL. </p>
  
  <p>I started with getting the Top 200 CurrentModifiedDemand for yesterday and get the Avg Rank over last year.</p>
  
  <pre><code>SELECT DBF.Filedate AS [Date],
         AVG(AMA.SalesRank) AS Avg_Rank,
         'Elite' AS Book_Group 
  FROM [ODS].[wholesale].[DailyBookFile] AS DBF
  INNER JOIN [ODS].[MarketplaceMonitor].[SalesRank] AS AMA ON (DBF.Isbn13 = AMA.ISBN13
                                                          AND DBF.FileDate = AMA.importdate)
  WHERE DBF.Isbn13 IN (SELECT TOP 200 Isbn13
                       FROM [ODS].[wholesale].[DailyBookFile]
                       WHERE FileDate = 20180122
                         AND CAST(CurrentModifiedDemandTotal AS int) &gt; 200)
    AND DBF.Filedate &gt; 20170101
  GROUP BY DBF.Filedate;
  </code></pre>
  
  <p>But the result is not what I want. So, now I want the ISBN for the Top 200 CurrentModifiedDemand for each day and their avg rank. I tried with this.</p>
  
  <pre><code>DECLARE @i int;
  SET @i = 20180122;
  WHILE (SELECT DISTINCT(DBF.Filedate)
         FROM [ODS].[wholesale].[DailyBookFile] AS DBF
         WHERE DBF.Filedate = @i) IS NOT NULL
  BEGIN
  
      SELECT DBF.Filedate AS [Date],
             AVG(AMA.SalesRank) AS Avg_Rank,
             'Elite' AS Book_Group 
      FROM [ODS].[wholesale].[DailyBookFile] AS DBF
      INNER JOIN [ODS].[MarketplaceMonitor].[SalesRank] as AMA ON DBF.Isbn13 = AMA.ISBN13
                                                              AND DBF.FileDate = AMA.importdate
      WHERE DBF.Isbn13 in (SELECT TOP 200 Isbn13
                           FROM [ODS].[wholesale].[DailyBookFile]
                           WHERE FileDate = @i
                             AND CAST (CurrentModifiedDemandTotal AS int) &gt; 500)
        AND DBF.Filedate = @i
      GROUP BY DBF.Filedate;
  
      SET @i = @i+1;
  
  END
  </code></pre>
  
  <p>In this I am getting one select query result in each window. Is there any way to have the result in a single table?</p>
  
  <p>P.S. The list of top 200 books every day will change according to the CurrentModifiedDemand. I want to take their avg. sales rank for that day.</p>