SQL Loop form between two days with ID

Question

I Want to create loop between two days and USERID For Example If I Choose between 01-01-2017 and 05-01-2017 procedure already insert into my table days between for each user every single user has a same days

I've tried the following

DECLARE @USERID int,@S_Date date,@E_Date date
SELECT @S_Date = @S_Date, @USERID = @USERID
While @S_Date <= @E_Date
begin
    select @S_Date, @USERID
    while @USERID < 3 
    begin
    INSERT INTO myTable values(@S_Date, @USERID)
        select @S_Date as S, @USERID as U
        set @USERID = @USERID + 1

    end
    set @S_Date = DateAdd(Day, 1, @S_Date)
end

Better suited using a recursive CTE and avoid the loop. http://stackoverflow.com/questions/7824831/generate-dates-between-date-ranges — xQbert, May 16 '17 at 15:18
@xQbert Recursive cte is much slower than set based approaches as the range generated increases.[Generate a set or sequence without loops - 2 - Aaron Bertrand](https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-2) — SqlZim, May 16 '17 at 15:23

SqlZim · Accepted Answer · 2017-05-16T15:36:38.967

declare @fromdate date = '20170101'
declare @thrudate date = '20170105'
declare @from_UserId int = 1;
declare @thru_UserId int = 3;
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate, @thrudate)+1) 
      [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto cross join n as kilo
                cross join n as tenK cross join n as hundredK
   order by [Date]
)
insert into myTable ([Date],UserId)
select [Date], u.UserId
from dates
  cross join (
    select UserId 
    from Users u
    where u.UserId >= @from_UserId
      and u.UserId <= @thru_UserId
      ) u;

The above uses stacked ctes to generate a numbers table, and uses that numbers table to generate dates between the date range provided.

It might not look as simple as using a while loop, but it will perform much better.

rextester demo: http://rextester.com/HHY62656

returns:

+------------+--------+
|    date    | userid |
+------------+--------+
| 2017-01-01 |      1 |
| 2017-01-02 |      1 |
| 2017-01-03 |      1 |
| 2017-01-04 |      1 |
| 2017-01-05 |      1 |
| 2017-01-01 |      2 |
| 2017-01-02 |      2 |
| 2017-01-03 |      2 |
| 2017-01-04 |      2 |
| 2017-01-05 |      2 |
| 2017-01-01 |      3 |
| 2017-01-02 |      3 |
| 2017-01-03 |      3 |
| 2017-01-04 |      3 |
| 2017-01-05 |      3 |
+------------+--------+

Number and Calendar table reference:

I want result be ..User (1) five days .. User (2) five days , and its on and its on whatever users i have — MuhammadElshater, May 16 '17 at 15:23
I dont want loop between two days only, I want between two days and ID with days , every ID has a Same days between — MuhammadElshater, May 16 '17 at 15:36
@MuhammadElshater I have updated my answer to include a `cross join` of a subquery that selects the `userid` from a table `users` where the `UserId` is between two values. I have also included a demo. — SqlZim, May 16 '17 at 15:37
another question, into your code, how can I insert datename into A table with date,, i added a colum name [dateOf Name] and I Use A DATENAME Func but doesn't work ! — MuhammadElshater, May 16 '17 at 17:02

SQL Loop form between two days with ID

1 Answers1