CODEWITHSUNDEEP
sqlsql-server-2008
Lex
Lex

Reputation: 891

SQL - insert using while loop (column name depending on the loop)

We have a report that needs to get data for 7 days into a result table like this:

declare @table table (ProductId int, product nvarchar(255), supplier nvarchar(255), day1 int,  day2 int, day3 int, day4 int, day5 int, day6 int, day7 int)

However the problem is that I currently run 7 insert statements that are nearly identical.

insert @table (ProductId, product, supplier, day1)
select 
   productId,
   product, 
   supplier,
   COUNT(productId)
from 
   @results
where 
   createdDate = @Date   
group by
   CreatedDate, ProductId, Product, supplier

This one inserts into day1, however I have to do the same with day2, day3.... just changing the @Date

What I would like to do is create a loop that will insert into the correct column depending which is the current loop (ie. is is processing day1, or day2...).

Is there a way to dynamically set it to the correct column name for the insert statement?

I'm using: sql server 2008R2

Upvotes: 0

Views: 2924

Answers (3)

Joe G Joseph
Joe G Joseph

Reputation: 24046

Instead of doing like this, I would suggest you to change the target table structure to have two more columns called day_count, which stores the count for the day and createdDate 

create table @table (productId int,product int, supplier int,
    createdDate date,day_count int)

this table can be populated using the query below

insert @table (ProductId, product, supplier,createdDate, day_count)
select 
   productId,
   product, 
   supplier,
   createdDate,
   COUNT(productId)
from 
   @results
where 
   createdDate between @Date and dateadd(dd, 7, @Date)
group by
   CreatedDate, ProductId, Product, supplier

And then use the below query to get the desired output

 declare @startDate date ='2012-07-02';
 with cte as(
select productId ,product , supplier ,createdDate, datediff(dd,
    @startDate,createdDate)+1 [day_num],
    day_count from #tmp)
 select productId ,product , supplier ,createdDate , 
 case when day_num=1 then day_count end [day1],
 case when day_num=2 then day_count end [day2],
 case when day_num=3 then day_count end [day3],
 case when day_num=4 then day_count end [day4],
 case when day_num=5 then day_count end [day5],
 case when day_num=6 then day_count end [day6],
 case when day_num=7 then day_count end [day7]
  from cte

Upvotes: 0

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

If you ever find yourself askign about the use of a loop when dealing with a SQL problem, you're probably not approaching the problem with the appropriate mind set.

starting point below.

SELECT  productId
      , product
      , supplier
      , day1 = SUM(CASE WHEN createdDate = @date THEN 1 ELSE 0 END)
      , day2 = SUM(CASE WHEN createdDate = DATEADD(d,1,@date) THEN 1 ELSE 0 END)...   


FROM    @results
WHERE   createdDate >= @Date
GROUP BY CreatedDate
      , ProductId
      , Product
      , supplier

Upvotes: 1

GarethD
GarethD

Reputation: 69759

Could you not try doing the insert all in one query using the PIVOT function in SQL-Server 2008?

I am assuming your days are consecutive, and your columns are Day1, Day2 etc.

;WITH Data AS
(   SELECT  ProductID,
            Product,
            Supplier,
            DATEDIFF(DAY, @Date, CreatedDate) + 1 AS DayNumber,
            ProductID AS [Counter]
    FROM    @Results
    WHERE   CreatedDate BETWEEN @Date AND DATEADD(DAY, 7, @Date)
)
INSERT @table (ProductID, Product, Supplier, Day1, Day2, Day3, Day4, Day5, Day6, Day7)
SELECT  *
FROM    Data
        PIVOT
        (   COUNT([Counter])
            FOR DayNumber IN ([1], [2], [3], [4], [5], [6], [7])
        ) pvt

Upvotes: 2

Related Questions