SQL: How this sql is working

T.Zacks 3,986 Reputation points
2022-03-20T18:48:38.94+00:00

here i am giving sql with sample data. please help me to understand how this sql is working.

CREATE TABLE [dbo].[tblCalenderDetail](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [MasterID] [int] NULL,
  [Period] [varchar](30) NULL,
  [IsDeleted] [char](1) NULL,
  [IsActual] [char](1) NULL,
  CONSTRAINT [PK_tblCalenderDetail] PRIMARY KEY CLUSTERED 
 (
  [ID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO

 ALTER TABLE [dbo].[tblCalenderDetail] ADD  CONSTRAINT [DF_tblCalenderDetail_IsDeleted]  DEFAULT ('N') FOR [IsDeleted]
 GO

insert into tblCalenderDetail(MasterID,Period,IsDeleted,IsActual)
 values(1,'1Q 2017','N','N'),
 (1,'2Q 2017','N','N'),
 (1,'3Q 2017','N','N'),
 (1,'4Q 2017','N','N'),
 (1,'3Q 2017','N','N'),
 (1,'2017 FY','N','N'),
 (1,'1Q 2018','N','N'),
 (1,'2Q 2018','N','N'),
 (1,'3Q 2018','N','N'),
 (1,'4Q 2018','N','N'),
 (1,'2018 FY','N','N'),
 (1,'1Q 2019','N','N'),
 (1,'2Q 2019','N','Y'),
 (1,'3Q 2019','N','N'),
 (1,'4Q 2019','N','N'),
 (1,'2019 FY','N','N'),
 (1,'1Q 2020','N','N'),
 (1,'2Q 2020','N','N'),
 (1,'3Q 2020','N','N'),
 (1,'4Q 2020','N','N'),
 (1,'2020 FY','N','N'),
 (1,'1Q 2021','N','N'),
 (1,'2Q 2021','N','N'),
 (1,'3Q 2021','N','N'),
 (1,'4Q 2021','N','N'),
 (1,'2021 FY','N','N')


;with Q
as (select *,
           dense_rank() over (order by y,
                                       charindex('FY', Period),
                                       charindex('2H', Period),
                                       charindex('4Q', Period),
                                       charindex('3Q', Period),
                                       charindex('1H', Period),
                                       charindex('2Q', Period),
                                       charindex('1Q', Period)
                             ) r
    from tblCalenderDetail
        cross apply
    (
        values (substring(Period, patindex('%[1-9][0-9][0-9][0-9]%', Period), 4))
    ) Y (y)
   )
select q1.Period,
       q1.Period + iif(q1.r <= q2.r, 'A', 'E') as NewPeriod
from Q q1
    inner join Q q2
        on q2.Period = '2Q 2019'
order by q1.y,
         q1.r

1) why patindex('%[1-9][0-9][0-9][0-9]%', Period) return only 4 & 1 ?
2) why four [0-9] bracket is there in patindex() with 0-9 ? one or two would be enough
3) why first option is [1-9] and rest is [0-9] ?

4) how below sql is working not clear. please help me.

 select q1.Period, q1.Period + iif(q1.r <= q2.r, 'A', 'E') as NewPeriod
 from Q q1 inner join Q q2 on q2.Period = '2Q 2019'
 order by q1.y, q1.r

Looking for help. thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,895 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 102.3K Reputation points
    2022-03-20T19:38:00.21+00:00

    1) why patindex('%[1-9][0-9][0-9][0-9]%', Period) return only 4 & 1 ?

    The pattern looks for a sequence of four digits, of which the first digits must not be 0. In your sample data, the year starts in either position 1 or 4.

     2) why four [0-9] bracket is there in patindex() with 0-9 ? one or two would be enough

    In the light of my answer to your first question, why would one or two be
    enough?

    3) why first option is [1-9] and rest is [0-9] ?

    There is something in the business logic, I guess. And I am not familiar with the underlying requirements. But presumably the programmer thought that if there is a sequence like 0898 it is not a year, and the programmer did not want to be involved.

    4) how below sql is working not clear. please help me.

    The query may be somewhat easier to understand if we rewrite as:

    select q1.Period, 
           NewPeriod = q1.Period + 
                 CASE WHEN q1.r <= (SELECT r FROM Q WHERE Period = '2Q 2019') THEN 'A' ELSE 'E' END
    from   Q q1 
    order by q1.y, q1.r
    

    The idea is apparently that if the period is before the second quarter of 2019, we should tack on A, else we should tack on E. Don't ask me why.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Bert Zhou-msft 3,421 Reputation points
    2022-03-21T03:24:20.473+00:00

    Hi,@T.Zacks

    Welcome to Microsoft T-SQL Q&A Forum!

    1) why patindex('%[1-9][0-9][0-9][0-9]%', Period) return only 4 & 1 ?
    First of all, the regular expression in parentheses is to find the number of 4 characters in the year column of period. It returns 4 or 1 because the year is searched from 1-4, which corresponds to 1-----year in the table , 2----year, ......4----year.

    2) why four [0-9] bracket is there in patindex() with 0-9 ? one or two would be enough
    I don't think it has any effect. The reason why 4 [] is set here may be because the developer has other needs.

    3) why first option is [1-9] and rest is [0-9] ?
    According to the data in the original table, it is speculated that the year cannot start with 0. For example, we cannot say that it is Month 0 of 2017, which is close to the actual meaning of life.

    4) how below sql is working
    The iif in the function is a logical function. If q1.r <= q2.r is satisfied, add a to period as a new column, e is the same, charindex('2H', Period), charindex('1H', Period) , these two search locations do not make any sense to me, you can refer to the case written by experts when it is usaged, achieves the same result as the source code.

    Best regards,
    Bert Zhou


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments