March 28, 2012 at 11:49 am
Hi All,
Using the data below, I would like to have the example output below. What is the best way to accomplish this?
Example Output:
PersonIDWorkedCodeEventdateStartTimeEndTimeStartTimeEndTimeStartTimeEndTimeStartTimeEndTimeTotalWorkedTimeMoneyAmount
10754917Meals3/27/2012$2.00
10754917Regualr3/27/20128.00
110602313/27/20126:1512:1512:4512:459.25
11061852Meals3/27/2012$2.00
110618523/27/20128:0012:3013:0016:458.25
14065041Jury Duty3/27/20128.00
130618193/27/201214:3020:0020:3023:0023:0003:0003:0007:1515.75
CREATE TABLE #PersonTime
(PersonID VARCHAR(10)
,eventdatedatetime
,startdtmdatetime
,enddtmdatetime
,workedtimeFLOAT(6)
,moneyamountFLOAT(6)
,workedcode VARCHAR(50)
,workedtypeVARCHAR(50)
)
INSERT INTO #PersonTime
SELECT '8756294','2012-03-27 00:00:00.000', NULL,NULL,'8.00000',NULL, 'Regular','WorkedDuration' UNION
SELECT '9064431','2012-03-27 00:00:00.000', '2012-03-27 07:30:00.000', '2012-03-27 12:45:00.000', '5.2500000', NULL, NULL, 'TotaledWorked' UNION
SELECT '9064431','2012-03-27 00:00:00.000', '2012-03-27 16:30:00.000', '2012-03-28 07:45:00.000', '14.7500000',NULL, NULL, 'TotaledWorked' UNION
SELECT '9064431','2012-03-27 00:00:00.000', '2012-03-28 09:15:00.000', '2012-03-28 11:45:00.000', '2.500000', NULL, NULL, 'TotaledWorked' UNION
SELECT '9764752','2012-03-27 00:00:00.000', '2012-03-27 06:00:00.000', '2012-03-27 08:00:00.000', '2.00000', NULL, NULL, 'TotaledWorked' UNION
SELECT '9764752','2012-03-27 00:00:00.000', '2012-03-27 08:45:00.000', '2012-03-27 12:45:00.000', '4.00000', NULL, NULL, 'TotaledWorked' UNION
SELECT '9764752','2012-03-27 00:00:00.000', '2012-03-27 13:15:00.000', '2012-03-27 14:45:00.000', '1.500000', NULL, NULL, 'TotaledWorked' UNION
SELECT '10754917','2012-03-27 00:00:00.000', NULL,NULL,'8.00000',NULL, 'Regular','WorkedDuration' UNION
SELECT '10754917','2012-03-27 00:00:00.000', NULL,NULL,NULL, '2.00000', 'Meals', 'CodeEntered' UNION
SELECT '11060231','2012-03-27 00:00:00.000', '2012-03-27 06:15:00.000', '2012-03-27 12:15:00.000', '6.00000', NULL, NULL, 'TotaledWorked' UNION
SELECT '11060231','2012-03-27 00:00:00.000', '2012-03-27 12:45:00.000', '2012-03-27 16:00:00.000', '3.2500000', NULL, NULL, 'TotaledWorked' UNION
SELECT '11061852','2012-03-27 00:00:00.000', '2012-03-27 08:00:00.000', '2012-03-27 12:30:00.000', '4.500000',NULL, NULL, 'TotaledWorked' UNION
SELECT '11061852','2012-03-27 00:00:00.000', NULL,NULL,NULL, '2.00000', 'Meals', 'CodeEntered' UNION
SELECT '11061852','2012-03-27 00:00:00.000', '2012-03-27 13:00:00.000', '2012-03-27 16:45:00.000', '3.7500000', NULL, NULL, 'TotaledWorked' UNION
SELECT '12754916','2012-03-27 00:00:00.000', '2012-03-27 09:00:00.000', '2012-03-27 12:45:00.000', '3.7500000', NULL, NULL, 'TotaledWorked' UNION
SELECT '12754916','2012-03-27 00:00:00.000', '2012-03-27 13:00:00.000', '2012-03-27 16:30:00.000', '3.500000', NULL, NULL, 'TotaledWorked' UNION
SELECT '12754916','2012-03-27 00:00:00.000', NULL,NULL,NULL, '2.00000', 'Meals', 'CodeEntered' UNION
SELECT '12756343','2012-03-27 00:00:00.000', '2012-03-27 08:00:00.000', '2012-03-27 12:15:00.000', '4.2500000', NULL, NULL, 'TotaledWorked' UNION
SELECT '12756343','2012-03-27 00:00:00.000', '2012-03-27 12:30:00.000', '2012-03-27 16:45:00.000', '4.2500000', NULL, NULL, 'TotaledWorked' UNION
SELECT '12756677','2012-03-27 00:00:00.000', NULL,NULL,'8.00000', NULL, 'TDIC', 'CodeEntered' UNION
SELECT '12757126','2012-03-27 00:00:00.000', '2012-03-27 06:30:00.000', '2012-03-27 15:45:00.000', '8.7500000', NULL, NULL, 'TotaledWorked' UNION
SELECT '12762826','2012-03-27 00:00:00.000', '2012-03-27 07:00:00.000', '2012-03-27 15:45:00.000', '8.7500000', NULL, NULL, 'TotaledWorked' UNION
SELECT '13061819','2012-03-27 00:00:00.000', '2012-03-27 14:30:00.000', '2012-03-27 20:00:00.000', '5.500000', NULL, NULL, 'TotaledWorked' UNION
SELECT '13061819','2012-03-27 00:00:00.000', '2012-03-27 23:00:00.000', '2012-03-28 03:00:00.000', '4.00000', NULL, NULL, 'TotaledWorked' UNION
SELECT '13061819','2012-03-27 00:00:00.000', '2012-03-28 03:30:00.000', '2012-03-28 07:15:00.000', '3.7500000', NULL, NULL, 'TotaledWorked' UNION
SELECT '13061819','2012-03-27 00:00:00.000', '2012-03-27 20:30:00.000', '2012-03-27 23:00:00.000', '2.500000', NULL, NULL, 'TotaledWorked' UNION
SELECT '14065041','2012-03-27 00:00:00.000', NULL,NULL,'8.00000', NULL, 'Jury Duty', 'CodeEntered'
SELECT PersonID, convert(varchar, eventdate, 101), CONVERT(VARCHAR,startdtm,108), CONVERT(VARCHAR,enddtm,108), workedtime, moneyamount, workedcode, workedtype FROM #PersonTime ORDER BY PERSONID
DROP TABLE #PersonTime
March 28, 2012 at 11:59 am
If there are frequent Start and end times, then the number of columns is going to increase heavily and the report will look ugly. Are you sure you want the report to be this way?
March 28, 2012 at 12:32 pm
Hi ColdCoffee, thanks for your response. The most start and end time a person will have it 4, so this should accommodate it.
March 29, 2012 at 6:36 am
Anyone with any idea on this?
March 29, 2012 at 6:52 am
EjSQLme (3/29/2012)
Anyone with any idea on this?
If it is always no more than 4, then this will work: -
SELECT PersonID, workedcode, CONVERT(VARCHAR(10), eventdate, 101) AS Eventdate,
MAX(CASE WHEN rn = 1 THEN CONVERT(VARCHAR(8),startdtm,108) END) AS StartTime,
MAX(CASE WHEN rn = 1 THEN CONVERT(VARCHAR(8),enddtm,108) END) AS EndTime,
MAX(CASE WHEN rn = 2 THEN CONVERT(VARCHAR(8),startdtm,108) END) AS StartTime,
MAX(CASE WHEN rn = 2 THEN CONVERT(VARCHAR(8),enddtm,108) END) AS EndTime,
MAX(CASE WHEN rn = 3 THEN CONVERT(VARCHAR(8),startdtm,108) END) AS StartTime,
MAX(CASE WHEN rn = 3 THEN CONVERT(VARCHAR(8),enddtm,108) END) AS EndTime,
MAX(CASE WHEN rn = 4 THEN CONVERT(VARCHAR(8),startdtm,108) END) AS StartTime,
MAX(CASE WHEN rn = 4 THEN CONVERT(VARCHAR(8),enddtm,108) END) AS EndTime,
SUM(workedtime) AS TotalWorkedTime,
'$'+CONVERT(VARCHAR(6),CONVERT(MONEY,SUM(moneyamount))) AS MoneyAmount
FROM (SELECT PersonID, eventdate, startdtm, enddtm, workedtime, moneyamount, workedcode, workedtype,
ROW_NUMBER() OVER(PARTITION BY PersonID, eventdate, workedcode ORDER BY startdtm, enddtm) AS rn
FROM #PersonTime) a
GROUP BY PersonID, workedcode, CONVERT(VARCHAR(10), eventdate, 101)
Results in this, using your sample data: -
PersonID workedcode Eventdate StartTime EndTime StartTime EndTime StartTime EndTime StartTime EndTime TotalWorkedTime MoneyAmount
---------- -------------------------------------------------- ---------- --------- -------- --------- -------- --------- -------- --------- -------- ---------------------- -----------
10754917 Meals 03/27/2012 NULL NULL NULL NULL NULL NULL NULL NULL NULL $2.00
10754917 Regular 03/27/2012 NULL NULL NULL NULL NULL NULL NULL NULL 8 NULL
11060231 NULL 03/27/2012 06:15:00 12:15:00 12:45:00 16:00:00 NULL NULL NULL NULL 9.25 NULL
11061852 NULL 03/27/2012 08:00:00 12:30:00 13:00:00 16:45:00 NULL NULL NULL NULL 8.25 NULL
11061852 Meals 03/27/2012 NULL NULL NULL NULL NULL NULL NULL NULL NULL $2.00
12754916 NULL 03/27/2012 09:00:00 12:45:00 13:00:00 16:30:00 NULL NULL NULL NULL 7.25 NULL
12754916 Meals 03/27/2012 NULL NULL NULL NULL NULL NULL NULL NULL NULL $2.00
12756343 NULL 03/27/2012 08:00:00 12:15:00 12:30:00 16:45:00 NULL NULL NULL NULL 8.5 NULL
12756677 TDIC 03/27/2012 NULL NULL NULL NULL NULL NULL NULL NULL 8 NULL
12757126 NULL 03/27/2012 06:30:00 15:45:00 NULL NULL NULL NULL NULL NULL 8.75 NULL
12762826 NULL 03/27/2012 07:00:00 15:45:00 NULL NULL NULL NULL NULL NULL 8.75 NULL
13061819 NULL 03/27/2012 14:30:00 20:00:00 20:30:00 23:00:00 23:00:00 03:00:00 03:30:00 07:15:00 15.75 NULL
14065041 Jury Duty 03/27/2012 NULL NULL NULL NULL NULL NULL NULL NULL 8 NULL
8756294 Regular 03/27/2012 NULL NULL NULL NULL NULL NULL NULL NULL 8 NULL
9064431 NULL 03/27/2012 07:30:00 12:45:00 16:30:00 07:45:00 09:15:00 11:45:00 NULL NULL 22.5 NULL
9764752 NULL 03/27/2012 06:00:00 08:00:00 08:45:00 12:45:00 13:15:00 14:45:00 NULL NULL 7.5 NULL
April 2, 2012 at 2:43 pm
Thank you so much! this worked
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply