combine today and total previous from today of that same column results in one sql query

Question

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

-- to display of particular date data

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

-- to display till date particular date data running total

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

I want to show running total till date and today (that day ) records in one sql query results .....

as on join there is two difference conditions.

expected result should be look like this

expected result for above query

Edit : I am able to achive this result by using these queries and joining them but i want to do this task in one single query instead of two queries

my current way can be check here . sorry for such long question but i think its necessary to understand question actually ..

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

what does your data look like and what would be the expected outcome ? — t-clausen.dk, Oct 12 '12 at 07:06
@t-clausen.dk hey question updated with current query and current results but they are comming from two different query , i want to merge these two in one to improve some speed for this stored procedure . — rahularyansharma, Oct 12 '12 at 07:14
I think there is no default feature in MS Sql server to get the running (cumulative) total. I have posted the same question once http://stackoverflow.com/questions/11664142/cumulative-total-in-ms-sql-server. It’s pretty easy in PL/Sql. I guess you are searching for the same. — TechDo, Oct 12 '12 at 07:34
It's been added to SQL Server 2012, but not 2008. SUM(Letter) OVER (ORDER BY ddate RANGE UNBOUNDED PRECEDING) LetterTotal - See http://www.sqlfiddle.com/#!6/1e69d/2 — Tobsey, Oct 12 '12 at 07:48
its means its not possible to combine these two queries in one query to obtain the desired result as in image — rahularyansharma, Oct 12 '12 at 08:55

Tobsey · Accepted Answer · 2012-10-12T13:03:37.660

I've modified the SQL Fiddle given in the comment that will give you the desired output, provided you already have the daily totals:

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

If you want to make it one statement you would need to replace the DailyTotals with your subquery that gives you the daily totals. However I'd suggest making that a view called DailyTotals and using that.

EDIT:

You can use a CTE to generate your date range instead of the temp table. I've modified your full query that you say works to join with the CTE instead of @temp. I've no way of testing it though. If this doesn't work please create a SLQ Fiddle with your schema and I'll try it again.

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

hey you have done great job but problem with your solution is that its only give me running total , i am not looking for running total , I am looking for a day value and sum of that column previous to that day ... — rahularyansharma, Oct 12 '12 at 10:36
The statement terminated. The maximum recursion 100 has been exhausted before statement completion. :( — rahularyansharma, Oct 12 '12 at 12:13
@rahularyansharma Edited again. See OPTION (MAXRECURSION 0) after each join to dates. By default SQL Server sets the maximum recursion level of a CTE to 100 to prevent an infinite loop. If you set it to 0, then there is no restriction on the number of loops which is very dangerous until you are sure your CTE is correct. I've set one to 500 and one to infinite above. I suggest you set it so that it's equal to the number of days between the max and min dates in your complaints table. once you're happy it works correctly you could set them both to 0 — Tobsey, Oct 12 '12 at 13:06
In case you didn't realise each recursion of the CTE adds the next day to the results, until it gets to the max date. So there are more than 100 days between the oldest and newest record in your complaints table. — Tobsey, Oct 12 '12 at 13:07

combine today and total previous from today of that same column results in one sql query

1 Answers1

Linked