sql server - get all day of month then mapping data to specific date

Question

I created a chart that will plot data to show value for present data on specific date

with this SQL

select substring(TRAN_DATE, 1, 4) MONTH, substring(TRAN_DATE, 5, 2) DATE, count(*) AMOUNT from  TA1606 group by TRAN_DATE

this is field structure

 TRAN_DATE  char(6)

with this create command

CREATE TABLE [dbo].[TA16](
    [TRAN_DATE] [char](6) NULL,
    [TERM] [char](16) NULL,
)

this my result is

MONTH   DATE    AMOUNT
1606    03        44  

but this is just only one row and one day i need to get all day of month in example is 06 June it will query all day in the month then mapping data to date as my result above shown

this is my expected result

    MONTH   DATE    AMOUNT
1606          1     0
1606          2     0
1606          3     44
1606          4     0
1606          5     0
1606          6     0
1606          7     0
1606          8     0 
1606          9     0
1606          10    0
1606          11    0
1606          12    0
1606          13    0
1606          14    0
1606          15    0
1606          16    0
1606          17    0
1606          18    0
1606          19    0
1606          20    0
1606          21    0
1606          22    0
1606          23    0
1606          24    0
1606          25    0
1606          26    0
1606          27    0
1606          28    0
1606          29    0
1606          30    0

Answer 1

Basically the idea is to use a left join between a calendar table and your data table. There are many ways to create a calendar table, I like to use a tally table for that. So the First thing you should do, if you don't already have a tally table, is to create one.
If you are asking your self what is a tally table and why do you need it, read this article by Jeff Moden

SELECT TOP 1001 IDENTITY(int,0,1) AS Number
    INTO Tally
    FROM sys.objects s1       
    CROSS JOIN sys.objects s2 
ALTER TABLE Tally ADD CONSTRAINT PK_Tally PRIMARY KEY CLUSTERED (Number)

Now, Create and populate sample data (Please save us this step in your next question)

DECLARE @TA16 TABLE (
    [TRAN_DATE] [char](6) NULL,
    [TERM] [char](16) NULL
)

INSERT INTO @TA16 VALUES ('160603', '44')

Then, Use a CTE to create a calendar for the relevant year (or month, what ever is best for you), And since you are using char to keep the date, we need another cte to convert that char value to proper date.

;With Calendar as
(
    SELECT DATEADD(DAY, Number, '2016-01-01') TheDate
    FROM Tally 
    WHERE Number < 367 -- Most years are 365 days, but leap years are 366, so it will cover leap years as well
), TA16WithActualDate AS
(
    SELECT  [TRAN_DATE], 
            [TERM],
            CONVERT(date, LEFT([TRAN_DATE], 2) +'.'+ SUBSTRING ([TRAN_DATE], 3, 2) +'.'+ RIGHT([TRAN_DATE], 2), 2) As ActualDate
    FROM @TA16
)

Now, select from Calendar left joined to the TA16WithActualDate cte and format the output as you wish:

SELECT  REPLACE(CONVERT(char(5), TheDate, 2), '.', '') As [Month],  
        RIGHT(CONVERT(char(8), TheDate, 2), 2) As [Date],
        ISNULL([TERM], 0) As Amount
FROM Calendar
LEFT JOIN TA16WithActualDate ON TheDate = TRAN_DATE
WHERE MONTH(TheDate) = 6

Results:

Month Date Amount
--- ---- ------
1606  01   0               
1606  02   0               
1606  03   44              
1606  04   0               
1606  05   0               
1606  06   0               
1606  07   0               
1606  08   0               
1606  09   0               
1606  10   0               
1606  11   0               
1606  12   0               
1606  13   0               
1606  14   0               
1606  15   0               
1606  16   0               
1606  17   0               
1606  18   0               
1606  19   0               
1606  20   0               
1606  21   0               
1606  22   0               
1606  23   0               
1606  24   0               
1606  25   0               
1606  26   0               
1606  27   0               
1606  28   0               
1606  29   0               
1606  30   0