Combine today and the common previous from today of the same column results in a single 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 

- display specific 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 

- display specific data up to date with the current amount

 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 the current total date and today (this day) in a single sql query request .....

since there are two difference conditions when connecting.

the expected result should look like this:

Edit: I can achieve this result using these queries and joining them, but I want to complete this task in one query instead of two queries

My current method can be checked here. sorry for such a long question, but I think it needs to be understood in reality.

 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 

Answer 1

I changed the SQL Fiddle indicated in the comment, which will give you the desired result if you already have 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 this one of the operators, you will need to replace DailyTotals with your subquery, which will give you daily totals. However, I would suggest doing this with the name DailyTotals and using it.

EDIT:

You can use CTE to create a date range instead of a temp table. I modified your full request, which, as you say, works to join the CTE instead of @temp. However, I have not tested it. If this does not work, create an SLQ script with your schema and I will try 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