To show the count per month from the date provided

  • VSSGeorge

    SSCrazy Eights

    Points: 8144

    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

    • This topic was modified 1 month ago by  VSSGeorge.
  • pietlinden

    SSC Guru

    Points: 62715

    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?

  • pietlinden

    SSC Guru

    Points: 62715

    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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply