Cross Tab Queries

Often we need to use cross tab queries
we can use Pivot function of sqlserver 2005.
consider an example
say, we have following sort of table
DECLARE @Test TABLE (
SalesPart char(11),
WCDate datetime,
[Delivery Date] datetime,
Qty int
)

INSERT INTO @Test
SELECT '883/575/013', CONVERT(datetime,'21/03/2010',103), CONVERT(datetime,'21-MAR-10 00:00:00.000',113), 4147 UNION ALL
SELECT '883/575/013', CONVERT(datetime,'21/03/2010',103), CONVERT(datetime,'22-MAR-10 00:00:00.000',113), 1963 UNION ALL
SELECT '883/575/013', CONVERT(datetime,'21/03/2010',103), CONVERT(datetime,'23-MAR-10 00:00:00.000',113), 2639 UNION ALL
SELECT '883/575/013', CONVERT(datetime,'21/03/2010',103), CONVERT(datetime,'24-MAR-10 00:00:00.000',113), 4498 UNION ALL
SELECT '883/575/013', CONVERT(datetime,'21/03/2010',103), CONVERT(datetime,'25-MAR-10 00:00:00.000',113), 3523 UNION ALL
SELECT '883/575/013', CONVERT(datetime,'21/03/2010',103), CONVERT(datetime,'27-MAR-10 00:00:00.000',113), 6721
We can write a Cross Tab query for the above data
like
SELECT SalesPart, WCDate, Sunday, Monday, Tuesday, Wednesday, Thursday, Saturday
FROM (
SELECT SalesPart, WCDate, datename(weekday ,[Delivery Date]) AS weekday, Qty
FROM @Test
) AS T
PIVOT (SUM(Qty) FOR weekday in ([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Saturday])) AS Pvt
Another Solution could be
SELECT T.SalesPart,


T.WCDate,

SUM(CASE WHEN CA.week_day = 'Sunday' THEN T.Qty ELSE 0 END) AS Sunday,

SUM(CASE WHEN CA.week_day = 'Monday' THEN T.Qty ELSE 0 END) AS Monday,

SUM(CASE WHEN CA.week_day = 'Tuesday' THEN T.Qty ELSE 0 END) AS Tuesday,

SUM(CASE WHEN CA.week_day = 'Wednesday' THEN T.Qty ELSE 0 END) AS Wednesday,

SUM(CASE WHEN CA.week_day = 'Thursday' THEN T.Qty ELSE 0 END) AS Thursday,

SUM(CASE WHEN CA.week_day = 'Friday' THEN T.Qty ELSE 0 END) AS Friday,

SUM(CASE WHEN CA.week_day = 'Saturday' THEN T.Qty ELSE 0 END) AS Saturday

FROM @Test T

CROSS APPLY (SELECT DATENAME(WEEKDAY, T.[Delivery Date])) CA (week_day)

GROUP BY

T.SalesPart,

T.WCDate;

Find a Sepecific parameter type in all storedprocedures

some times we may require to find all the stored procedures where a specific dataype is used as a parameter or where a specific parameter name is used .
we can search those strored procedures using "sys.all_parameters","systypes","sys.objects"
say we want to know in which stored procedures data type smallint is used , we can do so by using the follwoing SQL

select schema_name(o.schema_id) schema_name, o.name obj_name, p.name param_name, t.name param_type
from sys.all_parameters p
join systypes t on t.xusertype = p.user_type_id
join sys.objects o on o.object_id = p.object_id
where o.type = 'P' -- type procedure
and t.name = 'smallint' -- data type
and o.is_ms_shipped = 0 -- user created
order by
schema_name(o.schema_id),
o.name;
AND
To search for a specific parameter name we can edit the above query as
select schema_name(o.schema_id) schema_name, o.name obj_name, p.name param_name
from sys.all_parameters p

join sys.objects o on o.object_id = p.object_id
where o.type = 'P' -- type procedure
and o.is_ms_shipped = 0 -- user created
and p.name='@OperatorId'
order by
schema_name(o.schema_id),
o.name;

Update record in a Database on the basis of Other Database

Some times we need to sync our tables with other tables which may have some other database .
so we can use update feature like that
update person
set name = B.personname,
age=B.age

from DB1.dbo.person as A, DB2.dbo.person2 as B
WHERE A.id = B.personid

Find the Last Occurrence of a character in a string in SQL

Some times we need to get the last occurrence of a character in any string , charindex returns with the index of first occurrence of a character but we can still use it by playing a small trick
consider an example
suppose we have a string 'C:\ProgramFiles\Test\Test.xml'
and we want to get file name i.e Test.xml
we can use Reverse method to get the occurrence of '\' character , like
Declare @FileName varchar(100)
Set @FileName = 'C:\ProgramFiles\Test\Test.xml'

Select Substring(@FileName,
LEN(@FileName ) -
Charindex('\',Reverse(@FileName ))+2,
LEN(@FileName ))



OR
we can use RIGHT Function like
DECLARE @FileName VARCHAR(255)

SET @FileName = 'C:\ProgramFiles\Test\Test.xml'

SELECT RIGHT( @FileName , CHARINDEX( '\', REVERSE( @FileName ) + '\' ) - 1 )

Convert Minutes, Seconds to more readable Form in SQL

when we use datediff function in SQL to determine how log something is running, we have to make the choice how to represent it. If we choose minutes, SQL will round the datediff time. If we choose seconds, we'll have to determine how many minutes + some seconds this was For Example 5315027 seconds means 12:23:47 i.e 12 hours 23 minutes and 47 seconds which is more understandable than 5315027
we can write query as
select CONVERT(varchar(8),DATEADD(second,datediff(ss,GetDate(),Convert(Datetime,'07-07-10')) ,0),114)
or we can create a UDF like that
create function [dbo].[SecondsToText](@seconds int) returns varchar(8)
as
    begin
        return (select CONVERT(varchar(8),DATEADD(second,@seconds,0),114))
    end
go
Same can be Used with Minutes but of course with Slight change in parametes
select CONVERT(varchar(8),DATEADD(mi,datediff(mi,GetDate(),Convert(Datetime,'07-07-10')) ,0),114)
or a Function like that
create function [dbo].[MinutesToText](@minutes int) returns varchar(8)
as
    begin
        return (select CONVERT(varchar(8),DATEADD(mi,@minutes,0),114))
    end
go