SQL Server Stored procedure with datetime condition

ftf50wuq  于 2023-05-16  发布在  其他
关注(0)|答案(1)|浏览(104)

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

I have a stored procedure that satisfies the following condition:

  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'.
  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. output I'm getting for my previous condition: | 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 |
CREATE PROCEDURE Month_base_global
                @model varchar(20),   
                @start_date date,
                @end_date date
    AS
    BEGIN
    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 (r.CreatedDate) between (@start_date) and (@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
    END
    Exec Month_base_global 'ALL' , '2022-02-01', '2023-01-28'

Now I have few more condition Exec Month_base_global 'ALL' , null, '2023-01-28'

3.If Model = ALL or any particular model like 'Saa' or 'IDA' or anything, then Start_date = null and end_date = 2023-01-28. It has to fetch all the records from the start in the table of that particular model or ALL.

Exec Month_base_global 'ALL' , '2022-12-03', null

4.If If Model = ALL or any particular model like 'Saa' or 'IDA' or anything, then Start_date = 2022-12-03 and end_date = null means it has to show the records from that particular start_date to the till date(uptodate).

Exec Month_base_global 'ALL' , null, null

5.If Model = ALL or any particular model like 'Saa' or 'IDA' or anything, start_date = null, end_date = null means it has to show all the records from the start of the createddate to the till date(uptodate).

can someone help me with this? Thanks

sql-server

1条答案

按热度按时间
wwtsj6pe

wwtsj6pe1#

You can accomplish this by Initializing start date to a previous date, such as '1970-01-01' and end date to '2030-12-31' :

CREATE PROCEDURE Month_base_global
                @model varchar(20),   
                @p_start_date date,
                @p_end_date date
AS
BEGIN
DECLARE @start_date date;
DECLARE @end_date date;
    SET @start_date = @p_start_date ;
    SET @end_date = @p_end_date ;
    IF (@p_start_date IS NULL)
    BEGIN
       SET @start_date = '1970-01-01';
    END

    IF (@p_end_date IS NULL)
    BEGIN
       SET @end_date = '2030-12-31';
    END

    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 (r.CreatedDate) between (@start_date) and (@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
END

Demo here

赞(0）分享  回复(0）举报  2023-05-16
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com