Archive
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
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 TRANSACTIONINSERT 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 NULLCOMMIT 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
asset 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