Generate Time sheet file

Declare @StartDate as Datetime SET @StartDate='2014-06-26' Declare @EndDate as Datetime SET @EndDate='2014-07-26' Declare @YearMonth as varchar(6) set @YearMonth='201407' Declare @CurrDate as Datetime SET @CurrDate=@StartDate Declare @ct as int set @ct=1; Declare @dcnt as int set @dcnt=0 while (@startDate<@EndDate) begin if (datepart(dw,@startdate)<> 1) and( datepart(dw,@startdate)<>7) begin if @dcnt=0 begin set @CurrDate=@startdate end set @dcnt=@dcnt+1 end if (@dcnt=5) or (@startDate=dateadd(dd,-1,@EndDate)) begin insert into Dest SELECT 'User Name','','AVMI0'+ CAST(@ct AS VARCHAR(2))+ @YearMonth,'task'+CAST(@ct AS VARCHAR(2)),@startdate,@CurrDate,@dcnt,@dcnt*8,'','' As [Resolution],DATEADD(s, 2, @CurrDate),@startdate,@dcnt,@dcnt*8,'','' As [Created By],@CurrDate,'Closed(16)',DATEADD(s, 1, @CurrDate),DATEADD(s, 2, @CurrDate),'','','','','','','','','','','','','',@startdate,'','','','','','','','','','','','','','' set @ct=@ct+1 set @dcnt=0 end SET @startdate=dateadd(dd,1,@startdate) end go select * from Dest

Sql query based on Substring,Charindex functions

Hi Guys I have one question on a sql query if you know any one, if they can help to write a query. My question is if there is a string example1 "450 lbs weight capacity" example 2 "10-40 weight capacity" the result should be displayed for the first example minimum value maximum value weight 0 450 lbs Second example minimum value maximum value weight 10 40 lbs The results should be displayed in this way if you know some one please help me . --------------------------------------------------------------------------------------------
Solution
create table #temp(test varchar(2000)) insert into #temp values('450 lbs weight capacity')
insert into #temp values('10-40 weight capacity')
insert into #temp values('560-600 lbs weight capacity')
insert into #temp values('')
insert into #temp values(null)
insert into #temp values('ab')
--select * from #temp
select *,replace(substring(test,0,charindex('weight',test)),'lbs','') as value ,CASE WHEN CHARINDEX('-',replace(substring(test,0,charindex('weight',test)),'lbs',''))=0 THEN 0 ELSE SUBSTRING(replace(substring(test,0,charindex('weight',test)),'lbs',''),0,CHARINDEX('-',replace(substring(test,0,charindex('weight',test)), 'lbs',''))) END as minimum ,CASE WHEN CHARINDEX('-',replace(substring(test,0,charindex('weight',test)),'lbs',''))=0 THEN replace(substring(test,0,charindex('weight',test)), 'lbs','') ELSE SUBSTRING(replace(substring(test,0,charindex('weight',test)),'lbs',''),CHARINDEX('-',replace(substring(test,0,charindex('weight',test)), 'lbs',''))+1,LEN(replace(substring(test,0,charindex('weight',test)),'lbs',''))) END as maximum, 'lbs' as weightq from #temp

how to get sum of positive and negative numbers

Hi, I have table which containse positive and negative numbers
 I need sum of positive and negative numbers
 create table #esal(esal int)

insert into #esal values(-2)
insert into #esal values(-3)
insert into #esal values(12)
insert into #esal values(23)
 --select * from #esal


 select SUM(esal) from #esal group by case when esal>0 then '+ve' else '-ve' end

Source Result set
---------------

Source Result set

Destination result set
-----------------------

Destination Result set

To find all names who have same salry of eid=10

create table emp(eid int,ename varchar(20),esal money)

insert into  emp values(10,'Raju',200)

insert into  emp values(20,'ramu',200)

insert into  emp values(30,'muttu',300)

insert into  emp values(40,'vikarm',400)

insert into  emp values(50,'vina',500)

insert into  emp values(60,'guru',200)

select * from emp

--To find all names who have same salry of eid=10

select e2.eid,e2.esal,e2.ename  from emp e1,emp e2

where e1.eid=10 and e2.esal=e1.esal

Single updation change gender type

Create table empGender(eid int,ename varchar(20),eGender varchar(1))

insert into empGender values(1,'Raju','M')

insert into empGender values(2,'mura','M')

insert into empGender values(3,'kiran','M')

insert into empGender values(4,'rani','M')

insert into empGender values(5,'vani','F')

insert into empGender values(6,'ramya','F')

select * from empgender

update empgender set eGender=case eGender when 'M' Then 'F'   ELSE 'M' END            

select * from empgender

EID vs ManagerName

I have a table  empsalary with columns as eid,ename,emanager I need result table as

Create table empSalary(eid int,ename varchar(20),esal money,eManager int null)

insert into empsalary values(1,'rghu',20000,null)

insert into empsalary values(2,'en',15000,1)

insert into empsalary values(3,'ven',10000,2)

insert into empsalary values(4,’murthys’,15000,1)

insert into empsalary values(5,'karim',10000,2)

insert into empsalary values(6,'sir',18000,2)

insert into empsalary values(7,'siri',20000,null)

select *from empsalary order by esal desc

EId,Ename.Manager

select e.eid,e.ename,isnull(s.ename,'No Manager') as manager from empsalary e

left outer join (select * from empsalary )as  s on s.eid=e.emanager