SQL SERVER TUTORIAL IN DEPTH

The blog is to help users about sql server

Others

LightBlog

Breaking

Thursday 21 November 2013

How to remove last character in the string , 3 smart ways to handle it
November 21, 2013

How to remove last character in the string , 3 smart ways to handle it

1)

 declare @string varchar(100)='ab,cd,ef,'
 set  @string=  reverse (stuff(reverse(@string), 1, 1, ''))
 print @string


2) declare @string varchar(100)='ab,cd,ef,'
   set @string=  SUBSTRING(@string,1,LEN(ISNULL(@string,''))-1);
   print @string

                       
3) declare @string varchar(100)='ab,cd,ef,'
    set @VARDAYTAS= LEFT(@VARDAYTAS,nullif(LEN(@VARDAYTAS)-1,-1))
    print @string

o/p

ab,cd,ef
0
By at No comments:
November 21, 2013

How to use join smartly


Just look at the below queries

 A :

 declare @t table(id int, data1 int, data2 int)
declare @b table(id int, value1 int, value2 int)

insert into @t(id, data1, data2)
values(1, 100, 150)
insert into @t(id, data1, data2)
values(1, 200, 250)
insert into @t(id, data1, data2)
values(1, 300, 350)

insert into @b(id, value1, value2)
values(1, 1000, 1050)
 insert into @b(id, value1, value2)
values(1, 2000, 2050)
 insert into @b(id, value1, value2)
values(1, 3000, 3050)


select x. id, x.data1, x.data2, y.value1, y.value2 from
(
select ROW_NUMBER() over(partition by t.id order by t.id) Sr, t.id, t.data1, t.data2 from @t t
) x
full outer join
(
select ROW_NUMBER() over(partition by b.id order by b.id) Sr, b.id, b.value1, b.value2 from @b b
) y
on x.Sr = y.Sr









----------------------------------------------------------------------------------------------------------------

 B :

 declare @t table(id int, data1 int, data2 int)
declare @b table(id int, value1 int, value2 int)


insert into @b(id, value1, value2)
values(1, 1000, 1050)
 insert into @b(id, value1, value2)
values(1, 2000, 2050)
 insert into @b(id, value1, value2)
values(1, 3000, 3050)


select x. id, x.data1, x.data2, y.value1, y.value2 from
(
select ROW_NUMBER() over(partition by t.id order by t.id) Sr, t.id, t.data1, t.data2 from @t t
) x
full outer join
(
select ROW_NUMBER() over(partition by b.id order by b.id) Sr, b.id, b.value1, b.value2 from @b b
) y
on x.Sr = y.Sr









------------------------------------------------------------------------------------------------------

 C :

 declare @t table(id int, data1 int, data2 int)
declare @b table(id int, value1 int, value2 int)

insert into @t(id, data1, data2)
values(1, 100, 150)
insert into @t(id, data1, data2)
values(1, 200, 250)
insert into @t(id, data1, data2)
values(1, 300, 350)




select x. id, x.data1, x.data2, y.value1, y.value2 from
(
select ROW_NUMBER() over(partition by t.id order by t.id) Sr, t.id, t.data1, t.data2 from @t t
) x
full outer join
(
select ROW_NUMBER() over(partition by b.id order by b.id) Sr, b.id, b.value1, b.value2 from @b b
) y
on x.Sr = y.Sr





------------------------------------------------------------------------------------------------
D :

 declare @t table(id int, data1 int, data2 int)
declare @b table(id int, value1 int, value2 int)

insert into @t(id, data1, data2)
values(1, 100, 150)
--insert into @t(id, data1, data2)
--values(1, 200, 250)
--insert into @t(id, data1, data2)
--values(1, 300, 350)

insert into @b(id, value1, value2)
values(1, 1000, 1050)
 insert into @b(id, value1, value2)
values(1, 2000, 2050)
 insert into @b(id, value1, value2)
values(1, 3000, 3050)


select x. id, x.data1, x.data2, y.value1, y.value2 from
(
select ROW_NUMBER() over(partition by t.id order by t.id) Sr, t.id, t.data1, t.data2 from @t t
) x
full outer join
(
select ROW_NUMBER() over(partition by b.id order by b.id) Sr, b.id, b.value1, b.value2 from @b b
) y
on x.Sr = y.Sr








----------------------------------------<<<CTE >>--------------------------------------------------------------------------

 E :

 The Same thing works with CTE(i have included the table2 id too)


declare @t table(id int, data1 int, data2 int)
declare @b table(id int, value1 int, value2 int)

insert into @t(id, data1, data2)
values(1, 100, 150)
--insert into @t(id, data1, data2)
--values(1, 200, 250)
--insert into @t(id, data1, data2)
--values(1, 300, 350)

insert into @b(id, value1, value2)
values(1, 1000, 1050)
 insert into @b(id, value1, value2)
values(1, 2000, 2050)
 insert into @b(id, value1, value2)
values(1, 3000, 3050)


; with c as 
(
select ROW_NUMBER() over(partition by t.id order by t.id) r, t.id, t.data1, t.data2 from @t t
),e as 
(
select ROW_NUMBER() over(partition by b.id order by b.id) r, b.id, b.value1, b.value2 from @b b
)
select c.id,c.data1,c.data2,e.id,e.value1,e.value2
from c 
full outer join e on c.r = e.r
order by c.id desc











Good Luck.. 

 Kindly give the feedback if it helped you :)






0
By at No comments:

Monday 18 November 2013

November 18, 2013

How merger works in Sql Server in Database


-------Merge----
--select * from city








--now lets try to insert a new id...Let's do it by stored procedure

 alter procedure insertproc
@id int,
@name varchar(70)



as
begin

 update city set name=@name where id=@id

 if(@@rowcount=0)
begin

 insert into city(id,name) values(@id,@name)

 end

 end

 --exec insertproc 6,'calcutta'
--select * from city
















------------------------------but this can be done using merge statement--------
merge into city c
using (select 7 id,'lucknow' name) b
on(c.id=b.id)
when matched then
update set name=b.name
when not matched then
insert(id,name)values(b.id,b.name);

 --select * from city












merge into city c
using (select 7 id,'lucknowUpdated' name) b
on(c.id=b.id)
when matched then
update set name=b.name
when not matched then
insert(id,name)values(b.id,b.name);
--select * from city





create table citydesination
(
id1 int,
name1 varchar(70)
)

 select * from citydesination--no record
--lets insert one record 

 insert into citydesination(id1,name1) values(7,'lucknowindestination')

 select * from citydesination






--lets insert using merger

merge into citydesination d
using(select id,name from city)b
on (d.id1=b.id)
when matched then
update set name1=b.name
when not matched then
insert (id1,name1)values(b.id,b.name);

 select * from citydesination




---------------------------How i used it in my stored procedure in my project-----------------

                     merge into #temp1 t
                     using (select empcode from tbl_SMM_Roster where dtdate >                                     dateadd(dd,-30,getdate())group by empcode)  smm
                     on t.userid=smm.empcode
                     when matched then
                     UPDATE SET [SMM DETAILS] = (select CONVERT(VARCHAR(11),dtDate,6) 
                     from tbl_SMM_Roster WITH(NOLOCK) where empcode=@userid
                     order by dtdate desc
                     OFFSET 0 ROWS
                     FETCH NEXT 1 ROWS ONLY) ;

                     merge into #temp1 t
                     using (select empcode from tbl_VW_Roster where dtdate >                                       dateadd(dd,-30,getdate())group by empcode)  vw
                     on t.userid=vw.empcode
                     when matched then
                     UPDATE SET [GTT DETAILS] = (select CONVERT(VARCHAR(11),dtDate,6) 
                     from tbl_VW_Roster WITH(NOLOCK) where empcode=@userid
                     order by dtdate desc
                     OFFSET 0 ROWS
                     FETCH NEXT 1 ROWS ONLY) ;

-------------------------------------------------------------------------------------

                     


0
By at No comments:

Saturday 16 November 2013

Top n with TIES in sql server and ROW_NUM/RANK/DENSE RANK
November 16, 2013

Top n with TIES in sql server and ROW_NUM/RANK/DENSE RANK

select * from employee

id   value
23  100
24  200
25  300
26  300
27  300
28  400
29  500
30  500

select top 3 with ties from employee

 id   value
23  100
24  200
25  300
26  300
27  300


--------ROW NUMBER [JUST COUNT ]------
--------RANK[RANK ...SUPPOSE TWO ARE TIED....LETS SAY 3RD POSITION....THEN THE NEXT VALUE WILL BE 5TH POSITION.....THE TWO WHICH WERE TIED FOR 3RD..IT ASSUMES ONE WILL BE THIRD AND THE OTHER WILL BE 4TH.....SO THE NEXT NUM AFTER THIS WILL BE 5TH]
-------DENSE RANK[SUPPOSE TWO NUMBERS ARE TIED....IT ASSUMES THAT BOTH ARE TIED AT 3RD AND BOTH NEED TO SETTLE FOR THE THIRD RANK....SO THE NEXT NUMBER WILL BE 4TH]
BELOW EXAMPLE WILL CLARIFY YOU ALL THE THINGS

select custid, count(*) as numorders,
row_num() over (order by count(*) desc )  "row number"
rank() over (order by count(*) desc )  "rank"
dense_rank() over (order by count(*) desc )  "dense rank"
from orders
group by custid
order by numorders desc

custid      numorders      row number  rank    dense rank
12            39                       1                  1             1
13            37                       2                  2            2
14            26                       3                  3            3
15            25                       4                  4            4
16            25                       5                  4            4
17            25                       6                  4            4
18            20                       7                  7            5
19            18                       8                  8            6
20            18                       9                  8            7
21            17                      10                10           8
22            16                      11                11            9


--------

0
By at No comments:
union key and primary key differences
November 16, 2013

union key and primary key differences

1) Union key -- Creates non clustered index by default.
                     -- Can be set as null
                     -- Can have multiple unique key
                     -- Can be referenced by a foreign key

2) Primary key
                   --   Creates clustered index by default.
                     -- Can't be set as null
                     -- Can have only single primary key
                     -- Can be referenced by a foreign key

0
By at No comments:

Tuesday 12 November 2013

November 12, 2013

How the function is used trickily to get the desired result

ACTUAL DATA 





ALTER FUNCTION [dbo].[fn_Split_month]
(
       @Data varchar(max)
) 
RETURNS varchar(30)
AS 
BEGIN
       Declare @Month varchar(15),@Year varchar(4), @RtnValue varchar(30),@index int
       if(len(@Data)>0 and @Data<>'')
       begin
              SET @index = Charindex(':',@Data)
              SELECT @Month = month_text from tbl_month_master where month = ltrim(rtrim(Substring(@Data,1,Charindex(':',@Data)-1)))

              SET @Year = LTRIM(RTRIM(SUBSTRING(@Data,@index+1,LEN(@Data))))
                    
              set @RtnValue =  @Month + ', ' + @Year
       End
       Else
       begin
              set @RtnValue=''
       End

       Return @RtnValue
END

SELECT [dbo].[fn_Split_month] ('12:2009')


0
By at No comments:
November 12, 2013

How Ltrim/Rtrim/Replace works in Sql Server

YOU CANNOT USE TEMPORARY TABLE IN FUNCTION:


èI create a temporary table. And I was using that temporary table in function. I couldn’t use it. Then the table I have created below which will be used through out this topic.
TABLE CREATED

create table temp3
(
addressid int,
colony varchar(50),
subcolony varchar(100),
City varchar(50),
pincode int
)

insert into temp3
select 121,' sundi sai',' karan sai','Rgda',1000
union all select 122,'new colony','','Rgda',1001
union all select 123,' ','','',1002
union all select 124,'"newcolony"','','Rgda',1003
union all select 125,'      raniguda','    raniguda3','Rgda',1004


select * from temp3














FUNCTION CREATED

alter function [dbo].[fn_Get_trim]     
(     
   @address_id int
)  
returns varchar(500)
as
begin

declare @addressfinal varchar(500)

select
@addressfinal= coalesce(colony,subcolony,city) +' '+ convert(varchar,pincode)                           
from temp3
where
addressid=@address_id

return @addressfinal
end

select [dbo].[fn_Get_trim](121)



--As already we know the usage of coalesce. If colony is null then subcolony. If subcolony is null then city. But here colony is not null so we get colony alone.
 



select [dbo].[fn_Get_trim](125)

 
Look at this so much space .
LTRIM AND RTRIM COMES INTO PICTURE

alter function [dbo].[fn_Get_trim]     
(     
   @address_id int
)  
returns varchar(500)
as
begin

declare @addressfinal varchar(500)

select
@addressfinal= rtrim(LTRIM(coalesce(colony,subcolony,city))) +' '+ convert(varchar,pincode)                           
from temp3
where
addressid=@address_id

return @addressfinal
end


 

alter function [dbo].[fn_Get_trim]     
(     
   @address_id int
)  
returns varchar(500)
as
begin

declare @addressfinal varchar(500)

select
@addressfinal= Rtrim(LTRIM(isnull(colony,'')))+ ' ' +Rtrim(LTRIM(isnull(subcolony,'')))+' '+Rtrim(LTRIM(isnull(city,''))) +' '+ convert(varchar,pincode)                            
from temp3
where
addressid=@address_id

return @addressfinal
end

select [dbo].[fn_Get_trim](125)

o/p



 

--I don’t want this “”.  Now here where the Replace works.
@addressfinal= Rtrim(LTRIM(isnull(REPLACE(colony,'"',''),'')))+ ' ' +Rtrim(LTRIM(isnull(subcolony,'')))+' '+Rtrim(LTRIM(isnull(city,''))) +' '+ convert(varchar,pincode)                            

Now run it :

---------------------------------------------Cool J----------------------------------------------
0
By at No comments:

 test