0

This is initially intended to be a pivot display rather than manipulating data. However, I needed to show all days covered into selected month which the data doesn't have. Not all days we have payment. So for example, I have the month of May 2017, and we were only paid 6 times. I needed to show other days with 0 values as there was no payment supplied.

So what I wanted is to put the payment into the corresponding column/day. Below is my sample data for April.

enter image description here

Below is my current code..

    INSERT INTO @tPivot 
(
    --f_ID,
    f_Name,
    [1],    [2],    [3],    [4],    [5],    [6],    [7],    [8],    [9],    [10],
    [11],   [12],   [13],   [14],   [15],   [16],   [17],   [18],   [19],   [20],
    [21],   [22],   [23],   [24],   [25],   [26],   [27],   [28],   [29],   [30],
    [31]
)
SELECT c.f_completeName as 'Name',
        p.f_payment as 'Payment',
        DAY(p.f_date) as 'Date'
        FROM tCustomer c

I don't really know how this is called, so I cannot properly search the web. Hoping for some help.. My expected outcome would be..

Name  |  1  |  2  ...  |  11  | ... |  19

Man-- | .............. |  1000

Agu-- | ............................| 5000
AdorableVB
  • 1,383
  • 1
  • 13
  • 44

1 Answers1

0

with the help of Create date table then use below example 

   select 
--your fields
from [created_date_table] dt left join payment_table p on dt.date_field=p.f_date where dt.date_field between '2017-05-01' and '2017-05-31'
Community
  • 1
  • 1
Singaravelan
  • 809
  • 3
  • 19
  • 32