CODEWITHSUNDEEP
sql-server
Soumya G Nair
Soumya G Nair

Reputation: 1

Take Monthly data From table based on StartDate and endDate

I have a master table with details (Status, ID, Title etc) and a start date and end date. Require active details month wise for current year.

I did something like this which gave me month wise records but not considering year. And this code is too slow when table rows increase. Please help me out.

DECLARE @LoopCounter INT,
        @Max INT, 
        @MonthName NVARCHAR(100),
        @TransStatus int,
        @Year int
BEGIN
    SELECT @LoopCounter = min(id), @Max = max(Id) 
    FROM TableName

    CREATE TABLE #Active 
    (
        Id INT IDENTITY(1,1),
        Month varchar(30)
    )

    WHILE (@LoopCounter <= @Max)
    BEGIN
        INSERT into #Active
            SELECT DateName(MONTH, DATEADD(MONTH, nos.monthnos, (Select StartDate from TableName where id=@LoopCounter And Year(StartDate)=@Year)-1 ) )
            FROM (SELECT 1 monthnos
                    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) nos 
            WHERE nos.monthnos <= DATEDIFF(MONTH, (Select StartDate from TableName where id=@LoopCounter),
                                                  (Select EndDate from TableName where id=@LoopCounter)+1 )
        SET @LoopCounter = @LoopCounter + 1
    END
END

SELECT COUNT(*) As ActiveCount, Month 
FROM #Active 
GROUP BY Month

Upvotes: 0

Views: 63

Answers (1)

S3S
S3S

Reputation: 25152

Your question is a little confusing, but based on "require active details month wise for current year" I think something like this would be far quicker for you. 

    SELECT COUNT(*), YEAR([Start Date]), Month([Start Date])
    FROM MasterTable
    WHERE [Start Date] BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AND GETDATE()
    GROUP BY 
    YEAR([Start Date]), Month([Start Date])

Upvotes: 1

Related Questions