Finding last Column of data in a row? 1

[WurzelGummidge]

I would like to be able to return the LAST populated column for a range of columns.
I have a table with columns:
CUSTOMER,PRODUCT,M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12

The Mx Columns represent the last 12 months and contains a qty (number).

What i would like to do is find the LAST month of an order:
ie
Example Data:
M1 NULL
M2 NULL
M3 NULL
M4 NULL
M5 6
M7 3
M8 NULL
M9 10
M10 NULL
M11 NULL
M12 NULL

so in the above example, i would like to have '4' returned - ie LAST ORDERED 4 MONTHS AGO (M9 has last order qty in it)

 

[GShen]

Select Customer,
Product,
case
when @m1 is not null then 0
when @m2 is not null then 1
when @m3 is not null then 2
when @m4 is not null then 3
when @m5 is not null then 4
when @m6 is not null then 5
when @m7 is not null then 6
when @m8 is not null then 7
when @m9 is not null then 8
when @m10 is not null then 9
when @m11 is not null then 10
when @m12 is not null then 11
end as month

That should do it. Let me know how you make out.

 

[WurzelGummidge]

That was it
Just had to reverse the M columns - ie
M13 Then 0
M12 Then 1
M11 Then 2

....etc - That way it shows 2 for 2 months ago etc.

Cheers Mate !

 

[GShen]

Great! Glad I could help. Sorry, in my haste, I didn't recognize your table from last to 1st. But you got it!