Remote signout from gmail..........

Hello everyone,

Have you ever come to know who is using your account (mail) where and when , It is possible find it out at this location
http://gmailblog.blogspot.com/2008/07/remote-sign-out-and-info-to-help-you.html

Krunal Panchal

SQL Server: Convert 24 hour format time into 12 hour format

Hi,
Here is an example of converting time data type i.e.13:09 which is in 24 hr format into 12 hr format i.e  1:09 format.

Create Function :


alter FUNCTION dbo.Split1(@String varchar(8000), @Delimiter char(1))    
returns @temptable TABLE (items varchar(8000))    
as    
begin    
declare @idx int    
declare @slice varchar(8000)
declare @count int
declare @finaltime varchar(5)
set @count=0  
  
select @idx = 1    
if len(@String)<1 or @String is null  return    
  
while @idx!= 0    
begin    
set @count=@count+1
set @idx = charindex(@Delimiter,@String)    
if @idx!=0    
set @slice = left(@String,@idx - 1)    
else    
set @slice = @String    

if((len(@slice)>0))
begin
declare @hrtime varchar(2)
set @hrtime=convert(varchar(2),@slice)
if(@count=1)
begin
declare @hrinttime int
set @hrinttime=convert(int,@hrtime)
if(@hrinttime>12)
begin
set @hrinttime=@hrtime-12
end
else
begin
set @hrinttime=@hrtime
end


--insert into @temptable(Items) values(@hrinttime)
set @finaltime =@hrinttime
set @finaltime=@finaltime+':'


end
else
-- insert into @temptable(Items) values(@hrtime)
set @finaltime=@finaltime+@hrtime
  -- insert into @temptable(Items) values(@slice)
end

set @String = right(@String,len(@String) - @idx)    
if len(@String) = 0 break    
end

insert into @temptable (Items) values(@finaltime)
return    
end

Run Function in this way:

select top 10 * from Split1('13:09',':')  

SQL Server:Execution Time Calculation For Queries

Hi,
This is the example for calculating Query execution time in SQL
The procedure is very simple, first take start time of query execution and then write the query and then get the end time of query execution and then calculate their difference

And below is the execution plan for the query

For execution plan you have to just run the query and click on the icon menu i.e. Display Estimated Execution Plan

Happy Coding,
Regards,
Krunal Panchal

SQL SERVER:Generating CSV in select command and again Generating Row wise data

Hi everyone,


Declare @chapters varchar(max)
select @chapters=coalesce(@chapters+',','')+ch.Chapter
from Chapter ch,Subject sb ,Syllabus s,Classroom c
--for syllabus
where s.SyllabusID ='3'
--for classroom
and c.SyllabusID =s.SyllabusID
and c.Classroom =1
--for subject
and sb.SyllabusID =s.SyllabusID
and sb.ClassroomID =c.ClassroomID
--and sb.Subject ='Eng.Lit.'
--for chapters
and ch.SyllabusID =s.SyllabusID
and ch.ClassroomID =c.ClassroomID
and ch.SubjectID =sb.SubjectID

select @chapters

Select * from UF_CSVToTable (@chapters)

Function UF_CSVToTable 


USE [CMS2012]
GO
/****** Object:  UserDefinedFunction [dbo].[UF_CSVToTable]    Script Date: 11/22/2010 17:15:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[UF_CSVToTable]
(
 @psCSString VARCHAR(max)
)
RETURNS @otTemp TABLE(sID VARCHAR(2000))
AS
BEGIN
 DECLARE @sTemp VARCHAR(max)

 WHILE LEN(@psCSString) > 0
 BEGIN
  SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX(',', @psCSString) - 1, -1),
                    LEN(@psCSString)))
  SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX(',', @psCSString), 0),
                               LEN(@psCSString)) + 1, LEN(@psCSString))
  INSERT INTO @otTemp VALUES (@sTemp)
 END

RETURN
END

OutPut:

Happy Coding,
Regards,
Krunal Panchal

SQL SERVER: Generate Comma Separated List with SELECT statement

Hi everyone,
Many times i require the data from select command in comma seperated format
for e.g.

SELECT  [AdminId]
      ,[LoginId]
      ,[Password]
      ,[AdminType]
      ,[TaskId]
      ,[CreatedBy]
      ,[Status]
  FROM [CMS2012].[dbo].[Admin_Details]
  where AdminId in(3,4,5,6)
will give me result as...

But what i need is...
Login id:-
Mexus,Vibhorthegame@yahoo.co.in,qwe,123@mexuseducation.com
Password:-
Mexus,123123,qwe,123123
etc.....
so i wrote this SQL select statement...


Declare @Description varchar(4000)
Declare @password varchar(max)

select @Description  = coalesce(@Description + ',','')  + LoginId ,
@password =coalesce(@password+',','')+Password
FROM Admin_Details  where AdminId  in (3,4,5,6)

SELECT
     @Description ,@password

And what i got is...

Happy Coding,
Regards,
Krunal Panchal

Order By with VARCHAR

Hi everyone,

In order by clause of varchar(50) column for numeric values,the result is different from order by with integer column..

E.g. select * from classroom order by classroom (where classroom is varchar(max) column)
the result is:
       1
       10
       11
       12
        2
        3
      ........
now suppose i need it order by like 1 2 3 4 5 ... i need to convert the column to integer , instead of changing the design of table you could write your query as this...

select * from classroom order by cast(classroom as integer)

would give you result as 1 2 3 4 5........

Happy Coding,
Regards,
Krunal Panchal

How to get Id of Just Inserted Value in SQL

Hi everyone,
This is a Simple example of Getting Unique Id (Auto Id) for the inserted value after insertion of values in SQL table

Declare @subjectid INT =NULL out
-----Insertion Process-----------

Insert into Subject(SyllabusID,ClassroomID,Subject)
values(@syllabusid ,@classroomid ,@strsubject )

------Get Id into @subjectid----
set @subjectid =SCOPE_IDENTITY ();

Happy Coding,
Regards,
Krunal Panchal