的SQL Server 2014中选择总每天

问题描述：

你好我工作在SSRS 报表中的数据集，我有一个查询，其给出了积压的总要求：的SQL Server 2014中选择总每天

SELECT 
COUNT(*) as NB 
FROM p_rqt WITH (NOLOCK) 
INNER JOIN p_cpy WITH (NOLOCK) ON p_cpy.CpyInCde = p_rqt.OrigCpyInCde 
WHERE 
    CpyTypInCde IN (27, 31) 
    AND p_rqt.RqtNatInCde IN (74, 75, 76) 
    AND HeadRqtInCde = 0 
    AND p_rqt.OrigCpyInCde LIKE CASE WHEN @Client = 0 THEN '%' ELSE @Client END 
    AND ((RcvDte < DATEADD(day, 1, @DateDeb) AND RqtEndDte IS NULL) OR 
(RcvDte < DATEADD(day, 1, @DateDeb) AND RqtEndDte > DATEADD(day, 1, @DateDeb)))

，我想找回每天剩余的总金额。我试过很多事情是这样的：

SELECT CONVERT(date,rcvdte,103), count(*) as nb 
FROM p_rqt p WITH (NOLOCK) 
INNER JOIN p_cpy WITH (NOLOCK) ON p_cpy.CpyInCde = p.OrigCpyInCde 
WHERE 
    CpyTypInCde IN (27, 31) 
    AND p.RqtNatInCde IN (74, 75, 76) 
    AND HeadRqtInCde = 0 
    AND ((RcvDte < DATEADD(day, 1, '20170901') AND RqtEndDte IS NULL) OR (RcvDte < DATEADD(day, 1, '20170901') AND RqtEndDte > DATEADD(day, 1, '20170901'))) 
    group by CONVERT(date,rcvdte,103) 
    order by CONVERT(date,rcvdte,103)

我试过内部联接子查询，点心和其他的东西但我能设法做的是有每天添加的记录数量，我想是这样这样的：

date:    NB: 
01/01/2017  1950 
02/01/2017  1954    (+4 items) 
03/01/2017  1945    (-9 items)

谢谢

样本数据，一起来看看这个：HTTP：// www.sqlservercentral.com/blogs/spaghettidba/2015/04/24/how-to-post-at-sql-question-on-a-public-forum/ – Tanner

我建议在SSRS中进行计算并保留您的查询尽可能小（和快）。在SSRS内部，你可以使用RunningValue进行累计总计https://stackoverflow.com/questions/16462826/how-to-i-get-cumulative-monthly-subtotals-in-ssrs – niktrs

嗯，我想，但它是设计像这是一张带有多个UNION的表格，他们不希望我以另一种方式做 – wololo

答

所以，我找到了一个解决方案，但它实在是太慢了，我仍然张贴答案反正

  DECLARE @Tb TABLE ( Colonne1 Datetime, Colonne2 INT) 
DECLARE @Debut Datetime = '01/09/2017' 
WHILE @Debut < '13/09/2017' 
BEGIN 
    DECLARE @Compteur int = (
     SELECT 
       COUNT(1) NB 
       FROM p_rqt WITH (NOLOCK) 
       INNER JOIN p_cpy WITH (NOLOCK) ON p_cpy.CpyInCde = p_rqt.OrigCpyInCde 
       WHERE 
         CpyTypInCde IN (27, 31) 
         AND p_rqt.RqtNatInCde IN (74, 75, 76) 
         AND HeadRqtInCde = 0 
         AND p_rqt.OrigCpyInCde LIKE '%' 
         AND (
           (RcvDte < @Debut AND RqtEndDte IS NULL) 
           OR 
           (RcvDte < @Debut AND RqtEndDte > @Debut) 
         ) 
    ) 
    INSERT INTO @Tb (Colonne1, Colonne2) VALUES (@Debut, @Compteur) 
    SET @Debut = DATEADD(day, 1, @Debut) 
    IF @Debut > '13/09/2017' 
    BREAK 
    ELSE 
    CONTINUE 
    END 
    SELECT * FROM @Tb

，我们可以运行你对将是有益的查询

答

使用LAG：

WITH cte AS (
    SELECT 
     CONVERT(date, rcvdte, 103) AS date, 
     COUNT(*) AS nb 
    FROM p_rqt p WITH (NOLOCK) 
    INNER JOIN p_cpy WITH (NOLOCK) 
     ON p_cpy.CpyInCde = p.OrigCpyInCde 
    WHERE 
     CpyTypInCde IN (27, 31) AND 
     p.RqtNatInCde IN (74, 75, 76) AND 
     HeadRqtInCde = 0 AND 
     ((RcvDte < DATEADD(day, 1, '20170901') AND RqtEndDte IS NULL) OR (RcvDte < DATEADD(day, 1, '20170901') AND RqtEndDte > DATEADD(day, 1, '20170901'))) 
    GROUP BY CONVERT(date, rcvdte, 103) 
    ORDER BY CONVERT(date, rcvdte, 103) 
) 

SELECT 
    t1.date, 
    (SELECT SUM(t2.nb) FROM cte t2 WHERE t2.date <= t1.date) AS nb, 
    CASE WHEN t1.nb - LAG(t1.nb, 1, t1.nb) OVER (ORDER BY t1.date) > 0 
     THEN '(+' + (t1.nb - LAG(t1.nb, 1, t1.nb) OVER (ORDER BY t1.date)) + ' items)' 
     ELSE '(' + (t1.nb - LAG(t1.nb, 1, t1.nb) OVER (ORDER BY t1.date)) + ' items)' 
    END AS difference 
FROM cte t1 
ORDER BY t1.date;

它除了差异部分外没有任何作用，我还有每天添加的记录数量= / – wololo

的SQL Server 2014中选择总每天

相关推荐