Separate your work and leasure

Separate your work and private laptop!!
Leave your work at the right place (OFFICE)

Life Update 2011

Since the last I post my last post on March 2010, I’ve moved to different kind of job, I’ve moved from Jakarta to Medan and now Surabaya.

Life is good..

Before i dedicated this blog to my interest in IT, currently I am not as update as before. So you’ll see that I am going to write random things.. Such as life, my thought, or anything cross my mind

get user who altered stored procedure from server

First I create a table for logging purpose.

CREATE table Logtable
(username varchar(2000), Modify_Date datetime, ProcName varchar(2000))

Then I create the Trigger

CREATE TRIGGER safety
ON DATABASE
FOR ALTER_PROCEDURE
AS
declare @name varchar(200)

--look for the object with stored procedure type that has the last modify time.
set @name = (select  top 1 Name from sys.all_objects
where type_desc like 'SQL_STORED_PROCEDURE'
order by modify_date desc)

--insert the information u need to log table
INSERT INTO Logtable (UserName, Modify_Date, ProcName)
select suser_name(), getdate(), @name

create  a dummy proc for testing and alter it for testing the trigger

create procedure JustAProc
as
select 'JustAProc'
go

ALTER procedure JustAProc
AS BEGIN
PRINT 'X'
END

see the content of the log

select * from Logtable

username	Modify_Date	ProcName
Melissa	3/24/10 8:17 PM	JustAProc

and just drop it after you finish play with it

DROP TRIGGER safety
ON DATABASE;
go
DROP PROCEDURE JustAProc
GO
Drop Table Logtable

The Highest Score in a Class

Mrs. Jones is an admin in an elementry school called ‘SD Suka Tampil’. At the end of the school every six months she got to give report to the head master about students with the best score in every class. She has a table. Just a very simple table called StudentScore.

The structure is like

Name	Class	Score
Mina	1a	99
Arni	1b	98
Cukiyem	1a	55
Alfaredo	1c	78
Gilda	….

Aa.. just head to the query then to make this shorter.

CREATE TABLE #StudentScore (Name varchar(20), Class Char(2), Score int)

insert into #StudentScore (Name, Class, Score)
select 'Zee', '1a', 99 UNION ALL
select 'Arni', '1b', 98 UNION ALL
select 'Cukiyem', '1a', 55 UNION ALL
select 'Alfaredo', '1c', 78 UNION ALL
select 'Gilda', '1b', 88 UNION ALL
select 'Moore', '1a', 87 UNION ALL
select 'RiyanT', '1a', 90 UNION ALL
select 'Melissa', '1b', 98 UNION ALL
select 'Ninoood', '1c', 95

select * from #StudentScore

Name	Class	Score
Zee	1a	99
Arni	1b	98
Cukiyem	1a	55
Alfaredo	1c	78
Gilda	1b	88
Moore	1a	87
RiyanT	1a	90
Melissa	1b	98
Ninoood	1c	95

Ahaha that easy just use max

select Name, class, MAX(score) as 'The Highest'
from #StudentScore
group by Name, Class

Name	class	The Highest
Cukiyem	1a	55
Moore	1a	87
RiyanT	1a	90
Zee	1a	99
Arni	1b	98
Gilda	1b	88
Melissa	1b	98
Alfaredo	1c	78
Ninoood	1c	95

Why why why it don’t produce the right result? hehe cause you group it by the name and class. Hmm…
Let’s Find the highest first in every Class

select Class,  MAX(score) AS Score
from #StudentScore
group by Class

Class	Score
1a	99
1b	98
1c	95

Ahaaaa….

SELECT A.name, A.Class, A.Score as 'The Highest'
from #StudentScore A
JOIN (select Class,  MAX(score) AS Score
 from #StudentScore
 group by Class
 ) B ON A.Class = B.Class AND A.Score = B.Score

name	Class	The Highest
Ninoood	1c	95
Arni	1b	98
Melissa	1b	98
Zee	1a	99

Or use CTE

;WITH CTE AS (
SELECT Name, Class, Score,
ROW_NUMBER() OVER (PARTITION BY Class Order by Score DESC) AS RN
FROM #StudentScore
)SELECT Name, Class, Score FROM CTE
 WHERE RN = 1;

Name	Class	Score
Zee	1a	99
Arni	1b	98
Ninoood	1c	95

wait why we can see melissa in class 1b?  please see below

SELECT Name, Class, Score,
ROW_NUMBER() OVER (PARTITION BY Class Order by Score DESC) AS RN
FROM #StudentScore

Name	Class	Score	RN
Zee	1a	99	1
RiyanT	1a	90	2
Moore	1a	87	3
Cukiyem	1a	55	4
Arni	1b	98	1
Melissa	1b	98	2
Gilda	1b	88	3
Ninoood	1c	95	1
Alfaredo	1c	78	2

why melissa is grade as number 2 in class 1b? because row number sort the score based on score
and when they found two same score it’ll sort again by name. And A comes first.
so how to have this with CTE. Use Dense Rank!

SELECT Name, Class, Score,
Dense_rank() OVER (PARTITION BY Class Order by Score DESC) AS RN
FROM #StudentScore

Name	Class	Score	RN
Zee	1a	99	1
RiyanT	1a	90	2
Moore	1a	87	3
Cukiyem	1a	55	4
Arni	1b	98	1
Melissa	1b	98	1
Gilda	1b	88	2
Ninoood	1c	95	1
Alfaredo	1c	78	2

Now we have Melissa and Arni in the same position
tadaaaa.

;WITH CTE AS (
SELECT Name, Class, Score,
Dense_rank() OVER (PARTITION BY Class Order by Score DESC) AS RN
FROM #StudentScore
)SELECT Name, Class, Score FROM CTE
 WHERE RN = 1;

I present to You…

Report For Miss Jones..

Name	Class	Score
Zee	1a	99
Arni	1b	98
Melissa	1b	98
Ninoood	1c	95

Insert to another table after inserting to a table

This is quite an often question asked by people.

There are 2 options. Create a stored procedure with 2 steps

something like

This is quite an often question asked by people.
There are 2 options. Create a stored procedure with 2 steps something like. It’ll insert the last value inserted to table A to table B

CREATE PROCEDURE InsertInsert

AS BEGIN

declare @id int
Insert to table A
select 'A', 'B'

--take the last id inserted
SET @id = @@identity

GO

Insert to table B
Select Col1, Col2 from Table A
where ID = @id

END

or you can use trigger.

CREATE TRIGGER InsertToOtherTable
ON TableSource
AFTER INSERT
AS
INSERT INTO TableB
SELECT Col1, Col2
FROM INSERTED

BOL

Query to know the Object Level Permissions for all users of a Database

sometimes we need to know what user is allowed to do what then here’s the query to know the level permission of all user.

select pr.name as LoginName, state_desc,  permission_name as PermissionTo , s.name as schemaName, o.Name
from sys.database_permissions p
inner join  sys.objects o on  p.major_id =o.object_id
inner join sys.schemas s on o.schema_id=s.schema_id
inner join sys.database_principals pr on grantee_principal_id=pr.principal_id

LoginName  state_desc       PermissionTo  schemaName           Name
User1              GRANT            EXECUTE            dbo                       USP_A
User2              GRANT            EXECUTE             dbo                      USP_B

Insert into Identity Column

You create a table with identity column, You accidentally delete one of its row instead update it.. If you insert new column it’ll only increment to the last id inserted+1. You need it to be a specific id. So you try to insert it with the id specified but you cannot do that you get error.. So how to insert column to identity column?

CREATE TABLE #Customer(
ID INT IDENTITY NOT NULL PRIMARY KEY,
Name VARCHAR(40) NOT NULL
)
GO
-- Inserting values into #Customer table.
INSERT INTO #Customer(Name) VALUES ('Jake')
INSERT INTO #Customer(Name) VALUES ('Suzan')
INSERT INTO #Customer(Name) VALUES ('Marry')
INSERT INTO #Customer(Name) VALUES ('Sally')
GO

-- Accidentaly delete Marry (oh nooo I just want to add Marry's Maiden name!?!?!)
DELETE #Customer
WHERE Name = 'Marry'
GO

SELECT *
FROM #Customer
GO

-- Try to insert Marry again to ID 3;
-- got an error.
INSERT INTO #Customer (ID, Name) VALUES (3, 'Marry Marc')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT #Customer ON
GO

-- Try to insert an explicit ID value of 3.
INSERT INTO #Customer (ID, Name) VALUES (3, 'Marry Marc')
GO

SELECT *
FROM #Customer
GO

--Don't forget to set the identity insert back off. 
SET IDENTITY_INSERT #Customer OFF
GO

-- Drop Customer table.
DROP TABLE #Customer
GO

Huraaah!!

How to convert time format to minutes/duration

Have you ever face trouble when you need to convert values like ’00:45:00.00′ to 45 minutes or ’01:23:00.000′ to 83 minutes?

How do you do it?

here’s the input :

declare @time datetime
set @time = '01:45:00.000'

This is the first method, the idea is using datepart function to get the hour part and minute part of the data and multiply the hour by 60 then add it with the minute part.

select datepart(hour,@time) AS hour, datepart(MINUTE,@time) AS min,
datepart(hour,@time)*60 +datepart(MINUTE,@time) As Duration

the 2nd method, I like this one better cause it’s simpler. The idea is looking the difference in minutes from 00:00:00.000 represented by 0 to the data..

Select DATEDIFF(minute, 0, @time) As Duration

error :Msg 8133, Level 16, State 1, Line 7 ; None of the result expressions in a CASE specification can be NULL.

above error is happen because the SQl is confuse what is the datatype for the result expression in the case statement..

here’s simple script to produce the same error and the solutiooooon (at least it worked for me )

declare @i int
set @i = 3

declare @x int
set @x = 6

Select case when @i >8  OR @i <@x
then NULL
END AS 'Derived Column'
----error :Msg 8133, Level 16, State 1, Line 7
----None of the result expressions in a CASE specification can be NULL.

ALTERNATIVE :

Select case when @i >8  OR @i <@x
then 'Something' --different value
END AS 'Derived Column'

Why It Worked  : because you assign one of the value to varchar so sql thought ok the null is varchar, follow the other value)
second alternative :

Select case when @i >8  OR @i <@x
then NULL
Else 'X' -- put dummy thingy
END AS 'Derived Column'

Why it worked : It Worked because you don’t even try to assign it to NULL value

But but I need to assign it to NULL and no other value and I don’t want and else..

AHAAA…

Select case when @i >8  OR @i <@x
then cast(NULL as int)
END AS 'Derived Column'

why why ? here you cast the result to a datatype (here i cast it to int, sql is no longer confuse)..

No nooo.. I want assign it to a varchar then so be it..

Select case when @i >8  OR @i <@x
 then convert(varchar(10),NULL)
 END AS 'Derived Column'

DateTime to Varchar to Varbinary to Varchar to DateTime

My first post on 2010.. Yay!! The First is convert between datatypes. The Idea is convert from datetime to varchar to binary to varchar again to datetime ..

DECLARE @DateWithDateTime DATETIME
SET @DateWithDateTime = '2010-09-08 00:00:00:00'

DECLARE @Date varchar(200)
SET @Date = (SELECT CONVERT(VARCHAR(20), @DateWithDateTime, 120))

SELECT CONVERT(VARBINARY(100), @Date)AS theVarbinary
--theVarbinary
--0x323031302D30392D30382030303A30303A30303A3030

SELECT convert(VARCHAR(200),CONVERT(VARBINARY(100), @Date)) AS BackTodate
--BackTodate
--2010-09-08 00:00:00:00

hey where is back to datetime.. hahaha it’s just to complete the cycle(feels good)..