Share via

SQL Server How to conditionally attached value with table field

T.Zacks 3,986 Reputation points
2022-03-18T08:14:47.897+00:00

HERE is table structure with sample data provided to reproduce this issue in any PC. 

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

SAMPLE DATA 

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

my requirement is to concat A or E with Period field IsActual is set Y for 2Q 2019 and N for rest of the values.

so i need to concat A with Period values which is lower period than 2Q 2019 and also 2Q 2019 will have A

i need to concat E with Period values which is upper period than 2Q 2019. i have done this job this way but looking for better approach. please see my code which is giving right output. 

  DROP TABLE IF EXISTS #tmpPeriodAll  
  DECLARE @PeriodOrder INT  
  SET @PeriodOrder=0  
  
  Select Distinct A.* ,   
  Convert(numeric, case when A.Period like '%1Q%' then  ltrim(rtrim(REPLACE(A.Period,'1Q','')))+cast( 0 as varchar)      
     when A.Period like '%2Q%'  then ltrim(rtrim(REPLACE(A.Period,'2Q','')))+cast( 1 as varchar)      
     when A.Period like '%1H%'  then ltrim(rtrim(REPLACE(A.Period,'1H','')))+cast( 2 as varchar)      
     when A.Period like '%3Q%' then  ltrim(rtrim(REPLACE(A.Period,'3Q','')))+cast( 3 as varchar)      
     when (A.Period like '%4Q%' And A.Period not like '%53 Weeks%') then  ltrim(rtrim(REPLACE(REPLACE(A.Period,'4Q',''),'53 Weeks','')))+cast( 4 as varchar)      
     when A.Period like '%2H%'  then ltrim(rtrim(REPLACE(A.Period,'2H','')))+cast( 5 as varchar)      
     when (A.Period like '%4Q%' And A.Period like '%53 Week%') then ltrim(rtrim(REPLACE(REPLACE(A.Period,'4Q',''),'53 Weeks','')))+cast( 6 as varchar)      
     when (A.Period like '%FY%'  And A.Period not like '%53 Week%') then  ltrim(rtrim((REPLACE(A.Period,'FY',''))))+cast( 7 as varchar)      
     when A.Period like '%FY 53 Week%' then  ltrim(rtrim((REPLACE(A.Period,'FY 53 Weeks',''))))+cast( 8 as varchar)      
     when A.Period like '%Trans Period%' then  ltrim(rtrim((REPLACE(A.Period,'Trans Period',''))))+cast( 9 as varchar)      
   end) as PeriodOrder Into #tmpPeriodAll      
  FROM tblCalenderDetail A WHERE MasterID = 1 AND IsDeleted<>'Y'      
          
  --SELECT * FROM #tmpPeriodAll ORDER BY PeriodOrder    
  
  SELECT @PeriodOrder=PeriodOrder FROM #tmpPeriodAll WHERE IsActual='Y'  
  
  SELECT Period, Period+IIF(PeriodOrder<=@PeriodOrder,'A','E' ) AS NewPeriod FROM #tmpPeriodAll  
  ORDER BY PeriodOrder  
  
  DROP TABLE IF EXISTS #tmpPeriodAll

Another way i have achieve the task. 

DROP TABLE IF EXISTS #tmpPeriodAll  
   DECLARE @PeriodOrder INT  
   SET @PeriodOrder=0  
      
   Select Distinct A.* ,   
   Convert(numeric, case when A.Period like '%1Q%' then  ltrim(rtrim(REPLACE(A.Period,'1Q','')))+cast( 0 as varchar)      
      when A.Period like '%2Q%'  then ltrim(rtrim(REPLACE(A.Period,'2Q','')))+cast( 1 as varchar)      
      when A.Period like '%1H%'  then ltrim(rtrim(REPLACE(A.Period,'1H','')))+cast( 2 as varchar)      
      when A.Period like '%3Q%' then  ltrim(rtrim(REPLACE(A.Period,'3Q','')))+cast( 3 as varchar)      
      when (A.Period like '%4Q%' And A.Period not like '%53 Weeks%') then  ltrim(rtrim(REPLACE(REPLACE(A.Period,'4Q',''),'53 Weeks','')))+cast( 4 as varchar)      
      when A.Period like '%2H%'  then ltrim(rtrim(REPLACE(A.Period,'2H','')))+cast( 5 as varchar)      
      when (A.Period like '%4Q%' And A.Period like '%53 Week%') then ltrim(rtrim(REPLACE(REPLACE(A.Period,'4Q',''),'53 Weeks','')))+cast( 6 as varchar)      
      when (A.Period like '%FY%'  And A.Period not like '%53 Week%') then  ltrim(rtrim((REPLACE(A.Period,'FY',''))))+cast( 7 as varchar)      
      when A.Period like '%FY 53 Week%' then  ltrim(rtrim((REPLACE(A.Period,'FY 53 Weeks',''))))+cast( 8 as varchar)      
      when A.Period like '%Trans Period%' then  ltrim(rtrim((REPLACE(A.Period,'Trans Period',''))))+cast( 9 as varchar)      
    end) as PeriodOrder Into #tmpPeriodAll      
   FROM tblCalenderDetail A WHERE MasterID = 1 AND IsDeleted<>'Y'      
              
   --SELECT * FROM #tmpPeriodAll ORDER BY PeriodOrder    
      
   --SELECT @PeriodOrder=PeriodOrder FROM #tmpPeriodAll WHERE IsActual='Y'  
      
   SELECT a.Period, a.Period+IIF(a.PeriodOrder<=cd.PeriodOrder,'A','E' ) AS NewPeriod,a.PeriodOrder  
   FROM #tmpPeriodAll a OUTER APPLY   
   (  
 SELECT Period,PeriodOrder FROM #tmpPeriodAll WHERE IsActual='Y'  
   ) cd   
   ORDER BY a.PeriodOrder  
      
   DROP TABLE IF EXISTS #tmpPeriodAll

THANKS

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ronen Ariely 15,186 Reputation points
    2022-03-19T13:04:58.673+00:00

    Hi,

    Unfortunately a thread in a forum cannot replace a full course and you cannot become a nuclear scientist or a physics professor by reading a single message in a forum.

    In the forum we can only provide a specific solution for a specific technical focused question or to guide you to a place where you can get more information and start your learning of specific topic, but what you really need is at least a basic SQL course (free or paid, online or offline) which will take no longer than one week and will save you months of questions and discussions in forums.

    It passed about 20 years from the last time I thought it but I can say from first experience that any normal child that read/write English is old enough to learn SQL

    You should at least take a free online course IF SELF-LEARNING FIT YOU (which it does not for most people since they skip and jump and not follow the course fully in ordinarily way)

    1) what will be values ?

    Value of what? If you speak about the CROSS APPLY then please check if this help you: https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

    2) how to check what this code returning substring(Period, patindex('%[1-9][0-9][0-9][0-9]%', Period), 4)) ?

    You should learn what each part do first, start with understand what patindex is, and next what substring is. Check the links for simple explanation.

    If you want to check the result of what this part return then use it a select part like any other column that you present in the result set of select

    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

    I really want to give a direct short answer... but really... This code includes so many parts and I have no idea where to start and what exactly you already know. If you know nothing then you should learn the basic and if you lake of specific part (like specific function or a world then you should focus on that one

    I will try to guide you to the tutorial regarding the main point here which wraps the rest, but my recommendation is to take an ordered course and not to ry learn by jumping between points

    OVER : learn what OVER is, here: https://learn.microsoft.com/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15&WT.mc_id=DP-MVP-5001699

    I hope that this answers at least partially what you need :-)

    0 comments
  2. Bert Zhou-msft 3,421 Reputation points
    2022-03-21T07:21:21.113+00:00

    Hi，@T.Zacks

    Welcome to Microsoft T-SQL Q&A Forum!

    Seeing that you posted two identical cases here, please refer to the cte code implemented by Viorel-1, which is the clearest explanation I can think of to achieve your goal answer.
    Answering your comment here, use row_number() function or DENSE_RANK(), these two functions are used very similarly, it is equivalent to ROW_NUMBER() DISTINCT when you use it. If you have further questions, please continue to comment and we will assist you.

    Best regards,
    Bert Zhou

    0 comments