SQL: How this sql is working

Question

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

Answer 1

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.

Answer 2

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.