Calculations with count result from two queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Calculations with count result from two queries

Author

Topic

drtduarte
Starting Member

12 Posts

Posted - 2009-07-06 : 09:43:32

Dear Friends,

As I'm very new to tsql/sql queries, I would really appreciate some help on this query.

I have this two queries for two separated reports, generated quotes and approved quotes.

1st Query - Generated Quotes Report
select ft.name, count(*) as Total,
count (case when month(ft.fdata)=1 then 1 end ) as January,
count (case when month(ft.fdata)=2 then 1 end ) as February
from ft where ft.ndoc=9 and year(ft.fdata)=2009 group by ft.name order by Total desc

2nd Query - Approved Quotes Report
select ft.name, count(*) as Total,
count (case when month(ft.fdata)=1 then 1 end ) as January,
count (case when month(ft.fdata)=2 then 1 end ) as February
from ft where ft.ndoc=9 and ft.u_approved='true' and year(ft.fdata)=2009 group by ft.name order by Total desc

Now I need to now the conversion rate...

I don't have any idea about were to start...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 09:47:27

I don't have any idea how to help you.

You said you wanted the conversion rate. But what is conversion rate ? What's the formula ?

KH
[spoiler]Time is always against us[/spoiler]

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-06 : 09:53:45

may be approved quotes/generated quotes

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-06 : 09:57:56

Hard to tell without sample data and expected output but ,Maybe you mean this?

SELECT a.name, 
       ((b.total * 1.0) / (a.total * 1.0)) * 100 AS [ConversionRate] 
FROM   (SELECT   ft.name    AS [name], 
                 Count(* )  AS total, 
                 Count(CASE 
                         WHEN Month(ft.fdata) = 1 
                         THEN 1 
                       END) AS january, 
                 Count(CASE 
                         WHEN Month(ft.fdata) = 2 
                         THEN 1 
                       END) AS february 
        FROM     ft 
        WHERE    ft.ndoc = 9 
                 AND Year(ft.fdata) = 2009 
        GROUP BY ft.name) a 
       INNER JOIN (SELECT   ft.name    AS [name], 
                            Count(* )  AS total, 
                            Count(CASE 
                                    WHEN Month(ft.fdata) = 1 
                                    THEN 1 
                                  END) AS january, 
                            Count(CASE 
                                    WHEN Month(ft.fdata) = 2 
                                    THEN 1 
                                  END) AS february 
                   FROM     ft 
                   WHERE    ft.ndoc = 9 
                            AND ft.u_approved = 'true' 
                            AND Year(ft.fdata) = 2009 
                   GROUP BY ft.name) b 
         ON a.[name] = b.[name]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 10:01:30

[code]
SELECT name,
Total_Generated,
January_Generated,
February_Generated,
Total_Approved,
January_Approved,
February_Approved,
Rate_Approved = Total_Approved * 100.0 / Total_Generated
FROM
(
SELECT ft.name,
COUNT(*) AS Total_Generated,
COUNT (CASE WHEN MONTH(ft.fdata) = 1 THEN 1 END) AS January_Generated,
COUNT (CASE WHEN MONTH(ft.fdata) = 2 THEN 1 END) AS February_Generated,
COUNT (CASE WHEN ft.u_approved = 'true' THEN 1 END) AS Total_Approved
COUNT (CASE WHEN ft.u_approved = 'true' AND MONTH(ft.fdata) = 1 THEN 1 END) AS January_Approved,
COUNT (CASE WHEN ft.u_approved = 'true' AND MONTH(ft.fdata) = 2 THEN 1 END) AS February_Approved,
FROM ft
WHERE ft.ndoc = 9
AND YEAR(ft.fdata) = 2009
GROUP BY ft.name
) f
ORDER BY Total_Generated DESC
[/code]

KH
[spoiler]Time is always against us[/spoiler]

drtduarte
Starting Member

12 Posts

Posted - 2009-07-07 : 07:11:28

How, you guys are great...

Both very nice ways to get what I wanted.

The second option was doing exactly what I wanted, so the query chosen will be:

SELECT name,
January_Generated,
January_Approved,
February_Generated,
February_Approved,
March_Generated,
March_Approved,
April_Generated,
April_Approved,
May_Generated,
May_Approved,
June_Generated,
June_Approved,
July_Generated,
July_Approved,
August_Generated,
August_Approved,
September_Generated,
September_Approved,
October_Generated,
October_Approved,
November_Generated,
November_Approved,
December_Generated,
December_Approved,
Total_Generated,
Total_Approved,
Rate_Approved = Total_Approved * 100.0 / Total_Generated
FROM
(
SELECT ft.name,
COUNT(*) AS Total_Generated,
COUNT (CASE WHEN MONTH(ft.fdata) = 1 THEN 1 END) AS January_Generated,
COUNT (CASE WHEN MONTH(ft.fdata) = 2 THEN 1 END) AS February_Generated,
COUNT (CASE WHEN MONTH(ft.fdata) = 3 THEN 1 END) AS March_Generated,
COUNT (CASE WHEN MONTH(ft.fdata) = 4 THEN 1 END) AS April_Generated,
COUNT (CASE WHEN MONTH(ft.fdata) = 5 THEN 1 END) AS May_Generated,
COUNT (CASE WHEN MONTH(ft.fdata) = 6 THEN 1 END) AS June_Generated,
COUNT (CASE WHEN MONTH(ft.fdata) = 7 THEN 1 END) AS July_Generated,
COUNT (CASE WHEN MONTH(ft.fdata) = 8 THEN 1 END) AS August_Generated,
COUNT (CASE WHEN MONTH(ft.fdata) = 9 THEN 1 END) AS September_Generated,
COUNT (CASE WHEN MONTH(ft.fdata) = 10 THEN 1 END) AS October_Generated,
COUNT (CASE WHEN MONTH(ft.fdata) = 11 THEN 1 END) AS November_Generated,
COUNT (CASE WHEN MONTH(ft.fdata) = 12 THEN 1 END) AS December_Generated,
COUNT (CASE WHEN ft.u_oaceite = 'true' THEN 1 END) AS Total_Approved,
COUNT (CASE WHEN ft.u_oaceite = 'true' AND MONTH(ft.fdata) = 1 THEN 1 END) AS January_Approved,
COUNT (CASE WHEN ft.u_oaceite = 'true' AND MONTH(ft.fdata) = 2 THEN 1 END) AS February_Approved,
COUNT (CASE WHEN ft.u_oaceite = 'true' AND MONTH(ft.fdata) = 3 THEN 1 END) AS March_Approved,
COUNT (CASE WHEN ft.u_oaceite = 'true' AND MONTH(ft.fdata) = 4 THEN 1 END) AS April_Approved,
COUNT (CASE WHEN ft.u_oaceite = 'true' AND MONTH(ft.fdata) = 5 THEN 1 END) AS May_Approved,
COUNT (CASE WHEN ft.u_oaceite = 'true' AND MONTH(ft.fdata) = 6 THEN 1 END) AS June_Approved,
COUNT (CASE WHEN ft.u_oaceite = 'true' AND MONTH(ft.fdata) = 7 THEN 1 END) AS July_Approved,
COUNT (CASE WHEN ft.u_oaceite = 'true' AND MONTH(ft.fdata) = 8 THEN 1 END) AS August_Approved,
COUNT (CASE WHEN ft.u_oaceite = 'true' AND MONTH(ft.fdata) = 9 THEN 1 END) AS September_Approved,
COUNT (CASE WHEN ft.u_oaceite = 'true' AND MONTH(ft.fdata) = 10 THEN 1 END) AS October_Approved,
COUNT (CASE WHEN ft.u_oaceite = 'true' AND MONTH(ft.fdata) = 11 THEN 1 END) AS November_Approved,
COUNT (CASE WHEN ft.u_oaceite = 'true' AND MONTH(ft.fdata) = 12 THEN 1 END) AS December_Approved
FROM ft
WHERE ft.ndoc = 9
AND YEAR(ft.fdata) = 2009
GROUP BY ft.name
)a
ORDER BY Rate_Approved DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources