Need Urgent Query For Pending records

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)

Need Urgent Query For Pending records

Author

Topic

pradeep_iete
Yak Posting Veteran

84 Posts

Posted - 2008-12-02 : 03:43:14

I have 3 table

T1 dailyreport ( loginid, reported ,creationdate)
T2 holidaytable ( holidayId, date)
T3 employeeLeaveTable( loginID ,leavefromdate,leavetilldate)

Now
Table T1 need to be inserted daily for employee report.Ideally it accommodates all working day entries by all employeeID

Table T2 carries the all the date for official holidays

Table T3 carries the date for emplyeeID(loginID) on which date he was absent.

Now
I want to have a query Which will give me results like ( it cud be another way
But the result content should identical)

Date employeeID
24 October 2008 13, 14, 15
25 October 2008 12, 13, 17,14,15

NOTE: These employeID should not be in attendance table and the days should not be in holiday table. Such referrals should be involved in the query

I hope the question is clear

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 04:06:23

[code]SELECT t.creationdate as date,LEFT(ll.LoginList,LEN(ll.LoginList)-1) AS Employees
FROM
(SELECT DISTINCT t1.creationdate
FROM T1 t1
LEFT JOIN T2 t2
ON t2.date=t1.creationdate
WHERE t2.date IS NULL) t
CROSS APPLY (SELECT CAST(loginID AS varchar(10))+ ','
FROM T3
WHERE t.creationdate BETWEEN leavefromdate
AND DATEADD(dd,1,leavetilldate)
FOR XML PATH(''))ll(LoginList)[/code]

pradeep_iete
Yak Posting Veteran

84 Posts

Posted - 2008-12-02 : 05:24:11

Thanks Visakh,
IF i add one more table T4 ( employee(employeeid,Name,Designation) from which i need to display the EmployeeName.Also I need complete list of employee which have not submitted the report.In above case i am gettin name only from T3 (It may be possible that an employee has never been absent and has also not submitted the report.Then his name would not come as he is not in table T3.
Now i want all names to be displayed from T4 keeping my previous condition as it is.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 06:01:59

show your new output please

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 06:06:07

do you mean this

SELECT t.creationdate as date,LEFT(ll.LoginList,LEN(ll.LoginList)-1) AS Employees
FROM 
(SELECT DISTINCT t1.creationdate 
FROM T1 t1
LEFT JOIN T2 t2
ON t2.date=t1.creationdate 
WHERE t2.date IS NULL) t
CROSS APPLY (SELECT t4.EmployeeName + ','
             FROM T4 t4
             LEFT JOIN T3 t3
			 ON t3.loginID =t4.EmployeeID
             WHERE (t.creationdate BETWEEN t3.leavefromdate 
             AND DATEADD(dd,1,t3.leavetilldate))
             OR t3.loginID IS NULL
             FOR XML PATH(''))ll(LoginList)

pradeep_iete
Yak Posting Veteran

84 Posts

Posted - 2008-12-02 : 07:16:58

Visakh, Your Qurey is not returning correct result .I am posing probelm again with new addition

I have 4 table

T1 dailyreport ( loginid, reported ,creationdate)
T2 holidaytable ( holidayId, date)
T3 employeeLeaveTable( loginID ,leavefromdate,leavetilldate)
T4 employee ( employeeID,employeeName,Department)

Now
Table T1 need to be inserted daily for employee report.Ideally it accommodates all working day entries by all employeeID

Table T2 carries the all the date for official holidays

Table T3 carries the date for emplyeeID(loginID) on which date he was absent.

Now
I want to have a query Which will give me results like ( it cud be another way
But the result content should identical)

Date Pending Report by employeeName
24 October 2008 Ajay,
24 October 2008 Pradeesh,
24 October 2008 Venkatesh
25 October 2008 pradeep,
25 October 2008 Visakh,
25 October 2008 Manu

NOTE: These employeeName should not be in attendance table and the days should not be in holiday table. I want this name to be from Table T4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 07:23:42

will dailyreport contain record for each date?

pradeep_iete
Yak Posting Veteran

84 Posts

Posted - 2008-12-02 : 08:17:51

Yes , It is Compulsarily to be entered by all employee.We have to find out the pending reports
(EMployee name who has not filled a report with date ) after checking that he is not there in table
T3 and the same date is not in T2.
Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 09:06:12

quote:
Originally posted by pradeep_iete

Yes , It is Compulsarily to be entered by all employee.We have to find out the pending reports
(EMployee name who has not filled a report with date ) after checking that he is not there in table
T3 and the same date is not in T2.
Thank You

seems like this


SELECT t.creationdate as date,LEFT(ll.LoginList,LEN(ll.LoginList)-1) AS Employees
FROM 
(SELECT DISTINCT t1.creationdate 
FROM T1 t1
LEFT JOIN T2 t2
ON t2.date=t1.creationdate 
WHERE t2.date IS NULL) t
CROSS APPLY (SELECT t4.EmployeeName + ','
             FROM T4 t4
             LEFT JOIN T3 t3
			 ON t3.loginID =t4.EmployeeID
             WHERE t3.loginID IS NULL
             FOR XML PATH(''))ll(LoginList)

pradeep_iete
Yak Posting Veteran

84 Posts

Posted - 2008-12-03 : 01:55:10

I Visakh, above query did not do expeected result.But now you see

I have two derived tables now

Table 1 ( loginID, creationdate)

Table 2 ( employeeID, date)
In table 2 ( employeeid ,date) is for entire month days
Like
2 + ’1 Nov 08,
2 + ’2 Nov 08’
2 + ‘3 Nov 08’
……
2 + ‘30 Nov 08’
3 + ’1 Nov 08,
3 + ’2 Nov 08’
3 + ‘3 Nov 08’
……
3 + ‘30 Nov 08’
And like wise …
Now
I want to compare now both tables and return results set containing
Those list loginIDs that are not there in table T1 on the particular creationdate on month basis.
Can we use table T2 in some way to get our result.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

[code]SELECT t2.*
FROM Table2 t2
LEFT JOIN Table1 t1
ON t1.loginID=t2.EmployeeID
AND t1.creationdate=t2.date
WHERE t2.EmployeeID IS NULL [/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources