-3

I am trying to calculate how many people are on a waiting list or still on the waiting list at the end of the month. Ex. If 5 people are waiting in Jan and then in Feb 3 people get added to the list and one person in Feb gets off the list then at the end of Feb my count should be 7. I do have people on the list for over 365 days. So I need month and year.

My dates are in mm/dd/yyy . variables are dtlist(date went on list), dtofflist (date they went off list).

Any help would be appreciated.

  • 1
    Please update your question to include what RDBMS you're using (SQL Server, Oracle, etc.). Also provide the table definition(s) and sample data in the form of actual INSERT statements and the desired results. – Scott Hodgin Aug 23 '18 at 20:51
  • So sorry. I am using SQL server Management Studio. I do have individual IDKEY for each person that has been put on the waiting list. Thank you for any assistance. – user159189 Aug 24 '18 at 12:46
  • What version of SQL Server are you using? (2012, 2014, 2016, 2017)? – Scott Hodgin Aug 24 '18 at 12:50
  • I am using 2017. – user159189 Aug 24 '18 at 12:55
  • If you could share the schema of tables included in your query, it would be really helpful. – UdIt Solanki Aug 24 '18 at 14:00
0

You didn't include any sample data in your question, so I made some up. You would need to test my solution against your actual data to determine if it will work for you.

My solution uses a couple of common table expressions and a window function in the final select. I commented various parts of the code in an attempt to explain what that section was doing. My sample data also has some comments to document what the TotalOnList would be after that row was taken into consideration.

--Demo setup
set nocount on 
Declare @WaitingList table
(
   IdKey integer not null,
   dtlist    date not null, 
   dtofflist date

);

insert into @WaitingList (IdKey, dtlist, dtofflist)
values

--                                                  TotalOnList
(1,'2018-01-01', null),         --added in January      1
(1,'2018-01-02', null),         --added in January      2
(1,'2018-01-02', null),         --added in January      3
(1,'2018-01-02', null),         --added in January      4
(1,'2018-01-02', null),         --added in January      5

(1,'2018-02-01', null),         --added in February     6
(1,'2018-02-01', null),         --added in February     7
(1,'2018-02-01', null),         --added in February     8
(1,'2018-01-01', '2018-02-01'), --Left in Febuary       7

(1,'2018-02-01', '2018-03-01'), --Left in March         6

(1,'2018-04-01', null)  --added in April                7
;

--The solution

--union Dates for DateOnList and DateOffList
--Increment/Decrement accordingly
;WITH FormattedYearAndMonth
AS (
    --Increment for rows where dtofflist is null - these are people added to the list
    SELECT convert(VARCHAR(4), year(dtlist)) + right(replicate('0', 2) + convert(VARCHAR(2), month(dtlist)), 2) AS YearAndMonth
        ,+ 1 AS IncrementDecrement
    FROM @WaitingList
    WHERE dtofflist IS NULL

    UNION ALL

    --Decrement for rows where dtofflist is NOT null - these are people deleted from the list
    SELECT convert(VARCHAR(4), year(dtofflist)) + right(replicate('0', 2) + convert(VARCHAR(2), month(dtofflist)), 2) AS YearAndMonth
        ,- 1 AS IncrementDecrement
    FROM @WaitingList
    WHERE dtofflist IS NOT NULL
    )
    --Aggregate NetIncreaseForMonth
    ,AggregateForYearAndMonth
AS (
    SELECT YearAndMonth
        ,sum(IncrementDecrement) AS NetIncreaseForMonth
    FROM FormattedYearAndMonth
    GROUP BY YearAndMonth
    )
SELECT YearAndMonth
    ,Sum(NetIncreaseForMonth) OVER (
        ORDER BY YearAndMonth
        ) AS TotalOnList
FROM AggregateForYearAndMonth
ORDER BY YearAndMonth

| YearAndMonth | TotalOnList |
|--------------|-------------|
| 201801       | 5           |
| 201802       | 7           |
| 201803       | 6           |
| 201804       | 7           |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.