CODEWITHSUNDEEP
sqlsql-server
Nguyenal07
Nguyenal07

Reputation: 137

SQL Case Statement (Understanding)

I ran across this case statement in my database. I'm trying to figure out what it does. Basically what this case statement is trying to do is forecast the sum of toys for this week. Below is the case statement can someone break it down and explain it to me.

case 
    when ROW_NUMBER() over(order by datetbl.[date]) <> COUNT(*) over() 
    then null 
    else isnull(case 
        when case 
            when ROW_NUMBER() over(order by datetbl.[date]) = COUNT(*) over() 
            then (sum([Toy])/(datepart(dw,getdate())-1))*7 
        end = 0 
        then sum(Toy) + 1 
        else case 
            when ROW_NUMBER() over(order by datetbl.[date]) = COUNT(*) over() 
            then (sum([Toy])/(datepart(dw,getdate())-1))*7 
        end 
    end,0) 
end [ToyForcast]

thanks

Upvotes: 2

Views: 125

Answers (2)

Mackan
Mackan

Reputation: 6271

Apparently I'm a bit too late to the party, but I wanted to suggest an (for me) easier way to write this. This should return the same results:

case 
    when ROW_NUMBER() over(order by datetbl.[date]) <> COUNT(*) over() 
    then null 
    else 
       isnull(
          IIF(
              ((sum([Toy])/(datepart(dw,getdate())-1))*7) = 0, 
              sum(Toy) + 1, 
              ((sum([Toy])/(datepart(dw,getdate())-1))*7)
          ),0) 
end [ToyForcast]

This is assuming you're using at least sql server 2012, since it utilizes IIF(). If you use 2008 r2 or earlier, you can still skip the extra inner cases, since it's already clear that we're on the last row.

Since I had already prepared some comments, I'll post them too:

case when ROW_NUMBER() over(order by datetbl.[date]) <> COUNT(*) over() then null else

Essentially, ROW_NUMBER will return the current row number (ordered by date), and if this differs from the total row count it will return null. If not, it will proceed to:

isnull(
 case when 
  case when ROW_NUMBER() over(order by datetbl.[date]) = COUNT(*) over() then

This (the inner case) is one of the checks I removed in my example at the top. This will check that the current row is the last, but we already know that from the first case.

(sum([Toy])/(datepart(dw,getdate())-1))*7 end = 0 then sum(Toy) + 1 else
   case when ROW_NUMBER() over(order by datetbl.[date]) = COUNT(*) over() then 
     (sum([Toy])/(datepart(dw,getdate())-1))*7 
   end 
end,0)

Sum number of toys and divide it by the amount of days that have passed from monday. Then multiply this by 7 to get an approximate for the weekly total. If this equals 0 then return sum of toys + 1, else return the first expression (but first checking for the unnecessary "if this is the last row").

Upvotes: 1

Becuzz
Becuzz

Reputation: 6866

I've added comments to the query to try to break this down bit by bit.

case 
    when ROW_NUMBER() over(order by datetbl.[date]) <> COUNT(*) over() --this checks if you are on the last row of your dataset, if you are not => put a null in this column
    then null 
    else isnull(case 
        when case 
            when ROW_NUMBER() over(order by datetbl.[date]) = COUNT(*) over() --on the last row
            then (sum([Toy])/(datepart(dw,getdate())-1))*7 --find the average of toys so far this week extrapolated out to the end of the week (presumably to figure out how many toys will be used(?) by the end of the week
        end = 0 --if there are going to be 0 toys used by the end of the week show sum(refire) + 1, whatever that signifies
        then sum(refire) + 1 
        else case 
            when ROW_NUMBER() over(order by datetbl.[date]) = COUNT(*) over() --again on the last row
            then (sum([Toy])/(datepart(dw,getdate())-1))*7 --show the extrapolated value using the average used per day so far
        end 
    end,0) --if after all of that we ended up with a null value for the extrapolated value, replace the null with 0
end [ToyForcast]

Basically it tries to only run the calculation on the last row of the dataset for what I assume is a report. Then it tries to figure out what the Toys value for the end of the week is by calculating the average toys per day this week and multiplying it by 7 (to get the week end projection). If that projection results in a 0, put the sum of refire + 1 as the projection (no clue what that could mean). Finally if we ended up with null because we have no data, put in a 0 instead.

Hopefully this helps and my brain didn't miss something.

Upvotes: 4

Related Questions