Stored procedure with datetime

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
)

This table has the model, risk, and created date.

I have tried to write a stored procedure with a few conditions like

1. 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:

2. 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 :

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?

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.

DECLARE @startDate DateTime = '2023-04-01'
DECLARE @endDate DateTime = '2023-05-30'
DECLARE @model varchar(50) =  'IDP'  -- or replace with a model name 'Saa'
-- CREATE 3 distinct tables for RiskNames, Modules and MonthsBetween start and end date 
--  with the intention to CREATE a CARTESIAN PRODUCT out of them.
--The RISKS table also contains the ORDER which will be used in the Order by Clause of the final SELECT
DECLARE @distinctRisks Table (RiskName varchar(50), RiskOrder int)
INSERT INTO @distinctRisks (RiskName, RiskOrder)
SELECT 'Low', 1
UNION SELECT 'Medium', 2
UNION SELECT 'High', 3
UNION SELECT 'Critical',4
-- Modules table contains all the possible modules (or models.. this is not very clear for me)
DECLARE @distinctModules Table (ModuleName varchar(50))
INSERT INTO @distinctModules (ModuleName)
SELECT DISTINCT ModuleName from dbo.RiskTable
-- Months table contains all the months between start and end date. 
-- This time I've included the Year as well helps to accomodate the case when the search spans on multiple years
DECLARE @distinctMonths Table (MonthAndYear varchar(10)) 
;with months (date)
AS
(
SELECT @startDate
UNION ALL
SELECT DATEADD(month, 1, date)
from months
where DATEADD(month, 1, date) <= @endDate
)
INSERT INTO @distinctMonths (MonthAndYear)
SELECT FORMAT(date, 'MM-yyyy') 
from months
-- Now use the 3 tables and create a cartesian product Risks X Modules X Months
DECLARE @distinctRisksModulesAndMonths Table 
(
RiskName nvarchar(50), 
RiskOrder int,
ModuleName nvarchar(50), 
MonthAndYear varchar(10)
)
INSERT INTO @distinctRisksModulesAndMonths (RiskName, RiskOrder, ModuleName, MonthAndYear)
SELECT r.RiskName, r.RiskOrder, m.ModuleName, my.MonthAndYear 
FROM @distinctRisks r
CROSS JOIN @distinctModules m
CROSS JOIN @distinctMonths my
-- This is the table that will contain the results from the original select
DECLARE @values Table 
(
ModuleName nvarchar(50), 
RiskName nvarchar(50), 
MonthAndYear varchar(10),
RiskCount int
)
insert into @values(ModuleName, RiskName, MonthAndYear, RiskCount )
SELECT 
ModuleName,
AssociatedRisk,
FORMAT(CreatedDate, 'MM-yyyy'),
COUNT(1) as RiskCount
FROM [dbo].[RiskTable] 
WHERE (@model is null or ModuleName = @model)
AND @startDate <= CreatedDate and CreatedDate <= @endDate
GROUP BY FORMAT(CreatedDate, 'MM-yyyy'), AssociatedRisk, ModuleName
select * from @values
--FINAL RESULT
SELECT
m.ModuleName,
m.RiskName,
m.MonthAndYear,
ISNULL(c.RiskCount, 0)
FROM @distinctRisksModulesAndMonths m
LEFT JOIN @values c 
on c.RiskName = m.RiskName 
and c.ModuleName = m.ModuleName
and c.MonthAndYear = m.MonthAndYear
where @model is null or m.ModuleName = @model
order by MonthAndYear, RiskOrder

You can transform this to a stored procedure:

CREATE PROCEDURE GetRiskPerMonth
(
@startDate DateTime,
@endDate DateTime,
@model varchar(50) =  NULL
)
AS
BEGIN
SELECT ...
END

Then call it with

Exec GetRiskPerMonth '2023-04-01', '2023-04-30', 'IDP'

or just

Exec GetRiskPerMonth '2023-04-01', '2023-04-30'

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.

CREATE PROCEDURE Month_base_global
@model varchar(20),   
@start_date date,
@end_date date
AS
IF @model = 'ALL'
BEGIN
SELECT 'ALL' as [Module], ac.[Month], ac.AssociatedRisk as Risk, count(ri.ModuleName) as 'Total risks'
FROM (
SELECT DISTINCT convert(varchar(7), r.CreatedDate, 126) as [month], a.AssociatedRisk, m.ModuleName
FROM RiskTable r
FULL OUTER JOIN (
SELECT DISTINCT AssociatedRisk
FROM RiskTable
) as a on a.AssociatedRisk is not null
FULL OUTER JOIN (
SELECT DISTINCT ModuleName
FROM RiskTable
) as m on m.ModuleName is not null
WHERE MONTH(r.CreatedDate) between MONTH(@start_date) and MONTH(@end_date) 
) as ac
LEFT JOIN RiskTable ri on ac.[month] = convert(varchar(7), ri.CreatedDate, 126)
and ac.AssociatedRisk = ri.AssociatedRisk
and ac.ModuleName = ri.ModuleName
GROUP BY ac.[month], ac.AssociatedRisk
ORDER BY ac.[month], ac.AssociatedRisk desc
END
ELSE
BEGIN
SELECT @model as [Module], ac.[Month], ac.AssociatedRisk as Risk, count(ri.ModuleName) as 'Total risks'
FROM (
SELECT DISTINCT convert(varchar(7), r.CreatedDate, 126) as [month], a.AssociatedRisk, m.ModuleName
FROM RiskTable r
FULL OUTER JOIN (
SELECT DISTINCT AssociatedRisk
FROM RiskTable
) as a on a.AssociatedRisk is not null
FULL OUTER JOIN (
SELECT DISTINCT ModuleName
FROM RiskTable
) as m on m.ModuleName = @model
WHERE r.CreatedDate between DATEADD(mm, DATEDIFF(mm, 0, @start_date), 0) and eomonth(@end_date)
) as ac
LEFT JOIN RiskTable ri on ac.[month] = convert(varchar(7), ri.CreatedDate, 126)
and ac.AssociatedRisk = ri.AssociatedRisk
and ac.ModuleName = ri.ModuleName
GROUP BY ac.ModuleName, ac.[month], ac.AssociatedRisk
ORDER BY ac.[month], ac.AssociatedRisk desc
END

Demo here