DECLARE @tblUInfo TABLE (UserID INT, ParentUserID INT, UserName VARCHAR(100) )
INSERT INTO @tblUInfo VALUES (1,0, 'A')
INSERT INTO @tblUInfo VALUES (2,1, 'A1')
INSERT INTO @tblUInfo VALUES (3,1, 'A2')
INSERT INTO @tblUInfo VALUES (4,0, 'B')
INSERT INTO @tblUInfo VALUES (5,4, 'B1')
INSERT INTO @tblUInfo VALUES (6,4, 'B2')
;WITH cteNodes AS
(
--initialization
SELECT UserID, ParentUserID, ISNULL(UserName, '' ) AS UserTree, 0 AS UserLevel
FROM @tblUInfo
--WHERE ParentUserID IS NULL
UNION ALL
----recursive execution
SELECT P.UserID, N.ParentUserID, ISNULL( N.UserName, '' ), P.UserLevel + 1
FROM cteNodes AS P
INNER JOIN @tblUInfo AS N ON N.UserID = P.ParentUserID
)
--- SELECT * FROM cteNodes ORDER BY UserID, UserLevel OPTION (MaxRecursion 32767)
, cteEmployeePath AS
(
SELECT tblUInfo.UserID
, tblUInfo.UserName
,(
SELECT CAST(UserTree AS VARCHAR ) + ':'
FROM cteNodes AS UserParth
WHERE UserParth.UserID = tblUInfo.UserID
ORDER BY UserLevel DESC
FOR XML PATH('')
) AS UserTree
,(
SELECT MAX(UserLevel)
FROM cteNodes AS UserParth
WHERE UserParth.UserID = tblUInfo.UserID
) AS UserLevel
FROM @tblUInfo AS tblUInfo
)
SELECT UserID, ( REPLICATE('--', UserLevel) + UserName ) AS UserName
FROM cteEmployeePath
ORDER BY UserTree
OPTION (MaxRecursion 32767)
.Net, MVC, MVP, SQL, JQuery, JSON, Javascript
Showing posts with label cte. Show all posts
Showing posts with label cte. Show all posts
Wednesday, June 9, 2010
User Tree
Monday, January 25, 2010
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)
Subscribe to:
Posts (Atom)