SQLTeam.com | Weblogs | Forums

To show the count per month from the date provided

I have two tables namely Vendors & Visits.

Create Table Vendor (VendorId BIGINT IDENTITY(1,1) NOT NULL, VendorName Nvarchar(256))

Create Table Visits (VisitID BIGINT IDENTITY(1,1) NOT NULL,  VendorId BIGINT NOT NULL, VisitsFromDate DATE NOT NULL, VisitsToDate DATE NOT NULL )

**insert** **into** Vendor

**select** 'Buon Incorp.' **union**

**select** 'Jon Don Incorp.' union

**select** 'Sumo Onu Incorp.'

**insert** **into** Visits

**select** 2,'2020-01-02', '2020-01-08'   **union**

**select** 3, '2020-01-03', '2020-01-22' union

**select** 1, '2020-01-12', '2020-02-28' union

**select** 2, '2020-02-09', '2020-03-03'

I need to find the total visits of each vendor in each month.

If the fromdate-Todate duration involves two months say, Feb & Mar, 1 should be shown for each month.

I am showing it in a report, with Vendor Name on Y axis & Month Name on X axis. I am fetching the vendorname & monthnames for this purpose in my query.

I have written a query, but its not working as desired. Please help on this. Will be really grateful.

DECLARE @tempVendorVisitsperMonthMaster TABLE
	(
			 VendorID					bigint NULL
			,VendorName					nvarchar(max) NULL
			,TotalVendorVisitCount		bigint NULL
			,DateVal 				    date NULL
			,MonthName				    nvarchar(50) NULL
			,VisitsCountForMonth 	    bigint NULL
	);

		---VendorVisits
		SELECT  
		VD.VendorID, VD.VendorName 
		into #VendorVisitsMonth
		FROM VisitsMaster V
		INNER JOIN VendorMaster VD ON V.VendorID = VD.VendorID 
		where ISNULL(V.IsDeleted,0) = 0 AND ISNULL(VD.IsDeleted,0) = 0
		GROUP BY VD.VendorID, VD.VendorName

		MERGE @tempVendorVisitsperMonthMaster AS T
		USING(SELECT VendorID,VendorName from #VendorVisitsMonth) AS S 
		(VendorID,VendorName)
		ON (T.VendorID=S.VendorID)
		WHEN NOT MATCHED THEN
		INSERT(VendorID,VendorName)
		VALUES(S.VendorID,S.VendorName)
		WHEN MATCHED THEN
		UPDATE SET 
		T.VendorID	= ISNULL(S.VendorID,T.VendorID),
		T.VendorName = ISNULL(S.VendorName,T.VendorName);

		---Total VendorVisitCount 
		SELECT  
		VD.VendorID, Count(V.[TypeOfVisitID]) as TotalVendorVisitCount 
		into #TotalVendorVisitMonthCount
		FROM VisitsMaster V
		INNER JOIN TypeOfVisitsMaster TV ON V.[TypeOfVisitID] = TV.[TypeOfVisitID] 
		FULL JOIN VendorMaster VD ON V.VendorID = VD.VendorID 
		where ISNULL(V.IsDeleted,0) = 0 AND ISNULL(VD.IsDeleted,0) = 0
		GROUP BY VD.VendorID

		MERGE @tempVendorVisitsperMonthMaster AS T
		USING(SELECT VendorID,TotalVendorVisitCount from #TotalVendorVisitMonthCount) AS S 
		(VendorID,TotalVendorVisitCount)
		ON (T.VendorID=S.VendorID)
		WHEN NOT MATCHED THEN
		INSERT(VendorID,TotalVendorVisitCount)
		VALUES(S.VendorID,S.TotalVendorVisitCount)
		WHEN MATCHED THEN
		UPDATE SET 
		T.TotalVendorVisitCount	= ISNULL(S.TotalVendorVisitCount,T.TotalVendorVisitCount);

		DECLARE @VisitStartDate as nvarchar(50), @VisitEnddate as nvarchar(50);
		SELECT @VisitStartDate = Format( DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0),'dd/MM/yyyy')
			  ,@VisitEnddate = Format( DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1),'dd/MM/yyyy');

		SELECT * into #tempVisitcalendars from fnGetCalendarDates(@VisitStartDate, @VisitEnddate);

		MERGE @tempVendorVisitsperMonthMaster AS T
		USING(SELECT 
				CONVERT(date, CONVERT(date, DateVal, 103), 120) AS DateVal,
				MonthName
			  FROM #tempVisitcalendars) AS S 
			  (DateVal,MonthName)
		      ON (T.DateVal=S.DateVal)

		WHEN NOT MATCHED THEN
		INSERT(DateVal,MonthName)
		VALUES(S.DateVal,S.MonthName)

		WHEN MATCHED THEN
		UPDATE SET 
		T.DateVal		= ISNULL(S.DateVal,T.DateVal),
		T.MonthName		= ISNULL(S.MonthName,T.MonthName);

		SELECT D.VisitID,V.DateVal,V.MonthName 
		INTO #tmpVisitbyMonth 
		from @tempVendorVisitsperMonthMaster V 
		inner join VisitsMaster D ON D.FromDate <= V.DateVal and D.ToDate >=  V.DateVal;

		MERGE @tempVendorVisitsperMonthMaster AS T
		USING(SELECT MonthName,VisitsCountForMonth = COUNT(DateVal)  
		      from #tmpVisitbyMonth GROUP BY MonthName) AS S 
			 (MonthName,VisitsCountForMonth)
		ON (T.MonthName=S.MonthName)

		WHEN NOT MATCHED THEN
		INSERT(MonthName,VisitsCountForMonth)
		VALUES(S.MonthName,S.VisitsCountForMonth)

		WHEN MATCHED THEN
		UPDATE SET 
		T.MonthName				= ISNULL(S.MonthName,T.MonthName),
		T.VisitsCountForMonth		= ISNULL(S.VisitsCountForMonth,T.VisitsCountForMonth);

		SELECT  VendorID
				,VendorName
				,TotalVendorVisitCount
				,DateVal
				,MonthName
				,VisitsCountForMonth
		FROM @tempVendorVisitsperMonthMaster;	

The SQL function used in the query is below:

CREATE FUNCTION [dbo].[fnGetCalendarDates] ( @minDate_Str NVARCHAR(30), @maxDate_Str NVARCHAR(30))
RETURNS  @Result TABLE(DateVal NVARCHAR(30) NOT NULL, WeekdayName NVARCHAR(30) NOT NULL,MonthName NVARCHAR(30) NOT NULL)
AS
BEGIN
    DECLARE @minDate DATETIME, @maxDate DATETIME
    SET @minDate = CONVERT(Datetime, @minDate_Str,103)
    SET @maxDate = CONVERT(Datetime, @maxDate_Str,103)


    INSERT INTO @Result(DateVal, WeekdayName,MonthName)
    SELECT CONVERT(NVARCHAR(10),@minDate,103),  CONVERT(NVARCHAR(30), FORMAT(@minDate, 'ddd')), CONVERT(NVARCHAR(30),FORMAT(@minDate, 'MMM')); 
    WHILE @maxDate > @minDate
    BEGIN
        SET @minDate = (SELECT DATEADD(dd,1,@minDate))
        INSERT INTO @Result(DateVal, WeekdayName,MonthName)
        SELECT CONVERT(NVARCHAR(10),@minDate,103), CONVERT(NVARCHAR(30), FORMAT(@minDate, 'ddd')), CONVERT(NVARCHAR(30),FORMAT(@minDate, 'MMM')) ;
    END
    return
END 
GO

I give up. There are just too many errors in your script to make this worth pursuing (and I'm not talking about the *s, those were easy to fix).

@GeoJul please, before you post any code in this forum, thoroughly test it out locally on a test db or test server.
Please demonstrate some initiative , help us help you.

Hello guys, please suggest a online sql code syntax checker( where you normally use) where I can post this & correct it

Instal free version of SQL such SQL express or sql developer. Why not try Ssms on your local machine? Or sql fiddle online

http://sqlfiddle.com/

1 Like