如何操作透视数据并插入缺少的日期。 [英] How do I manipulate the pivoted data and insert the missing dates.

问题描述

我们知道如果该日期有交易，记录将存储到数据库中。但是如果某个日期我们没有交易，那么交易日期将不会记录在数据库中。现在，我将做一个报告，我需要显示日期，包括没有交易。包括周六和周日。我在我的sql查询中使用了pivot方法。



我尝试了什么：

As we know that the record will stored to the database if there is a transaction on that date. But if there is certain date that we had no transactions, No date of transactions will be keep on record to the database. Now, I will make a report, I need to display the dates including no transactions. Including Saturday and Sunday. I used pivot method to my sql query.

What I have tried:

SELECT *
FROM (
    SELECT 
       A.employee_id,B.firstname, B.branch_code , A.trans_date as [Date],A.bioinoutmode as [Type], 
        A.trans_time as Time 
    FROM biometricdata A
LEFT JOIN Employees B ON A.employee_id = B.employee_id
WHERE A.employee_id = B.employee_id
) as s
PIVOT
(
    MAX(Time)
    FOR [Type] IN ([0],[1],[2],[3],[4],[5])
)AS pvt Order by DATE

推荐答案

我通常通过生成一个列表来执行此操作我需要的日期（参见在SQL中生成序列 [ ^ ]）然后使用该表在LEFT JOIN到我的实际数据表。

例如

I usually do this by generating a list of the dates that I need (see Generating a Sequence in SQL[^] ) and then using that table in a LEFT JOIN to my actual data table.
E.g.

SELECT ....-- column list
FROM MyListOfDates D
LEFT OUTER JOIN biometricdata A ON D.DesiredDate = A.[DATE] -- or whatever your column is
LEFT OUTER JOIN Employees B ON A.employee_id = B.employee_id

只需记得处理从A& A返回的结果为NULL B表示空日期

Just remember to handle the resulting NULLs returned from A & B for the empty dates

这篇关于如何操作透视数据并插入缺少的日期。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！