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