存储过程带有日期时间。

huangapple go评论66阅读模式
英文:

Stored procedure with datetime

问题

我创建了一个带有以下数据集的表格

创建表 [dbo].[RiskTable]
(
    [ReqID] [int] NOT NULL,
    [ModuleName] [nvarchar](50) NULL,
    [CreatedDate] [datetime2](7) NULL,
    [AssociatedRisk] [nvarchar](50) NULL
)
模型 关联风险 创建日期
IDP 2022-12-18 00:00:00.0000000
Saa 2023-01-02 00:00:00.0000000
Saa 2023-01-07 00:00:00.0000000
Saa 严重 2023-01-29 00:00:00.0000000
Saa 2023-02-05 00:00:00.0000000
Saa 2023-02-07 00:00:00.0000000
EEE 严重 2023-03-09 00:00:00.0000000
信息 2023-04-08 00:00:00.0000000
IDP 2023-04-28 00:00:00.0000000
IDP 2023-05-08 00:00:00.0000000

此表包含模型、风险和创建日期。

我尝试编写了一个存储过程,其中包含一些条件。

  1. 如果我给定模型 = saa,start_date = 2023-01-01,end_date = 2023-02-10,则希望显示该模型“saa”在这些特定月份(2023-01-01(一月),2023-02-10(二月))的所有风险(低、中、高和严重)的总数。

它必须为我提取如下数据:

模型 月份 风险 风险总数
Saa 一月或 01 0
Saa 一月或 01 1
Saa 一月或 01 1
Saa 一月或 01 严重 1
Saa 二月或 02 2
Saa 二月或 02 0
Saa 二月或 02 0
Saa 二月或 02 严重 0
  1. 如果我给定模型 = Null,start_date = 2023-04-01,end_date = 2023-05-11,则希望显示所有模型在这些特定月份(2023-04-01(四月),2023-05-11(五月))的所有风险(低、中、高和严重)的总数。

它必须为我提取如下数据:

模型 月份 风险 风险总数
所有 四月或 04 1
所有 四月或 04 0
所有 四月或 04 1
所有 四月或 04 严重 0
所有 五月或 05 0
所有 五月或 05 1
所有 五月或 05 0
所有 五月或 05 严重 0

我尝试编写了两个存储过程:

创建存储过程 Month_base
AS
    SELECT 
        月份(CreatedDate) 月份,          
        COUNT(CASE WHEN AssociatedRisk = '低' THEN 1 END) AS ,
        COUNT(CASE WHEN AssociatedRisk = '中' THEN 1 END) AS , 
        COUNT(CASE WHEN AssociatedRisk = '高' THEN 1 END) AS , 
        COUNT(CASE WHEN AssociatedRisk = '严重' THEN 1 END) AS 严重
    FROM      
        RiskTable 
    WHERE
        年份(CreatedDate) ='2023'
    GROUP BY
        月份(CreatedDate) 

执行 Month_base

接下来的存储过程带有一个 if 条件。

ALTER PROCEDURE avgg_calc
    @model_name nvarchar(max) = NULL,
    @start_date datetime2 = NULL,
    @end_date datetime2 = NULL
AS
    DECLARE @sql nvarchar(max);

    -- 必须调用另一个存储过程并创建临时表
    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';

    打印 @sql;  

这就是我尝试过的,感觉走错了方向。我必须使用 while 来解决这个问题,但我不确定如何为这些条件编写查询。

可以有人帮我编写一个包括这些条件的存储过程查询吗?

英文:

I have created a table with the following set of data

CREATE TABLE [dbo].[RiskTable]
(
&#160;&#160;&#160; [ReqID] [int] NOT NULL,
&#160;&#160;&#160; [ModuleName] [nvarchar](50) NULL,
&#160;&#160;&#160; [CreatedDate] [datetime2](7) NULL,
&#160;&#160;&#160; [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

  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 = &#39;Low&#39; THEN 1 END) AS Low,
        COUNT(CASE WHEN AssociatedRisk = &#39;Medium&#39; THEN 1 END) AS Medium, 
        COUNT(CASE WHEN AssociatedRisk = &#39;High&#39; THEN 1 END) AS High, 
        COUNT(CASE WHEN AssociatedRisk = &#39;Critical&#39; THEN 1 END) AS Critical
    FROM      
        RiskTable 
    WHERE
        YEAR(CreatedDate) =&#39;2023&#39;
    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 = &#39;WHERE 1 = 1&#39;;

    IF @model_name IS NOT NULL
        SET @sql += &#39;
    AND Model = @_model_name&#39;;

    IF @start_date IS NOT NULL
        SET @sql += &#39;
    AND CreatedDate &gt;= @_start_date&#39;;

    IF @end_date IS NOT NULL
        SET @sql += &#39;
    AND CreatedDate &lt; @_end_date&#39;;

    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?

答案1

得分: 1

Here is the translated code:

**EDIT:** 我根据要求更新了我的脚本。我内联添加了一些注释。我认为使用临时表会更清晰。

DECLARE @startDate DateTime = '2023-04-01'
DECLARE @endDate DateTime = '2023-05-30'
DECLARE @model varchar(50) = 'IDP' -- 或者替换为模型名称 'Saa'

-- 创建3个不同的表,用于RiskNames、Modules和MonthsBetween开始和结束日期之间的所有月份,以创建它们的笛卡尔积。

-- RISKS表还包含将在最终SELECT的ORDER BY子句中使用的ORDER。
DECLARE @distinctRisks Table (RiskName varchar(50), RiskOrder int)
INSERT INTO @distinctRisks (RiskName, RiskOrder)
SELECT '低', 1
UNION SELECT '中', 2
UNION SELECT '高', 3
UNION SELECT '关键', 4

-- Modules表包含所有可能的模块(或模型.. 对我来说不太清楚)
DECLARE @distinctModules Table (ModuleName varchar(50))
INSERT INTO @distinctModules (ModuleName)
SELECT DISTINCT ModuleName from dbo.RiskTable

-- Months表包含开始和结束日期之间的所有月份。 这次我还包括了年份,以适应搜索跨越多年的情况。
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

-- 现在使用这3个表并创建一个Risk 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

-- 这是包含原始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

-- 最终结果
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

你可以将此代码转换为存储过程:

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

BEGIN
  SELECT ...

END

然后使用以下方式调用它:

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

或者只需

Exec GetRiskPerMonth '2023-04-01', '2023-04-30'
英文:

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 = &#39;2023-04-01&#39;
DECLARE @endDate DateTime = &#39;2023-05-30&#39;
DECLARE @model varchar(50) =  &#39;IDP&#39;  -- or replace with a model name &#39;Saa&#39;
-- 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 &#39;Low&#39;, 1
UNION SELECT &#39;Medium&#39;, 2
UNION SELECT &#39;High&#39;, 3
UNION SELECT &#39;Critical&#39;,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&#39;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) &lt;= @endDate
)
INSERT INTO @distinctMonths (MonthAndYear)
SELECT FORMAT(date, &#39;MM-yyyy&#39;) 
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, &#39;MM-yyyy&#39;),
COUNT(1) as RiskCount
FROM [dbo].[RiskTable] 
WHERE (@model is null or ModuleName = @model)
AND @startDate &lt;= CreatedDate and CreatedDate &lt;= @endDate
GROUP BY FORMAT(CreatedDate, &#39;MM-yyyy&#39;), 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 &#39;2023-04-01&#39;, &#39;2023-04-30&#39;, &#39;IDP&#39;

or just

Exec GetRiskPerMonth &#39;2023-04-01&#39;, &#39;2023-04-30&#39;

答案2

得分: 0

这个查询将处理两种情况,如果传入ALL,存储过程将检索按月份和风险分组的数据,如果传入模块,结果将按月份、风险和模块分组。

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
        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
        GROUP BY ac.ModuleName, ac.[month], ac.AssociatedRisk
        ORDER BY ac.[month], ac.AssociatedRisk desc
    END

在此处查看演示

英文:

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 = &#39;ALL&#39;
BEGIN
SELECT &#39;ALL&#39; as [Module], ac.[Month], ac.AssociatedRisk as Risk, count(ri.ModuleName) as &#39;Total risks&#39;
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 &#39;Total risks&#39;
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

huangapple
  • 本文由 发表于 2023年5月14日 16:51:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/76246623.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定