T-SQL/MSBI Knowledge Share Videos

T- SQL Miscellaneous 3

Create custom identity Column in SQL Server (Numeric + Alphabet)


IF EXISTS(SELECT * FROM sys.tables WHERE name='Test_Custom_Identity')
DROP TABLE Test_Custom_Identity

CREATE TABLE dbo.Test_Custom_Identity
(
       Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
       Name VARCHAR(100)   
)     

IF EXISTS(SELECT * FROM sys.objects WHERE name='Fn_Custom_Identity')
DROP FUNCTION Fn_Custom_Identity

CREATE FUNCTION dbo.Fn_Custom_Identity(@Id INT)
RETURNS CHAR(50)
AS
BEGIN
       RETURN CONVERT(VARCHAR(10), @id)+'a'
END

ALTER TABLE dbo.Test_Custom_Identity
ADD CustomId AS dbo.Fn_Custom_Identity(Id)


INSERT INTO Test_Custom_Identity (Name) values ('fghfgh')
INSERT INTO Test_Custom_Identity (Name) values ('fdfsd')

SELECT * FROM Test_Custom_Identity

Query to get, for the given ItemCode and Amount get the Total Amount of each item in each row
CREATE TABLE dbo.ItemDetails
(
      ItemCode INT,
      ItemName VARCHAR(200),
      Amount MONEY,
)

INSERT INTO dbo.ItemDetails VALUES(1,'AAA',100)
INSERT INTO dbo.ItemDetails VALUES(1,'AAA',200)
INSERT INTO dbo.ItemDetails VALUES(2,'BBB',600)
INSERT INTO dbo.ItemDetails VALUES(2,'BBB',800)
INSERT INTO dbo.ItemDetails VALUES(3,'BBB',200)
INSERT INTO dbo.ItemDetails VALUES(3,'CCC',600)

SELECT *,SUM(Amount)OVER(PARTITION BY ItemCode)TotalItemAmount
FROM dbo.ItemDetails



1 comment:

RS Trainings said...

I really appreciate for your efforts to make things easy to understand. I was really many students struggling to understand certain concepts but you made it clear and help me bring back my confidence.


MSBI Online Training in usa
MSBI Online Training in india
MSBI Online Training in hyderabad