Use of PIVOT in SQL

SELECT * FROM (SELECT

CASE

WHEN ProductCode=’SH’ THEN ‘SH’

WHEN ProductCode =’SF’ AND InvestmentAccountID =-1 THEN ‘CFDINdex’

ELSE ‘SF’ END AS ProductCode

FROM Positions WHERE Id= <any Value>

)

AS data

PIVOT

(

COUNT(ProductCode)

FOR ProductCode IN(SH, SF) –Name of column that you want to your output column wise

) AS pvt

 

Advertisements

Ageing procedure

This procedure will return data in age range format.

DECLARE @StartDate DATETIME =’2009-1-1′
DECLARE @EndDate DATETIME=’2010-12-31′
DECLARE @Table TABLE([MONTH] Varchar(20),
[18-24] money,
[25-34] money,
[35-44] money,
[45-54] money,
[55-64] money,
[65+] MONEY,
[Total] money)
INSERT INTO @Table
SELECT CONVERT(VARCHAR(4),YEAR(o.dtApplication)) + ‘/’ + CONVERT(VARCHAR(2),MONTH(o.dtApplication)) AS [MONTH],
sum(case when DATEDIFF(yy, o.DOB, GETDATE()) BETWEEN 18 and 24 then 1 else 0 end) as ’18-24′,
sum(case when DATEDIFF(yy, o.DOB, GETDATE()) BETWEEN 25 and 34 then 1 else 0 end) as ’25-34′,
sum(case when DATEDIFF(yy, o.DOB, GETDATE()) BETWEEN 35 and 44 then 1 else 0 end) as ’35-44′,
sum(case when DATEDIFF(yy, o.DOB, GETDATE()) BETWEEN 45 and 54 then 1 else 0 end) as ’45-54′,
sum(case when DATEDIFF(yy, o.DOB, GETDATE()) BETWEEN 55 and 64 then 1 else 0 end) as ’55-64′,
sum(case when DATEDIFF(yy, o.DOB, GETDATE()) > 64 then 1 else 0 end) as ’65+’,
0 AS [Total]
FROM table o WITH(NOLOCK) where o.dtApplication between @StartDate and @EndDate
GROUP BY CONVERT(VARCHAR(4),YEAR(o.dtApplication)) + ‘/’ + CONVERT(VARCHAR(2),MONTH(o.dtApplication))
— Update total
UPDATE @Table SET [Total] = [18-24] + [25-34] + [35-44] + [45-54] + [55-64] + [65+]
–SELECT * from @Table
— Update average
UPDATE @Table
SET [18-24] =[18-24]/(Total/100),
[25-34] =[25-34]/(Total/100),
[35-44] =[35-44]/(Total/100),
[45-54] =[45-54]/(Total/100),
[55-64] =[55-64]/(Total/100),
[65+] =[65+]/(Total/100)
–Add Bancorp average
INSERT INTO @Table
VALUES ( ‘Average’,
(select AVG([18-24]) FROM @Table),
(select AVG([25-34]) FROM @Table),
(select AVG([35-44]) FROM @Table),
(select AVG([45-54]) FROM @Table),
(select AVG([55-64]) FROM @Table),
(select AVG([65+]) FROM @Table),
(select AVG([Total]) FROM @Table)
)
— Select all
SELECT * from @Table

Insert Non-Duplicate record into table

Insert record into table from another source but igonre those who are already exists.
Put a Outer Join and check for ID Not NULL Becuase those record ,who has already inserted into table has a valid ID. AND null means that these records are not inserted

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spName]’) AND type in (N’P’, N’PC’))
BEGIN
PRINT ‘DROPPING PROCEDURE [dbo].[spName]’
DROP PROCEDURE [dbo].[spName]
END
GO

IF OBJECT_ID(‘tempdb..#TempFee’) IS NOT NULL
BEGIN
DROP TABLE #TempFee
END
CREATE TABLE #TempFee(
–columns……
) ON [PRIMARY]

DECLARE @FeeID INT
IF @FeeID > 0
BEGIN
BEGIN TRY
BEGIN TRANSACTION

INSERT INTO Table1(columns….)
SELECT columns …….FROM #TempFee WITH (NOLOCK)
INNER JOIN Table2 c WITH (NOLOCK) ON nfi.ID= c.ID AND c.State = @STATE
LEFT OUTER JOIN table 3 cfc ON cfc.Id = cl.ID
AND cfc.Active = 1
WHERE nfi.ID IS NOT NULL AND cfc.Id IS NULL

COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
DECLARE @ErrorNumber INT;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorMessage VARCHAR(1000)

SELECT @ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorMessage = ERROR_MESSAGE()

RAISERROR (@ErrorNumber, @ErrorSeverity, @ErrorState)
END CATCH
END

Monthly tab data display with grouping

select c.Name as Client,c.ClientName,
sum(case when month(o.CreatedDate) = 1 then 1 else 0 end) as Jan,
sum(case when month(o.CreatedDate) = 2 then 1 else 0 end) as Feb,
sum(case when month(o.CreatedDate) = 3 then 1 else 0 end) as Mar,
sum(case when month(o.CreatedDate) = 4 then 1 else 0 end) as Apr,
sum(case when month(o.CreatedDate) = 5 then 1 else 0 end) as May,
sum(case when month(o.CreatedDate) = 6 then 1 else 0 end) as Jun,
sum(case when month(o.CreatedDate) = 7 then 1 else 0 end) as Jul,
sum(case when month(o.CreatedDate) = 8 then 1 else 0 end) as Aug,
sum(case when month(o.CreatedDate) = 9 then 1 else 0 end) as Sep,
sum(case when month(o.CreatedDate) = 10 then 1 else 0 end) as Oct,
sum(case when month(o.CreatedDate) = 11 then 1 else 0 end) as Nov,
sum(case when month(o.CreatedDate) = 12 then 1 else 0 end) as Dec,
count(o.ColumnName) as Total
from table1 o with (nolock)
inner join table2 c on c.ID=o.ID
where o.CreatedDate between @StartDate and @EndDate
and c.Id= @ID
group by c.Name

Search use of Column in DB

SELECT DISTINCT o.name AS ObjectName,
CASE o.xtype
WHEN ‘C’ THEN ‘CHECK constraint’
WHEN ‘D’ THEN ‘Default or DEFAULT constraint’
WHEN ‘F’ THEN ‘FOREIGN KEY constraint’
WHEN ‘FN’ THEN ‘Scalar function’
WHEN ‘IF’ THEN ‘In-lined table-function’
WHEN ‘K’ THEN ‘PRIMARY KEY or UNIQUE constraint’
WHEN ‘L’ THEN ‘Log’
WHEN ‘P’ THEN ‘Stored procedure’
WHEN ‘R’ THEN ‘Rule’
WHEN ‘RF’ THEN ‘Replication filter stored procedure’
WHEN ‘S’ THEN ‘System table’
WHEN ‘TF’ THEN ‘Table function’
WHEN ‘TR’ THEN ‘Trigger’
WHEN ‘U’ THEN ‘User table’
WHEN ‘V’ THEN ‘View’
WHEN ‘X’ THEN ‘Extended stored procedure’
ELSE o.xtype
END AS ObjectType, OBJECT_SCHEMA_NAME(o.id) AS [Schema],
ISNULL( p.Name, ‘[db]’) AS Location
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
LEFT JOIN sys.schemas s ON s.schema_id = o.schema_ver
LEFT JOIN sysobjects p ON o.Parent_obj=p.id
WHERE c.text LIKE ‘%tt%’
AND o.xtype=’P’
ORDER BY Location, ObjectName

Creating a DDL Trigger

CREATE TRIGGER [name of trigger]
ON [scope (database|server)]
FOR [event]
As
–Trigger definition here…

example-

create trigger DDL_Trigger
on database
for create_procedure, alter_procedure, drop_procedure
as

set nocount on

declare @data xml
set @data = EVENTDATA()

insert into dbo.eventslog(eventtype, objectname, objecttype, sqlcommand, username)
values(@data.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘varchar(50)’),
@data.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘varchar(256)’),
@data.value(‘(/EVENT_INSTANCE/ObjectType)[1]’, ‘varchar(25)’),
@data.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘varchar(max)’),
@data.value(‘(/EVENT_INSTANCE/LoginName)[1]’, ‘varchar(256)’)
)

What’s that EVENTDATA() thingy?

EVENTDATA is where we’re going to get our data for the change log system. It’s basically an XML datatype that looks like this:

<EVENT_INSTANCE>
<EventType>event </EventType>

<PostTime>date-time</PostTime>
<SPID>spid</SPID>
<ServerName>name </ServerName>

<LoginName>login </LoginName>
<UserName>name</UserName>
<DatabaseName>name</DatabaseName>

<SchemaName>name</SchemaName>
<ObjectName>name</ObjectName>
<ObjectType>type</ObjectType>

<TSQLCommand>command</TSQLCommand>
</EVENT_INSTANCE>

Creating Comma Separate Values List from Table – UDF – SP

The table is example is:TableName: NumberTable
NumberCols
first-record
second-record
third-record
fourth-record
fifth-record

Output : first-record,second-record,third-record,fourth-record,fifth-record

Create FUNCTION GetDateAsString
(
@OrderID int
)
RETURNS varchar(100)
AS
BEGIN
DECLARE @listStr VARCHAR(MAX)
SET @listStr = ”
SELECT @listStr = COALESCE(@listStr+’,’ ,”) + convert(varchar,PubDate,103)
FROM NumberTable where NumberTable.ID=@OrderID
if len(@listStr)> 2
SELECT @listStr=SUBSTRING(@listStr,2,len(@listStr)-1)
return @listStr
end