SQL Server

declare @tb1 table (Id int identity(1,1), Name nvarchar(50), CheckInCheckOut bit, CheckInCheckOutDateTime datetime)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)Values(‘Poter, Harry’,1,’2011-08-30 17:00:15.090′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,0,’2011-08-30 17:00:20.140′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,1,’2011-08-30 17:00:24.890′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,0,’2011-08-30 17:00:29.983′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,1,’2011-08-31 17:00:38.983′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,0,’2011-08-31 17:00:44.967′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,1,’2011-09-01 17:00:54.950′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,0,’2011-09-01 17:01:03.590′)
–insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,1,’2011-09-07 15:07:22.140′)
–select * from @tb1 order by CheckInCheckOutDateTime asc

declare @tb2 table (Id int, Name nvarchar(50), CheckInCheckOut bit, ClockIn datetime, ClockOut datetime, lastadd bit)
Declare @cnt int
set @cnt = 0;
while (@cnt< (select count(*) from @tb1))
Begin
if((select id from @tb1 where Id=@cnt+1)=1)
Begin
if((select CheckInCheckOut from @tb1 where Id=@cnt+1)=1)
Begin
Insert into @tb2(Id,Name,CheckInCheckOut,ClockIn,ClockOut,lastadd)
(select id,Name,CheckInCheckOut,CheckInCheckOutDateTime,null,CheckInCheckOut from @tb1 where Id=1)
End
Else
Begin
Insert into @tb2(Id,Name,CheckInCheckOut,ClockIn,ClockOut,lastadd)
(select id,Name,CheckInCheckOut,null,CheckInCheckOutDateTime,CheckInCheckOut from @tb1 where Id=1)
End
End
else
Begin
if((select top 1 lastadd from @tb2 order by Id desc)(select CheckInCheckOut from @tb1 where Id=@cnt+1))
Begin
if((select top 1 lastadd from @tb2 order by Id desc)=1)
Begin
update @tb2
set ClockOut = (select CheckInCheckOutDateTime from @tb1 where Id=@cnt+1)
,lastadd = (select CheckInCheckOut from @tb1 where Id=@cnt+1)
where Id = (select top 1 id from @tb2 order by Id desc)
end
else
begin
Insert into @tb2(Id,Name,CheckInCheckOut,ClockIn,ClockOut,lastadd)
(select id,Name,CheckInCheckOut,CheckInCheckOutDateTime,null,CheckInCheckOut from @tb1 where Id=@cnt+1)
end
End
else
begin
if((select CheckInCheckOut from @tb1 where Id=@cnt+1)=1)
begin
Insert into @tb2(Id,Name,CheckInCheckOut,ClockIn,ClockOut,lastadd)
(select id,Name,CheckInCheckOut,CheckInCheckOutDateTime,null,CheckInCheckOut from @tb1 where Id=@cnt+1)
end
else
begin
Insert into @tb2(Id,Name,CheckInCheckOut,ClockIn,ClockOut,lastadd)
(select id,Name,CheckInCheckOut,null,CheckInCheckOutDateTime,CheckInCheckOut from @tb1 where Id=@cnt+1)
end
end
End
set @cnt=@cnt+1
End
select * from @tb2

—-Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
—-Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
—-Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth
ResultSet:
LastDay_PreviousMonth
———————–
2007-07-31 23:59:59.000

LastDay_CurrentMonth
———————–
2007-08-31 23:59:59.000

LastDay_NextMonth
———————–
2007-09-30 23:59:59.000

If you want to find last day of month of any day specified use following script.
–Last Day of Any Month and Year
DECLARE @dtDate DATETIME
SET @dtDate = ‘8/18/2007’
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
LastDay_AnyMonth
ResultSet:
LastDay_AnyMonth
———————–

declare @body1 nvarchar(MAX)
set @body1 = N’

‘
print @body1
EXEC msdb.dbo.sp_send_dbmail @recipients=’sunil@iglobeconsulting.com’,
@subject = ‘My Mail Test’,
@body = @body1,
@profile_name = ‘Prakash Rathod’,
@body_format = ‘HTML’ ;

We used to use uniqueidentifier so many times in our database, since it is one of the unique field in table, we may need to put it in dynamic SQL quite a few times but I have seen so many times that even seasoned developer don’t know how to use UniqueIdentifier in dynamic SQL, may be in Store procedure in SQL Server. This is the reason I tempted to write something for this topic.

Let us see it practically:

–create table for testing
if OBJECT_ID(‘IDTester’) is not null drop table IDTester
create table IDTester
(
ID uniqueidentifier default newid(),
name varchar(20)
)
GO

–insert few records
insert into IDTester(name)
select ‘Ritesh’ union all
select ‘Rajan’ union all
select ‘Bihag’ union all
select ‘Abhijit’
GO

–let us see what we come up with
select * from IDTester
GO

–create simple SP
Create proc SPIDTester
@ID uniqueidentifier
as
begin
select * from IDTester where ID=@ID
end
GO

—-I got ‘7F1D8BC8-48AA-437E-B19F-4ABD139AD5E5’ for first record
—-you may get something else as a ID of first records.
exec spidtester ‘7F1D8BC8-48AA-437E-B19F-4ABD139AD5E5′
GO

–let us create another SP with dynamic SQL but it will show us an error
Create proc SPIDTester2
@ID uniqueidentifier
as
begin
declare @sql varchar(max)
set @sql=’select * from IDTester where ID=’ + @ID
exec (@sql)
end
GO
–if you will try to create above SP, you will be greeted with
–following error
–Msg 402, Level 16, State 1, Procedure SPIDTester2, Line 6
–The data types varchar and uniqueidentifier are incompatible in the add operator.

–you have to use sp_executeSQL to get rid of above error
–with additional parameter
create proc SPIDTester2
@ID uniqueidentifier
as
begin
declare @sql nvarchar(max)
set @sql=’select * from IDTester where ID=@I’
exec sp_executesql @sql,N’@I uniqueidentifier’,@I=@ID
end
GO

–let us see whether SP actually works
exec spidtester2 ‘7F1D8BC8-48AA-437E-B19F-4ABD139AD5E5′
GO

———————————– Pass more then one parameter —————————————-

DECLARE @SQL_String NVARCHAR(max)
DECLARE @Parameter_Definition NVARCHAR(max)

SET @SQL_String = N’
EXEC GetEmail2 @EmployeeId = @EmployeeId_input, @Email = @Email_out OUTPUT
‘

SET @Parameter_Definition = N’
@EmployeeId_input uniqueidentifier,
@Email_out nvarchar(50) OUTPUT’

DECLARE @EmployeeId uniqueidentifier
DECLARE @Email nvarchar(50)

SET @EmployeeId = ‘997B3351-F876-414B-9C63-B90EC967B69B’

EXECUTE sp_executesql @SQL_String, @Parameter_Definition, @EmployeeId_input = @EmployeeId, @Email_out = @Email OUTPUT

SELECT @Email as Email

GO

ex. 1. select top 10 * from dbo.split(‘Chennai,Bangalore,Mumbai’,’,’)

Link: http://blog.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx

Fine duplicate row
SELECT ColumnName,
COUNT(ColumnName) AS ColumnName
FROM tbl_Employee_M
GROUP BY ColumnName
HAVING ( COUNT(ColumnName) > 1 )

Delete duplicate rows

WITH DUPLICATE(ROW,GROUPROW,ID,FNAME,LNAME)
AS
(
SELECT ROW,GROUPROW= CASE WHEN ID=ID
THEN
(SELECT COUNT(*) FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW,
ID,LNAME,FNAME FROM MYTABLE
) AS A WHERE A.ID=B.ID AND
A.ROW<B.ROW)+1 END,ID,FNAME,LNAME FROM (SELECT ROW_NUMBER() OVER (ORDER
BY ID) AS ROW,
ID,LNAME,FNAME FROM MYTABLE
)AS B
)
DELETE FROM DUPLICATE WHERE GROUPROW1

Declare @table1 table (
SalesPerson varchar(50), Product varchar(50), SalesAmount float )

insert into @table1(SalesPerson,Product, SalesAmount) values (‘Bob’, ‘Pickles’, 100)
insert into @table1(SalesPerson,Product, SalesAmount) values (‘Sue’, ‘Oranges’, 50)
insert into @table1(SalesPerson,Product, SalesAmount) values (‘Bob’, ‘Pickles’, 25)
insert into @table1(SalesPerson,Product, SalesAmount) values (‘Bob’, ‘Oranges’, 300)
insert into @table1(SalesPerson,Product, SalesAmount) values (‘Sue’, ‘Oranges’, 500)

select * from @table1

SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
FROM
(SELECT SalesPerson, Product, SalesAmount
FROM @table1 ) ps
PIVOT
(
SUM (SalesAmount)
FOR Product IN
( [Oranges], [Pickles])
) AS pvt

exec sp_rename ‘TimeZone’, ‘TimeZone_Old’

Without Any Records

select * into Temp_TimeZone from ExistingTableName where 1=2

With Records

select * into Temp_TimeZone from ExistingTableName

select SUBSTRING((select ‘,’ + State From State Where State.Cid = Country.Cid
For xml path(”)),2,1000) as statename, Country from Country

Above query generate State name with comma seperator : result as shown below