select help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select help

Author

Topic

abcd
Yak Posting Veteran

92 Posts

Posted - 2008-12-09 : 00:19:23

hi all....m giving a small data of my problem....do help
there is table staffattendence having fields as
attendenceid int
picdate datetime
isleavetype varchar
ispispunched int

isleavetype column contains entry like normal duty,onduty,ontour

my query is to
1.count how many times a person have been on duty,ontour
2.i only want the data between the two picdates specified and input will be given as (frommonth) and (fromyear) to (tomonth) (toyear)....

output as

userid,onduty,ontour

hope i am able to clarify my problem...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 02:00:03

can you show what you tried till now?

abcd
Yak Posting Veteran

92 Posts

Posted - 2008-12-09 : 03:36:01

thnks for the concern
here is what i have done so far..

query is completed succesfully,but the problem comes while execution.

In output only the column names are coming and no data is present j...ust one row having all the column name desired in the select query.

alter PROCEDURE staff_monthlydetails

@CompanyId int

,@DeptId int

,@FromYear varchar

,@Frommonth varchar

,@toYear varchar

,@tomonth varchar

AS

BEGIN

SELECT distinct

StaffAtten.userid

,ULogin.username

,d.deptname

,Tour=(select count(IsLeaveType) from staff_attendencedetails

where isLeavetype='T' )

--and month(picdate)=@monthid and year(picdate)=@yearid and userid=staffatten.userid)

,OnDuty=(select count(IsLeaveType) from staff_attendencedetails

where isLeavetype='o' )

--and month(picdate)=@monthid and year(picdate)=@yearid)

,smattendence.monthid

FROM

Staff_AttendenceDetails AS StaffAtten

Left join Staff_MonthAttendence AS SmAttendence ON SmAttendence.userid=StaffAtten .userid

Left join User_Login AS ULogin ON ULogin.userid=StaffAtten.userid

Left join User_Colink AS UcoLink ON UcoLink.userid=StaffAtten .userid

Left join Dept AS d ON UcoLink.deptid=d.deptid

Left join Company_Details AS CDetails ON d.companyid=CDetails.companyid

WHERE

CDetails.companyid=@companyid

AND d.deptid=@deptid

AND staffatten.PicDate

BETWEEN

CAST(@FromYear + '/' + @FromMonth + '/01' AS datetime) AND

DATEADD(second, -1, DATEADD(month, 1, CAST(@ToYear + '/' + @ToMonth + '/01' AS datetime)))

GROUP BY

StaffAtten.userid

,ULogin.username

,d.deptname

,StaffAtten.isleavetype

,SmAttendence.monthid

END

--EXEC staff_monthlydetails 2,5,'2008','11','2008','12'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 11:06:31

something like


DECLARE @StartDate datetime,@EndDate datetime

SELECT @StartDate =DATEADD(mm,frommonth-1,DATEADD(yy,@fromyear-1900,0)),
@EndDate =DATEADD(mm,tomonth,DATEADD(yy,@toyear-1900,0))


SELECT userid,
COUNT(CASE WHEN isleavetype='T' THEN isleavetype ELSE NULL END) AS ontour,
COUNT(CASE WHEN isleavetype='o' THEN isleavetype ELSE NULL END) AS onduty
FROM staffattendence 
WHERE picdate BETWEEN @StartDate AND @EndDate
GROUP BY userid

if this is not what you want,please post your sample output

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources