The table contains the following fields: ProjectNumber, DateCreated and DateKickedOff.
When a project is "created", the date it was created is entered into the DateCreated field and the DateKickedOff field is left NULL.
When the project is "kicked off", the date it was kicked off is entered into the DateKickedOff field. This can be the same day it was created or days, weeks or months later.
So at any given time, there are a number of projects "created", but not yet "kicked off" - IE. projects "in queue".
Identifying how many projects were "in queue" on a specific day is easy.
select count(*) from projects where Date_Created <= [any_date] and (DateKickedOff is NULL or DateKickedOff > [any_date])
However, what I am looking to do is create a query that will allow me to chart the history of how many projects were "in queue" on the last day of each month for a given time period.
Here's my best go at it without any usable data to test it:
DECLARE @start_date date
DECLARE @end_date date
SET @start_date = '20210101'
SET @end_date = '20210630'
--******************************************************************************
/* force @start_date to 1st of month and @end_date to last day of month */
SET @start_date = DATEADD(MONTH, DATEDIFF(MONTH, 0, @start_date), 0)
SET @end_date = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @end_date) + 1, 0))
;WITH
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
SELECT 0 AS number UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
),
cte_months AS (
SELECT DATEADD(MONTH, 0, @start_date) AS month
FROM cte_tally1000 t
WHERE t.number BETWEEN 0 AND DATEDIFF(MONTH, @start_date, @end_date)
)
SELECT
cm.month,
COUNT(*) AS in_queue_count
FROM dbo.your_data_table ydt
INNER JOIN cte_months cm ON ydt.DateKickedOff IS NULL OR
(ydt.DateKickedOff <= cm.month)
here is one way you can provide sample data. without sample data, it is just a guess. help us help you. you have access to your sql server, but we do not. sample data gives us a peek at the data you are working with.
declare @ragnorock table(ProjectNumber varchar(150),
DateCreated date,
DateKickedOff date)
insert into @ragnorock
select top 100 name,
dateadd(dd, object_id, getdate()),
case
when object_id % 2 = 0 then null
else dateadd(wk, object_id, getdate())
end
from sys.objects
where object_id between 1 and 31
select * from @ragnorock
Either I am not using your suggestion correctly, or it is not what I am looking for. Attached I have mocked up sample data and a sample result set. For clarification, I have highlighted in yellow the 4 rows that would be considered "in queue" as of the last day in March. 3 were NULL and one was kicked off after March 31.
My code was not correct. Using the sample data gen'd by @yosiasz (thanks!), I was able to adjust my code:
DECLARE @start_date date
DECLARE @end_date date
SET @start_date = '20210101'
SET @end_date = '20210630'
--******************************************************************************
/* force @start_date to 1st of month and @end_date to last day of month */
SET @start_date = DATEADD(MONTH, DATEDIFF(MONTH, 0, @start_date), 0)
SET @end_date = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @end_date) + 1, 0))
;WITH
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
SELECT 0 AS number UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
),
cte_months AS (
SELECT DATEADD(MONTH, t.number, @start_date) AS month
FROM cte_tally1000 t
WHERE t.number BETWEEN 0 AND DATEDIFF(MONTH, @start_date, @end_date)
)
SELECT
cm.month,
COUNT(*) AS in_queue_count
FROM cte_months cm
LEFT OUTER JOIN @ragnorock ydt ON
(ydt.DateKickedOff IS NULL OR
ydt.DateKickedOff >= DATEADD(MONTH, +1, cm.month))
WHERE
ydt.DateCreated >= @start_date AND
ydt.DateCreated < DATEADD(DAY, 1, @end_date)
GROUP BY cm.month
ORDER BY cm.month
so, again please provide your sample data not as an image but real usable sql script DDL and DML. We are not going to retype all of that for you on our side. Look at the sample script I provided, match it to your image you posted and post back here
