带条件的Crystal Report时间差公式及跨库劳动力效能报表问询

Got it, let's break down how to build this report for your manufacturing business. We'll focus on linking your two MS SQL databases, calculating the key durations, and correlating them via employee IDs.

1. Set Up Cross-Database Access #

First, you need to make sure your query can reach both databases. The approach depends on whether they're on the same SQL Server instance or separate ones:

Same Server Instance #

If both Database A and B are on the same server, you can reference tables directly using the full three-part name:

SELECT *
FROM [DatabaseA].[dbo].[EmployeeWorkSessions] -- Replace with your actual schema/table name
JOIN [DatabaseB].[dbo].[EmployeeAttendance] -- Replace with your actual schema/table name
    ON [DatabaseA].[dbo].[EmployeeWorkSessions].EmployeeID = [DatabaseB].[dbo].[EmployeeAttendance].EmployeeID

Separate Server Instances #

If they're on different servers, you'll need to set up a linked server first. Run this on the server where you'll execute the report query (replace placeholders with your server details):

EXEC sp_addlinkedserver
    @server = 'LinkedServerNameForDBB', -- Pick a name for the linked server
    @srvproduct = '',
    @provider = 'SQLNCLI',
    @datasrc = 'ServerNameForDBB\InstanceName'; -- Your Database B server/instance

-- Optional: Set up login mapping if needed
EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'LinkedServerNameForDBB',
    @useself = 'FALSE',
    @rmtuser = 'DBB_Username',
    @rmtpassword = 'DBB_Password';

Once the linked server is set up, you can reference Database B tables like: [LinkedServerNameForDBB].[DatabaseB].[dbo].[EmployeeAttendance]

2. Core Query to Calculate & Compare Durations #

Now, let's write the query that calculates total work session duration (from Database A) and total on-site attendance duration (from Database B), then joins them by employee ID.

Assuming your tables have these key fields:

• Database A: EmployeeID, LoginTime (start of work session), LogoutTime (end of work session)
• Database B: EmployeeID, CheckInTime (arrival), CheckOutTime (departure)

Here's the query:

WITH WorkSessionDurations AS (
    SELECT
        EmployeeID,
        SUM(DATEDIFF(MINUTE, LoginTime, LogoutTime)) AS TotalWorkSessionMinutes,
        -- Convert to hours for readability
        CAST(SUM(DATEDIFF(MINUTE, LoginTime, LogoutTime)) AS FLOAT) / 60 AS TotalWorkSessionHours
    FROM [DatabaseA].[dbo].[EmployeeWorkSessions]
    WHERE LoginTime IS NOT NULL AND LogoutTime IS NOT NULL -- Filter out invalid records
    GROUP BY EmployeeID
),
AttendanceDurations AS (
    SELECT
        EmployeeID,
        SUM(DATEDIFF(MINUTE, CheckInTime, CheckOutTime)) AS TotalOnSiteMinutes,
        CAST(SUM(DATEDIFF(MINUTE, CheckInTime, CheckOutTime)) AS FLOAT) / 60 AS TotalOnSiteHours
    FROM [DatabaseB].[dbo].[EmployeeAttendance]
    WHERE CheckInTime IS NOT NULL AND CheckOutTime IS NOT NULL -- Filter out invalid records
    GROUP BY EmployeeID
)
SELECT
    COALESCE(ws.EmployeeID, att.EmployeeID) AS EmployeeID,
    ISNULL(ws.TotalWorkSessionHours, 0) AS TotalWorkSessionHours,
    ISNULL(att.TotalOnSiteHours, 0) AS TotalOnSiteHours,
    -- Calculate the difference to highlight gaps
    ISNULL(att.TotalOnSiteHours, 0) - ISNULL(ws.TotalWorkSessionHours, 0) AS DurationGapHours
FROM WorkSessionDurations ws
FULL OUTER JOIN AttendanceDurations att
    ON ws.EmployeeID = att.EmployeeID
ORDER BY EmployeeID;

• We use FULL OUTER JOIN to include employees who might have records in one database but not the other (e.g., an employee who checked in but didn't log into work systems).
• COALESCE and ISNULL handle null values to avoid missing data in the final report.
• The DurationGapHours field shows how much time an employee was on-site but not logged into work sessions (or vice versa), which is key for labor efficiency analysis.

3. Report Presentation Tips #

• Visualize the Gap: Use a bar chart to compare TotalWorkSessionHours and TotalOnSiteHours per employee, with a separate series for DurationGapHours to highlight inefficiencies.
• Filter by Time Period: Add parameters to let users filter by date range (e.g., weekly, monthly) since you'll likely run this report on a recurring basis.
• Add Employee Details: Join with an employee directory table (if available in either database) to include names, departments, etc., for better readability.

4. Key Considerations #

• Data Quality: Validate that time fields are not null and that logout/checkout times are after login/check-in times (you can add checks in the CTEs if needed).
• Performance: Add indexes on EmployeeID, LoginTime, LogoutTime (Database A) and EmployeeID, CheckInTime, CheckOutTime (Database B) to speed up the query, especially with large datasets.
• Permissions: Ensure the SQL account running the report has SELECT permissions on both databases (and the linked server if used).

内容的提问来源于stack exchange，提问作者user6731224