I have created a table with the following set of data
CREATE TABLE [dbo].[RiskTable]
(
[ReqID] [int] NOT NULL,
[ModuleName] [nvarchar](50) NULL,
[CreatedDate] [datetime2](7) NULL,
[AssociatedRisk] [nvarchar](50) NULL
)
Model | AssociatedRisk | Created Date |
---|---|---|
IDP | High | 2022-12-18 00:00:00.0000000 |
Saa | High | 2023-01-02 00:00:00.0000000 |
Saa | Medium | 2023-01-07 00:00:00.0000000 |
Saa | Critical | 2023-01-29 00:00:00.0000000 |
Saa | Low | 2023-02-05 00:00:00.0000000 |
Saa | Low | 2023-02-07 00:00:00.0000000 |
EEE | Critical | 2023-03-09 00:00:00.0000000 |
Info | Low | 2023-04-08 00:00:00.0000000 |
IDP | High | 2023-04-28 00:00:00.0000000 |
IDP | Medium | 2023-05-08 00:00:00.0000000 |
This table has the model, risk, and created date.
I have tried to write a stored procedure with a few conditions like
- If I'm giving model = saa, start_date = 2023-01-01, end_date = 2023-02-10, I want it to show me the total number of risks (low, Medium, High, and Critical) of those particular months (2023-01-01(Jan), 2023-02-10(Feb)) of the model 'saa'.
It has to fetch me data like this:
| Model | Month | Risk | Total no. of risk |
| ------------ | ------------ | ------------ | ------------ |
| Saa | Jan or 01 | Low | 0 |
| Saa | Jan or 01 | Medium | 1 |
| Saa | Jan or 01 | High | 1 |
| Saa | Jan or 01 | Critical | 1 |
| Saa | Feb or 02 | Low | 2 |
| Saa | Feb or 02 | Medium | 0 |
| Saa | Feb or 02 | High | 0 |
| Saa | Feb or 02 | Critical | 0 |
- If I'm giving model = Null, start_date = 2023-04-01, end_date = 2023-05-11, I want it to show me the total number of risks (low, Medium, High, and Critical) of those particular months (2023-04-01(Apr), 2023-05-11(May)) of all the models.
It has to fetch me data like this :
| Model | Month | Risk | Total no. of risk |
| ------------ | ------------ | ------------ | ------------ |
| All | Apr or 04 | Low | 1 |
| All | Apr or 04 | Medium | 0 |
| All | Apr or 04 | High | 1 |
| All | Apr or 04 | Critical | 0 |
| All | May or 05 | Low | 0 |
| All | May or 05 | Medium | 1 |
| All | May or 05 | High | 0 |
| All | May or 05 | Critical | 0 |
I tried to write 2 stored procedures:
CREATE PROCEDURE Month_base
AS
SELECT
MONTH(CreatedDate) MONTH,
COUNT(CASE WHEN AssociatedRisk = 'Low' THEN 1 END) AS Low,
COUNT(CASE WHEN AssociatedRisk = 'Medium' THEN 1 END) AS Medium,
COUNT(CASE WHEN AssociatedRisk = 'High' THEN 1 END) AS High,
COUNT(CASE WHEN AssociatedRisk = 'Critical' THEN 1 END) AS Critical
FROM
RiskTable
WHERE
YEAR(CreatedDate) ='2023'
GROUP BY
MONTH(CreatedDate)
EXEC Month_base
Next stored procedure with an if
condition.
ALTER PROCEDURE avgg_calc
@model_name nvarchar(max) = NULL,
@start_date datetime2 = NULL,
@end_date datetime2 = NULL
AS
DECLARE @sql nvarchar(max);
-- have to call another stored procedure and create temporary table
SET @sql = 'WHERE 1 = 1';
IF @model_name IS NOT NULL
SET @sql += '
AND Model = @_model_name';
IF @start_date IS NOT NULL
SET @sql += '
AND CreatedDate >= @_start_date';
IF @end_date IS NOT NULL
SET @sql += '
AND CreatedDate < @_end_date';
PRINT @sql;
This is what I have tried, it feels like I'm on the wrong track. I have to use while
to get it, but I exactly don't know how to write a query for these conditions.
Can someone help me in writing a stored procedure query including these conditions?
2条答案
按热度按时间nuypyhwy1#
EDIT: I've updated my script according to the requirements. I've added some comments inline. I think that using temporary tables makes it clearer.
You can transform this to a stored procedure:
Then call it with
or just
kwvwclae2#
This query will handle both cases, if
ALL
is passed, the proc will retrieve data grouped by month and risk, and if a module is passed, the result will be grouped by month, risk, and module.Demo here