CODEWITHSUNDEEP
sqlt-sql
Slint
Slint

Reputation: 355

Percentage in one query

I've tried to combine these two query to produce a percentage value.

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, regdate)) AS rdate, 
       COUNT(*) as sum 
FROM TBL where regdate > '2013-02-21' 
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, regdate)) 
ORDER BY rdate DESC 

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, slutdatum)) AS sdate, 
       COUNT(*) as sum 
FROM TBL 
WHERE slutdatum > '2013-02-21' 
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, slutdatum))       
ORDER BY sdate DESC

But i really don't get it. I've tried like shown below but that only produce a error, the two query works by It's own; (I could run the queries separated and do the math after getting the values but that only seams to overdo it).

SELECT (SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, regdate)) AS rdate, count(*) as sum 
        FROM TBL where regdate > '2013-02-21' 
        GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, regdate)) 
        ORDER BY rdate DESC) / (select DATEADD(dd, 0, DATEDIFF(dd, 0, slutdatum)) AS sdate,    
COUNT(*) as sum from TBL where slutdatum > '2013-02-21' 
GROUP BY DATEADD(dd, 0,    
DATEDIFF(dd, 0, slutdatum)) ORDER BY sdate DESC) * 100 from tbl

How do I make one query that produces two columns, date and percentage for the last 7 days?

Upvotes: 0

Views: 184

Answers (2)

PM 77-1
PM 77-1

Reputation: 13334

Since you will be comparing events that happen on the same day, we can JOIN on the date but we will need FULL OUTER JOIN to include days when there were only breaks or only fixes.

Here's the updated code:

SELECT COALESCE(reg.rdate,cl.sdate) as [Date], ISNULL(reg.sum, 0) as [Registered], ISNULL(cl.sum, 0) as [Closed], 
       CASE WHEN cl.sum IS NULL THEN 0
            WHEN ISNULL(cl.sum,0) > ISNULL(reg.sum,0) THEN 100 
            ELSE CONVERT(float,cl.sum)/reg.sum*100 END as [Percentage]  
FROM 

(SELECT regdate AS rdate, COUNT(regdate) as sum 
FROM TBL where regdate > CONVERT(datetime,'20130221',112) 
GROUP BY regdate ) reg 

FULL OUTER JOIN 

(SELECT slutdatum AS sdate, COUNT(slutdatum) as sum 
FROM TBL WHERE slutdatum > CONVERT(datetime,'20130221',112)
GROUP BY slutdatum) cl

ON (reg.rdate=cl.sdate)
ORDER BY COALESCE(reg.rdate,cl.sdate);

I created SQL Fiddle with the test data so you can experiment with the code: http://sqlfiddle.com/#!3/58a33/1.

Upvotes: 1

frankish
frankish

Reputation: 6826

You can use (SELECT ...) like a table. So, try SELECT ... FROM (SELECT ...) as A, (SELECT ...) as B

Now you can use values from A and B in the same formula.

Upvotes: 0

Related Questions