Hey everyone,
I am rather new to this, but I'm making way and learning a great deal.
However, after much research I've become lost on a topic and I'm hoping
you can help me out.
I have the following query:
SELECT Title, [Date], Number
FROM (
SELECT
'OptOuts' [Title], CONVERT(varchar, DateOptedOut, 101) [Date] ,
count(contacts.id) [Number]
FROM contacts
WHERE DateOptedOut > '10/06/2005'
GROUP BY CONVERT(varchar, DateOptedOut, 101)
UNION
SELECT
'Signups' [Title], CONVERT(varchar, dateEntered, 101) [Date] ,
count(contacts.id) [Number]
FROM contacts
WHERE dateEntered > '10/06/2005'
GROUP BY CONVERT(varchar, dateEntered, 101)
)[Trend]
Order BY [Date]
This query works great in that it shows me how many people signed up
and opted out per day in one nice worksheet. However, I need for it to
do one more thing...
I am trying to get it to divide the two numbers to come up with
something called "Churn Rate" which is basically the OptOuts / Signups.
The one small other problem I'm running into is that if there are no
optouts on a certain day, it doesn't show the date and the number 0...
which would be nice.
If anyone has any ideas as to how I can get this done I would really,
really appreciate it! Thank you very much!By the way, I tried doing something like this:
SELECT ((SELECT count(contacts.id) FROM contacts WHERE WHERE
DateOptedOut > '10/06/2005')/(SELECT count(contacts.id) FROM
contacts WHERE dateEntered > '10/06/2005')) AS Percentage
But became lost when I tried to work that into the current query and
put it below the "Signup" and "Optout"... while trying to achieve this
look:
OptOuts-- Date -- Number
Signups-- Date -- Number
Churn -- Date -- Percentage
Thanks again for all your help!|||Hi, maybe something like
SELECT Title, [Date], [NumberOptOuts] + [NumberSignups] as [Number],
case when [NumberSignups] <> 0 then
[NumberOptOuts] / [NumberSignups] else
0 end
as [Churn Out]
FROM (
SELECT
'OptOuts' [Title], CONVERT(varchar, DateOptedOut, 101) [Date] ,
count(contacts.id) [NumberOptOuts], 0 as [NumberSignups]
FROM contacts
WHERE DateOptedOut > '10/06/2005'
GROUP BY CONVERT(varchar, DateOptedOut, 101)
UNION
SELECT
'Signups' [Title], CONVERT(varchar, dateEntered, 101) [Date] ,
0 as [NumberOptOuts], count(contacts.id) [NumberSignups]
FROM contacts
WHERE dateEntered > '10/06/2005'
GROUP BY CONVERT(varchar, dateEntered, 101)
)[Trend]
Order BY [Date]
It looks like it might work :)
Peter|||Thanks for the attempt Peter, but for some reason the Churn Out column
just spits out zeros... :(
Any other ideas? Thanks again for everything!|||Sorry to bump this thread, but I was just curious if anyone else had
ideas...|||On 22 Nov 2005 07:54:39 -0800, andrew.tatum@.gmail.com wrote:
>Thanks for the attempt Peter, but for some reason the Churn Out column
>just spits out zeros... :(
>Any other ideas? Thanks again for everything!
Hi Andrew,
That's because both values used in the division are integers. SQL Server
will also use an integer to store the result.
SELECT 4 / 7
SELECT 4.0 / 7
or
SELECT 4 / 7.0
SELECT CAST(4 AS numeric(5,3)) / 7
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Nah, the problem is that this is union and every row has 0 either in
NumberOptOuts or NumberSignups.
I was too fast with sending the code before :)
wouldnt something like the following work instead?
Peter
SELECT Title, [Date], [NumberOptOuts] + [NumberSignups] as [Number],
case when [NumberSignups] <> 0 then
[NumberOptOuts] / [NumberSignups] else
0 end
as [Churn Out]
FROM
(
SELECT
'OptOuts' [Title], CONVERT(varchar, a.DateOptedOut, 101) [Date] ,
count(a.id) [NumberOptOuts], b.SignUpCount as [NumberSignups]
FROM contacts a,
(select count(id) SignUpCount from contacts where dateEntered > '10/06/2005'
GROUP BY CONVERT(varchar, dateEntered, 101)) b
WHERE a.DateOptedOut > '10/06/2005'
GROUP BY CONVERT(varchar, a.DateOptedOut, 101)
UNION
SELECT
'Signups' [Title], CONVERT(varchar, dateEntered, 101) [Date] ,
b.OptOutCount as [NumberOptOuts], count(contacts.id) [NumberSignups]
FROM contacts a,
(select count(id) OptOutCount from contacts where DateOptedOut >
'10/06/2005'
GROUP BY CONVERT(varchar, DateOptedOut, 101)) b
WHERE dateEntered > '10/06/2005'
GROUP BY CONVERT(varchar, dateEntered, 101)
)[Trend]
Order BY [Date]|||On Thu, 24 Nov 2005 17:15:05 -0000, Rogas69 wrote:
>Nah, the problem is that this is union and every row has 0 either in
>NumberOptOuts or NumberSignups.
>I was too fast with sending the code before :)
>wouldnt something like the following work instead?
(snip)
Hi Peter,
I had a quick look at it, but I'm afraid you are overcomplicating
things. How aboout trying this one instead:
DECLARE @.CutOffDate datetime
SET @.CutOffDate = '20050610' -- or '20051006'; you used ambiguous format
SELECT Date,
SUM(OptOut) AS NumberOptOuts,
SUM(SignUp) AS NumberSignUps,
SUM(OptOut + SignUp) AS Number,
(1.0 * SUM(OptOut)) / NULLIF(SUM(SignUp), 0) AS ChurnOut
FROM (SELECT DateOptedOut AS Date, 1 AS OptOut, 0 AS SignUp
FROM contacts
WHERE DateOptedOut > @.CutOffDate
UNION ALL
SELECT DateEntered AS Date, 0 AS OptOut, 1 AS SignUp
FROM contacts
WHERE DateEntered > @.CutOffDate) AS x
GROUP BY Date
ORDER BY Date
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||yes Hugo, you're right. that was just first approach as close to the
original query as possible. I supposed that there could have been better way
to implement it :)
as far as date is concerned - this date format was 'derived' from the
original query, i am using always yyyymmdd format :)
peter|||On Mon, 28 Nov 2005 11:42:24 -0000, Rogas69 wrote:
>yes Hugo, you're right. that was just first approach as close to the
>original query as possible. I supposed that there could have been better wa
y
>to implement it :)
>as far as date is concerned - this date format was 'derived' from the
>original query, i am using always yyyymmdd format :)
Hi Peter,
My apologies - when I wrote my reply, I somehow had you
with
Andrew (the original poster).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
