February 21, 2020 at 7:01 pm
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.
Also, please correct wherever I am wrong.
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
February 21, 2020 at 8:42 pm
Could you clarify what this means "I have written a query, but its not working as desired."? What is the output you got and what was the output you expected?
How are you counting visits if they cross months?
February 22, 2020 at 1:31 am
Wow. I'm way to lazy to write all that.
Setup:
use tempdb;
go
Create Table Vendor (
VendorId BIGINT IDENTITY(1,1) NOT NULL
, VendorName varchar(256)
);
Create Table Visits (
VisitID BIGINT IDENTITY(1,1) NOT NULL
, VendorId BIGINT NOT NULL
, VisitsFromDate DATE NOT NULL
, VisitsToDate DATE NOT NULL
);
GO
CREATE TABLE Calendar(CalendarDate DATE, MonthOfYear TINYINT, Yr INT);
insert into Vendor(VendorName)
VALUES ('Buon Incorp.'), ('Jon Don Incorp.'),('Sumo Onu Incorp.');
insert into Visits (VendorId, VisitsFromDate ,VisitsToDate)
VALUES (2,'2020-01-02', '2020-01-08'),
(3, '2020-01-03', '2020-01-22'),
(1, '2020-01-12', '2020-02-28'),
(2, '2020-02-09', '2020-03-03');
DECLARE @StartDate DATE,
@EndDate DATE;
SELECT @StartDate = MIN(VisitsFromDate)
, @EndDate = MAX(VisitsToDate)
FROM Visits;
WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO Calendar(CalendarDate) VALUES (@StartDate);
SET @StartDate = DATEADD(day,1,@StartDate);
END
UPDATE Calendar
SET MonthOfYear = MONTH(CalendarDate),
Yr = YEAR(CalendarDate);
use tempdb;
go
Create Table Vendor (
VendorId BIGINT IDENTITY(1,1) NOT NULL
, VendorName varchar(256)
);
Create Table Visits (
VisitID BIGINT IDENTITY(1,1) NOT NULL
, VendorId BIGINT NOT NULL
, VisitsFromDate DATE NOT NULL
, VisitsToDate DATE NOT NULL
);
CREATE TABLE Calendar(CalendarDate DATE, MonthOfYear TINYINT, Yr INT);
GO
insert into Vendor(VendorName)
VALUES ('Buon Incorp.'), ('Jon Don Incorp.'),('Sumo Onu Incorp.');
insert into Visits (VendorId, VisitsFromDate ,VisitsToDate)
VALUES (2,'2020-01-02', '2020-01-08'),
(3, '2020-01-03', '2020-01-22'),
(1, '2020-01-12', '2020-02-28'),
(2, '2020-02-09', '2020-03-03');
DECLARE @StartDate DATE,
@EndDate DATE;
SELECT @StartDate = MIN(VisitsFromDate)
, @EndDate = MAX(VisitsToDate)
FROM Visits;
-- populate calendar table
WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO Calendar(CalendarDate) VALUES (@StartDate);
SET @StartDate = DATEADD(day,1,@StartDate);
END
-- fill in Months and Years
UPDATE Calendar
SET MonthOfYear = MONTH(CalendarDate),
Yr = YEAR(CalendarDate);
Now that everything is set up, the answer is trivial.
SELECT
-- , v.VisitsFromDate
-- , v.VisitsToDate
-- , c.CalendarDate
c.Yr
, c.MonthOfYear
, COUNT(v.VisitID) AS VisitDays
FROM Visits v
INNER JOIN Calendar c
ON c.CalendarDate>=v.VisitsFromDate
AND c.CalendarDate <= v.VisitsToDate
GROUP BY
c.Yr
, c.MonthOfYear
ORDER BY
c.Yr
, c.MonthOfYear;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply