Database

Member 14182925 wrote:

what should it be normalised to.

Member 14182925 wrote:

The detailed process of implementing the database using SQL

Member 14182925 wrote:

including the normalisation of the table (should that be required),

Member 14182925 wrote:

and the use of SQL commands in order to create and populate the tables with data

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Member 14182925 wrote:

If you're willing to help

Quote:

If there is absolutely nothing in common between the databases then I would probably have two separate databases and manage them separately.

Quote:

We are currently using schemas in a corporate database to indicate which "area" (application if you like) "owns" the data in the table(s) and using the dbo schema for common data (i.e. no tables with the same name in different schema).

docs.Oracle.com[^] wrote:

A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include the following types of objects: (list of stuff)

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

WITH cte As
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY RequestDate DESC) As RN,
        EmpID,
        RequestNo As CurrentReqNo,
        RequestDate As CurrentReqDate,
        RequestType As CurrentReqType,
        RequestStatus As CurrentReqStatus,
        LEAD(RequestNo) OVER (PARTITION BY EmpID ORDER BY RequestDate DESC) As PreviousReqNo,
        LEAD(RequestDate) OVER (PARTITION BY EmpID ORDER BY RequestDate DESC) As PreviousReqDate,
        LEAD(RequestType) OVER (PARTITION BY EmpID ORDER BY RequestDate DESC) As PreviousReqType,
        LEAD(RequestStatus) OVER (PARTITION BY EmpID ORDER BY RequestDate DESC) As PreviousReqStatus
    FROM
        YourTable
)
SELECT
    EmpID,
    CurrentReqNo,
    CurrentReqDate,
    CurrentReqType,
    CurrentReqStatus,
    PreviousReqNo,
    PreviousReqDate,
    PreviousReqType,
    PreviousReqStatus
FROM
    cte
WHERE
    RN = 1
;

WITH cte As
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY RequestDate DESC) As RN,
        EmpID,
        RequestNo,
        RequestDate,
        RequestType,
        RequestStatus
    FROM
        YourTable
)
SELECT
    C.EmpID,
    C.RequestNo As CurrentReqNo,
    C.RequestDate As CurrentReqDate,
    C.RequestType As CurrentReqType,
    C.RequestStatus As CurrentReqStatus,
    P.RequestNo As PreviousReqNo,
    P.RequestDate As PreviousReqDate,
    P.RequestType As PreviousReqType,
    P.RequestStatus As PreviousReqStatus
FROM
    cte As C
    LEFT JOIN cte As P
    ON P.EmpID = C.EmpID
    And P.RN = C.RN + 1
WHERE
    C.RN = 1
;