Convert Comma separated values into Rows in Sql

create FUNCTION Split(@String varchar(MAX), @Delimiter char(1))      
returns @temptable TABLE (items varchar(MAX))      
as      
begin     
    declare @idx int      
    declare @slice varchar(8000)      

    select @idx = 1      
        if len(@String)<1 or @String is null  return      

    while @idx!= 0      
    begin      
        set @idx = charindex(@Delimiter,@String)      
        if @idx!=0      
            set @slice = left(@String,@idx - 1)      
        else      
            set @slice = @String      

        if(len(@slice)>0) 
            insert into @temptable(Items) values(@slice)      

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

Retrieve Salary With Range in sql

Declare @MaxSal bigint,
@NoOfRange int,@lowerlimit bigint,@higherlimit bigint,
@Initial int,
@RangeQuery nvarchar(max),@No_Of_Employee int,@UpdateQuery nvarchar(max)
set @Initial=0
set @MaxSal=(select Max(sal) from tbl_Salary)
--print @MaxSal
set @NoOfRange=(@MaxSal/999)+1
--print @NoOfRange
if OBJECT_ID('tempdb..#tblRange') is not null   
drop table #tblRange 
create Table #tblRange(LowerRange varchar(max),HigherRange varchar(max),   
No_Of_Employee int)
--set @RangeQuery='insert into #tblRange(LowerRange,HigherRange) values '
while(@Initial<@NoOfRange)
begin
set @lowerlimit=@Initial*1000
set @higherlimit=@Initial*1000+999
--print @lowerlimit
--print @higherlimit
--set @RangeQuery=@RangeQuery+'('+@lowerlimit+','+@higherlimit+'),'
insert into #tblRange(LowerRange,HigherRange) values(@lowerlimit,@higherlimit)
print @RangeQuery
set @Initial=@Initial+1
end
--select * from #tblRange
Declare C1 Cursor for select LowerRange,HigherRange from #tblRange   
open C1   
fetch next from C1 into @lowerlimit,@higherlimit   
while(@@FETCH_STATUS=0)   
begin
set @No_Of_Employee=(select count(*) from tbl_Salary where sal between @lowerlimit and @higherlimit)
print @No_Of_Employee
--set @UpdateQuery='Update #tblRange set No_Of_Employee = '+@No_Of_Employee+' where LowerRange ='+@lowerlimit+' and HigherRange ='+@higherlimit+''
--print @UpdateQuery
--exec (@UpdateQuery)
Update #tblRange set No_Of_Employee =@No_Of_Employee where LowerRange=@lowerlimit and HigherRange=@higherlimit
fetch next from C1 into @lowerlimit,@higherlimit  
end   
close C1   
deallocate C1
select LowerRange+'-'+HigherRange as Range, No_Of_Employee from #tblRange
--select LowerRange,HigherRange, No_Of_Employee from #tblRange     

Get the records separated by comma in sqlserver

CREATE Procedure sp_GetMobileNo_CommaSeparated

@Fcode varchar(20) ,

@FromDate varchar(20),

@ToDate varchar(20)

as

begin

DECLARE @List VARCHAR(max)

SELECT @List = ISNULL(@List + ',', '') + CAST(F_MOBILE AS VARCHAR)

FROM XStudentMasterCopy

WHERE FranchiseCode = @Fcode

and ADMNDATE between convert(varchar(15),@FromDate,103) and convert(varchar(15),@ToDate,103)

SELECT @List



end

Paging in sqlserver

DECLARE @RowsPerPage INT = 10, @PageNumber INT = 15

SELECT RowNum,AutoID,ADMNNO,STDFIRSTNAME,F_MOBILE,ADMNDATE

FROM (

SELECT AutoID,ADMNNO,STDFIRSTNAME,F_MOBILE,ADMNDATE,

ROW_NUMBER() OVER (ORDER BY AutoID) AS RowNum

FROM XStudentMasterCopy where ADMNDATE

between convert(varchar(15),'06/14/2013',103) and convert(varchar(15),'06/15/2013',103) ) AS SOD

WHERE SOD.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1

AND @RowsPerPage*(@PageNumber)

Get the Procedure name in which particular table is used

SELECT DISTINCT so.name FROM syscomments sc INNER JOIN sysobjects so on sc.id=so.id WHERE sc.text LIKE '%BatchMaster%'

Extract single row of a table separated by comma

DECLARE @XYList varchar(MAX)
SET @XYList = ''

SELECT @XYList = @XYList + CONVERT(varchar, X) + ',' + CONVERT(varchar, Y) + ','
FROM POINTS

-- Remove last comma
SELECT LEFT(@XYList, LEN(@XYList) - 1)

Concatenate multiple rows to single having similar group

SELECT
    Id,Name,
    Stuff(
    (SELECT '/' + State
       FROM Table_3 t2
        WHERE t2.Id=t1.Id and t2.Name=t1.Name
        FOR XML PATH(''),type).value('.','nvarchar(max)'), 1, 1, '') AS Class
FROM
    Table_3 t1
GROUP BY Id,Name

Use Column alias in Where Condition

select row_number() over (order by (select(1))) as SNo,* from (select SM.ADMNNO,           
               convert(varchar(13),SM.ADMNDATE,103) as ADMNDATE,SM.STDFIRSTNAME,             
               Convert(varchar(13),SM.DOB,103) as DOB, SM.F_MOBILE,SM.F_NAME,             
               SM.PRESENT_CLASS,SM.PRESENT_SEC,SM.ACTIVE,SM.FranchiseCode,SM.StudentRegNo,           
               (sum(SM.FeeOpeningBal)+sum(SL.Debit)-sum(SL.Credit)) AS Balance ,           
               convert(varchar(15),(sum(SM.FeeOpeningBal)+sum(SL.Debit)-sum(SL.Credit))) as BalanceS           
               from STUDENTMASTER SM Inner Join StudentLedger SL           
               on SM.ADMNNO=SL.ADMNNO AND SM.FranchiseCode=SL.FranchiseCode           
               where SM.FranchiseCode='DL') DerivedTable where Balance<>0

Comma separated values in sqlserver

http://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/

Get the database space used in sqlserver

select
    a.FILEID,
    [FILE_SIZE_MB] =
        convert(decimal(12,2),round(a.size/128.000,2)),
    [SPACE_USED_MB] =
        convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
    [FREE_SPACE_MB] =
        convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
    NAME = left(a.NAME,15),
    FILENAME = left(a.FILENAME,30)
from
    dbo.sysfiles a

Converting Columns into Rows in sqlserver

;with mycte
 as
 (
SELECT rn,cols,val
FROM   (SELECT  row_number() over(order by (select(1))) rn ,A,B,C,D,E,F,G,H,I,J
        FROM  #AsPerDateFinal) AS src1
UNPIVOT (val FOR cols
IN ( A,B,C,D,E,F,G,H,I,J)) AS unpvt
 )
 
SELECT [1] as NintyDays ,[2] as SixtyDays ,[3] as ThirtyDays,[4] as SevenDays,[5] as Today
FROM   (SELECT rn,cols,val
        FROM   mycte) AS src2 PIVOT
( Max(val) FOR rn IN ([1], [2], [3],[4],[5])) AS pvt










declare @sql1 varchar(max)
declare @sql2 varchar(max)
set @sql2=(SELECT STUFF ( ( SELECT DISTINCT  ',[' +CAST(statecd AS VARCHAR)+']' FROM M_States WHERE statecd<42 FOR XML PATH('')),1,1,'' ) )
set @sql1='SELECT '+@sql2+' FROM (SELECT STATENM,STATECD FROM M_STATES WHERE STATECD<42) AS K
PIVOT(MAX(STATENM) FOR STATECD IN ('+@sql2+')) AS P'
--SELECT @sql1
EXECUTE(@sql1)

Sql Server Various Testings

USE [dams]
GO
/****** Object:  StoredProcedure [damsdata].[sp_DashBoardAsPerDateUpdated]    Script Date: 12/11/2013 17:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [damsdata].[sp_DashBoardAsPerDateUpdated]
(
@FranchiseCode varchar(10),
@Session nvarchar(10),
@FirstDay varchar(20),
@SevenDay varchar(20),
@ThirtyDay varchar(20),
@sixtyDay varchar(20),
@NintyDay varchar(20)
)
as
begin

Declare @DayChange varchar(20),

@TotalStudentsAsPer varchar(max),
@NewStudentsAsPer varchar(max),
@CollectionAsPer decimal(20,2),

@ServiceTaxAsPer decimal(20,2),
@ChequeReturnAsPer decimal(20,2),
@FeeConcessionAsPer decimal(20,2),
@FeeRefundAsPer decimal(20,2),
@BadDebtAsPer decimal(20,2),
@SmsSentAsPer bigint,
@RunningBatchesAsPer varchar(max)

DECLARE @DateRange table (DRange varchar(20))
insert into @DateRange (DRange) values (@NintyDay),(@sixtyDay),(@ThirtyDay),(@SevenDay),(@FirstDay)

if OBJECT_ID('tempdb..#AsPerDateFinal') is not null
drop table #AsPerDateFinal

create Table #AsPerDateFinal(A varchar(max),B varchar(max),
C varchar(max),D varchar(max),E varchar(max),F varchar(max)
,G varchar(max),H varchar(max),I varchar(max),J varchar(max))

Declare C1 Cursor for select DRange from @DateRange
open C1
fetch next from C1 into @DayChange
while(@@FETCH_STATUS=0)
begin

 set @TotalStudentsAsPer=(Select Count(*) as TotalStudents FROM STUDENTMASTER WHERE FranchiseCode=@FranchiseCode and  
 ADMNDATE <  CONVERT(VARCHAR(15),@DayChange,103))

 SET @NewStudentsAsPer=(Select count(*) as NewAdmissions from STUDENTMASTER where FranchiseCode=@FranchiseCode AND Session=@Session  
 and ADMNDATE < CONVERT(VARCHAR(15),@DayChange,103))

 set @CollectionAsPer=(select SUM(CurrentPaid) AS Collection from FeeReceipt where FranchiseCode=@FranchiseCode  
 and ReceiptDate < CONVERT(VARCHAR(15),@DayChange,103))

 set @ServiceTaxAsPer=(select SUM(STax) AS ServiceTax from FeeReceipt where FranchiseCode=@FranchiseCode  
and ReceiptDate < CONVERT(VARCHAR(15),@DayChange,103))

  set @ChequeReturnAsPer=(select SUM(AMOUNT) as  ChequeReturn from CHEQUERETURN where FranchiseCode=@FranchiseCode  
and vDATE < CONVERT(VARCHAR(15),@DayChange,103))

  set @FeeConcessionAsPer=(select SUM(AMOUNT) as  FeeConcession from FeeConcession where FranchiseCode=@FranchiseCode 
 and VDATE < CONVERT(VARCHAR(15),@DayChange,103))

  set @FeeRefundAsPer=(select SUM(AMOUNT) as  FeeRefund from FeeRefund where FranchiseCode=@FranchiseCode 
 and DATE < CONVERT(VARCHAR(15),@DayChange,103))

  set @BadDebtAsPer=(select SUM(AMOUNT) as  BadDebt from BadDebt where FranchiseCode=@FranchiseCode 
 and VDATE < CONVERT(VARCHAR(15),@DayChange,103))

 set @SmsSentAsPer=(select Count(*) as  SmsSent from SMSSentHistory where FranchiseCode=@FranchiseCode 
 and MDate < CONVERT(VARCHAR(15),@DayChange,103))

 set @RunningBatchesAsPer=(select Count(*) as  RunningBatches from BatchMaster where FranchiseCode=@FranchiseCode and BatchCompleteFlag='N' 
 and StartDate < CONVERT(VARCHAR(15),@DayChange,103))


 insert into #AsPerDateFinal (A,B,C,D,E,F,G,H,I,J)
        values (@TotalStudentsAsPer,@NewStudentsAsPer,@CollectionAsPer,@ChequeReturnAsPer,
                @FeeConcessionAsPer,@FeeRefundAsPer,@BadDebtAsPer,@SmsSentAsPer,@ServiceTaxAsPer,@RunningBatchesAsPer)
       

fetch next from C1 into @DayChange
end
close C1
deallocate C1

--select * from #AsPerDateFinal


;with mycte
 as
 (
SELECT rn,cols,val
FROM   (SELECT  row_number() over(order by (select(1))) rn ,A,B,C,D,E,F,G,H,I,J
        FROM  #AsPerDateFinal) AS src1
UNPIVOT (val FOR cols
IN ( A,B,C,D,E,F,G,H,I,J)) AS unpvt
 )
 
SELECT [1] as NintyDays ,[2] as SixtyDays ,[3] as ThirtyDays,[4] as SevenDays,[5] as Today
FROM   (SELECT rn,cols,val
        FROM   mycte) AS src2 PIVOT
( Max(val) FOR rn IN ([1], [2], [3],[4],[5])) AS pvt

end

Reset Identity Value while there is no records in table

DBCC CHECKIDENT ('TEMP_User', RESEED, 0)

Reset Identity Value while there is no records in table

DBCC CHECKIDENT ('TEMP_User', RESEED, 0)

isNull with Cast in Sqlserver

Select        cast(isNull(FR1.Collection,0) as decimal(20,2)) as Collection,
              cast(isNull(FR2.ServiceTax,0) as decimal(20,2)) as ServiceTax,
              cast(isNull(BD.BadDebt,0) as decimal(20,2)) as BadDebt
     From  

(select SUM(CurrentPaid) AS Collection from FeeReceipt where FranchiseCode='DL') FR1,

(select SUM(STax) AS ServiceTax from FeeReceipt where FranchiseCode='DL') FR2,
(select SUM(AMOUNT) as  BadDebt from BadDebt where FranchiseCode='DL'
and VDATE between CONVERT(VARCHAR(15),'03/01/2013',103) AND CONVERT(VARCHAR(15),'10/01/2013',103)) BD

Breaking a single column into multiple Columns based on a condition in Sql

 select AdmnNo,ReceiptNo,
       convert(varchar(15),ReceiptDate,103) as ReceiptDate,      
       ISNULL((select SUM(CurrentPaid) from FeeReceipt where PaymentMode='cash'
       and ReceiptNo=FR.ReceiptNo and ADMNNO=FR.ADMNNO and ReceiptDate=Fr.ReceiptDate),0) as Cash,
       ISNULL((select SUM(CurrentPaid) from FeeReceipt where PaymentMode='cheque'
      and ReceiptNo=FR.ReceiptNo and ADMNNO=FR.ADMNNO and ReceiptDate=Fr.ReceiptDate),0) as Cheque
      from FeeReceipt FR
      where FR.AdmnNo=150215

Procedure With Joining with Multiple Tables

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Alter procedure sp_FranchisewiseCollection_Report
(
@FromDate varchar(4000),
@ToDate varchar(4000)
)
as
begin
declare @sqlStr varchar(4000)
set @sqlStr=''

set @sqlstr='select FR.FranchiseCode,
FM.FranchiseName,
(select SUM(CurrentPaid) from FeeReceipt where PaymentMode=''Cash''
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103) and FranchiseCode=FR.FranchiseCode) Cash_Collection ,

(select SUM(CurrentPaid) from FeeReceipt where PaymentMode IN (''Cheque'',''CREDIT CARD'')
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103) and FranchiseCode=FR.FranchiseCode) Bank_Collection ,

(select SUM(CurrentPaid) from FeeReceipt where PaymentMode IN (''Cheque'',''CREDIT CARD'',''Cash'')
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103) and FranchiseCode=FR.FranchiseCode) Total_Collection ,

(select SUM(Fine) from FeeReceipt where
 ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103) and FranchiseCode=FR.FranchiseCode) Fine_Collected ,

 CR.Amount as Chq_Return

from FeeReceipt FR
left join

(select FranchiseCode,ISNULL(SUM(AMOUNT),0) as "Amount" FROM CHEQUERETURN WHERE vDATE between
 CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
group by FranchiseCode ) CR
on FR.FranchiseCode=CR.FranchiseCode

left join

(select Code,FranchiseName from FranchiseMaster) FM

on FR.FranchiseCode=FM.Code
group by FR.FranchiseCode,CR.Amount,FM.FranchiseName
order by FR.FranchiseCode'

exec(@sqlstr)
 
end

Procedure Retruning Value

Alter Procedure sp_DuplicateClientCode
(
@ClientCode nvarchar(max))
as
begin
Declare @Duplicate int
if exists(select * from ItSchool_ClientUserPass_Mst where ClientCode=@ClientCode)
 set @Duplicate=1
 else
 set @Duplicate=0
 return @Duplicate
end


SqlConnection con = new SqlConnection(constr);
        SqlCommand cmdD = new SqlCommand("sp_DuplicateClientCode", con);
        cmdD.CommandType = CommandType.StoredProcedure;
        cmdD.Parameters.Add("@ClientCode", SqlDbType.VarChar).Value = txtccode.Text;
        var returnParameter = cmdD.Parameters.Add("@Duplicate", SqlDbType.Int);
        returnParameter.Direction = ParameterDirection.ReturnValue;
        con.Open();
        cmdD.ExecuteNonQuery();
        var result = returnParameter.Value;      
        con.Close();

Running Total in SqlServer

SELECT a.Id, a.sal, (SELECT SUM(b.sal)
                       FROM tbl_Testing b
                       WHERE b.id <= a.id) as Total
FROM   tbl_Testing a
ORDER BY a.id;

 Running Total Based on Multiple Columns

select *,(select sum(Credit)-sum(Debit) from Transactions where Tid<=t.Tid)
as Balance from Transactions as t

Using Case Statement in sqlserver and Changing the datatype of the column for displaying

Using Case Statement in sqlserver

Select Id,Name=case when Name='a' then 'x' else Name end from tbl_Testing

Changing the datatype of the column for displaying

Select Id,convert(decimal(10,2),sal) as sal from tbl_Testing