今天，总以前在一个SQL查询相结合来自同一列的结果今天(combine today and tota

declare @temp table (ddate datetime)

insert @temp
select DATEDIFF(d,0,CONVERT(smalldatetime,'09/30/2012')  -Number)
from master..spt_values
where type='p' and number < DatePart(d,'09/30/2012')
order by 1

DECLARE @DeptCode int =1

-以显示特定日期数据的

select   ComplaintMedia_Abbri,
ddate,COUNT(ComplaintMedia)  as c
from    Complaint 
INNER JOIN @temp 
ON convert(datetime,convert(varchar(10),ComplaintDate,101),101)=convert(datetime,convert(varchar(10),ddate,101),101)        
WHERE isnull(Receivedby_Dept,Relatesto_Dept)=1     
group by ComplaintMedia_Abbri,ddate
order by ddate,ComplaintMedia_Abbri

-显示直到运行总时间特定日期数据

select   ComplaintMedia_Abbri,ddate,
    COUNT(ComplaintMedia_Abbri)  as c
    from    Complaint 

    INNER JOIN @temp 
    ON convert(datetime,convert(varchar(10),ComplaintDate,101),101)<=convert(datetime,convert(varchar(10),ddate,101),101)       

    WHERE isnull(Receivedby_Dept,Relatesto_Dept)=1     
    group by ComplaintMedia_Abbri,ddate

我想显示运行总计直到日期和今天（当天）在一个SQL查询结果记录.....

作为连接有两个差的条件。

预期的结果应该是这样的

编辑：我可以用这些查询和加入他们实现这一目标的结果，但我希望做这个任务在一个单一的查询，而不是两个查询

我目前的方式，可以在这里查看。对不起，这么长的问题，但我认为它必须要了解的问题其实..

declare @temp table (ddate datetime)

insert @temp
select DATEDIFF(d,0,CONVERT(smalldatetime,'09/30/2012')  -Number)
from master..spt_values
where type='p' and number < DatePart(d,'09/30/2012')
order by 1

--select * from @temp

SELECT * FROM 

        (select ddate,ISNULL(L,0) AS Letter,
          ISNULL(P,0) AS Phone,
          ISNULL(E,0) AS Email,
          ISNULL(W,0) AS WEB 
          FROM 
          (
            select   ComplaintMedia_Abbri,
          ddate,COUNT(ComplaintMedia)  as c
            from    Complaint 
            INNER JOIN @temp 
            ON convert(datetime,convert(varchar(10),ComplaintDate,101),101)=convert(datetime,convert(varchar(10),ddate,101),101)        
            WHERE isnull(Receivedby_Dept,Relatesto_Dept)=1     
            group by ComplaintMedia_Abbri,ddate
    ) p 

    pivot (SUM(c) FOR ComplaintMedia_Abbri IN (E,W,L,P)) AS pvt 

    ) AS [A]

    INNER JOIN 

    (
    select ddate,ISNULL(L,0) AS LetterTot,
      ISNULL(P,0) AS PhoneTot,
      ISNULL(E,0) AS EmailTot,
      ISNULL(W,0) AS WEBTot
      FROM 
      (

    select   ComplaintMedia_Abbri,ddate,
    COUNT(ComplaintMedia_Abbri)  as c
    from    Complaint 

    INNER JOIN @temp 
    ON convert(datetime,convert(varchar(10),ComplaintDate,101),101)<=convert(datetime,convert(varchar(10),ddate,101),101)       

    WHERE isnull(Receivedby_Dept,Relatesto_Dept)=1     
    group by ComplaintMedia_Abbri,ddate
    ) p 

   pivot (SUM(c) FOR ComplaintMedia_Abbri IN (E,W,L,P)) AS pvt



    ) AS [B]

   ON A.ddate=B.ddate

   order by A.ddate

我修改的，会给你所需的输出的注释中给出的SQL小提琴，只要你已经拥有了日总量：

http://www.sqlfiddle.com/#!6/09168/2

DECLARE @startDate datetime
DECLARE @endDate datetime

SELECT @startDate = '2012-10-08'
SELECT @endDate = '2012-10-12'

SELECT
    DT1.ddate,
    DT1.phone,
    DT1.letter,
    DT1.email,
    DT1.web,
    SUM(DT2.phone) phoneTotal,
    SUM(DT2.letter) letterTotal,
    SUM(DT2.email) emailTotal,
    SUM(DT2.web) webTotal 
  FROM
    DailyTotals DT1
    LEFT JOIN DailyTotals DT2 ON DT1.ddate >= DT2.ddate AND DT2.ddate >= @startDate
  WHERE
    DT1.ddate <= @endDate
  GROUP BY
  DT1.ddate,
  DT1.phone,
  DT1.letter,
  DT1.email,
  DT1.web

如果你想它一个说法，你就需要更换你的子查询，让您的日常总数的DailyTotals。不过我建议做一个观点叫DailyTotals和使用。

编辑：

您可以使用CTE来生成日期范围，而不是临时表。我已经修改了你的，你说的作品与CTE，而不是@temp加入完整的查询。我没有，虽然测试它的方式。如果这不起作用，请创建SLQ拨弄你的模式，我会再次尝试。

WITH Dates AS
(
    SELECT CONVERT(date, MIN(ComplaintTime)) AS ddate,
    MAX(ComplaintTime) as EndDate
    FROM
      Complaints
    UNION ALL
    SELECT DATEADD(DAY, 1, ddate), EndDate
    FROM Dates
    WHERE DATEADD(DAY, 1, ddate) <= EndDate
    )

SELECT * FROM 

        (select ddate,ISNULL(L,0) AS Letter,
          ISNULL(P,0) AS Phone,
          ISNULL(E,0) AS Email,
          ISNULL(W,0) AS WEB 
          FROM 
          (
            select   ComplaintMedia_Abbri,
          ddate,COUNT(ComplaintMedia)  as c
            from    Complaint 
            INNER JOIN Dates
            ON convert(date,ComplaintDate)=ddate OPTION (MAXRECURSION 500)
            WHERE isnull(Receivedby_Dept,Relatesto_Dept)=1     
            group by ComplaintMedia_Abbri,ddate
    ) p 

    pivot (SUM(c) FOR ComplaintMedia_Abbri IN (E,W,L,P)) AS pvt 

    ) AS [A]

    INNER JOIN 

    (
    select ddate,ISNULL(L,0) AS LetterTot,
      ISNULL(P,0) AS PhoneTot,
      ISNULL(E,0) AS EmailTot,
      ISNULL(W,0) AS WEBTot
      FROM 
      (

    select   ComplaintMedia_Abbri,ddate,
    COUNT(ComplaintMedia_Abbri)  as c
    from    Complaint 

    INNER JOIN Dates OPTION (MAXRECURSION 0)
    ON CONVERT(date,ComplaintDate) <= ddate OPTION (MAXRECURSION 0)

    WHERE isnull(Receivedby_Dept,Relatesto_Dept)=1     
    group by ComplaintMedia_Abbri,ddate
    ) p 

   pivot (SUM(c) FOR ComplaintMedia_Abbri IN (E,W,L,P)) AS pvt



    ) AS [B]

   ON A.ddate=B.ddate

   order by A.ddate