I am trying to run 3 different select query on 1 table where i return the count of created, done, pending tasks based on created_date and modified_date where i can differentiate the done from pending in modified_date based on the field status

when i run each query separately it return correct answer.

But what i want is to combine these 3 queries in one table as below:

 ----------------------  ------------------ --------------------- 
|  Total_created_files  | Total_done_files | Total_pending_files |
 ----------------------- ------------------ --------------------- 
|          14           |         40       |         9           | 
 ----------------------- ------------------ ---------------------           

code:

select 
count([create_date]) as Total_created_files
FROM [TEC_APP].[dbo].[case_to_do]
where  [create_date] >='2022-05-01 00:00:00.000'
AND
CAST([create_date] AS date) <=  CAST(GETDATE() AS date)

select 
count([modified_date]) as Total_done_files
FROM [TEC_APP].[dbo].[case_to_do]
where  [modified_date] >='2022-05-01 00:00:00.000'
AND
CAST([modified_date] AS date) <=  CAST(GETDATE() AS date)
AND
status = 'DONE'

select 
count([modified_date]) as Total_pending_files
FROM [TEC_APP].[dbo].[case_to_do]
where  [modified_date] >='2022-05-01 00:00:00.000'
AND
CAST([modified_date] AS date) <=  CAST(GETDATE() AS date)
AND
status = 'Pending'                                                  

CodePudding user response：

Use conditional aggregation with case expression:

SELECT SUM(case when <First Condition here> then 1 else 0 end) as Total_created_files,
       SUM(case when <Second Condition here> then 1 else 0 end) as Total_done_files,
       SUM(case when <Third Condition here> then 1 else 0 end) as Total_pending_files
FROM [TEC_APP].[dbo].[case_to_do]

CodePudding user response：

Use conditional aggregation to COUNT as needed. I make a couple of assumptions here, but this should be what you are after:

USE TEC_APP;
GO

SELECT COUNT(CASE WHEN [create_date] < DATEADD(DAY,1,CONVERT(date,GETDATE())) THEN 1 END) AS Total_created_files,
       COUNT(CASE WHEN [modified_date] >= '20220501' AND [modified_date] < DATEADD(DAY,1,CONVERT(date,GETDATE())) AND Status = 'DONE' THEN 1 END) AS Total_done_files,
       COUNT(CASE WHEN [modified_date] >= '20220501' AND [modified_date] < DATEADD(DAY,1,CONVERT(date,GETDATE())) AND Status = 'Pending' THEN 1 END) AS Total_pending_files
FROM [dbo].[case_to_do]
WHERE create_date >= >= '20220501'; --Presumably something can't be modified before it's created.