locked
The correct datamodel for Paymentstatus? RRS feed

  • Question

  • Goal:
    Create a report that look like this below.

    ------------------------
            Paid            Unpaid           UnableTopay
    Year    (Show quantity) (Show quantity)  (Show quantity) 
    
    ------------------------

    (Show quantity for instance 5 paid invoices in year 2010.) Amount is for instance 50 dollars in a specific invoice.

    Questions:
    How should the database model look like?

    Do I need to create three new column in table "Payment_status_schema" that is Paid, Unpaid and UnableToPay. If a specific invoice is "paid" in the column Paid shall have numnber 1 and column "unpaid" and "unalbeToPay" shall have number 0.

    When I want to count quantity of paid invoices it is enough to sum all the value in the column "paid".

    Is this the correct approach to do it?

    Info:
    *Please take a look at the Star schema datamodel.

    Monday, September 10, 2018 8:45 PM

Answers

  • Sounds good

    a Table to store Payment Status would be enough with status id

    Then against date and amount track the status id to denote the status of payment

    Didnt understand the relevance of attribute name though. What does that represent?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, September 11, 2018 10:35 AM
  • Hi Sakura Data,

     

    You could add three columns "Paid, Unpaid and UnableToPay" to the Payment_status_schema table. Then you can use the following code to get the goal report as you described.

     

    select
    DimDate.Year as Year, SUM(Payment_Status_schema.Paid) as Paid,
    SUM(Payment_Status_schema.Unpaid) as Unpaid,
    SUM(Payment_Status_schema.UnableTopay) as UnableTopay
    from Payment_Status_schema 
    join DimDate on DimDate.DimDate_id=Payment_Status_schema.DimDate_id 
    group by DimDate.Year
    
    /*Result
    
    Year        Paid        Unpaid      UnableTopay
    
    ---------------------- ----------- -----------
    
    2011        1           0           0
    
    2012        2           0           0
    
    2013        0           1           0
    
    2014        0           0           1
    
    */

     

    By the way, I noticed that in DimpaymentStatus table there has a payment_status column, could you please tell us if the value of the payment_status has something to do with the Paid, Unpaid and UnableToPay.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, September 11, 2018 9:58 AM

All replies

  • Hi Sakura Data,

     

    You could add three columns "Paid, Unpaid and UnableToPay" to the Payment_status_schema table. Then you can use the following code to get the goal report as you described.

     

    select
    DimDate.Year as Year, SUM(Payment_Status_schema.Paid) as Paid,
    SUM(Payment_Status_schema.Unpaid) as Unpaid,
    SUM(Payment_Status_schema.UnableTopay) as UnableTopay
    from Payment_Status_schema 
    join DimDate on DimDate.DimDate_id=Payment_Status_schema.DimDate_id 
    group by DimDate.Year
    
    /*Result
    
    Year        Paid        Unpaid      UnableTopay
    
    ---------------------- ----------- -----------
    
    2011        1           0           0
    
    2012        2           0           0
    
    2013        0           1           0
    
    2014        0           0           1
    
    */

     

    By the way, I noticed that in DimpaymentStatus table there has a payment_status column, could you please tell us if the value of the payment_status has something to do with the Paid, Unpaid and UnableToPay.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, September 11, 2018 9:58 AM
  • Sounds good

    a Table to store Payment Status would be enough with status id

    Then against date and amount track the status id to denote the status of payment

    Didnt understand the relevance of attribute name though. What does that represent?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, September 11, 2018 10:35 AM
  • By the way, I noticed that in DimpaymentStatus table there has a payment_status column, could you please tell us if the value of the payment_status has something to do with the Paid, Unpaid and UnableToPay. 

    Thank you for your help!

    Yes, column payment_status is related to the Paid, unpaid and UnableToPay from table payment_status_schema.

    Tuesday, September 11, 2018 12:04 PM

  • Didnt understand the relevance of attribute name though. What does that represent?



    Thank you for your help!

    I forgot to remove the text "attribute name" from the database model.

    Tuesday, September 11, 2018 12:10 PM
  • Hi Sakura Data,

     

    Could you please tell us if you have solved your question. If so, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    If you have any other questions, please let me know.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, September 12, 2018 1:36 AM