如何快速显示相应门店相应日期为空的情况

szlixiaolong 2015-03-30 04:36:05
表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



...全文
219 25 打赏 收藏 转发到动态 举报
写回复
用AI写文章
25 条回复
切换为时间正序
请发表友善的回复…
发表回复
szlixiaolong 2015-03-31
  • 打赏
  • 举报
 回复
引用 23 楼 ky_min 的回复:
[quote=引用 22 楼 szlixiaolong 的回复:]可否再加一个branch_name字段？
DECLARE @StartDate DATETIME,@EndDate DATETIME
SET @StartDate='20150201'
SET @EndDate='20150304'
;WITH CTE AS(
    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','*')
)
SELECT T1.branch_no,T3.branch_name,DATEADD(DAY,T2.number,@StartDate)oper_date
FROM(SELECT branch_no FROM CTE GROUP BY branch_no)T1
	JOIN master..spt_values T2 ON T2.type='P'AND T2.number<DATEDIFF(DAY,@StartDate,@EndDate)+1
	JOIN bi_t_branch_info T3 ON T1.branch_no=T3.branch_no
	LEFT JOIN CTE T4 ON T1.branch_no=T4.branch_no AND DATEADD(DAY,T2.number,@StartDate)=T4.oper_date
WHERE T4.branch_no IS NULL
[/quote] 你好，我自己修改好了，谢谢您。
还在加载中灬 2015-03-31
  • 打赏
  • 举报
 回复
引用 22 楼 szlixiaolong 的回复:
可否再加一个branch_name字段？
DECLARE @StartDate DATETIME,@EndDate DATETIME
SET @StartDate='20150201'
SET @EndDate='20150304'
;WITH CTE AS(
    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','*')
)
SELECT T1.branch_no,T3.branch_name,DATEADD(DAY,T2.number,@StartDate)oper_date
FROM(SELECT branch_no FROM CTE GROUP BY branch_no)T1
	JOIN master..spt_values T2 ON T2.type='P'AND T2.number<DATEDIFF(DAY,@StartDate,@EndDate)+1
	JOIN bi_t_branch_info T3 ON T1.branch_no=T3.branch_no
	LEFT JOIN CTE T4 ON T1.branch_no=T4.branch_no AND DATEADD(DAY,T2.number,@StartDate)=T4.oper_date
WHERE T4.branch_no IS NULL
szlixiaolong 2015-03-31
  • 打赏
  • 举报
 回复
引用 18 楼 ky_min 的回复:
[quote=引用 17 楼 szlixiaolong 的回复:]你好，执行以上语句后，会把所有门店未上传数据的日期情况全部显示出来了，而不是显示编号为94的情况。
如果是这样，和门店表没有啥关系啊，不需要用到它~~ 
DECLARE @StartDate DATETIME,@EndDate DATETIME
SET @StartDate='20150201'
SET @EndDate='20150304'
;WITH CTE AS(
    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','*')
)
SELECT T1.branch_no,DATEADD(DAY,T2.number,@StartDate)oper_date
FROM(SELECT branch_no FROM CTE GROUP BY branch_no)T1
	JOIN master..spt_values T2 ON T2.type='P'AND T2.number<DATEDIFF(DAY,@StartDate,@EndDate)+1
	LEFT JOIN CTE T4 ON T1.branch_no=T4.branch_no AND DATEADD(DAY,T2.number,@StartDate)=T4.oper_date
WHERE T4.branch_no IS NULL
你试下[/quote] 可否再加一个branch_name字段？
szlixiaolong 2015-03-31
  • 打赏
  • 举报
 回复
引用 19 楼 Tiger_Zhao 的回复:
#14: 不可能，有测试为证。 
WITH bi_t_branch_info(branch_no) AS (
    SELECT '94'
),
pos_t_daysum_item(branch_no,oper_date) AS (
--  SELECT '94','20150301' UNION ALL
    SELECT '94','20150302' UNION ALL
    SELECT '94','20150303' UNION ALL
    SELECT '94','20150304' UNION ALL
    SELECT '94','20150305' UNION ALL
    SELECT '94','20150306' UNION ALL
    SELECT '94','20150307' UNION ALL
    SELECT '94','20150308' UNION ALL
    SELECT '94','20150309' UNION ALL
    SELECT '94','20150310' UNION ALL
    SELECT '94','20150311' UNION ALL
    SELECT '94','20150312' UNION ALL
    SELECT '94','20150313' UNION ALL
    SELECT '94','20150314' UNION ALL
    SELECT '94','20150315' UNION ALL
    SELECT '94','20150316' UNION ALL
    SELECT '94','20150317' UNION ALL
    SELECT '94','20150318' UNION ALL
    SELECT '94','20150319' UNION ALL
    SELECT '94','20150320' UNION ALL
    SELECT '94','20150321' UNION ALL
    SELECT '94','20150322' UNION ALL
    SELECT '94','20150323' UNION ALL
    SELECT '94','20150324' UNION ALL
    SELECT '94','20150325' UNION ALL
    SELECT '94','20150326' UNION ALL
--  SELECT '94','20150327' UNION ALL
    SELECT '94','20150328' UNION ALL
    SELECT '94','20150329' UNION ALL
    SELECT '94','20150330' UNION ALL
    SELECT '94','20150331'
),
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
branch_no oper_date
--------- -----------------------
94        2015-03-01 00:00:00.000
94        2015-03-27 00:00:00.000
谢谢您，可以了。
szlixiaolong 2015-03-31
  • 打赏
  • 举报
 回复
引用 18 楼 ky_min 的回复:
[quote=引用 17 楼 szlixiaolong 的回复:]你好，执行以上语句后，会把所有门店未上传数据的日期情况全部显示出来了，而不是显示编号为94的情况。
如果是这样，和门店表没有啥关系啊，不需要用到它~~ 
DECLARE @StartDate DATETIME,@EndDate DATETIME
SET @StartDate='20150201'
SET @EndDate='20150304'
;WITH CTE AS(
    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','*')
)
SELECT T1.branch_no,DATEADD(DAY,T2.number,@StartDate)oper_date
FROM(SELECT branch_no FROM CTE GROUP BY branch_no)T1
	JOIN master..spt_values T2 ON T2.type='P'AND T2.number<DATEDIFF(DAY,@StartDate,@EndDate)+1
	LEFT JOIN CTE T4 ON T1.branch_no=T4.branch_no AND DATEADD(DAY,T2.number,@StartDate)=T4.oper_date
WHERE T4.branch_no IS NULL
你试下[/quote] 执行以上语句就可以了。
Tiger_Zhao 2015-03-31
  • 打赏
  • 举报
 回复
#14: 不可能，有测试为证。
WITH bi_t_branch_info(branch_no) AS (

    SELECT '94'

),

pos_t_daysum_item(branch_no,oper_date) AS (

--  SELECT '94','20150301' UNION ALL

    SELECT '94','20150302' UNION ALL

    SELECT '94','20150303' UNION ALL

    SELECT '94','20150304' UNION ALL

    SELECT '94','20150305' UNION ALL

    SELECT '94','20150306' UNION ALL

    SELECT '94','20150307' UNION ALL

    SELECT '94','20150308' UNION ALL

    SELECT '94','20150309' UNION ALL

    SELECT '94','20150310' UNION ALL

    SELECT '94','20150311' UNION ALL

    SELECT '94','20150312' UNION ALL

    SELECT '94','20150313' UNION ALL

    SELECT '94','20150314' UNION ALL

    SELECT '94','20150315' UNION ALL

    SELECT '94','20150316' UNION ALL

    SELECT '94','20150317' UNION ALL

    SELECT '94','20150318' UNION ALL

    SELECT '94','20150319' UNION ALL

    SELECT '94','20150320' UNION ALL

    SELECT '94','20150321' UNION ALL

    SELECT '94','20150322' UNION ALL

    SELECT '94','20150323' UNION ALL

    SELECT '94','20150324' UNION ALL

    SELECT '94','20150325' UNION ALL

    SELECT '94','20150326' UNION ALL

--  SELECT '94','20150327' UNION ALL

    SELECT '94','20150328' UNION ALL

    SELECT '94','20150329' UNION ALL

    SELECT '94','20150330' UNION ALL

    SELECT '94','20150331'

),

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

branch_no oper_date

--------- -----------------------

94        2015-03-01 00:00:00.000

94        2015-03-27 00:00:00.000
还在加载中灬 2015-03-31
  • 打赏
  • 举报
 回复
引用 17 楼 szlixiaolong 的回复:
你好，执行以上语句后，会把所有门店未上传数据的日期情况全部显示出来了，而不是显示编号为94的情况。
如果是这样，和门店表没有啥关系啊，不需要用到它~~ 
DECLARE @StartDate DATETIME,@EndDate DATETIME
SET @StartDate='20150201'
SET @EndDate='20150304'
;WITH CTE AS(
    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','*')
)
SELECT T1.branch_no,DATEADD(DAY,T2.number,@StartDate)oper_date
FROM(SELECT branch_no FROM CTE GROUP BY branch_no)T1
	JOIN master..spt_values T2 ON T2.type='P'AND T2.number<DATEDIFF(DAY,@StartDate,@EndDate)+1
	LEFT JOIN CTE T4 ON T1.branch_no=T4.branch_no AND DATEADD(DAY,T2.number,@StartDate)=T4.oper_date
WHERE T4.branch_no IS NULL
你试下
szlixiaolong 2015-03-31
  • 打赏
  • 举报
 回复
引用 16 楼 ky_min 的回复:
[quote=引用 15 楼 szlixiaolong 的回复:] [quote=引用 13 楼 ky_min 的回复:] 
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
您好，执行你的语句后，问题解决好了，非常感谢您的回答。[/quote]我调整了下，可以适应随意日期范围了 之前只适应单个月份的范围 
DECLARE @StartDate DATETIME,@EndDate DATETIME
SET @StartDate='20150201'
SET @EndDate='20150304'
--开始日期和结束日期可以随意替换
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<DATEDIFF(DAY,@StartDate,@EndDate)+1
	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 DATEADD(DAY,T2.number,@StartDate)=T4.oper_date
WHERE T4.branch_no IS NULL
[/quote] 你好，执行以上语句后，会把所有门店未上传数据的日期情况全部显示出来了，而不是显示编号为94的情况。
还在加载中灬 2015-03-31
  • 打赏
  • 举报
 回复
引用 15 楼 szlixiaolong 的回复:
[quote=引用 13 楼 ky_min 的回复:] 
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
您好，执行你的语句后，问题解决好了，非常感谢您的回答。[/quote]我调整了下，可以适应随意日期范围了 之前只适应单个月份的范围 
DECLARE @StartDate DATETIME,@EndDate DATETIME
SET @StartDate='20150201'
SET @EndDate='20150304'
--开始日期和结束日期可以随意替换
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<DATEDIFF(DAY,@StartDate,@EndDate)+1
	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 DATEADD(DAY,T2.number,@StartDate)=T4.oper_date
WHERE T4.branch_no IS NULL
szlixiaolong 2015-03-31
  • 打赏
  • 举报
 回复
引用 13 楼 ky_min 的回复:
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
您好，执行你的语句后，问题解决好了，非常感谢您的回答。
szlixiaolong 2015-03-31
  • 打赏
  • 举报
 回复
引用 10 楼 Tiger_Zhao 的回复:
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
你好，感谢您的回答，执行以上语句后，把所有门店和日期都显示出来了，无法达到我想要的结果，谢谢。
还在加载中灬 2015-03-31
  • 打赏
  • 举报
 回复 
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
szlixiaolong 2015-03-31
  • 打赏
  • 举报
 回复
引用 9 楼 ky_min 的回复:
再改下去可能会很大一坨SQL，要不你把表结构同需求告诉我 以下我是猜的，你看下
DECLARE @StartDate DATETIME,@EndDate DATETIME
SET @StartDate='20150301'
SET @EndDate='20150331'
;WITH T1 AS(
	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','*')
)
SELECT T1.branch_no,DATEADD(DAY,T2.number,@StartDate)oper_date
FROM(SELECT branch_no FROM T1 GROUP BY branch_no)T1
	JOIN master..spt_values T2 ON T2.type='P'AND T2.number<DATEPART(DAY,@EndDate)
	LEFT JOIN(
		SELECT T1.branch_no,T1.oper_date FROM T1
			JOIN bi_t_branch_info T3 ON T1.branch_no=T3.branch_no
	)T4 ON T1.branch_no=T4.branch_no AND T2.number=DATEPART(DAY,T4.oper_date)-1
WHERE T4.branch_no IS NULL
你好，表1：bi_t_branch_info 是门店名称表，表1字段：branch_no(门店编号)，branch_name(门店名称),表2：pos_t_daysum_item是门店日结表,表2字段：branch_no,oper_date(日期)，我的需求：通过SQL语句可以查出哪些门店没有上传数据，导致日结数据中没有相应门店相应哪天数据？理想实现如下： branch_no 未上传数据的日期如下 94 2015-03-27 01 2015-03-28 02 2015-03-28 03 2015-03-28 04 2015-03-28 05 2015-03-28
szlixiaolong 2015-03-31
  • 打赏
  • 举报
 回复
引用 10 楼 Tiger_Zhao 的回复:
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
你好，数据库是SQL 2008的，以上语句可以执行吗？
Tiger_Zhao 2015-03-30
  • 打赏
  • 举报
 回复 
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
还在加载中灬 2015-03-30
  • 打赏
  • 举报
 回复
再改下去可能会很大一坨SQL，要不你把表结构同需求告诉我 以下我是猜的，你看下
DECLARE @StartDate DATETIME,@EndDate DATETIME
SET @StartDate='20150301'
SET @EndDate='20150331'
;WITH T1 AS(
	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','*')
)
SELECT T1.branch_no,DATEADD(DAY,T2.number,@StartDate)oper_date
FROM(SELECT branch_no FROM T1 GROUP BY branch_no)T1
	JOIN master..spt_values T2 ON T2.type='P'AND T2.number<DATEPART(DAY,@EndDate)
	LEFT JOIN(
		SELECT T1.branch_no,T1.oper_date FROM T1
			JOIN bi_t_branch_info T3 ON T1.branch_no=T3.branch_no
	)T4 ON T1.branch_no=T4.branch_no AND T2.number=DATEPART(DAY,T4.oper_date)-1
WHERE T4.branch_no IS NULL
szlixiaolong 2015-03-30
  • 打赏
  • 举报
 回复
引用 5 楼 ky_min 的回复:
你是说不要30号和31吗? 如果1号没有数据，要显示1号吗
如果1号没有数据，要显示1号没有上传数据。 如果27号没有数据，就显示27号没有上传数据。 我的理想实现如下： branch_no 未上传数据的日期如下 94 2015-03-01 94 2015-03-27 等待你的回答。
szlixiaolong 2015-03-30
  • 打赏
  • 举报
 回复
引用 5 楼 ky_min 的回复:
你是说不要30号和31吗? 如果1号没有数据，要显示1号吗
如果27号没有数据，就显示27号没有上传数据。 我的理想实现如下： branch_no 未上传数据的日期如下 94 2015-03-27
szlixiaolong 2015-03-30
  • 打赏
  • 举报
 回复
引用 5 楼 ky_min 的回复:
你是说不要30号和31吗? 如果1号没有数据，要显示1号吗
如果27号没有数据，就显示27号没有数据。 我的理想实现如下： branch_no 未上传数据的日期 94 2015-03-27
加载更多回复（5）
O2O优惠券使用预测数据集.rar
本数据集为研究人员提供了O2O场景相关的丰富数据，通过分析建模精准预测用户是否会在规定时间内使用相应优惠券。 数据说明 注意： 为了保护用户和商家的隐私，所有数据均作匿名处理，同时采用了有偏采样和必要过滤...
2012版软件工程实验指导书.doc
1．项目开发过程建议采用快速原型与增量开发相集合的模式，在基本明确需求的情况下建立系统整体原型供需求的讨论和确定，在需求和系统架构确定后，选取一个具有代表性或系统核心部分的子系统进行详细的设计开发。...
里诺销售管理软件 v3.29.zip
本软件适合各类企事业单位和门店单位销售管理的需要，为企业达到“留住客户、提升销售”的目的。 里诺销售管理软件功能如下 1、完善的销售，采购，收付款模块，让您能方便地制做单据； 2、库存查询功能，让您以...
飞恒进销存管理系统v7.21(源代码)
3.为酒店进销存增加一个日结，日结后根据酒店销售情况来减库存（加过程pcdDayAuditHotel, 注意：进销存里的货品编码要跟酒店里的酒水编码要一样） 2006-03-07 1、 出仓的时候增加客户欠款提示。 2、 O_Outstore表中...
会员系统设计方案.doc
λ 应用扩展性和系统开放性 在整个系统平台上提供相应的内容服务综合应用，针对公司实际情况和需求，在 平台上定制相关的应用，能够满足当前的需求，同时，对于未来功能的发展，能够预 留一定的应用扩展空间。...
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章