Way to Store 12 Months Payment Status in a Single Column?

Question

I don't post often, but I have been banging my head against a wall for a while now and figured I would reach out to the experts. Please note that I was given this project as is because consulting.

I have a table with monthly payment statuses. I need to create actions based off of all 12 statuses i.e. the first record would get an action of "Fully Paid" while the second would get "Need to pay January" and the third would get "Account Hold."

    DECLARE @Payments TABLE (January NVARCHAR(20), February NVARCHAR(20), March NVARCHAR(20), April NVARCHAR(20), May NVARCHAR(20), June NVARCHAR(20), 
July NVARCHAR(20), August NVARCHAR(20), September NVARCHAR(20), October NVARCHAR(20), November NVARCHAR(20), December NVARCHAR(20))

INSERT @Payments VALUES 
('Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid'),
('Unpaid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid'),
('Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Account Hold')

Does anyone have a recommendation for handling this? The best thing I have come up with is a single column containing all of the payment statuses, so at least I could just reference a single field (i.e. 111111111111 would mean "Fully Paid," 211111111111" would mean "Need to Pay January, " etc.) I am not sure that would even help me out, but I need something better than manually working out all the permutations. Any help is MUCH appreciated.

You could do it using 12 bits of int, but sounds like that you have an design (db) before this. I think it's rarely good to use 1 column for more values/statuses etc — cantSleepNow, Mar 22 '16 at 21:26
You should use 12 separate rows at least, you might want to have some thing like Customer_Number, Pay_Year, Pay_Month. This would be better and track across multiple years. — Chuck, Mar 22 '16 at 21:44
While I once had the same problem and also went with your suggestion (12 bits) it is very cumbersome to deal with. This is especially due to the fact that standard T-SQL does not support the conversion of binary to int (and back again) yet. You would need to program your own converter: http://stackoverflow.com/questions/127116/sql-server-convert-integer-to-binary-string So, I'd go with `UNPIVOT` and store it in two columns: Month, Status. — Ralph, Mar 22 '16 at 21:45
Are you only concerned with the first unpaid month? What if both Jan and Feb are unpaid? — nathan_jr, Mar 23 '16 at 16:14
No Nathan, I am concerned with every potential payment combination over a 12 month span. — user1454772, Mar 23 '16 at 18:24
If you post more about the business logic you need to implement I think you will get more useful answers. — shawnt00, Mar 23 '16 at 19:20

nathan_jr · Answer 1 · 2016-03-23T19:30:43.320

Its hard to say without knowing all of your requirements, but if you're stuck with that @Payments table design, I like the pivot option:

DECLARE @Payments TABLE (Id int primary key, January NVARCHAR(20), February NVARCHAR(20), March NVARCHAR(20), April NVARCHAR(20), May NVARCHAR(20), June NVARCHAR(20), 
July NVARCHAR(20), August NVARCHAR(20), September NVARCHAR(20), October NVARCHAR(20), November NVARCHAR(20), December NVARCHAR(20))

INSERT @Payments VALUES 
(1, 'Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid'),
(2, 'Unpaid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid'),
(3, 'Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Paid','Account Hold')

select  Id, PaymentStatus, PaymentMonth
from    @Payments p 
unpivot (   PaymentStatus 
            for PaymentMonth in (January, February, March, April, May, June, July, August, September, October, November, December)
        ) d
where   PaymentStatus <> 'Paid'

Returns:

Id  PaymentStatus   PaymentMonth
--  -------------   ------------
2   Unpaid          January
3   Account Hold    December

The flag mask is fun, but I'm not convinced it will scale with your needs and the single column doesn't buy you much. I guarantee the developer who comes after you will think you're a jerk :)

declare @months table (i int, name varchar(10))
insert into @months
    select  i, datename(month, dateadd(month, i, 0) - 1)
    from    (   select 1 union all  select 2 union all select 3 union all select 4 union all
                select 5 union all  select 6 union all select 7 union all select 8 union all
                select 9 union all  select 10 union all select 11 union all select 12)d(i)

declare @PaymentStatus table (Id int primary key, Mask varchar(12));
insert into @PaymentStatus
    select 1, '100020000001' union all
    select 2, '000000000001'

select  Id, [PaymentStatus] = case Flag when 1 then 'Unpaid' when 2 then 'On Hold' end, PaymentMonth
    from    @PaymentStatus
    cross
    apply       
            (   select  m.Ordinal, m.Flag, c.PaymentMonth
                from    (   select  i, substring(mask, i, 1)
                            from    @months
                        ) m (Ordinal, Flag)
                join    (   select  i, name
                            from    @months m
                        ) c (Ordinal, PaymentMonth) on
                        m.Ordinal = c.Ordinal
            ) d
    where   Flag <> 0; --paid

Returns:

Id  PaymentStatus   PaymentMonth
--  -------------   ------------
1   Unpaid          January
1   On Hold         May
1   Unpaid          December
2   Unpaid          December

Although your solution is a bit too complex for my simple mind with too many tables. I like it very much. It is clear and straight forward and a typical OLTP solution. My solution is more of a OLAP solution. Anyway, `user1454772` now has two good solutions to chose from. You got my thumbs up. — Ralph, Mar 23 '16 at 19:22
@Ralph ah, i mistook you for OP. The mask example is fun but not exactly useful. — nathan_jr, Mar 23 '16 at 19:31

Ralph · Answer 2 · 2016-03-23T18:52:54.733

I know this is not what you want. But this is only to show how I'd suggest going about it (as described in my comment).

    select  *
    from    (
             select *
             from   @Payments as p unpivot 
( cStatus for cMonth in (January, February, March, April, May, June, July,
                         August, September, October, November, December) ) as u
            ) as t
    where   t.cStatus <> 'Paid'

Update:

Based on your comment (concerning you IDs) here is a slightly improved solution which might better suit your needs. First the slightly adapted setup:

declare @Payments table
    (
     cYear int,
     January nvarchar(20),
     February nvarchar(20),
     March nvarchar(20),
     April nvarchar(20),
     May nvarchar(20),
     June nvarchar(20),
     July nvarchar(20),
     August nvarchar(20),
     September nvarchar(20),
     October nvarchar(20),
     November nvarchar(20),
     December nvarchar(20)
    )

insert  @Payments
values  (2016, 'Paid', 'Paid', 'Paid', 'Paid', 'Paid', 'Paid', 'Paid', 'Paid',
         'Paid', 'Paid', 'Paid', 'Paid'),
        (2015, 'Unpaid', 'Paid', 'Paid', 'Paid', 'Paid', 'Paid', 'Paid',
         'Paid', 'Paid', 'Paid', 'Paid', 'Paid'),
        (2014, 'Paid', 'Paid', 'Paid', 'Paid', 'Paid', 'Paid', 'Paid', 'Paid',
         'Paid', 'Paid', 'Paid', 'Account Hold')

declare @result table
    (
     cPeriod int primary key
                 not null,
     cStatus nvarchar(20)
    )

Now, translate (unpivot) the data into two columns and insert the result into a separate table:

insert  into @result
        select  u.cYear * 100 + case u.cMonth
                                  when N'January' then 1
                                  when N'February' then 2
                                  when N'March' then 3
                                  when N'April' then 4
                                  when N'May' then 5
                                  when N'June' then 6
                                  when N'July' then 7
                                  when N'August' then 8
                                  when N'September' then 9
                                  when N'October' then 10
                                  when N'November' then 11
                                  when N'December' then 12
                                  else 0
                                end,
                u.cStatus
        from    @Payments as p unpivot 
( cStatus for cMonth in (January, February, March, April, May, June, July,
                         August, September, October, November, December) ) as u

Note, that I chose to add a year to the initial data set. Yet, any number could be used and integrated with another. My "ID" consists now of YYYYMM. But it could be also IDidIDidMM or any other format you choose. Like this you can keep it down to two columns (which is very close to your original request to having only one column). The handling of this data is now fairly easy and straight forward.

Example by Status:

select  *
from    @result as r
where   r.cStatus <> N'Paid'
        or r.cStatus is null
union all
select  *
from    @result as r
where   r.cStatus = N'Paid'

Example by Year:

select  *
from    @result as r
where   r.cPeriod / 100 = 2015  -- Year 2015

Example by Month:

select  *
from    @result as r
where   r.cPeriod % 100 = 2 -- Februrary

Ah, this is pretty cool since it gives me the data in a manageable format. The only issue would be joining the unique ID that each payment status has. I did not include the ID in my sample query because I did not think it was necessary, but if you include the ID in your unpivot, you are left with a new row with only the ID. — user1454772, Mar 23 '16 at 13:54

score 0 · Answer 3 · answered Mar 22 '16 at 21:48

I recommend to not use bit-wise logic, because if in case you have more statuses in future, you'll be in trouble.

However storing data this way is has some disadvantage, On of them is you cannot easily query the stored data.

if you still want to do that, I suggest use a varchar field and store each status as a alphabet character like (AAAAAAA, ABAAAAA, CAABAAAA).

score 0 · Answer 4 · answered Mar 22 '16 at 21:48

Maybe you can use a case statement to determine your action. Just a sketch not a working statement:

select 
  [action] = case 
      --check if on hold
      when January = 'Account Hold' or February = 'Account Hold' --or ...
          then 'Account Hold'
      --check for first unpaid month
      when January = 'Unpaid'
          then 'Need to pay January'
      when February = 'Unpaid'
          then 'Need to pay February'
      --repeat for all months
      else 'Fully Paid'
    end

Way to Store 12 Months Payment Status in a Single Column?

4 Answers4