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