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

Please edit your question to include the relevant table DDL and sample data as DML. Also, read Aaron Bertrand's [Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) — Zohar Peled, Jul 06 '16 at 07:32
my uncleared question was just like this question http://stackoverflow.com/questions/15570627/query-to-return-all-the-days-of-a-month — Touchy Vivace, Jul 06 '16 at 07:50
I'd recommend Aaron's articles every day :-) He has a series of articles called Bad habits to kick, everyone of them that I've read was great. — Zohar Peled, Jul 06 '16 at 08:05

score 1 · Accepted Answer · edited May 23 '17 at 12:08

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

your answer was absolutely clear :) and work like a charm, thank your very much — Touchy Vivace, Jul 06 '16 at 08:53

StackUser · Answer 2 · 2016-07-06T07:43:36.593

0

Try like this,

SELECT TOP 1 substring(TRAN_DATE, 1, 4) MONTH
    ,substring(TRAN_DATE, 5, 2) DATE
    ,count(*) AMOUNT
FROM TA1606
GROUP BY TRAN_DATE
ORDER BY 3 DESC

This query would probably give result like this.

MONTH   DATE  AMOUNT
1606   3     44

edited Jul 06 '16 at 07:43

answered Jul 06 '16 at 07:21

StackUser

5,370
2
24
44

your code show me just `Month DATE AMOUNT` `16June 03 44` – Touchy Vivace Jul 06 '16 at 07:24
What's your expected result. – StackUser Jul 06 '16 at 07:30
my expected result was under "I need them to show as this" my difinite result was just `1606 03 44` – Touchy Vivace Jul 06 '16 at 07:31
Your are expecting only one row with Amount>0 right – StackUser Jul 06 '16 at 07:34

get all day of month then mapping data to specific date

2 Answers2

Linked

Related