SQL MERGE

10 pts.
Tags:
SQL
SQL MERGE
I have a view like below generated from my time and attendance software. staffNo EmployeeName title TransactionType Cdate time 556 JOHN WEB DEVELOPER IN ENTRY 24/01/2008 13:47:30 556 JOHN WEB DEVELOPER OUT ENTRY 24/01/2008 16:38:33 556 JOHN WEB DEVELOPER OUT ENTRY 24/01/2008 16:40:03 556 JOHN WEB DEVELOPER IN ENTRY 26/01/2008 10:05:39 556 JOHN WEB DEVELOPER IN ENTRY 26/01/2008 10:05:45 556 JOHN WEB DEVELOPER OUT ENTRY 26/01/2008 14:07:06 556 JOHN WEB DEVELOPER OUT ENTRY 27/01/2008 16:40:45 Now i need to get this data like below. staffNo EmployeeName title Cdate IN OUT 556 JOHN WEB DEVELOPER 24/01/2008 13:47:30 16:40:03 556 JOHN WEB DEVELOPER 25/01/2008 NO RECS NO RECS 556 JOHN WEB DEVELOPER 26/01/2008 10:05:39 14:07:06 556 JOHN WEB DEVELOPER 27/01/2008 NO RECS 16:40:45

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

If I understand the problem, you want to produce one row per day and employee for every day from the employee’s first transaction to the employee’s last transaction. (For example, John had transactions from Jan. 24 to Jan. 27, so you want a row for each day in that range, even days like Jan. 25 when John had no transactions.) You can do that with an outer join of a table that contains all dates with a table containing the employee information, including the first and last transaction dates. Then, for each day+employee combination, you want the times of the earliest ‘IN ENTRY’ transaction and the latest ‘OUT ENTRY’ transaction (or ‘NO RECS’ if there were none on that day). That involves another outer join the transaction data.

Basically, then, you want a query like

<pre>
SELECT …
FROM d – interesting Cdates
LEFT OUTER JOIN
s – staff
ON d.Cdate BETWEEN s.minCdate AND s.maxCdate
LEFT OUTER JOIN
t – transaction data
ON d.cDate = t.cDate
;
</pre>

The full solution (shown below) derives the “tables” d, s and t from the attendance view. It would be more efficient (and perhaps simpler) to derive them from the base tables upon which the view is based.
You didn’t say what kind of system you’re using. The following solution works in Oracle. Some details (like how you map all date+time values in a calendar day to a single one) will be different in other systems.
The solution below refers to a “counter table” called intgr which has a single column (n) containing the integer values 0, 1, 2, … . You can generate such a counter table on the fly; again, there are different techniques in different products.

<pre>
SELECT s.staffNo
, s.EmployeeName
, s.title
, TO_CHAR ( d.cDate
, ‘DD/MM/YYYY’
) AS Cdt
, COALESCE ( TO_CHAR ( t.inCDate
, ‘HH24:MI:SS’
)
, ‘NO RECS’
) AS inDt
, COALESCE ( TO_CHAR ( t.outCDate
, ‘HH24:MI:SS’
)
, ‘NO RECS’
) AS outDt
FROM ( – Begin in-line view d of interesting Cdates
SELECT minCdate + n AS Cdate
FROM intgr
CROSS JOIN ( – Begin in-line view to compute minCdate, maxCdate
SELECT TRUNC (MIN (Cdate)) AS minCdate
, TRUNC (MAX (Cdate)) AS maxCdate
FROM attendance
) – End in-line view to compute minCdate, maxCdate
WHERE n <= maxCdate – minCdate
) d – End in-line view d of interesting Cdates
LEFT OUTER JOIN
( – Begin in-line view s of staff
SELECT staffNo
, EmployeeName
, title
, TRUNC (MIN (Cdate)) AS minCdate
, TRUNC (MAX (Cdate)) AS maxCdate
FROM attendance
GROUP BY staffNo
, EmployeeName
, title
) s – End in-line view s of staff
ON d.Cdate BETWEEN s.minCdate AND s.maxCdate
LEFT OUTER JOIN
( – Begin in-line view t with transaction data
SELECT staffNo
, TRUNC (CDate) AS Cdate
, MIN ( CASE
WHEN transactionType = ‘IN ENTRY’
THEN Cdate
END
) AS inCdate
, MAX ( CASE
WHEN transactionType = ‘OUT ENTRY’
THEN Cdate
END
) AS outCdate
FROM attendance
GROUP BY staffNo
, TRUNC (cDate)
) t – End in-line view t with transaction data
ON d.cDate = t.cDate
WHERE s.staffNo IS NOT NULL
ORDER BY s.staffNo
, d.Cdate
;
</pre>

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following