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