asked
Monthly wise count using date calculation
I have the table named company with columns company name, create date, etc. I want to get the count of companies created for this month. I have the stored procedure for weekly wise count CREATE PROCEDURE [dbo].[pr_NewCmpCount] @StartDate DATETIME = NULL --'20130508' , @EndDate DATETIME = NULL --'20130515' AS BEGIN DECLARE @DateDiff INT , @MainDate DATETIME IF @StartDate IS NULL AND @EndDate IS NULL BEGIN IF (DATEPART(dw,GETDATE()) > 3) SET @DateDiff = 0 ELSE SET @DateDiff = 6 SET @MainDate = DATEADD (MINUTE, -30, DATEADD (HOUR, -5, DATEADD(wk, DATEDIFF(wk,@DateDiff,GETDATE()), 0))) SELECT @StartDate = DATEADD (MINUTE, -30, DATEADD (HOUR, -5, DATEADD(wk, DATEDIFF(wk,0,@MainDate), -5))) SELECT @EndDate = DATEADD (MINUTE, -30, DATEADD (HOUR, -5, DATEADD(wk, DATEDIFF(wk,0,@MainDate), 2))) SELECT CONVERT (VARCHAR, Count(*)) AS [Count] , CONVERT (DATE, DATEADD (DD, 1, @StartDate)) AS [StartDate] , CONVERT (DATE, @EndDate) AS [EndDate] FROM Company WITH(NOLOCK) WHERE CreateDate >= @StartDate AND CreateDate < @EndDate END ELSE IF ((CONVERT (DATE, @StartDate) < CONVERT (DATE, @EndDate)) AND (CONVERT (DATE, @EndDate) < CONVERT (DATE, GETDATE()))) BEGIN SET @StartDate = CONVERT (DATE, @StartDate) SET @EndDate = CONVERT (DATE, @EndDate) SELECT @StartDate = DATEADD (MINUTE, -30, DATEADD (HOUR, -5, @StartDate)) SELECT @EndDate = DATEADD (MINUTE, 30, DATEADD (HOUR, 18, @EndDate)) SELECT CONVERT (VARCHAR, Count(*)) AS [Count] , CONVERT (DATE, DATEADD (DD, 1, @StartDate)) AS [StartDate] , CONVERT (DATE, @EndDate) AS [EndDate] FROM Company WITH(NOLOCK) WHERE CreateDate >= @StartDate AND CreateDate < @EndDate END ELSE SELECT 'Please Run After Tuesday of Every Week or Given the Valid Date' [Count] , CONVERT (DATE, @StartDate) AS [StartDate] , CONVERT (DATE, @EndDate) AS [EndDate] END Output for this stored procedure is Count Startdate EndDate 10 2016-03-13 2016-03-22 Expected output Count Startdate EndDate 20 2016-02-01 2016-02-29 Where do I need to modify in my stored procedure to get monthly wise count?
Not sure exactly of your data, but working out the dates for the previous month is fairly easy. I didn't try and unpick the logic you have in the proc. declare @startdate datetime declare @enddate datetime if @startdate is null set @startdate = getdate() if @enddate is null set @enddate = getdate() select dateadd(month, datediff(month, 0, @startdate) - 1, 0) select dateadd(day, -1, dateadd(month, datediff(month, 0, @enddate), 0)) You can then use these against the company table.
17 People are following this question.
