SQL Server Stored procedure with datetime

ct3nt3jp  于 2023-05-16  发布在  其他
关注(0)|答案(2)|浏览(124)

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
)
ModelAssociatedRiskCreated Date
IDPHigh2022-12-18 00:00:00.0000000
SaaHigh2023-01-02 00:00:00.0000000
SaaMedium2023-01-07 00:00:00.0000000
SaaCritical2023-01-29 00:00:00.0000000
SaaLow2023-02-05 00:00:00.0000000
SaaLow2023-02-07 00:00:00.0000000
EEECritical2023-03-09 00:00:00.0000000
InfoLow2023-04-08 00:00:00.0000000
IDPHigh2023-04-28 00:00:00.0000000
IDPMedium2023-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

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

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

nuypyhwy

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.

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'
kwvwclae

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.

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

相关问题