Trying to pull data from two separate timeframes

Transact-SQL
1

I have a query where I am trying to pull the count from 3 columns (asVoluntary, Voluntary Met, and Voluntary Time) based on the time frame 07-01-2018 through 07-31-2018 which works just fine. With the other column (TotalVoluntaryCasesStillOpen) I am trying to only pull the count from this column only when the column is NULL no matter the date. What the query is currently pulling is all the rows that are NULL during the time period between 07-01-2018 through 07-31-2018. I am assuming that I am needing an additional SELECT statement within the WHERE clause but am needing some assistance with regards to this. Is anyone able to provide what I am trying to do?

Thanks
Deanna

select a.FullName as Employee,
case when d.VolInfo is null then 0 else d.VolInfo end asVoluntary,
case when e.VolMet is null then 0 else e.VolMet end as [Voluntary Met],
case when d.VolTime is null then 0 else d.VolTime end as [Voluntary Time],
--case when e.VolExc is null then 0 else e.VolExc end as VolExcMet,
case when e.TotalVoluntaryCasesStillOpened is null then 1 else e.TotalVoluntaryCasesStillOpened end as TotalVoluntaryCasesStillOpened,

from
(
SELECT     SystemUserId, FullName
FROM         SystemUserBase
)a
left outer join
(

SELECT     
	     COUNT(Custom_retirementExtensionBase.Custom_name) AS [VolInfo], 
	     Avg(dbo.fx_TotalWorkdays(Custom_retirementExtensionBase.Custom_AllCompletedDocsRcvd,Custom_retirementExtensionBase.Custom_retirementpkgcompleted)) AS VolTime,
	     COUNT(CASE WHEN Custom_retirementExtensionBase.Custom_retirementpkgcompleted IS NULL THEN 1 ELSE 0 END) AS TotalVoluntaryCasesStillOpened,
	   --or Custom_retirementExtensionBase.Custom_retirementpkgcompleted >= '01-01-2010' THEN 1 ELSE 0 END) AS TotalVoluntaryCasesStillOpened,
	     SystemUserBase.SystemUserId
                                                                                                  
FROM         
	     Custom_retirementBase INNER JOIN
             Custom_retirementExtensionBase ON 
             Custom_retirementBase.Custom_retirementId = Custom_retirementExtensionBase.Custom_retirementId INNER JOIN
             StringMap ON Custom_retirementExtensionBase.Custom_TypeofRequest = StringMap.AttributeValue INNER JOIN
             IncidentBase ON Custom_retirementExtensionBase.Custom_RetirementDataId = IncidentBase.IncidentId INNER JOIN
             SystemUserBase ON Custom_retirementBase.OwnerId = SystemUserBase.SystemUserId
WHERE                 
	     ((StringMap.AttributeName = N'custom_typeofrequest') and 
	     AttributeValue = 9) and 
	     (SystemUserBase.FullName IN('Joe Bob')) AND
             (StringMap.Value = 'Voluntary Retirement') AND
	     Custom_retirementExtensionBase.Custom_retirementpkgcompleted >= '07-01-2018' and Custom_retirementExtensionBase.Custom_retirementpkgcompleted <= '07-31-2018'
																
																
GROUP BY SystemUserBase.FullName, SystemUserBase.SystemUserId


)d
on a.SystemUserId=d.SystemUserId
left outer join
(

SELECT     

	     COUNT(CASE WHEN dbo.fx_TotalWorkdays(Custom_retirementExtensionBase.Custom_AllCompletedDocsRcvd,Custom_retirementExtensionBase.Custom_retirementpkgcompleted) <=10 THEN 1 ELSE 0 END) AS VolMet, 
	   --COUNT(CASE WHEN dbo.fx_TotalWorkdays(Custom_retirementExtensionBase.Custom_AllCompletedDocsRcvd,Custom_retirementExtensionBase.Custom_retirementpkgcompleted) <=5 THEN 1 ELSE 0 END) AS VolExc, 
	     COUNT(CASE WHEN Custom_retirementExtensionBase.Custom_retirementpkgcompleted IS NULL THEN 1 ELSE 0 END) AS TotalVoluntaryCasesStillOpened,
	   --or Custom_retirementExtensionBase.Custom_retirementpkgcompleted >= '01-01-2010' THEN 1 ELSE 0 END) AS TotalVoluntaryCasesStillOpened,
	     SystemUserBase.SystemUserId
                                                                                                  
FROM
		         
	     Custom_retirementBase INNER JOIN
             Custom_retirementExtensionBase ON 
             Custom_retirementBase.Custom_retirementId = Custom_retirementExtensionBase.Custom_retirementId INNER JOIN
             StringMap ON Custom_retirementExtensionBase.Custom_TypeofRequest = StringMap.AttributeValue INNER JOIN
             IncidentBase ON Custom_retirementExtensionBase.Custom_RetirementDataId = IncidentBase.IncidentId INNER JOIN
             SystemUserBase ON Custom_retirementBase.OwnerId = SystemUserBase.SystemUserId
WHERE                 

	     ((StringMap.AttributeName = N'custom_typeofrequest') and AttributeValue = 9) and
             (SystemUserBase.FullName IN('Joe Bob')) AND
             (StringMap.Value = 'Voluntary Retirement') AND
             Custom_retirementExtensionBase.Custom_retirementpkgcompleted >= '07-01-2018' and Custom_retirementExtensionBase.Custom_retirementpkgcompleted <= '07-31-2018'
																
																

																
GROUP BY  SystemUserBase.FullName,SystemUserBase.SystemUserId

)e
on a.SystemUserId=e.SystemUserId

Data:

Employee: Joe Bob
asVoluntary: 3
Voluntary Met: 3
Voluntary Time: 1
TotalVoluntaryCasesStillOpened:

2

Hi Deanna,
You will be much more likely to receive help if you do the typing. Include create table statements and inserts statements with sample data and expected result using the sample data you post.

1 Like