How to make a monthly report table using query in SQL Server stored procedure?

Question

I want to make a table like this (first month until the current month) in SQL Server:

But I'm struggling with the query.

I have a query that insert into temporary table using a stored procedure, when I execute the stored procedure, it keeps executing the query.

This is my stored procedure:

ALTER PROCEDURE [dbo].[get_dashboard_bulan]
    @month int,
    @year int
AS
    SET DATEFORMAT DMY
    SET NOCOUNT ON
BEGIN
    DECLARE @TEMP_TABLE TABLE (bulan int,
                               NOA int,
                               OSPENGAJUAN money, 
                               OSDISETUJUI money, 
                               NOABANDING int)

    DECLARE @firstdate INT = 1,
            @datenow INT= month ((select DATEADD(day, -1, DATEADD(month, @Month, DATEADD(year, @Year - 1900, 0))))), 
            @NOA INT = 0,
            @OSPENGAJUAN MONEY = 0,
            @OSDISETUJUI MONEY = 0,
            @NOABANDING INT = 0

    BEGIN TRY
        IF @month = MONTH(GETDATE()) AND @year = YEAR(GETDATE())
        BEGIN
            SET @datenow = MONTH(GETDATE())
        END

        WHILE @firstdate <= @datenow
        BEGIN
            SET @NOA = (SELECT COUNT(tiket_id) 
                        FROM m_tiket 
                        WHERE MONTH(created_at) = @firstdate)
            SET @OSPENGAJUAN = (SELECT SUM(plafond) AS plafond 
                                FROM m_tiket 
                                WHERE MONTH(created_at) = @firstdate)
            SET @OSDISETUJUI = (SELECT SUM(plafond_disetujui) AS plafond_disetujui 
                                FROM m_tiket 
                                WHERE MONTH(created_at) = @firstdate)
            SET @NOABANDING = (SELECT COUNT(tiket_id) 
                               FROM m_track 
                               WHERE status_id = 10 AND MONTH(created_at) = @firstdate)

            INSERT INTO @TEMP_TABLE 
            VALUES (@firstdate, @NOA, @OSPENGAJUAN, @OSDISETUJUI, @NOABANDING);

            SET @firstdate = month(DATEADD(DAY,1,@firstdate))+1
        END
    END TRY
    BEGIN CATCH
    END CATCH
END

SELECT 
    bulan, noa, 
    IIF(ospengajuan IS NULL, 0, ospengajuan) AS ospengajuan, 
    IIF(osdisetujui IS NULL, 0, osdisetujui) AS osdisetujui, 
    noabanding 
FROM 
    @TEMP_TABLE

Jaydip Jadhav · Accepted Answer · 2016-11-29T04:19:59.273

1

If understood correctly, problem with this line

SET @firstdate = month(DATEADD(DAY,1,@firstdate))+1

it alway set @firstdate equals to 2, since @firstdate is Int type variable and if you want to increment this you just need to simple increment logic.

Try to replace this with

SET @firstdate = @firstdate + 1

edited Nov 29 '16 at 04:19

answered Nov 29 '16 at 04:14

Jaydip Jadhav

12,179
6
24
40

1

Yes, you understand correctly :-). Doing `SELECT DATEADD(DAY,1,1);` returns `1900-01-08 00:00:00.000`, which should help explain the issue. – Solomon Rutzky Nov 29 '16 at 04:20

score 0 · Answer 2 · edited May 23 '17 at 12:30

0

You are declaring your @TEMP_TABLE table-valued variable inside the stored procedure. The moment you end the stored procedure, this table-valued variable disappears. If you want the values stored persistently, so that you can use them after the procedure finishes, insert them into a (temporary) table. See e.g. this question.

edited May 23 '17 at 12:30

Community

1
1

answered Nov 29 '16 at 03:26

Henning Koehler

2,456
1
16
20

You need to clarify what you mean by "a (temporary) table". A true temp table (`#tablename`) is scoped to the current stored procedure and will be dropped when it terminates - no different from a table variable. – alroc Nov 29 '16 at 03:59

How to make a monthly report table using query in SQL Server stored procedure?

2 Answers2