Find 2nd Monday Of The Month

Hi,

Recently one of our fellow DBA friend asked a question on MSDN. Question was, he want to have a job with 2 steps, First step will run everyday and pass on the information but second step should only get executed on 2nd Monday of the Month.

Now how to do that, ofcourse he has to put some conditions to check whether its 2nd Monday of the Month or not and then process code accordingly. So question is how to find 2nd Monday of the Month. Here is the code friends.

DECLARE @DAY VARCHAR(10)


DECLARE @DATE VARCHAR(10)

DECLARE @TODAY VARCHAR(10)


SET @DAY='Monday'

SET @DATE=CONVERT(varchar(10),DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,14-datepart(day,getdate()),getdate())), 0),111)
SET @TODAY=CONVERT(VARCHAR(10),getdate(),111)
IF @DAY= DATENAME(WEEKDAY,DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,14-datepart(day,getdate()),getdate())), 0))

AND @TODAY=@DATE

BEGIN

SELECT 'Its Second '+@DAY+' Of The Month And Date Is '+@TODAY

END

ELSE

SELECT 'Its '+@DAY+ ' Today and Date Is '+@TODAY
 
Hope this will help.

Query First 2 Records For Each Alphabet

Hi Friends,

Disclaimer first ;)
This question came on MSDN site few days back where one of our friend was seeking help to write a query which can retrieve first 2 records for each alphabet from a set of records. I thought of sharing it with you. But yes content/resolution is not mine I am just posting here for others help.

We have a table say named as "MSDN" with below structure & records in it.
















Results expected out of the query was like this:


So how to achieve this?

Thanks for our MSDN friend John to provide this query:

Select msdn.*
From msdn
Join
(
select empno, rank() over (partition by left(empname,1) order by empname) as r from msdn
) i on
msdn.empno=i.empno
and i.r<=2
order by empname

Original MSND post:
http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/9a0bee9c-855f-4591-b73b-82a71300b9d7

Calculate Days, Hours, Mins & Seconds From Two Dates

Hi,

Recently one of our friend posted a question on MSDN. Question was to how to calculate Different between 2 dates in terms of Day, Hour, Month & Seconds. One of our other friend (Santy The Tango Charlie) shared the code. I though of sharing it with you as well.

SET NOCOUNT ON

GO

DECLARE @CDate DATETIME

DECLARE @EDate DATETIME

SET @CDate = getdate()

--SET @CDate = '2011-01-01 10:00:00'

SET @EDate = str(year(@CDate))+'-12-31'

select 'StartDate' = @CDate , 'YearEndDate' = @EDate select 'Months' = DATEDIFF(mm,@CDate,@EDate) , 'Days' = DATEDIFF(dd, DATEADD(mm,(DATEDIFF(mm,@CDate,@EDate)),@CDate) ,@EDate) , 'Hours' = case when (DATEDIFF(hh,@CDate,convert(varchar(11),@EDate+1,101)) - (DATEDIFF(dd,@CDate,@EDate)*24))=24 then '00' else DATEDIFF(hh,@CDate,convert(varchar(11),@EDate+1,101)) - (DATEDIFF(dd,@CDate,@EDate)*24) end , 'Minutes' = case when (abs(DATEDIFF(mi,'01:00:00','00:'+str(substring(convert(varchar(11),@CDate,108),4,2))+':00')))=60 then '00' else abs(DATEDIFF(mi,'01:00:00','00:'+str(substring(convert(varchar(11),@CDate,108),4,2))+':00')) end , 'Seconds' = case when (abs(DATEDIFF(ss,'00:01:00','00:00:'+str(substring(convert(varchar(11),@CDate,108),7,2) ))))=60 then '00' else abs(DATEDIFF(ss,'00:01:00','00:00:'+str(substring(convert(varchar(11),@CDate,108),7,2) )))

end

Original Post http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/1aa5e849-043a-4870-8379-8ba6db19be95 Regards Gurpreet Sethi

Multiline On A Single Record in SQL

Hi,

I came across a SQL post on MSDN where one of our fellow SQL Colleague asked how to Display Single Record in Multiline. Rest of the story is below:

Problem Description
*******************
is it possible to have multi-line in a single record in sql table?? for example

rowid employee address

1 addin adhika jakarta 123456

indonesia

the address column is multiline, is it possible to achieve that?

Resolution Code
*****************

use tempdb
go


Create table #EMP(id int, name varchar(50), address varchar(100))
go

insert into #EMP values(1,'addin adhika','jakarta 123456 indonesia')
go

DECLARE @CrLf CHAR(2);
SET @CrLf = CHAR(13) ;
select id,name,substring(address,1,15)+char(13)+substring(address,15,datalength(address)) from #EMP
drop table #EMP
go

Note: Make Sure We Run This Code In QA And Output Should Be Set To TEXT.

SUM of Hours and Minutes SQL 2005

Hi,

I came across a post where one of our fellow SQL coleague asked a question i.e. In SQL Server 2005 how to do SUM of Hours and Minutes. So here comes rest of story:

Problem Description
***********************
I want to get SUM of Hours and Minutes in SQL Server 2005 in To SELECT Query Like : SELECT SUM(OTHours) FROM EmpInOutRecords.

Example: I Have OverTime Hours Like: 01:25, 02:30, 05:56, 00:50

Now I Want To SUM of This Total Hours and Minutes Like Answer is : 10:41

Resolution Code
****************

DECLARE @Sample TABLE
( data CHAR(5)
)


INSERT @Sample SELECT '01:25' UNION ALL
SELECT '02:30' UNION ALL
SELECT '05:56' UNION ALL
SELECT '00:50'
SELECT
STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theHours + theMinutes, '19000101'), 8), 1, 2, CAST((theHours + theMinutes) / 3600 AS VARCHAR(12)))
FROM (
SELECT ABS(SUM(CASE CHARINDEX(':', data) WHEN 0 THEN 0 ELSE 3600 * LEFT(data, CHARINDEX(':', data) - 1) END)) AS theHours,
ABS(SUM(CASE CHARINDEX(':', data) WHEN 0 THEN 0 ELSE 60 * SUBSTRING(data, CHARINDEX(':', data) + 1, 2) END)) AS theMinutes
FROM @Sample
) AS d

Trigger to Get Information Who Updated a Table

Hi,

I came thru a post in MSDN where some one as how he can save details (like SPID, Name) for user who update a specific table.

Below is the code for that

--Create table for storing values

create table idtrack (id int,uname varchar(100),date datetime)

--Create Trigger on table (table1 in this example)

--Trigger will copy SPID, USER NAME & DATE when a update was fired on table1.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create TRIGGER dbo.testtrigger ON dbo.table1
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
insert into idtrack (id,uname,date)select @@SPID,user_name(),getdate()
END
GO


http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/50bc5c3c-797c-4a18-8b9a-4e52d2465b4f

SELECT LAST 2 RECORDS IN A TABLE

Hi Friends,

Recently I got a question on MSDN post to find last 2 records in a table. Below is the query which i figure out in order to do that.

create table hh (Id Int, Name varchar(20), salary int)
go
insert into hh values (5,'ankit',233)
insert into hh values (1,'amit',777)
insert into hh values (6,'anuj',666)
go
select identity(int,1,1) as SlNo,* into #temp from hh
select * from (select top 2 * from #temp order by slno
desc) a order by slno
drop table #temp
go

Below is that post : http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/4d57e34f-85c6-4105-9a17-6b60dc1b251a

Happy Learning......