find out the continuous date or single date using sql server

CREATE TABLE #Employee_Leave

(emp_id INTEGER NOT NULL,

 leave_date DATEtime NOT NULL

 

 PRIMARY KEY (emp_id, leave_date));

 

INSERT INTO #Employee_Leave(emp_id,leave_date)

 

select 47, '2012-11-29'

union all

select 47, '2012-11-30'

union all

select 47, '2012-12-04'

union all

select 47, '2012-12-05'

union all

select 47, '2012-12-07'

union all

select 47, '2012-12-10'

union all

select 48, '2012-11-29'

union all

select 48, '2012-11-30'

union all

select 48, '2012-12-04'

union all

select 48, '2012-12-05'

union all

select 48, '2012-12-07'

union all

select 48, '2012-12-10'

union all

select 48, '2012-10-14'

 

;WITH

cteGroupedDates AS

( --=== Find the unique dates and assign them to a group.

     -- The group looks like a date but the date means nothing except that adjacent

     -- dates will be a part of the same group.

 SELECT emp_id,

        UniqueDate = leave_date,

        DateGroup  = DATEADD(dd

            ,-ROW_NUMBER() OVER (

                PARTITION BY emp_id ORDER BY emp_id,leave_date)

            ,CASE DATEPART(dw, leave_date) WHEN 2 THEN DATEADD(dd, -2, leave_date) ELSE leave_date END )

  FROM #Employee_Leave

  GROUP BY emp_id,leave_date

)

--===== Now, if we find the MIN and MAX date for each DateGroup, we'll have the

     -- Start and End dates of each group of contiguous dates.  While we're at it,

     -- we can also figure out how many days are in each range of days.

 SELECT emp_id,

        StartDate = MIN(UniqueDate),

        EndDate   = MAX(UniqueDate),

        [Days]    = DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1,

        [Type]    = CASE WHEN DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1 = 1 THEN 'Single'

                        WHEN DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1 > 1 AND

                            DATEPART(dw, MIN(UniqueDate)) > DATEPART(dw, MAX(UniqueDate)) THEN 'Connecting'

                        ELSE 'Continuous' END

   FROM cteGroupedDates

  GROUP BY emp_id,DateGroup

  ORDER BY emp_id,StartDate

 

DROP TABLE #Employee_Leave

 

 

Output

emp_id  StartDate                                   EndDate                                    Days      Type

47         2012-11-29 00:00:00.000           2012-11-30 00:00:00.000           2          Continuous

47         2012-12-04 00:00:00.000           2012-12-05 00:00:00.000           2          Continuous

47         2012-12-07 00:00:00.000           2012-12-10 00:00:00.000           4          Connecting

48         2012-10-14 00:00:00.000           2012-10-14 00:00:00.000           1          Single

48         2012-11-29 00:00:00.000           2012-11-30 00:00:00.000           2          Continuous

48         2012-12-04 00:00:00.000           2012-12-05 00:00:00.000           2          Continuous

48         2012-12-07 00:00:00.000           2012-12-10 00:00:00.000           4          Connecting

Find out the Lower caracter count in a string

CREATE

FUNCTION CountLowerCase

(

@input

nvarchar(50)

)

RETURNS

int

AS

BEGIN

declare @len int

declare @i int

declare @count int

declare @ascii int

set @len = len(@input)

set @i = 1

set @count = 0

while @i <= @len

begin

set @ascii = ascii(substring(@input, @i, 1))

if @ascii >= 97 and @ascii <= 122

begin

set @count = @count +1

end

set @i = @i + 1

end

return @count

END

/*Output*/

select

dbo.CountLowerCase('SQLserver2008')

output

=6

Find out the Upper caracter count in a string

CREATE

FUNCTION CountUpperCase

(

@input

nvarchar(50)

)

RETURNS

int

AS

BEGIN

declare @len int

declare @i int

declare @count int

declare @ascii int

set @len = len(@input)

set @i = 1

set @count = 0

while @i <= @len

begin

set @ascii = ascii(substring(@input, @i, 1))

if @ascii >= 65 and @ascii <= 90

begin

set @count = @count +1

end

set @i = @i + 1

end

return @count

END

/*Output*/

select

dbo.CountUpperCase('SQLserver2008')

output

=3

Find out 2nd,3rd and N th maximum value in a table

Solution to finding the 2nd highest salary:

Select MAX(Salary) From Employee

Where Salary Not In (Select Max(Salary) From Employee

 

Using not equal to operator:

select MAX(Salary) from Employee

WHERE Salary <> (select MAX(Salary) from Employee )

 

 

To finding the nth highest salary

 

SELECT *

FROM Employee Emp1

WHERE (N-1) = (

SELECT COUNT(DISTINCT(Emp2.Salary))

FROM Employee Emp2

WHERE Emp2.Salary > Emp1.Salary)

Using order by: 

SELECT TOP 1 salary
  FROM (
  SELECT DISTINCT TOP n salary
  FROM employee
  ORDER BY salary DESC) a
  ORDER BY salary

CONVERT AMOUNT(NUMBER) TO WORD(STRING) USING SQL SERVER

CONVERT AMOUNT(NUMBER) TO WORD(STRING) USING SQL SERVER

_______________________________________________________

 

/*TABLE CREATION*/

 CREATE TABLE  TblCurrencyBelow20  (ID INT IDENTITY(0,1), Word VARCHAR(32))

 CREATE TABLE  TblCurrencyBelow100 (ID INT IDENTITY(2,1), Word VARCHAR(32))

 

 /*VALUE INSERT*/

    INSERT TblCurrencyBelow20 (Word) VALUES ('ZERO')

    INSERT TblCurrencyBelow20 (Word) VALUES ('ONE')

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'TWO' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'THREE')

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'FOUR' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'FIVE' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'SIX' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'SEVEN' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'EIGHT')

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'NINE')

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'TEN')

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'ELEVEN' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'TWELVE' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'THIRTEEN' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'FOURTEEN')

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'FIFTEEN' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'SIXTEEN' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'SEVENTEEN')

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'EIGHTEEN' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'NINETEEN' )

 

    INSERT TblCurrencyBelow100 VALUES ('TWENTY')

    INSERT TblCurrencyBelow100 VALUES ('THIRTY')

    INSERT TblCurrencyBelow100 VALUES ('FORTY')

    INSERT TblCurrencyBelow100 VALUES ('FIFTY')

    INSERT TblCurrencyBelow100 VALUES ('SIXTY')

    INSERT TblCurrencyBelow100 VALUES ('SEVENTY')

    INSERT TblCurrencyBelow100 VALUES ('EIGHTY')

    INSERT TblCurrencyBelow100 VALUES ('NINETY')

 

/*CREATE FUNCTION FOR RETURN AMOUNT IN WORD*/

CREATE FUNCTION fnNumberToWords

(

    @Number AS BIGINT

) RETURNS VARCHAR(MAX)

AS

BEGIN

     DECLARE @BelowHundred AS VARCHAR(126)

 

    IF @Number > 99

    BEGIN

        SELECT @belowHundred = dbo.fnNumberToWords( @Number % 100)

    END

 

    DECLARE @NumberInWords VARCHAR(MAX)

    SET @NumberInWords  =

    (

      SELECT

        CASE

            WHEN @Number = 0 THEN  ''

 

            WHEN @Number BETWEEN 1 AND 19

                THEN (SELECT Word FROM TblCurrencyBelow20 WITH(NOLOCK) WHERE ID=@Number)

 

            WHEN @Number BETWEEN 20 AND 99

                THEN (SELECT Word FROM TblCurrencyBelow100 WITH(NOLOCK) WHERE ID=@Number/10)+ '-' + dbo.fnNumberToWords( @Number % 10)

 

            WHEN @Number BETWEEN 100 AND 999

                THEN (dbo.fnNumberToWords( @Number / 100)) + ' HUNDRED '+

                        CASE

                            WHEN @belowHundred <> ''

                                THEN 'AND ' + @belowHundred else @belowHundred

                        END

 

            WHEN @Number BETWEEN 1000 AND 999999

                THEN (dbo.fnNumberToWords( @Number / 1000))+ ' THOUSAND '+ dbo.fnNumberToWords( @Number % 1000) 

 

            WHEN @Number BETWEEN 1000000 AND 999999999

                THEN (dbo.fnNumberToWords( @Number / 1000000)) + ' MILLION '+ dbo.fnNumberToWords( @Number % 1000000)

 

            WHEN @Number BETWEEN 1000000000 AND 999999999999

                THEN (dbo.fnNumberToWords( @Number / 1000000000))+' BILLION '+ dbo.fnNumberToWords( @Number % 1000000000)

           

            ELSE ' INVALID INPUT'

        END

    )

 

    SELECT @NumberInWords = RTRIM(@NumberInWords)

 

    SELECT @NumberInWords = RTRIM(LEFT(@NumberInWords,LEN(@NumberInWords)-1)) WHERE RIGHT(@NumberInWords,1)='-'

 

    RETURN (@NumberInWords)

 

END

 

/*OUTPU*/

SELECT DBO.fnNumberToWords(1) AS AMOUNT_IN_WORD

AMOUNT_IN_WORD

ONE

 

SELECT DBO.fnNumberToWords(37) AS AMOUNT_IN_WORD

AMOUNT_IN_WORD

THIRTY-SEVEN

 

SELECT DBO.fnNumberToWords(178) AS AMOUNT_IN_WORD

AMOUNT_IN_WORD

ONE HUNDRED AND SEVENTY-EIGHT

 

SELECT DBO.fnNumberToWords(1345) AS AMOUNT_IN_WORD

AMOUNT_IN_WORD

ONE THOUSAND THREE HUNDRED AND FORTY-FIVE

find out what are the days available in two different dates

declare

@startdate varchar(30)

declare

@enddate varchar(30)

Declare

@init int

Declare

@count int

select

@startdate='2013-01-18 00:00:00.000',@enddate='2013-01-25 00:00:00.000',@init=0

select

@count=DATEDIFF(day,@startdate,@enddate)

if

OBJECT_ID('#tab')>0

drop

table #tab

create

table #tab(sno int identity(1,1),[date] datetime)

while

@init<=@count

begin

insert

into #tab([date])

select

dateadd(day,@init,@startdate)

select

@init=@init+1

end

select

* from #tab

First charactor upper case using delimiter

 

First charactor upper case using delimiter

Declare @RowData nvarchar(2000)

Declare @SplitOn nvarchar(5)

Declare @Cnt int

Declare @cnt1 int

Declare @init int

Declare @datavalue nvarchar(200)

Declare @len int

Declare @datavalue1 nvarchar(200)

select @RowData='split. a string based on delimiter',@SplitOn=' '

select @Cnt = 1

select @datavalue=''

select @init=1

select @datavalue1=''

Declare @RtnValue table

(

Id

int identity(1,1),

Data

nvarchar(100)

)

select @RowData=REPLACE(@RowData,'.','')

/*Break the string using delimiter and then insert that in to temp table*/

While (Charindex(@SplitOn,@RowData)>0)

Begin

Insert Into @RtnValue (data)

Select

Data

= ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

End

Insert Into @RtnValue (data)

Select Data = ltrim(rtrim(@RowData))

select @cnt1=count(*) from @RtnValue

/*update first char upper case remaining lower case*/

while @init<=@cnt1

begin

select @datavalue=DATA from @RtnValue where Id=@init

select @len=len(@datavalue)

update @RtnValue set DATA=upper(left(@datavalue,1))+SUBSTRING(@datavalue,2,@len-1) where Id=@init

select @init = @init + 1

end

select @init=1

/*Concate that char to original position.*/

while @init<=@cnt1

begin

select @datavalue1=@datavalue1+' '+DATA from @RtnValue where Id=@init

select @init=@init+1

end

select @datavalue1 as 'Capital'

 

--Output

 

Capital

--------------------------------------

Split A String Based On Delimiter