获取一个月中的每一天，然后将数据映射到特定日期 [英] get all day of month then mapping data to specific date

问题描述

我创建了一个图表，该图表将绘制数据以显示特定日期当前数据的值

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

使用此 SQL

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

这是字段结构

 TRAN_DATE  char(6)

使用这个创建命令

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

这是我的结果

MONTH   DATE    AMOUNT
1606    03        44  

但这只是一排一天我需要在示例中获得一个月的所有天数是 06 June 它会查询一个月中的整天然后将数据映射到 date 作为我的结果上图

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

这是我的预期结果

    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

推荐答案

基本上这个想法是在日历表和数据表之间使用左联接.创建日历表的方法有很多种，为此我喜欢使用计数表.因此，如果您还没有理货表，您应该做的第一件事是 创建一个.
如果您问自己什么是计数表以及为什么需要它，阅读这篇文章作者是杰夫·摩登

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')

然后，使用 CTE 为相关年份(或月份，最适合您的时间)创建日历，由于您使用 char 来保持日期，我们需要另一个 cte 到 将该字符值转换为正确的日期.

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
)

现在，从 Calendar 中选择 左加入 TA16WithActualDate cte 并根据需要格式化输出:

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

结果:

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    

这篇关于获取一个月中的每一天，然后将数据映射到特定日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！