1 
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
Improve this question
6
  • 1
    what does your data look like and what would be the expected outcome ? Commented Oct 12, 2012 at 7:06
  • expected columns are second and third part query results... Commented Oct 12, 2012 at 7:09
  • @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 . Commented Oct 12, 2012 at 7: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 stackoverflow.com/questions/11664142/…. It’s pretty easy in PL/Sql. I guess you are searching for the same. Commented Oct 12, 2012 at 7:34
  • It's been added to SQL Server 2012, but not 2008. SUM(Letter) OVER (ORDER BY ddate RANGE UNBOUNDED PRECEDING) LetterTotal - See sqlfiddle.com/#!6/1e69d/2 Commented Oct 12, 2012 at 7:48

1 Answer 1

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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 ...
The statement terminated. The maximum recursion 100 has been exhausted before statement completion. :(
@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
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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.