Click here to Skip to main content
15,886,788 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi,

this is my query

SQL
select distinct
E.USERID, U.EmployeeName,
Convert(date,LOCALTIMESTAMP) as ATTDate,
(select min(Convert(TIME,TIMESTAMPS)) from access_event_logs As MINCE where CAST(MINCE.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MINCE.EVENTID ='In' and MINCE.USERID=E.USERID) as InTime,
(select max(Convert(TIME,TIMESTAMPS)) from access_event_logs As MAXCE where CAST(MAXCE.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=E.USERID) as OutTime
,(select DATEDIFF(MINUTE, '8:30:00', (select min(Convert(TIME,TIMESTAMPS)) from access_event_logs As MINCE2 where CAST(MINCE2.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MINCE2.EVENTID ='In' and MINCE2.USERID=E.USERID))) as LateTime
,(select DATEDIFF(MINUTE, (select max(Convert(TIME,TIMESTAMPS)) from access_event_logs As MAXCE where CAST(MAXCE.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=E.USERID), '14:30')) as EarlyTime
from access_event_logs As E
Inner join EmployeeRegistration as U on E.userid = U.userid
WHERE E.USERID='002' AND Convert(date,LOCALTIMESTAMP) between '03/2/2014' and '03/16/2014'



how to get report in vb.net...

iam using another table eventlog.... this table eventid columns create rows (in and out)...
this query...

how to cal on vb.net.... i want employee late report
Posted
Comments
Saravanan Maddy 8-Apr-14 18:59pm    
Create Stored Procedure In Sql and Link that stored Procedure to Crystal Report.

hi Akhil:

Check this page, here is very simple and straight Crystal report with Stored Procedure

Stored Proc In Crystal Report[^]
 
Share this answer
 
1.First you set up the the odbc Connecton and then open new report ,then select the ODBC connection and Corresponding fields (eg:USERID, EmployeeName)

2.Go Database menu in crystal reports > Show Sql Query > put your query there

another method is create an SP
and add to crystal report
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900