Things not clarified ,,

More than one rows per date

how spend date was calculated

how late and half date was updTED

etc

GIVE PROPER EXPLANATION ON HOW THE DATA RESIDES , AND WHAT...

usE Narayana Vyas Kondreddi's stored procedure sp_generate_inserts http://vyaskn.tripod.com/code/generate_inserts.txt in a SQL Server database.

Use this proc to generate the insert statement

Send the data as Insert scripts .

Send the sample data in queries ..

I am getting the result of the query as below based on the sample insert input

EID ENameLateHALFDAYAbsentOffDaySPENDTIME

17074ABCEmployee301151:24

;WITH AllDateCTE

AS

(

SELECT Eid,EName,Date,SpendTime,Remarks FROM ATTEND_LOG WHERE EID=17074

UNION ALL

SELECT Eid,EName,DATEADD(DD,-1,Date) AS Date,NULL AS SpendTime,

CASE

WHEN DATENAME(weekday,DATEADD(DD,-1,Date)) ='SUNDAY'

THEN CONVERT(VARCHAR(50),'OffDay' )

ELSE CONVERT(VARCHAR(50),'Absent' )

END as Remarks

FROM AllDateCTE

WHERE DATEADD(DD,-1,Date)...

I meant ,the previous select will add the absent rows alone

you have to include your calculation as below ,

;WITH AllDateCTE

AS

(

SELECT Eid,EName,Date,SpendTime,Remarks FROM ATTEND_LOG WHERE EID=17074

UNION ALL

SELECT...

You can calculate the absent details by the code below, then you can incorporate your code

CREATE TABLE Attend_log

(

Eid INT,

EName VARCHAR(50),

LogDate DATETIME,

SpendTime Datetime,

Remarks VARCHAR(50)

)

INSERT INTO Attend_log (Eid,EName,LogDate,SpendTime,Remarks)

SELECT '17074','ABCEmployee','2013-01-01 00:00:00.000','1900-01-01...

Send sample data ..With create table and insert ..specify the business rules

Thanks Patrick.

Shoul be asked in any crystal forums.. this s SQL Server related one

Its not really adding any value..

Yeah,UMG Developer s right ...

You can't just pass "xls" as the extension and have it put it into proper Excel format. No matter what extension you use, it'll have the...

Why you haven't gone for in-place upgrade?

It should return the data ..see the sample below

are the both columns have the same collation SQL_Latin1_General_CP1_CI_AS

if not add the collation in the where clause

CREATE TABLE USERACCOUNT...