Sunday, January 31, 2010

LINQ Extended method for Update Data

Extended Method

public delegate void Func(TArg0 element);

///
/// Executes an Update statement block on all elements in an IEnumerable sequence.
///
/// The source element type.
/// The source sequence.
/// The update statement to execute for each element.
/// The numer of records affected.
public static int Update(this IEnumerable source, Func update)
{
if (source == null) throw new ArgumentNullException("source");
if (update == null) throw new ArgumentNullException("update");
if (typeof(TSource).IsValueType)
throw new NotSupportedException("value type elements are not supported by update.");

int count = 0;
foreach (TSource element in source)
{
update(element);
count++;
}
return count;
}

Example :

int intUpdate = lstItem
.Where(a => a.ItemGroupId.Equals(lngItemGroupId))
.Update(b =>
{
b.GroupName = strGroupName;
b.GroupLocation = strLocation;
}
);

Monday, January 25, 2010

Truncate Log


--BACKUP LOG WITH TRUNCATE_ONLY
--Go


--============= For sql 2008 =====================
USE
GO
SELECT file_id, name FROM sys.database_files
GO
DBCC SHRINKFILE (2, TRUNCATEONLY);

GET Database diffrence


----Declaration
BEGIN
DECLARE @Status_TableNotFound INT = 1
DECLARE @Status_FieldNotFound INT = 2
DECLARE @Status_FieldTypeChnage INT = 3
DECLARE @Status_FieldLengthChange INT = 4
ENd

--Get Table / Filed List
BEGIN
SELECT
--- db1
db1Objects.id AS db1Id
, db1Objects.name AS db1TableName
, db1Column.name AS db1FieldName
, db1Column.xType as db1FieldType
, UPPER(Type_Name(db1Column.xtype)) AS db1FieldTypeName
, db1Column.length as db1FieldLength
, db1Column.ColStat & 1 AS db1IsIdentity

--- db2
, ISNULL( db2Objects.id, 0) AS db2Id
, ISNULL( db2Objects.name, '') AS db2TableName
, ISNULL( db2Column.name, '') AS db2FieldName
, ISNULL( db2Column.xType, 0 ) as db2FieldType
, UPPER(ISNULL( Type_Name(db2Column.xtype), '' )) AS db2FieldTypeName
, ISNULL( db2Column.length, 0 ) as db2FieldLength
, ISNULL( db2Column.ColStat & 1, 0 ) AS db2IsIdentity
, 0 AS Status
INTO #FieldCheck
FROM <DBName1>.dbo.SysObjects AS db1Objects
INNER JOIN <DBName1>.dbo.SysColumns AS db1Column ON db1Column.id = db1Objects.id
AND db1Objects.xtype = 'U'
AND db1Objects.category = 0

LEFT OUTER JOIN <DBName2>.dbo.SysObjects AS db2Objects ON db2Objects.name = db1Objects.name
AND db2Objects.xtype = 'U'
AND db2Objects.category = 0
LEFT OUTER JOIN <DBName2>.dbo.SysColumns AS db2Column ON db2Column.id = db2Objects.id
AND db2Column.name = db1Column.name
END

--- Check for Status
BEGIN
--- TableNotFound
UPDATE #FieldCheck
SET Status = @Status_TableNotFound
WHERE db1TableName != db2TableName

--- FieldNotFound
UPDATE #FieldCheck
SET Status = @Status_FieldNotFound
WHERE db1FieldName != db2FieldName
AND Status = 0

--- FieldTypeChnage
UPDATE #FieldCheck
SET Status = @Status_FieldTypeChnage
WHERE db1FieldName = db2FieldName
AND db1FieldTypeName != db2FieldTypeName
AND Status = 0

--- FieldLengthChange
UPDATE #FieldCheck
SET Status = @Status_FieldLengthChange
WHERE db1FieldLength != db2FieldLength
AND Status = 0
END


--- TableNotFound
SELECT DISTINCT db1TableName AS TableNotFound
FROM #FieldCheck
WHERE Status = @Status_TableNotFound

--- FieldNotFound
SELECT DISTINCT
db1TableName AS TableName
, db1FieldName FieldNotFound
, 'ALTER TABLE ' + db1TableName +
' ADD ' + db1FieldName +
' ' + db1FieldTypeName +
' ' + (
CASE
WHEN db1FieldTypeName IN ( 'VARCHAR', 'NVARCHAR' )
THEN '(' + CAST( db1FieldLength AS VARCHAR(10) ) + ')'
ELSE ''
END
) +
'; UPDATE ' + db1TableName +
' SET ' + db1FieldName + ' = ' +
(
CASE
WHEN db1FieldTypeName LIKE '%CHAR%'
OR db1FieldTypeName LIKE '%TEXT%'
OR db1FieldTypeName LIKE '%XML%'
THEN ''''''
WHEN db1FieldTypeName LIKE 'INT'
OR db1FieldTypeName LIKE 'SMALLINT'
OR db1FieldTypeName LIKE 'BIGINT'
OR db1FieldTypeName LIKE '%BINARY%'
OR db1FieldTypeName LIKE 'float'
OR db1FieldTypeName = 'BIT'
THEN '0'
ELSE 'object'
END
)
' ; '
FROM #FieldCheck
WHERE Status = @Status_FieldNotFound

--- FieldTypeChnage
SELECT DISTINCT db1TableName AS TableName, db1FieldName AS FieldName, db1FieldTypeName, db2FieldTypeName
FROM #FieldCheck
WHERE Status = @Status_FieldTypeChnage


--- FieldLengthChange
SELECT DISTINCT db1TableName AS TableName, db1FieldName AS FieldName, db1FieldTypeName AS FieldType, db1FieldLength, db2FieldLength
FROM #FieldCheck
WHERE Status = @Status_FieldLengthChange

DROP TABLE #FieldCheck

Find and Replace text in Datatable


SET NOCOUNT ON
DECLARE @FindName VARCHAR(MAX) = 'AAA' --- <- Find Value
DECLARE @ReplaceName VARCHAR(MAX) = 'BBB' --- <- Replace Value

DECLARE @Table TABLE
(
ID INT IDENTITY(1,1)
, TableName VARCHAR(100)
, ColumnName VARCHAR(100)
)
INSERT INTO @Table
SELECT
tableSchema.TABLE_SCHEMA + '.' + SysObjects.name
, SysColumns.Name
FROM sysobjects
INNER JOIN SysColumns ON SysColumns.id = sysobjects .id
AND SysColumns.xtype IN ( 167, 175, 239, 231, 35, 99 )
INNER JOIN information_schema.tables AS tableSchema ON tableSchema.TABLE_NAME = SysObjects.name
WHERE sysobjects.xtype='U'
ORDER BY sysobjects.Name, SysColumns.Name

DECLARE @NoOfRecord INT = 0
DECLARE @CurrentRecord INT = 1
DECLARE @TableName VARCHAR(100) = ''
DECLARE @ColumnName VARCHAR(100) = ''
DECLARE @SQLCommand VARCHAR(MAX) = ''

SELECT @NoOfRecord = MAX(ID)
FROM @Table

WHILE (@CurrentRecord <= @NoOfRecord )
BEGIN
SELECT
@TableName = TableName
, @ColumnName = ColumnName
FROM @Table
WHERE ID = @CurrentRecord

SET @SQLCommand = 'IF EXISTS(SELECT * FROM ' + @TableName + ' WHERE ' + @ColumnName + ' LIKE ''%' + @FindName + '%'')' +
' BEGIN ' +
' PRINT ''UPDATE ' + @TableName +
' SET ' + @ColumnName + ' = REPLACE(' + @ColumnName + ', ''''' + @FindName + ''''', ''''' + @ReplaceName + ''''') ''' +
' END '

-- PRINT @SQLCommand
EXECUTE(@SQLCommand)
SET @CurrentRecord = @CurrentRecord + 1
END

Second highest Salary


DECLARE @Employees TABLE(
EmployeeID INT IDENTITY,
EmployeeName VARCHAR(15),
Department VARCHAR(15),
Salary NUMERIC(16,2)
)

INSERT INTO @Employees VALUES('T Cook','Finance', 40000)
INSERT INTO @Employees VALUES('D Michael','Finance', 25000)
INSERT INTO @Employees VALUES('A Smith','Finance', 25000)
INSERT INTO @Employees VALUES('jj','Finance', 25000)
INSERT INTO @Employees VALUES('D Adams','Finance', 15000)

INSERT INTO @Employees VALUES('M Williams','IT', 80000)
INSERT INTO @Employees VALUES('D Jones','IT', 40000)
INSERT INTO @Employees VALUES('J Miller','IT', 50000)
INSERT INTO @Employees VALUES('L Lewis','IT', 50000)

INSERT INTO @Employees VALUES('A Anderson','Back-Office', 25000)
INSERT INTO @Employees VALUES('S Martin','Back-Office', 15000)
INSERT INTO @Employees VALUES('J Garcia','Back-Office', 15000)
INSERT INTO @Employees VALUES('T Clerk','Back-Office', 10000)

SELECT *
FROM
(
SELECT *, DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS EmpRank
FROM @Employees
) AS Employees
WHERE EmpRank = 2

Get field list from string


DECLARE @FileFormat VARCHAR(50)
SET @FileFormat = '1#34#6#8#01#34#6#8#01#34#6#8#01#34#6#8#0'

;WITH MyTable AS
(
SELECT @FileFormat AS FileFormat
, CHARINDEX('#', @FileFormat) + 1 AS FirstIndex
, (
CHARINDEX('#', SUBSTRING(@FileFormat, CHARINDEX('#', @FileFormat ) + 1, LEN( @FileFormat ) ) ) - 1
) AS SecondIndex

UNION ALL

SELECT SUBSTRING( FileFormat, FirstIndex + SecondIndex + 1, LEN( FileFormat ) ) AS FileFormat
, CHARINDEX('#', SUBSTRING( FileFormat, FirstIndex + SecondIndex + 1, LEN( FileFormat ) ) ) + 1 AS FirstIndex
, (
CHARINDEX('#', SUBSTRING(SUBSTRING( FileFormat, FirstIndex + SecondIndex + 1, LEN( FileFormat ) ), CHARINDEX('#', SUBSTRING( FileFormat, FirstIndex + SecondIndex + 1, LEN( FileFormat ) ) ) + 1, LEN( FileFormat ) ) ) - 1
) AS SecondIndex
FROM MyTable
WHERE LEN( FileFormat ) > 0
AND SecondIndex > 0
)

SELECT
(
CASE
WHEN FirstIndex - 2 > 0 THEN SUBSTRING(FileFormat, 1, FirstIndex - 2)
ELSE SUBSTRING(FileFormat, 1, LEN( FirstIndex ) )
END
) AS PreviousField
,(
CASE
WHEN SecondIndex > 0 THEN SUBSTRING(FileFormat, FirstIndex, SecondIndex)
ELSE ''
END
) AS FieldName
FROM MyTable
OPTION (MAXRECURSION 0)

Get Datatabase LOG Size


SELECT
(size * 8)/1024.0 AS size_in_mb,
CASE
WHEN CAST( max_size AS BIGINT )= -1 THEN 9999999 -- Unlimited growth, so handle this how you want
ELSE (CAST( max_size AS BIGINT ) * 8)/1024.0
END AS max_size_in_mb
FROM
sys.database_files
WHERE
data_space_id = 0 -- Log file

Insert query of datatable


DECLARE @PassTableName as varchar(1000)

SET @PassTableName = 'tbl_ReturnsEFileFieldMapping' --- 'Type Table Name'

BEGIN

DECLARE @SQL_Insert AS Varchar(8000)
DECLARE @SQL_Values AS Varchar(8000)

DECLARE @FieldName AS Varchar(200)
DECLARE @FieldType as int
DECLARE @FieldTypeName as varchar(100)
DECLARE @FieldLength as int
DECLARE @FieldIdentity as int

SET @SQL_Insert = 'SELECT ''INSERT INTO ' + @PassTableName + ' ( '
SET @SQL_Values = ' VALUES ( '

DECLARE Fields_Cursor CURSOR FOR
SELECT Name AS FieldName,
xType as FieldType,
Type_Name(xType) AS FieldTypeName,
Length as FieldLength,
ColStat & 1 AS IsIdentity
FROM SysColumns
WHERE ID IN ( SELECT ID FROM SysObjects WHERE Name = REPLACE( REPLACE( REPLACE( @PassTableName, 'dbo.', ''), 'Report.', ''), 'EQISAdmin.', '') )

OPEN Fields_Cursor
FETCH NEXT FROM Fields_Cursor INTO @FieldName, @FieldType, @FieldTypeName, @FieldLength, @FieldIdentity

WHILE @@FETCH_STATUS = 0
BEGIN

-- IF @FieldIdentity = 0 -- Identity is No
-- BEGIN

SET @SQL_Insert = @SQL_Insert + @FieldName + ', '

IF @FieldType = 48 OR @FieldType = 56 OR @FieldType = 127 OR @FieldType = 59 OR @FieldType = 52 -- Small Int OR Integer OR Big Integer OR Real OR SmallInt
BEGIN
SET @SQL_Values = @SQL_Values + ''' + Convert( varchar(20), ISNULL(' + @FieldName + ', '''' ) ) + '', '
END
ELSE IF @FieldType = 106 OR @FieldType = 108 -- Decimal OR Numeric
BEGIN
SET @SQL_Values = @SQL_Values + ''' + Convert( varchar(30), ISNULL(' + @FieldName + ', '''') ) + '', '
END
ELSE IF @FieldType = 60 OR @FieldType = 122 -- Money OR Small Money
BEGIN
SET @SQL_Values = @SQL_Values + ''' + Convert( varchar(30), Convert( Decimal(30,4), ISNULL(' + @FieldName + ', 0) )) + '', '
END
ELSE IF @FieldType = 62 -- Float
BEGIN
SET @SQL_Values = @SQL_Values + ''' + Convert( varchar(30), Convert( Decimal(30,10), ISNULL(' + @FieldName + ',0) )) + '', '
END
ELSE IF @FieldType = 104 -- Bit
BEGIN
SET @SQL_Values = @SQL_Values + ''' + Convert( varchar(1), ISNULL(' + @FieldName + ','''') ) + '', '
END
ELSE IF @FieldType = 61 OR @FieldType = 58 -- Datetime OR SmallDatetime
BEGIN
SET @SQL_Values = @SQL_Values + ''''''' + Convert( varchar(20), ISNULL(' + @FieldName + ','''') ) + '''''', '
END
ELSE IF @FieldType = 167 OR @FieldType = 175 OR @FieldType = 239 OR @FieldType = 231 -- Varchar OR Char OR nChar OR nVarchar
BEGIN
SET @SQL_Values = @SQL_Values + ''''''' + Replace( ISNULL(' + @FieldName + ', ''''), '''''''', '''''''''''' ) + '''''', '
END
ELSE IF @FieldType = 35 OR @FieldType = 99 -- Text OR nText
BEGIN
SET @SQL_Values = @SQL_Values + ''''''' + Replace( Convert( varchar(8000), ISNULL(' + @FieldName + ','''')), '''''''', '''''''''''' ) + '''''', '
END
ELSE IF @FieldType = 241 -- XML
BEGIN
SET @SQL_Values = @SQL_Values + ''''''' + Replace( Convert( varchar(8000), ISNULL(' + @FieldName + ','''')), '''''''', '''''''''''' ) + '''''', '
END

-- END


FETCH NEXT FROM Fields_Cursor INTO @FieldName, @FieldType, @FieldTypeName, @FieldLength, @FieldIdentity

END

CLOSE Fields_Cursor
DEALLOCATE Fields_Cursor

SET @SQL_Insert = LTRIM( RTRIM( @SQL_Insert ) )
IF LEN( @SQL_Insert ) > 1
BEGIN
SET @SQL_Insert = SUBSTRING( @SQL_Insert, 1, LEN( @SQL_Insert ) -1 )
END

SET @SQL_Values = LTRIM( RTRIM( @SQL_Values ) )
IF LEN( @SQL_Values ) > 1
BEGIN
SET @SQL_Values = SUBSTRING(@SQL_Values, 1, LEN( @SQL_Values ) -1 )
END

--PRINT ( @SQL_Insert + ' ) ' + @SQL_Values + ' ) '' FROM ' + @PassTableName )
EXEC ( @SQL_Insert + ' ) ' + @SQL_Values + ' ) '' FROM ' + @PassTableName )

END
GO

Hierarchical Tree Structures in Database Tables


DECLARE @emp TABLE (
EmployeeID INT,
FirstName VARCHAR(15),
LastName VARCHAR(15),
ReportsTo INT
)


DECLARE @ord TABLE (
OrderID INT,
EmployeeID INT
)


INSERT INTO @emp(EmployeeID, FirstName, LastName, ReportsTo)
SELECT 2,'Andrew','Fuller',NULL
UNION ALL SELECT 1,'Nancy','Davolio',2
UNION ALL SELECT 3,'Janet','Leverling',2
UNION ALL SELECT 4,'Margaret','Peacock',2
UNION ALL SELECT 5,'Steven','Buchanan',2
UNION ALL SELECT 8,'Laura','Callahan',2
UNION ALL SELECT 6,'Michael','Suyama',5
UNION ALL SELECT 7,'Robert','King',5
UNION ALL SELECT 9,'Anne','Dodsworth',5
--UNION ALL SELECT 10,'A','A',1
--UNION ALL SELECT 11,'B','B',1
--UNION ALL SELECT 12,'C','C',1
--UNION ALL SELECT 13,'D','D',10

--SELECT * FROM @emp


INSERT INTO @ord (OrderID, EmployeeID)
SELECT 10258,1 UNION ALL
SELECT 10270,1 UNION ALL
SELECT 10275,1 UNION ALL
SELECT 10265,2 UNION ALL
SELECT 10277,2 UNION ALL
SELECT 10251,3 UNION ALL
SELECT 10253,3 UNION ALL
SELECT 10256,3 UNION ALL
SELECT 10250,4 UNION ALL
SELECT 10252,4 UNION ALL
SELECT 10248,5 UNION ALL
SELECT 10254,5 UNION ALL
SELECT 10249,6 UNION ALL
SELECT 10289,7 UNION ALL
SELECT 10303,7 UNION ALL
SELECT 10308,7 UNION ALL
SELECT 10262,8 UNION ALL
SELECT 10268,8 UNION ALL
SELECT 10276,8 UNION ALL
SELECT 10278,8 UNION ALL
SELECT 10255,9 UNION ALL
SELECT 10263,9


;WITH cteNodes AS
(
--initialization
SELECT EmployeeID, ISNULL(ReportsTo, 0 ) AS ReportsTo, 0 AS EmpLevel
FROM @emp

UNION ALL

----recursive execution
SELECT P.EmployeeID, ISNULL( N.ReportsTo, 0 ), P.EmpLevel + 1
FROM cteNodes AS P
INNER JOIN @emp AS N ON N.EmployeeID = P.ReportsTo
)
, cteEmployeePath AS
(
SELECT DISTINCT Emp.EmployeeID
, ISNULL( Emp.ReportsTo, 0 ) AS ReportsTo
,(
SELECT '$' + CAST(ReportsTo AS VARCHAR(20) ) + '$'
FROM cteNodes AS EmpParth
WHERE EmpParth.EmployeeID = Emp.EmployeeID
ORDER BY EmpLevel DESC
FOR XML PATH('')
)
+ '$' + CAST( Emp.EmployeeID AS VARCHAR(10) ) + '$' AS EmployeePath
,(
SELECT '$0$'
FROM cteNodes AS EmpParth
WHERE EmpParth.EmployeeID = Emp.EmployeeID
ORDER BY EmpLevel DESC
FOR XML PATH('')
) AS EmpLevel
FROM @emp AS Emp
)

SELECT
(
REPLICATE(' ', 3*( LEN( REPLACE( cteEmployeePath.EmpLevel, '$', '') ) - 1 ) )
+ Emp.LastName
+ ' '
+ Emp.FirstName
) AS EmployeeName
, ( LEN( REPLACE( cteEmployeePath.EmpLevel, '$', '') ) - 1 ) AS Level
, SUM( CASE WHEN Ord.EmployeeID = Emp.EmployeeID THEN 1 ELSE 0 END ) AS by_self
, SUM( CASE WHEN Ord.EmployeeID != Emp.EmployeeID THEN 1 ELSE 0 END ) AS by_sub
, COUNT( Ord.OrderId ) AS total
FROM cteEmployeePath
INNER JOIN @emp AS Emp ON Emp.EmployeeID = cteEmployeePath.EmployeeID
INNER JOIN cteEmployeePath AS UnderEmployee ON UnderEmployee.EmployeePath Like cteEmployeePath.EmployeePath + '%'
INNER JOIN @ord AS Ord ON Ord.EmployeeID = UnderEmployee.EmployeeID
GROUP BY Emp.EmployeeId, cteEmployeePath.EmpLevel, Emp.FirstName, Emp.LastName, cteEmployeePath.EmployeePath
ORDER BY cteEmployeePath.EmployeePath

Backup & Restore Database


---- Backup Database :
BEGIN
backup database to disk = 'c:\.bak'
END

---- Restore Database :
BEGIN
RESTORE DATABASE Salary
FROM DISK = N'C:\DBBackUp 2009-12-23.bak'
WITH FILE = 1,
MOVE N'Salary' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB.mdf',
MOVE N'Salary_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL \Data\DB_Log.ldf',
NOUNLOAD, STATS = 10
END

Fibonacci Sequence


WITH FibonacciSequence AS
(

SELECT
1 AS SrNo
, ( CAST( 0 AS FLOAT ) ) AS c1
, ( CAST( 1 AS FLOAT ) ) AS c2

UNION ALL

SELECT
SrNo + 1 AS SrNo
, ( CAST( c2 AS FLOAT ) ) AS c1
, ( CAST( ( c1 + c2 ) AS FLOAT ) ) AS c2
FROM FibonacciSequence
WHERE SrNo < 100

)

SELECT SrNo, c1
FROM FibonacciSequence
OPTION (MAXRECURSION 0);

Digital Scanner Result


SET NOCOUNT ON
DECLARE @t TABLE (Id int, ScanNumber NVARCHAR(116))

INSERT INTO @t
SELECT 1,--> 000 007 059
' _ _ _ _ _ _ _ _ _
| || || || || | || ||_ |_|
|_||_||_||_||_| ||_| _| _|

'

UNION
SELECT 2, --> 490 067 715
' _ _ _ _ _ _ _
|_||_|| || ||_ | | ||_
| _||_||_||_| | | | _|

'

UNION
SELECT 3, --> 680 X68 279
' _ _ _ _ _ _ _ _
|_ |_|| || ||_ |_| _| ||_|
|_||_||_||_||_||_||_ | _|

'

UNION
SELECT 4, --> 490 867 716
' _ _ _ _ _ _ _
|_||_|| ||_||_ | | ||_
| _||_||_||_| | | ||_|

'

UNION
SELECT 5, --> X90 867 716
' _ _ _ _ _ _ _
| ||_|| ||_||_ | | ||_
| _||_||_||_| | | ||_|

'

UNION
SELECT 6, --> 012 345 678
' _ _ _ _ _ _ _
| | | _| _||_||_ |_ ||_|
|_| ||_ _| | _||_| ||_|

'


;with ScanLine as
(
SELECT
ID
, 1 AS LineNumber
, SUBSTRING( REPLACE( ScanNumber, ' ', '$'), PATINDEX('%' + CHAR(13) + CHAR(10) + '%', ScanNumber ) + 2, LEN( ScanNumber ) ) AS ScanNumber
, SUBSTRING( REPLACE( ScanNumber, ' ', '$'), 1, PATINDEX('%' + CHAR(13) + CHAR(10) + '%', ScanNumber ) ) AS LineString
FROM @t

UNION ALL

SELECT
ID
, LineNumber + 1 AS LineNumber
, SUBSTRING( ScanNumber, PATINDEX('%' + CHAR(13) + CHAR(10) + '%', ScanNumber ) + 2, LEN( ScanNumber ) ) AS ScanNumber
, SUBSTRING( ScanNumber, 1, PATINDEX('%' + CHAR(13) + CHAR(10) + '%', ScanNumber ) ) AS LineString
FROM ScanLine
WHERE LEN(LineString) > 0
)

---- SELECT * FROM ScanLine ORDER BY Id, LineNumber OPTION (MAXRECURSION 0);

, ScanDigit AS
(
SELECT
Id
, LineNumber
, 1 AS CharNumber
, SUBSTRING( REPLACE( REPLACE( LineString, CHAR(13), ''), CHAR(10), ''), 4, LEN(LineString)) AS LineString
, SUBSTRING( REPLACE( REPLACE( LineString, CHAR(13), ''), CHAR(10), ''), 1, 3) AS CharString
FROM ScanLine
WHERE LineNumber <= 3

UNION ALL

SELECT
Id
, LineNumber
, CharNumber + 1 AS CharNumber
, SUBSTRING( LineString, 4, LEN(LineString)) AS LineString
, SUBSTRING( LineString, 1, 3) AS CharString
FROM ScanDigit
WHERE LEN(LineString) > 0
)

----- SELECT * FROM ScanDigit ORDER BY ID, CharNumber, LineNumber

, ScanNumber AS
(
SELECT
DISTINCT
ID
, CharNumber
, (
--REPLACE(
--REPLACE(
REPLACE( (
SELECT ',' + CharString + REPLICATE('$', 3 - LEN(CharString) )
FROM ScanDigit AS ConcanetChar
WHERE ConcanetChar.Id = ScanDigit.Id
AND ConcanetChar.CharNumber = ScanDigit.CharNumber
ORDER BY LineNumber
FOR XML PATH('')
), ',', '')
-- , ' ', ' ')
--, '
'
, '')
) AS CharString
FROM ScanDigit
)

---- SELECT * FROM ScanNumber ORDER BY ID, CharNumber OPTION (MAXRECURSION 0);

, DisplayNumber AS
(
SELECT
DISTINCT
ID
, CharNumber
, (
CASE CharString
WHEN '$_$|$||_|' THEN '0'
WHEN '$$$$$|$$|' THEN '1'
WHEN '$_$$_||_$' THEN '2'
WHEN '$_$$_|$_|' THEN '3'
WHEN '$$$|_|$$|' THEN '4'
WHEN '$_$|_$$_|' THEN '5'
WHEN '$_$|_$|_|' THEN '6'
WHEN '$_$$$|$$|' THEN '7'
WHEN '$_$|_||_|' THEN '8'
WHEN '$_$|_|$_|' THEN '9'
WHEN '' THEN ''
ELSE 'X'
END
) AS DisplayChar
FROM ScanNumber
)

--- SELECT * FROM DisplayNumber ORDER BY ID, CharNumber OPTION (MAXRECURSION 0);

SELECT DISTINCT
ID
, REPLACE( (
SELECT ',' + DisplayChar
FROM DisplayNumber AS Digit
WHERE Digit.Id = DisplayNumber.Id
ORDER BY CharNumber
FOR XML PATH('')
), ',', '') AS ScanNumber
FROM DisplayNumber
ORDER BY ID
OPTION (MAXRECURSION 0);

Calendar


SET NOCOUNT ON

SET LANGUAGE FRENCH

----;with MonthData AS
----(
---- SELECT DATEADD(MM, 11, CAST('2009/01/01' AS DATETIME) ) AS Dt

---- UNION ALL

---- SELECT DATEADD(DD, 1, Dt) AS Dt
---- FROM MonthData
---- WHERE MONTH(Dt) = MONTH(GETDATE())
----)
----SELECT * FROM MonthData

----SET LANGUAGE us_english



--SET LANGUAGE FRENCH

DECLARE @tblCalendar TABLE (Mth INT, Yr INT)

INSERT @tblCalendar(Mth, Yr) SELECT 8, 2009
INSERT @tblCalendar(Mth, Yr) SELECT 2, 1900
INSERT @tblCalendar(Mth, Yr) SELECT 10,1959

DECLARE @tblCalendarData TABLE (CalDate DATETIME)

INSERT INTO @tblCalendarData
SELECT DATEADD(MM, ( Mth - 1 ), CAST( ( '01/01/' + CAST( Yr AS VARCHAR(4) ) ) AS DATETIME ) )
FROM @tblCalendar

;with MonthData AS
(
SELECT CalDate, MONTH(CalDate) AS Mth, YEAR(CalDate) AS Yr, DATEPART(DW, CalDate) AS DW, DATEPART(WW, CalDate) AS WW
FROM @tblCalendarData

UNION ALL

SELECT DATEADD(DD, 1, CalDate) AS CalDate, Mth, Yr, DATEPART(DW, DATEADD(DD, 1, CalDate)) AS DW, DATEPART(WW, DATEADD(DD, 1, CalDate)) AS WW
FROM MonthData
WHERE MONTH(CalDate) IN ( SELECT Mth FROM @tblCalendarData )
)
, MonthView AS
(
SELECT
Yr
, Mth
, WW
, [1] AS W1
, [2] AS W2
, [3] AS W3
, [4] AS W4
, [5] AS W5
, [6] AS W6
, [7] AS W7
FROM
(
SELECT CAST( DATEPART( DAY, CalDate) AS VARCHAR(2) ) AS CalDate, WW, DW, Mth, Yr
FROM MonthData
) up
PIVOT ( MIN(CalDate) FOR DW IN ( [1], [2], [3], [4], [5], [6], [7] ) ) AS pvt
)
, CalendarView AS
(
SELECT
(
'|'
+ REPLICATE(' ', 4 - LEN( ISNULL( W1, '' ) ) ) + CAST( ISNULL( W1, '' ) AS VARCHAR(2) )
+ REPLICATE(' ', 4 - LEN( ISNULL( W2, '' ) ) ) + CAST( ISNULL( W2, '' ) AS VARCHAR(2) )
+ REPLICATE(' ', 4 - LEN( ISNULL( W3, '' ) ) ) + CAST( ISNULL( W3, '' ) AS VARCHAR(2) )
+ REPLICATE(' ', 4 - LEN( ISNULL( W4, '' ) ) ) + CAST( ISNULL( W4, '' ) AS VARCHAR(2) )
+ REPLICATE(' ', 4 - LEN( ISNULL( W5, '' ) ) ) + CAST( ISNULL( W5, '' ) AS VARCHAR(2) )
+ REPLICATE(' ', 4 - LEN( ISNULL( W6, '' ) ) ) + CAST( ISNULL( W6, '' ) AS VARCHAR(2) )
+ REPLICATE(' ', 4 - LEN( ISNULL( W7, '' ) ) ) + CAST( ISNULL( W7, '' ) AS VARCHAR(2) )
+ '|'
) AS Calender, Yr, Mth, WW
FROM MonthView

UNION ALL

SELECT REPLICATE('-', 30), YEAR(CalDate) AS Yr, MONTH(CalDate) AS Mth, -3 AS WW
FROM @tblCalendarData

UNION ALL

SELECT
(
REPLICATE(' ', ( 28 - LEN( DateName( mm, CalDate ) + ' ' + CAST( Year(CalDate) AS VARCHAR(4) ) ) ) /2 )
+ DateName( mm, CalDate ) + ' ' + CAST( Year(CalDate) AS VARCHAR(4) )
) AS Calender
, YEAR(CalDate) AS Yr, MONTH(CalDate) AS Mth, -2 AS WW
FROM @tblCalendarData

UNION ALL

SELECT '|' + REPLICATE('=', 28) + '|' AS Calender, YEAR(CalDate) AS Yr, MONTH(CalDate) AS Mth, -1 AS WW
FROM @tblCalendarData

UNION ALL

SELECT '| Sun Mon Tue Wed Thu Fri Sat|' AS Calender, YEAR(CalDate) AS Yr, MONTH(CalDate) AS Mth, 0 AS WW
FROM @tblCalendarData

UNION ALL

SELECT REPLICATE('=', 30) AS Calender, YEAR(CalDate) AS Yr, MONTH(CalDate) AS Mth, 999 AS WW
FROM @tblCalendarData
)

SELECT
(
CASE WW
WHEN -2
THEN '|' + Calender + REPLICATE(' ', (28 - LEN( RTRIM( Calender ) ) ) ) + '|'
ELSE Calender
END
) AS Calender
FROM CalendarView
ORDER BY Yr, Mth, WW
OPTION (MAXRECURSION 0)

SET LANGUAGE us_english

Year Dates


DECLARE @Year AS INT,
@FirstDateOfYear DATETIME,
@LastDateOfYear DATETIME
-- You can change @year to any year you desire
SELECT @year = 2010
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year - 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year - 1900 + 1, 0)

SELECT @FirstDateOfYear, @LastDateOfYear
-- Creating Query to Prepare Year Data
;WITH cte AS
(
SELECT 1 AS DayID
, @FirstDateOfYear AS FromDate
, DATENAME(dw, @FirstDateOfYear) AS Dayname

UNION ALL

SELECT cte.DayID + 1 AS DayID
, DATEADD(d, 1 ,cte.FromDate)
, DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
FROM cte
WHERE DATEADD(d,1,cte.FromDate) < @LastDateOfYear
)
SELECT FromDate AS Date, Dayname
FROM CTE
WHERE DayName IN ('Saturday','Sunday')
/*
WHERE DayName IN ('Saturday,Sunday') -- For Weekend
WHERE DayName NOT IN ('Saturday','Sunday') -- For Not Weekday
WHERE DayName LIKE 'Monday' -- For Monday
WHERE DayName LIKE 'Sunday' -- For Sunday
*/
OPTION (MaxRecursion 370)

Merge Query W/O Using Merge


DECLARE @tblSource Table ( ID INT, GroupID INT, Name VARCHAR(10) )
DECLARE @tblTarget Table ( ID INT, GroupID INT, Name VARCHAR(10) )

INSERT INTO @tblSource VALUES (1,1,'a1')
INSERT INTO @tblSource VALUES (2,1,'a2')
INSERT INTO @tblSource VALUES (3,1,'a3')
INSERT INTO @tblSource VALUES (4,1,'a4')

INSERT INTO @tblTarget VALUES (1,1,'a1')
INSERT INTO @tblTarget VALUES (3,1,'a3')
INSERT INTO @tblTarget VALUES (5,1,'a5')
INSERT INTO @tblTarget VALUES (6,2,'b2')
INSERT INTO @tblTarget VALUES (7,2,'b1')

-----Delete Record
SELECT *
FROM @tblSource AS tblSource
FULL OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID
WHERE tblSource.ID IS NULL
AND tblTarget.ID IS NOT NULL
AND tblTarget.GroupID = 1

-----Update Record
SELECT *
FROM @tblSource AS tblSource
FULL OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID
WHERE tblSource.ID IS NOT NULL
AND tblTarget.ID IS NOT NULL

-----Inserted Record
SELECT *
FROM @tblSource AS tblSource
FULL OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID
WHERE
tblSource.ID IS NOT NULL
AND tblTarget.ID IS NULL
AND tblSource.GroupID = 1

--SELECT *
--FROM @tblSource AS tblSource
-- LEFT OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID

--SELECT *
--FROM @tblSource AS tblSource
-- RIGHT OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID

--SELECT *
--FROM @tblSource AS tblSource
-- FULL OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID

PIVOT


DECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME

----------------
SET NOCOUNT ON

SET @FromDate = '10/1/2009'
SET @ToDate = '10/1/2010'

CREATE Table #tblSchool ( SchoolName VARCHAR(20), [Date] DATETIME, NoOfBoys INT, NoOfGirls INT )

DECLARE @SqlQuery VARCHAR(MAX)
DECLARE @Months VARCHAR(MAX)
DECLARE @DisplayMonths VARCHAR(MAX)
SET @SqlQuery = ''

INSERT INTO #tblSchool VALUES ('aa','1/1/2010', 10, 15)
INSERT INTO #tblSchool VALUES ('aa','1/2/2010', 15, 17)
INSERT INTO #tblSchool VALUES ('aa','2/1/2010', 18, 19)
INSERT INTO #tblSchool VALUES ('aa','2/2/2010', 21, 22)

INSERT INTO #tblSchool VALUES ('bb','1/1/2010', 11, 17)
INSERT INTO #tblSchool VALUES ('bb','1/2/2010', 16, 19)
INSERT INTO #tblSchool VALUES ('bb','2/1/2010', 19, 21)
INSERT INTO #tblSchool VALUES ('bb','2/2/2010', 25, 25)

;with MonthData AS
(
SELECT DATEADD(DD, 1 - DAY( @FromDate ), @FromDate ) AS MonthDates

UNION ALL

SELECT DATEADD(MM, 1, MonthDates ) AS MonthDates
FROM MonthData
WHERE MonthDates <= @ToDate
)

SELECT *
INTO #MonthData
FROM MonthData
ORDER BY MonthDates

SELECT @Months = STUFF(
(
SELECT ', [' + SUBSTRING( CONVERT( VARCHAR(20), MonthDates ), 1, 3 )
+ ' ' + SUBSTRING( CONVERT( VARCHAR(20), MonthDates ), 8, 4 )
+ ']'
FROM #MonthData
FOR XML PATH('')
),1,1,'')

SELECT @DisplayMonths = STUFF(
(
SELECT ', ISNULL( [' + SUBSTRING( CONVERT( VARCHAR(20), MonthDates ), 1, 3 )
+ ' ' + SUBSTRING( CONVERT( VARCHAR(20), MonthDates ), 8, 4 )
+ '], 0 ) AS [' + SUBSTRING( CONVERT( VARCHAR(20), MonthDates ), 1, 3 )
+ ' ' + SUBSTRING( CONVERT( VARCHAR(20), MonthDates ), 8, 4 )
+ ']'
FROM #MonthData
FOR XML PATH('')
),1,1,'')

SET @SqlQuery ='SELECT SchoolName, ' + @DisplayMonths + '
FROM
(
SELECT
SchoolName
, (
SUBSTRING( CONVERT( VARCHAR(20), [Date] ), 1, 3 )
+ '
' '' + SUBSTRING( CONVERT( VARCHAR(20), [Date] ), 8, 4 )
) AS BillMonth
, ( NoOfBoys + NoOfGirls ) AS TotalStudent
FROM #tblSchool
WHERE #tblSchool.[Date] BETWEEN '
'' + CAST( @FromDate AS VARCHAR(20) ) + ''' AND ''' + CAST( @ToDate AS VARCHAR(20) ) + '''
) AS ItemDetail
PIVOT ( SUM(TotalStudent) FOR BillMonth IN ( '
+ @Months + ' ) ) AS pvt '

PRINT ( @SqlQuery )
EXECUTE ( @SqlQuery )

DROP TABLE #tblSchool
DROP TABLE #MonthData

Find Field into Datatable


DECLARE @FieldName VARCHAR(255)

SET @FieldName = 'YTD'

SELECT
Obj.Name AS ObjectName
, (
CASE Obj.XType
WHEN 'F' then 'foreign keys'
WHEN 'FN' then 'user defined functions'
WHEN 'TF' then 'user defined table functions'
WHEN 'P' then 'stored procedures'
WHEN 'PK' then 'primary keys'
WHEN 'S' then 'system tables'
WHEN 'TR' then 'triggers'
WHEN 'U' then 'user table'
ELSE Obj.XType
END
) AS ObjectType
, Col.Name AS FieldName
, Col.xType as FieldType
, Type_Name(Col.xType) AS FieldTypeName
, Col.Length as FieldLength
, Col.ColStat & 1 AS IsIdentity
FROM SysColumns AS Col
INNER JOIN SysObjects AS Obj ON Obj.Id = Col.Id
WHERE Col.Name like '%' + @FieldName + '%'
ORDER BY ObjectType, ObjectName

Comma Seprate String


DECLARE @Table TABLE (ID1 INT, ID2 INT, ID3 INT, Name VARCHAR(10))

INSERT INTO @Table VALUES (1,1,1,'a')
INSERT INTO @Table VALUES (1,1,1,'b')
INSERT INTO @Table VALUES (2,1,1,'a')
INSERT INTO @Table VALUES (2,1,1,'b')
INSERT INTO @Table VALUES (3,1,1,'a')
INSERT INTO @Table VALUES (3,1,1,'b')

SELECT * FROM @Table

SELECT
DISTINCT
ID1
, ID2
, ID3
, REPLACE(
REPLACE(
REPLACE(
CAST((
SELECT Name
FROM @Table AS B
WHERE b.ID1 = a.ID1
AND b.ID2 = a.ID2
AND b.ID3 = a.ID3
FOR XML PATH('')
) AS VARCHAR(MAX) )
, ''
, ', '
)
, ''
, ''
)
, ''
, ''
) AS Name
FROM @Table AS A

SELECT
DISTINCT
ID1
, ID2
, ID3
, REPLACE(
(
SELECT Name AS 'data()'
FROM @Table AS B
WHERE b.ID1 = a.ID1
AND b.ID2 = a.ID2
AND b.ID3 = a.ID3
FOR XML PATH('')
)
, ' '
, ', '
) AS Name
FROM @Table AS A

SELECT STUFF((
SELECT ',' + s.Name
FROM @Table s
ORDER BY s.Name
FOR XML PATH('')
),1,1,'') AS CSV


SELECT Name AS 'data()'
FROM @Table
FOR XML PATH('')

Find Content into Database


SET NOCOUNT ON

DECLARE @NoOfCount INT
DECLARE @CurrentId INT
DECLARE @ProecdueName VARCHAR(100)
DECLARE @FindContent VARCHAR(100)
DECLARE @XType VARCHAR(10)

SET @CurrentId = 1
SET @ProecdueName = ''
SET @FindContent = 'procAlertsUpdateAlert' -- Type Content for find

DECLARE @Table TABLE
(
ID INT IDENTITY(1,1)
, ProecdueName VARCHAR(100)
, XType VARCHAR(10)
)

DECLARE @TableProcedureContent TABLE
(
Content VARCHAR(MAX)
)

INSERT INTO @Table
SELECT Name, XType
FROM SysObjects
WHERE XType IN ( 'FN', 'IF', 'TF', 'P', 'TR', 'V')
AND Category = 0
ORDER BY XType, Name

SELECT @NoOfCount = COUNT(ID) FROM @Table

WHILE ( @CurrentId <= @NoOfCount )
BEGIN
DELETE FROM @TableProcedureContent

SELECT @ProecdueName = ProecdueName
, @XType = XType
FROM @Table
WHERE Id = @CurrentId

INSERT INTO @TableProcedureContent
EXECUTE sp_helptext @ProecdueName

IF ( EXISTS(
SELECT *
FROM @TableProcedureContent
WHERE Content Like '%' + @FindContent + '%'
) )
BEGIN
PRINT ( CASE @XType
WHEN 'P' THEN 'Procedure'
WHEN 'FN' THEN 'Function'
WHEN 'TR' THEN 'Trigger'
ELSE ''
END ) + ' ' + @ProecdueName
END

SET @CurrentId = @CurrentId + 1
END

Sunday, January 24, 2010

Convert Comma Seprate String to Row


DECLARE @TableA TABLE (Name VARCHAR(100) )
INSERT INTO @TableA VALUES ('MARY,MA,JENNY')

DECLARE @TableB TABLE (Name VARCHAR(100), DISTRICT_NO INT )
INSERT INTO @TableB VALUES ('MARY', 2)
INSERT INTO @TableB VALUES ('YY', 3)
INSERT INTO @TableB VALUES ('JOHN', 4)

DECLARE @XML XML

SELECT @XML = CAST( (
SELECT '<name>' + REPLACE(Name, ',', '</name><name>') + '</name>'
FROM @TableA
) AS XML )

SELECT *
FROM @TableB AS TableB
INNER JOIN (
SELECT
TableA.value('.', 'VARCHAR(100)') AS Name
FROM @XML.nodes('Name')e(TableA)
) AS TableA ON TableA.Name = TableB.Name

Tuesday, January 19, 2010

PIVOT IN Multipal Column


DECLARE @Table TABLE ( OrderId INT, ItemId INT, ItemQty INT, ItemValue INT)

INSERT INTO @Table VALUES (1,1,1,10)
INSERT INTO @Table VALUES (1,2,2,20)
INSERT INTO @Table VALUES (1,3,3,30)

INSERT INTO @Table VALUES (2,1,1,11)
INSERT INTO @Table VALUES (2,2,2,21)
INSERT INTO @Table VALUES (2,3,3,31)

INSERT INTO @Table VALUES (3,1,1,12)
INSERT INTO @Table VALUES (3,2,2,22)
INSERT INTO @Table VALUES (3,3,3,32)

SELECT
OrderId
, SUM([Q1]) AS Q1
, SUM([Q2]) AS Q2
, SUM([Q3]) AS Q3
, SUM([V1]) AS V1
, SUM([V2]) AS V2
, SUM([V3]) AS V3
FROM
(
SELECT
OrderId
, 'Q' + CAST( ItemId AS VARCHAR(10) ) AS QtyItemCd
, ItemQty
, 'V' + CAST( ItemId AS VARCHAR(10) ) AS ValueItemCd
, ItemValue
FROM @Table
) AS OrderDetail
PIVOT ( SUM(ItemQty) FOR QtyItemCd IN ( [Q1], [Q2], [Q3] ) ) AS pvtQty
PIVOT ( SUM(ItemValue) FOR ValueItemCd IN ( [V1], [V2], [V3] ) ) AS pvtValue
GROUP BY OrderId
ORDER BY OrderId