SQLTeam.com | Weblogs | Forums

Help writing a Query

I have a table that tracks projects.

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.

Thank you in advance!!

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)
1 Like

Well, at least it wasn't something easy and obvious... I will try this out and let you know how I make out. Thank you so much.

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
1 Like

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
2 Likes

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

1 Like

Thank you so much for your help @ScottPletcher!! This is awesome!

I had to tweak the WHERE clause a little bit to make it work exactly how I wanted it to.

See below...

WHERE
(ydt.DateKickedOff >= cm.month or ydt.DateKickedOff is NULL) AND
ydt.DateCreated < EOMONTH(cm.month)

sorry @yosiasz ... i will do better next time.

1 Like