Unable to generate my report

I have to generate a report in this format:

staffcode Name dateintime dateoutime duration

for example:

Staffcode Name 01-08-2010intime 01-08-20100uttime 01-08-2010duration

1001 Amit 09:00 18:30 09:30

In sql server management studio for 5 days i have written query like this

Declare @intime table
(
Staffcode varchar(7),
Name varchar(100),
[01-08-2010 InTime] datetime,
[02-08-2010 InTime] datetime,
[03-08-2010 InTime] datetime,
[04-08-2010 InTime] datetime,
[05-08-2010 InTime] datetime
)

Declare @outtime table
(
Staffcode varchar(7),
Name varchar(100),
[01-08-2010 outTime] datetime,
[02-08-2010 outTime] datetime,
[03-08-2010 outTime] datetime,
[04-08-2010 outTime] datetime,
[05-08-2010 outTime] datetime
)

Declare @Duration table
(
Staffcode varchar(7),
Name varchar(100),
[01-08-2010 Duration] datetime,
[02-08-2010 Duration] datetime,
[03-08-2010 Duration] datetime,
[04-08-2010 Duration] datetime,
[05-08-2010 Duration] datetime
)

Insert into @Intime
SELECT
StaffCode,
Name,
[01-08-2010] as [01-08-2010],
[02-08-2010] as [02-08-2010],
[03-08-2010] as [03-08-2010],
[04-08-2010] as [04-08-2010],
[05-08-2010] as [05-08-2010]
FROM
(
SELECT
StaffCode,
Name,
CONVERT(VARCHAR(10),AttendanceDate,105) AttendanceDate,
CONVERT(VARCHAR(5),MIN(FirstPunch),108) InTime
FROM AttendanceLog
JOIN Staff on Staff.Id = AttendanceLog.StaffId
WHERE AttendanceDate BETWEEN '2010-08-01' And '2010-08-05'
AND Staffcode BETWEEN '10001' AND '10999'
AND name <>'' and workstatus = 'Is Working'
GROUP BY
name,StaffCode,AttendanceDate
)p

PIVOT
(
  MIN(InTime)
  FOR AttendanceDate
  IN
(
[01-08-2010],
[02-08-2010],
[03-08-2010],
[04-08-2010],
[05-08-2010]
)
)AS pvt


Insert into @Outtime

SELECT
StaffCode,
Name,
[01-08-2010] as [01-08-2010],
[02-08-2010] as [02-08-2010],
[03-08-2010] as [03-08-2010],
[04-08-2010] as [04-08-2010],
[05-08-2010] as [05-08-2010]
FROM
(
SELECT
StaffCode,
Name,
CONVERT(VARCHAR(10),AttendanceDate,105) AttendanceDate,
CONVERT(VARCHAR(5),MAX(LastPunch),108) OutTime
FROM AttendanceLog
JOIN Staff on Staff.Id = AttendanceLog.StaffId
WHERE AttendanceDate BETWEEN '2010-08-01' And '2010-08-05'
AND Staffcode BETWEEN '10001' AND '10999'
AND name <>'' and workstatus = 'Is Working'
GROUP BY
name,StaffCode,AttendanceDate
)p

PIVOT
(
  MAX(OutTime)
  FOR AttendanceDate
  IN
(
[01-08-2010],
[02-08-2010],
[03-08-2010],
[04-08-2010],
[05-08-2010]
)
)AS pvt

Insert into @Duration

SELECT
StaffCode,
Name,
[01-08-2010] as [01-08-2010],
[02-08-2010] as [02-08-2010],
[03-08-2010] as [03-08-2010],
[04-08-2010] as [04-08-2010],
[05-08-2010] as [05-08-2010]
FROM
(
SELECT
StaffCode,
Name,
CONVERT(VARCHAR(10),AttendanceDate,105) AttendanceDate,
CONVERT(VARCHAR(5),(MAX(LastPunch) - MIN(FirstPunch)),114) Duration
FROM AttendanceLog
JOIN Staff on Staff.Id = AttendanceLog.StaffId
WHERE AttendanceDate BETWEEN '2010-08-01' And '2010-08-05'
AND Staffcode BETWEEN '1001' AND '1999'
AND name <>'' and workstatus = 'Is Working'
GROUP BY
name,StaffCode,AttendanceDate
)p

PIVOT
(
  MAX(Duration)
  FOR AttendanceDate
  IN
(
[01-08-2010],
[02-08-2010],
[03-08-2010],
[04-08-2010],
[05-08-2010]
)
)AS pvt

Select

I.StaffCode,
I.Name,
CONVERT(VarCHAR(5),[01-08-2010 InTime],114)  [01-08-2010 InTime],
CONVERT(VarCHAR(5),[01-08-2010 OutTime],114) [01-08-2010 OutTime],
CONVERT(VarCHAR(5),[01-08-2010 Duration],114)[01-08-2010 Duration],
CONVERT(VarCHAR(5),[02-08-2010 InTime],114)     [02-08-2010 InTime],
CONVERT(VarCHAR(5),[02-08-2010 OutTime],114) [02-08-2010 OutTime],
CONVERT(VarCHAR(5),[02-08-2010 Duration],114)[02-08-2010 Duration],
CONVERT(VarCHAR(5),[03-08-2010 InTime],114)     [03-08-2010 InTime],
CONVERT(VarCHAR(5),[03-08-2010 OutTime],114) [03-08-2010 OutTime],
CONVERT(VarCHAR(5),[03-08-2010 Duration],114)[03-08-2010 Duration],
CONVERT(VarCHAR(5),[04-08-2010 InTime],114)     [04-08-2010 InTime],
CONVERT(VarCHAR(5),[04-08-2010 OutTime],114) [04-08-2010 OutTime],
CONVERT(VarCHAR(5),[04-08-2010 Duration],114)[04-08-2010 Duration],
CONVERT(VarCHAR(5),[05-08-2010 InTime],114)     [05-08-2010 InTime],
CONVERT(VarCHAR(5),[05-08-2010 OutTime],114) [05-08-2010 OutTime],
CONVERT(VarCHAR(5),[05-08-2010 Duration],114)[05-08-2010 Duration]
From @Intime I
JOIN @Outtime O on I.StaffCode=O.StaffCode
JOIN @Duration D on I.StaffCode=D.Staffcode
order by Staffcode

From this query i am getting intime, outtime and duration for all the employees who is working in my company..

I have generated report for these 5 days also..

But now i want to generate report for n number of days...

for that what i need to do.

But i want all the dates in column only..

Please anyone tell me what to do for this...

Please tell me what i need to change in query???

I am working with sql server 2005.. One more thing i think we need to use while loop... But i am confuse how to use and where to use.. because i am new in this field..

Thanks & Regards

First of all, you don't need three tables for "in-", "out-" time and duration. You only need one which would incorporate this data in a single CTE.

Then, you would want to generate for a given timespan an array of days in varchar format.

declare @from datetime = '20100901', @to datetime = '20100910'
;with Calendar as
(
  SELECT CAST(@from as datetime) AS [date]
  UNION ALL
  SELECT DATEADD(dd, 1, [date])
  FROM Calendar
  WHERE DATEADD(dd, 1, [date]) <= @to
)
SELECT substring(convert(varchar, [date], 121), 1, 10) Day
FROM Calendar c
OPTION (MAXRECURSION 0);

Then you would assemble those days in a single string and insert it in places where you use those dates in the sql code.

Then you would run this monster of a query using exec command.

On a side note. It's all cool and peachy to use sql for this task, but personally I would install Report Builder and build this report from flat data that you have and there are all sorts of aggregation functions available in there that you can't easily imitate using just sql without any helper functions.

Update. Actual implementation of a dynamic pivot may be found here: http://goo.gl/7Wkk

ansaurus

tags:

views:

answers:

Unable to generate my report

related questions