Clever Geek Handbook

Fixed month query in transaction table for SSRS chart

I have the example data below. But when I draw it in SSRS, Feb 2012 does not appear because there was no sale in Feb 2012. I need all months to appear on the chart even if there is no transaction data.

How can I solve this? This table grows over time, but sometimes there is no data for certain months. I had the idea below to fix this but it doesn't work. Please, help.

Table tTable

:

╔════â•Ķ═════════════════â•Ķ══════════════â•Ķ══════════╗
║ ID ║ TransactionDate ║ Month Number ║ Amount   ║
║ 1  ║ 3-Jan-12        ║ 1            ║ $1       ║
║ 2  ║ 3-Mar-12        ║ 3            ║ $56      ║
║ 3  ║ 3-Apr-12        ║ 4            ║ $6       ║
║ 4  ║ 3-May-12        ║ 5            ║ $8       ║
║ 5  ║ 3-Jun-12        ║ 6            ║ $11      ║
║ 6  ║ 3-Jul-12        ║ 7            ║ $8       ║
║ 7  ║ 3-Aug-12        ║ 8            ║ $5       ║
║ 8  ║ 3-Sep-12        ║ 9            ║ $2       ║
║ 9  ║ 3-Oct-12        ║ 10           ║ $1       ║
║ 10 ║ 3-Nov-12        ║ 11           ║ $300     ║
║ 11 ║ 3-Dec-12        ║ 12           ║ $21      ║
║ 12 ║ 3-Jan-13        ║ 1            ║ $54      ║
║ 13 ║ 3-Feb-13        ║ 2            ║ $54      ║
║ 14 ║ 3-Mar-13        ║ 3            ║ $87      ║
║ 15 ║ 3-Apr-13        ║ 4            ║ $99      ║
║ 16 ║ 3-May-13        ║ 5            ║ $12      ║
║ 17 ║ 3-Jun-13        ║ 6            ║ $2,187   ║
║ 18 ║ 3-Jul-13        ║ 7            ║ $21,487  ║
║ 19 ║ 3-Aug-13        ║ 8            ║ $1,214   ║
║ 20 ║ 3-Sep-13        ║ 9            ║ $21      ║
║ 21 ║ 3-Oct-13        ║ 10           ║ $21      ║
║ 22 ║ 3-Nov-13        ║ 11           ║ $235     ║
║ 23 ║ 3-Dec-13        ║ 12           ║ $2,313   ║
╚════â•Đ═════════════════â•Đ══════════════â•Đ══════════╝

At first I thought a full outer join would work. But I have no ID in the table tMonth

. so it still doesn't work.

SELECT cMonth FROM (
SELECT 1 "cMonth"
UNION 
SELECT 2
UNION 
SELECT 3
UNION 
SELECT 4
UNION 
SELECT 5
UNION 
SELECT 6
UNION 
SELECT 7
UNION 
SELECT 8
UNION 
SELECT 9
UNION 
SELECT 10
UNION 
SELECT 11
UNION 
SELECT 12)tMonth
FULL OUTER JOIN tTable ON tTable.MonthNumber = tMonth.cMonth

My hope was to come up with this result

+-----------------+--------------+------+----------+
| TransactionDate | Month Number | Year |  Amount  |
+-----------------+--------------+------+----------+
| 3-Jan-12        |            1 | 2012 | $1       |
<b>| NULL            |            2 | 2012 | NULL     |</b>
| 3-Mar-12        |            3 | 2012 | $56      |
| 3-Apr-12        |            4 | 2012 | $6       |
| 3-May-12        |            5 | 2012 | $8       |
| 3-Jun-12        |            6 | 2012 | $11      |
| 3-Jul-12        |            7 | 2012 | $8       |
| 3-Aug-12        |            8 | 2012 | $5       |
| 3-Sep-12        |            9 | 2012 | $2       |
| 3-Oct-12        |           10 | 2012 | $1       |
| 3-Nov-12        |           11 | 2012 | $300     |
| 3-Dec-12        |           12 | 2012 | $21      |
| 3-Jan-13        |            1 | 2013 | $54      |
| 3-Feb-13        |            2 | 2013 | $54      |
| 3-Mar-13        |            3 | 2013 | $87      |
| 3-Apr-13        |            4 | 2013 | $99      |
| 3-May-13        |            5 | 2013 | $12      |
| 3-Jun-13        |            6 | 2013 | $2,187   |
| 3-Jul-13        |            7 | 2013 | $21,487  |
| 3-Aug-13        |            8 | 2013 | $1,214   |
| 3-Sep-13        |            9 | 2013 | $21      |
| 3-Oct-13        |           10 | 2013 | $21      |
| 3-Nov-13        |           11 | 2013 | $235     |
| 3-Dec-13        |           12 | 2013 | $2,313   |
+-----------------+--------------+------+----------+

I just understand that the year column is another problem.

+3


source to share


3 answers


I solve this using CTE to create a calendar year for years to come

DECLARE @START_DATE DATETIME
DECLARE @ENDDATE DATETIME
SET @START_DATE = '20110101'
SET @ENDDATE = '20151231'
;
WITH CTE_DATES AS
(
SELECT
       @START_DATE DateValue UNION ALL SELECT
       DateValue + 1
FROM CTE_DATES
WHERE DateValue + 1 < @ENDDATE)

       SELECT
             CAST(DateValue AS date) "DateValue"
           into #Calendar
       FROM CTE_DATES
       OPTION (MAXRECURSION 0)

SELECT sum(Amount),iMonth,iYear FROM (
SELECT month(datevalue) "iMonth",Year(datevalue) "iYear" FROM #Calendar
GROUP BY month(datevalue),Year(datevalue) 
ORDER BY Year(datevalue) ,month(datevalue))Cal
LEFT JOIN tTable ON tTable.MonthNumber = Cal.iMonth AND Year(tTable.TransactionDate)=Cal.iYear OR tTable.TransactionDate IS NULL
GROUP BY iMonth,iYear


I got the CTE calendar from this link http://www.sqlshack.com/sql-server-using-recursive-cte-persisted-computed-columns-create-calendar-table/

0


source


How to use a Tally table to get months and years and then use a left join from that to tTable:



SELECT TOP 100
        IDENTITY( INT,0,1 ) AS N
INTO    tTable
FROM    master.dbo.syscolumns s1 ,
        master.dbo.syscolumns s2 

DECLARE @startdate DATETIME ,
    @enddate DATETIME
SELECT  @startdate = MIN(Transactiondate) ,
        @enddate = MAX(Transactiondate)
FROM    #temp;
WITH    tMonth
          AS ( SELECT   DATEADD(MONTH, DATEDIFF(MONTH, N, @startdate) + N, 0) AS Months
               FROM     #tally
               WHERE    N <= DATEDIFF(MONTH, @startdate, @enddate)
             )
    SELECT  tTable.TransactionDate ,
            MONTH(tMonth.Months) ,
            YEAR(tMonth.Months) AS YearNumber ,
            tTable.Amount
    FROM    tMonth
            LEFT OUTER JOIN tTable ON MONTH(tMonth.Months) = tTable.MonthNumber
                                      AND YEAR(tMonth.Months) = YEAR(tTable.Transactiondate)

DROP TABLE #tally
0


source


Try it. Use Recursive CTE

to generate months, then useLEFT OUTER JOIN 

DECLARE @Mindate DATETIME,
        @Maxdate DATETIME

SELECT @Mindate = Min(Transactiondate),
       @Maxdate = Max(Transactiondate)
FROM   tablename;

WITH cte
     AS (SELECT @Mindate AS Dates
         UNION ALL
         SELECT Dateadd(Month, 1, Dates)
         FROM   cte
         WHERE  dates < @Maxdate)
SELECT b.TransactionDate,
       Isnull([b.Month Number], Datepart(Month, a.Dates)) [Month Number],
       Isnull(Year(b.TransactionDate), Year(a.Dates))     [Year],
       Amount
FROM   cte a
       LEFT OUTER JOIN tablename b
                    ON Month(a.Dates) = Month(b.TransactionDate)
                       AND Year(a.Dates) = Year(b.TransactionDate)
0


source






More articles:


All Articles