Jump to content

SQL experts ki question


Hakuna_Matata

Recommended Posts

Day zero insert script.

ID, Number, Name, Desc

1, 5, xyz, Good

2, 2, abc, Average 

3, 1, dsa, poor

 

Desc column : if number between 4-5..Good                 

                         if number between 2-3..Average   

                         if number between 1-1.5...poor

ila oka 50 records ki script rayali...new table create chesyakunda oke table create chesi cheyadam ela.same table NUmber column paina Case statement work avatledu.

..please help

 

 

 

Link to comment
Share on other sites

Try this:

Insert into Table_A

(Select id,

Case

when (number >= 1 && number <= 1.5) then 'Poor'

when (number >= 2 && number <= 3) then 'Average '

when (number >= 4 && number <= 5) then 'Good'

End as Number,

Name

from tbl_b

Order by number desc);

  • Upvote 2
Link to comment
Share on other sites

8 hours ago, Chinna84 said:

Try this:

Insert into Table_A

(Select id,

Case

when (number >= 1 && number <= 1.5) then 'Poor'

when (number >= 2 && number <= 3) then 'Average '

when (number >= 4 && number <= 5) then 'Good'

End as Number,

Name

from tbl_b

Order by number desc);

From another table kakunda edaina way unda ba..from tbl_b ante first ippudu tbl_b create cheyali

Link to comment
Share on other sites

25 minutes ago, Hakuna_Matata said:

on_insert_trigger  eppudu use cheyale ba..try chestha

first create tabel with DESCOLUMN allow nulls nvarchar(50) and than create a on insert trigger on your table. 

 

USE [DBNAME]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[trig_TABLENAME_INSERT_TRIGGER]
ON [dbo].[TABLENAME]
FOR INSERT
AS
BEGIN

      DECLARE   @Id                                  INT

                          ,@Number                       FLOAT

                        ,@Desccolumnname       NVARCHAR(50)

        SET @Number = (SELECT i.Number FROM inserted i)

        SET @Id = (SELECT i.ID FROM inserted i)

                SET @Desccolumnname  = CASE

                                                                  WHEN (@Number>= 1 && @Number<= 1.5) THEN     'Poor'

                                                                   WHEN (@Number>= 2 && @Number<= 3) THEN         'Average '

                                                                   WHEN  (@Number>= 4 && @Number<= 5) THEN        'Good'

                  End

         UPDATE [dbo].[TABLENAME] SET DESCOLUMN = @Desccolumnname  where ID = @Id 

END

  • Upvote 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...