Script to generate calender for a month

by Amol 29. April 2010 12:14
Now days we can see variety of calender controls to display on web page. We might not or rarely used t-SQL to generate calender.
This code is just to test our T-SQL logic.
 
Here is T_SQL code.
 

SET NOCOUNT ON

DECLARE @TempT TABLE (MM INT, YYYY INT)

INSERT @TempT(MM, YYYY) SELECT 02, 2009

INSERT @TempT(MM, YYYY) SELECT 03, 2010

;

WITH Numbers(LineNumber) AS

(

    SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4 UNION ALL

    SELECT 5 UNION ALL

    SELECT 6 UNION ALL

    SELECT 7 UNION ALL

    SELECT 8 UNION ALL

    SELECT 9 UNION ALL

    SELECT 10 UNION ALL

    SELECT 11 UNION ALL

    SELECT 12

)

, FirstAndLastDayOfMonth AS

(

    SELECT CAST(CONVERT(VARCHAR,DATEADD(dd,-(DAY(CAST(CAST(YYYY AS VARCHAR) + '-' + CAST(MM AS VARCHAR) + '-' + '1' AS DATETIME))-1),CAST(CAST(YYYY AS VARCHAR) + '-' + CAST(MM AS VARCHAR) + '-' + '1' AS DATETIME)),101) AS DATETIME) AS FirstDay

          ,CAST(CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,CAST(CAST(YYYY AS VARCHAR) + '-' + CAST(MM AS VARCHAR) + '-' + '1' AS DATETIME)))),DATEADD(mm,1,CAST(CAST(YYYY AS VARCHAR) + '-' + CAST(MM AS VARCHAR) + '-' + '1' AS DATETIME))),101) AS DATETIME) AS LastDay

    FROM @TempT

)

, YearMonthAndDays AS

(

    SELECT

         UPPER(DATENAME(MONTH, FirstDay)) + N' ' + DATENAME(YEAR, FirstDay) AS Header

        ,REPLICATE(N' ', 4 * ((CASE WHEN DATEPART(WEEKDAY, FirstDay) + @@DATEFIRST > 7 THEN DATEPART(WEEKDAY, FirstDay) + @@DATEFIRST - 7

                               ELSE DATEPART(WEEKDAY, FirstDay) + @@DATEFIRST

                               END) - 1)) +

         SUBSTRING(N'   1   2   3   4   5   6   7   8   9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31',

            1, 4 * DAY(LastDay) + 1) + N'                             ' AS AllDays

        ,ROW_NUMBER() OVER(ORDER BY FirstDay) AS RowNum

    FROM FirstAndLastDayOfMonth

)

, WeekHeader AS

(

   SELECT 'Sun Mon Tue Wed Thu Fri Sat' AS DayNames

)

, Calendar AS

(

    SELECT

         CASE LineNumber

            WHEN 1 THEN N'*=============================*'

            WHEN 2 THEN N'|' + LEFT(REPLICATE(N' ', (29 - LEN(Header)) / 2) + Header + REPLICATE(N' ', 29), 29) + N'|'

            WHEN 3 THEN N'|=============================|'

            WHEN 4 THEN N'|' + N' ' + DayNames + N' ' + N'|'

            WHEN 5 THEN N'|-----------------------------|'

            WHEN 12 THEN N'*-----------------------------*'

            ELSE N'|' + SUBSTRING(AllDays, 1 + (LineNumber - 6) * 28, 28) + N' ' + N'|'

         END AS Outputlines

        ,RowNum

        ,LineNumber

    FROM YearMonthAndDays

    CROSS JOIN WeekHeader

    CROSS JOIN Numbers

    WHERE LineNumber < 6

       OR LEN(SUBSTRING(AllDays, 1 + (LineNumber - 6) * 28, 28)) > 0

       OR LineNumber = 12

)

SELECT  Outputlines

FROM    Calendar

ORDER BY RowNum, LineNumber

Tags:

SQL Server Scripts

User defined function to split string by delimiter

by Amol 10. September 2009 11:53

In SQL Server, there is no inbuilt function to split the string by delimiter. If there is need to split string, user defined function has to be created for this task.

Main purpose of split function is to split given string by given delimiter. Here is the user defined SQL Server split function.

CREATE FUNCTION [dbo].[Split]
(   
    @InputString VARCHAR (MAX), 
    @Delimiter VARCHAR (10)
)
RETURNS @TempTable TABLE ([Id] INT IDENTITY (1, 1) NOT NULL,
                          [Values] VARCHAR (8000) NULL)
AS
BEGIN
    DECLARE  @Length INT
            ,@Index INT
            ,@LastIndex INT
            ,@Counter INT
    SET @InputString = @InputString + @Delimiter
    SET @Length = len(@InputString)
    SET @Index = 1
    SET @Counter = 1
    WHILE (@Counter < @Length)
    BEGIN
        IF charindex(@Delimiter, @InputString, @Index) > 0 
        BEGIN
            SET @lastIndex = charindex(@Delimiter, @InputString, @Index) - @Index
            INSERT INTO @TempTable ([Values]) 
            SELECT substring(@InputString, @Index, @LastIndex)
            SET @Index = charindex(@Delimiter, @InputString, @Index) + len(@Delimiter)
        END
        SET @Counter = @Counter + 1
    END
RETURN
END

 

Tags: ,

SQL Server Scripts

Update column in sequential order

by Amol 16. August 2009 11:59

This example can be very useful if you would want to add sequence number to a column.Below example will update the table with sequence number even if the table contains duplicate values.

See the example below:

--Create the sequence table

CREATE TABLE Sequence (ID INT, [Name] VARCHAR(100))

GO

--Insert the data into Sequence table

INSERT INTO Sequence (ID, [Name])

SELECT NULL, 'xyz1' UNION ALL
SELECT NULL, 'xyz2' UNION ALL
SELECT NULL, 'xyz3' UNION ALL
SELECT NULL, 'xyz4' UNION ALL
SELECT NULL, 'xyz5' UNION ALL
SELECT NULL, 'xyz6' UNION ALL
SELECT NULL, 'xyz6' UNION ALL
SELECT NULL, 'xyz6' 

 GO

 

--Update the Sequence table with the value of ID

DECLARE @ID INT
SET @ID =
UPDATE Sequence SET @ID = ID = @ID + 1

--Query Sequence table

SELECT *
FROM Sequence;


Tags: , ,

SQL Server Scripts

Category

Recent Posts

Tag cloud