CODEWITHSUNDEEP
sql-servert-sqldefault-valuebetweendivide
gotqn
gotqn

Reputation: 43646

T-SQL Divide value across rows

I have the following columns and data in table:

PeriodID Days
1        NULL
2        NULL
3        NULL
4        NULL
5        NULL

Then I have days that should divide across the rows as follows:

  1. If Days < 5 (for example 2) I will have:

    PeriodID Days
1        NULL
2        NULL
3        NULL
4        1
5        1

  2. If days >= 5 and days%5=0 (for example 5) I will have:

    PeriodID Days
1        1    
2        1    
3        1    
4        1    
5        1

  3. If days > 5 and days%5!=0 (for example 12) I will have:

    PeriodID Days
1        3    
2        3    
3        2    
4        2    
5        2

I am able doing this with loops, and I hope for better solution using some smart technique or T-SQL function. Thanks in advance.

Upvotes: 0

Views: 459

Answers (1)

PinnyM
PinnyM

Reputation: 35531

This should do it for you:

DECLARE @numDays int
SET @numDays = 12

UPDATE someTable
SET Days = CASE WHEN @numDays < 5 
                  THEN CASE WHEN @numDays >= 6 - PeriodId THEN 1 ELSE NULL END
                ELSE FLOOR((@numDays + 5 - PeriodId) / 5)
           END

Upvotes: 2

Related Questions