Help need to create a stored procedure using individual queries in a effective way

Transact-SQL
1

I have to frame a query to show the following results in a stored procedure as individual columns.

a) No.of studies from the year 2015 on wards.
b) No.of studies from the year 2015 on wards which are having no start date recorded.
c) No.of studies from the year 2015 on wards which are having no end date recorded.

I have individual queries like this. Just need your help and ideas to put inside a query in a stored procedure.

select COUNT(studycode) AS StudyCount from Studies where CAST(LEFT(DATEPART(yyyy, GETDATE()), 2)  + SUBSTRING(studyCode, 2, 2) AS NVARCHAR(30)) >= '2015'

select COUNT(studycode) AS StudyCount from Studies where studyStartDate IS NULL AND 
CAST(LEFT(DATEPART(yyyy, GETDATE()), 2)  + SUBSTRING(studyCode, 2, 2) AS NVARCHAR(30)) >= '2015'

select COUNT(studycode) AS StudyCount from Studies where studyEndDate IS NULL AND 
CAST(LEFT(DATEPART(yyyy, GETDATE()), 2)  + SUBSTRING(studyCode, 2, 2) AS NVARCHAR(30)) >= '2015'
2

I tried something like this. Is this the efficient way?

DECLARE @Results TABLE
(
    TotalStudiesCount INT,
	TotalStudiesCountScheStDtNull INT,
	TotalStudiesCountScheEndDtNull INT
)

INSERT INTO @Results

SELECT TotalStudiesCount = COUNT(studycode) from Studies where CAST(LEFT(DATEPART(yyyy, GETDATE()), 2)  + SUBSTRING(studyCode, 2, 2) AS NVARCHAR(30)) >= '2015',

TotalStudiesCountScheStDtNull = COUNT(studycode) from Studies where studyScheduledStartDate IS NULL AND 
CAST(LEFT(DATEPART(yyyy, GETDATE()), 2)  + SUBSTRING(studyCode, 2, 2) AS NVARCHAR(30)) >= '2015',

TotalStudiesCountScheEndDtNull = COUNT(studycode) from Studies where studyScheduledEndDate IS NULL AND 
CAST(LEFT(DATEPART(yyyy, GETDATE()), 2)  + SUBSTRING(studyCode, 2, 2) AS NVARCHAR(30)) >= '2015'
3

This query will read once from the table (and will work till the end of this century):

select COUNT(studycode)
      ,COUNT(CASE WHEN studyStartDate IS NULL then studycode ELSE NULL END)
      ,COUNT(CASE WHEN studyEndDate   IS NULL then studycode ELSE NULL END)
  from Studies
 where SUBSTRING(studyCode, 2, 2) >= '15'
;

What's the logic on the studyCode?

1 Like