表1：bi_t_branch_info 是门店名称表，表1字段：branch_no，branch_name,表2：pos_t_daysum_item是门店日结表,表2字段：branch_no,oper_date
通用SQL语句如下：
SELECT left(a.branch_no,2) as branch_no,
oper_date = cast( a.oper_date as datetime)
FROM pos_t_daysum_item a ,bi_t_branch_info b
WHERE ( a.oper_date >= '20150301')
And ( a.oper_date <= '20150331')
And ( left(a.branch_no,2) in ('94','*')) and b.branch_no=left(a.branch_no,2)
GROUP by left(a.branch_no,2),b.branch_name,
a.oper_date
ORDER by 1,a.oper_date
图中，刚才缺少2015-03-27 00:00:00.000，通过SQL语句可以查出哪些门店没有上传数据，导致日结数据中没有相应门店相应哪天数据？理想实现如下：
branch_no 未上传数据的日期
94 2015-03-27
------解决思路----------------------
WITH t1 AS ( -- 应有记录
SELECT b.branch_no,
DATEADD(day,n.number,'2015-03-01') oper_date
FROM bi_t_branch_info b,
(SELECT number
FROM master..spt_values
WHERE type = 'p'
AND number < 31
) n
)
, t2 AS ( -- 已有记录
SELECT left(a.branch_no,2) branch_no,
cast(a.oper_date as datetime) oper_date
FROM pos_t_daysum_item a
WHERE ( a.oper_date >= '20150301')
And ( a.oper_date <= '20150331')
And ( left(a.branch_no,2) in ('94','*'))
GROUP by left(a.branch_no,2), cast(a.oper_date as datetime)
)
-- 缺少
SELECT t1.*
FROM t1
LEFT JOIN t2
ON t1.branch_no = t2.branch_no
AND t1.oper_date = t2.oper_date
WHERE t2.oper_date IS NULL
------解决思路----------------------
DECLARE @StartDate DATETIME,@EndDate DATETIME
SET @StartDate='20150301'
SET @EndDate='20150331'
--我把开始日期和结束日期提取出来了，你可以随意替换上面的日期范围
SELECT T1.branch_no,DATEADD(DAY,T2.number,@StartDate)oper_date
FROM bi_t_branch_info T1
JOIN master..spt_values T2 ON T2.type='P'AND T2.number<DATEPART(DAY,@EndDate)
LEFT JOIN(
SELECT LEFT(branch_no,2)branch_no
,oper_date
FROM pos_t_daysum_item
WHERE oper_date>=@StartDate AND oper_date<=@EndDate
AND LEFT(branch_no,2)IN('94','*')
)T4 ON T1.branch_no=T4.branch_no AND T2.number=DATEPART(DAY,T4.oper_date)-1
WHERE T4.branch_no IS NULL