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)
 Retrieving + dividing two Rowcounts

Author  Topic 

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-19 : 16:59:00
I have two amounts that I want to divide into eachother - the rowcount of this piece:


select Account from dbo.x
where type='A'

group by Account
having datediff(mm,min(CreatedAt),max(LastShipmentAt)) > @1
AND min(year(CreatedAtdt)) = @2
AND min(month(CreatedAtdt)) = @3


and the same amount without the rowcount, so I get the fraction
statement limited by having / statement unlimited


However, since I can't use COUNT(Account), because then I can't use a GROUP BY clause. I need a GROUP BY clause for those HAVING clauses, which will not embed in a WHERE subquery. I tried to integrate the ROWCOUNT into a function, but that is not allowed, so I tired a sproc.

Code follows:

CREATE PROC [dbo].[StayingByMonth]
(@1 int, @2 int, @Period int, @3 int OUTPUT)

AS

select distinct Account from dbo.x
where type='A'

group by Account
having datediff(mm,min(CreatedAt),max(LastShipmentAt)) > @1
AND min(year(CreatedAtdt)) = @2
AND min(month(CreatedAtdt)) = @3

SELECT @GoodRows = @@ROWCOUNT


and other slight modifications, but I always only get the table back as output without the rowcount, or it doesn't parse because I did not declare @Goodrows, or [if I try to turn the table into an exec] says the exec is invalid. Why?

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-19 : 17:03:38
Oh, here's my temp-table code - it's even worse:

 ALTER PROC [dbo].[StayingByMonth]
(@1 int, @2 int, @3 int)

AS

DECLARE @GoodRows int

create table #temp (Account int)

insert into #temp
select distinct Account from dbo.x
where type='A'

group by Account
having datediff(mm,min(CreatedAt),max(LastShipmentAt)) > @1
AND min(year(CreatedAtdt)) = @2
AND min(month(CreatedAtdt)) = @3

SELECT @GoodRows = (SELECT COUNT(Account) FROM #temp)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-19 : 17:04:30
Heard of derived tables?
select 1.0 * value1 / value2
from (
select count(Account) AS value1 from dbo.x
where type='A'
group by Account
having datediff(mm,min(CreatedAt),max(LastShipmentAt)) > @1
AND min(year(CreatedAtdt)) = @2
AND min(month(CreatedAtdt)) = @3
) as table1
cross join (select count(Account) As value2 from dbo.x
where type='A'
group by Account) as table2

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-19 : 17:15:31
quote:
Originally posted by Peso

Heard of derived tables?[code]select 1.0 * value1 / value2
from (
select count(Account) AS value1 from dbo.x
group by Account



If I have to leave the GREOUP BY clause in, so I can use the HAVING clause, then I get the count(account) for each account (ie duplicate entries), not total rowcount.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-19 : 17:51:31
It might help if you explained what you actually what, instead of posting a lot of code that doesn't work in hopes that we can figure out from that what you do want.

Explain the business problem, and then show the table structures, some sample data, and the expected output.


CODO ERGO SUM
Go to Top of Page

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-19 : 18:06:15
Touche.

I am trying to find the percent of people who joined in a given month who are still customers after, say, 6 months of initiation.

The Month, Year, and MonthsAfterWards are important to be variables, because I want to make a table that looks as such:

1MoLater 2 MoLater
July06
Aug06
Sept06

etc.

The relevant data is laid out in a log database.

AccountNumber FirstTime LastTime Activity

Every week, the first and last times customers were active that week is uploaded to the server, along with an ID unique per customer. Inactive customers do not have an upload at all. To check if they are still active customers, I would check if their last activity listed - the Largest LastTime value for the customer's ID - was anytime recent.

By comparing the month of the last LastTime and first FirstDate, I can make a live feed of how long customers were active.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-19 : 18:08:29
Sigh...
Sample data?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-19 : 18:08:42
Sigh...
Expected output?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-19 : 18:14:32
quote:
Originally posted by Peso

Sigh...
Sample data?
Expected output?



Data is pretty simple:

Acount: 1114576, 45fdc7654, etc.
Times are just datetime values.
The expected output is a rowcount - in how many cases is this true?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-19 : 18:20:37
I can't believe your logic.
When you send in YEAR = 2005 and MONTH = 6, you will only get months June-December for years 2005 and later! What happens to January-May?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-19 : 18:27:24
quote:
Originally posted by Peso

I can't believe your logic.
When you send in YEAR = 2005 and MONTH = 6, you will only get months June-December for years 2005 and later! What happens to January-May?



If the chart goes

1moLater 2moLater 3moLater (Later since creation)
1/2006
2/2006
(mo account made)


then it checks one month later than its own row. so it will check for the amount still open it

2/2006 3/2006 4/2006
3/2006 4/2006 5/2006
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-19 : 18:28:47
[code]select 1.0 * COUNT(DISTINCT table1.account) / COUNT(DISTINCT table2.account)
from (
select Account
from dbo.x
where type = 'A'
group by Account
having datediff(mm, min(CreatedAt), max(LastShipmentAt)) > @1
AND min(year(CreatedAtdt)) = @2
AND min(month(CreatedAtdt)) = @3
) as table1
cross join (
select Account
from dbo.x
where type = 'A'
group by Account
) as table2[/code]

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-19 : 18:38:14
[code]select account,
joinedat,
lastshipment,
CASE
WHEN DATEDIFF(MONTH, joinedat, lastshipment) > @1 then 'Still customer'
else 'Customer has left the building'
end as Status
from (
select Account,
MIN(CreatedAt) AS JoinedAt,
MAX(LastShipmentAt) AS LastShipment,
from dbo.x
where type = 'A'
AND CreatedAt >= DATEADD(YEAR, @2 - 1900, DATEADD(MONTH, @3 - 1, 0))
group by Account
) AS d
order by Account[/code]

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-19 : 18:44:58
quote:
Originally posted by Arithmomaniac

Touche.
First we have to understand what you are trying to accomplish.
We have repeatedly asked you to post proper sample data and expected output to no vail!

If you do not know how to post these things, have a look at this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81373

It took about 3-4 minutes to solve, because the OP did a good job and the problem was easily understood and the answer was already posted for comparison.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-19 : 18:45:28
quote:
Originally posted by Peso

select		account,
joinedat,
lastshipment,
CASE
WHEN DATEDIFF(MONTH, joinedat, lastshipment) > @1 then 'Still customer'
else 'Customer has left the building'
end as Status
from (
select Account,
MIN(CreatedAt) AS JoinedAt,
MAX(LastShipmentAt) AS LastShipment,
from dbo.x
where type = 'A'
AND CreatedAt >= DATEADD(YEAR, @2 - 1900, DATEADD(MONTH, @3 - 1, 0))
group by Account
) AS d
order by Account


Peter Larsson
Helsingborg, Sweden



Thank you so much! That's good code! (And sorry for being so exasperating)...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-19 : 18:49:19
The code above also uses ALL months after a certain point in time denoted by year @2 and month @3.
Your original solution ONLY uses the months after @3 for ALL years after @2, missing all months BEFORE @3.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-19 : 19:03:29
Although the code works really well, I 'm wondering why I can't copy the Status column five times. Could you please explain?

Thanks a bunch,
Arithmomaniac
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-19 : 19:09:49
Here we go again...
Copy what status five times?

This
select		account,
joinedat,
lastshipment,
CASE
WHEN DATEDIFF(MONTH, joinedat, lastshipment) > 1 then 1
else 0
end as 1monthafter,
CASE
WHEN DATEDIFF(MONTH, joinedat, lastshipment) > 2 then 1
else 0
end as 2monthafter,
CASE
WHEN DATEDIFF(MONTH, joinedat, lastshipment) > 3 then 1
else 'Customer has left the building'
end as 3monthafter,
CASE
WHEN DATEDIFF(MONTH, joinedat, lastshipment) > 4 then 1
else 0
end as 4monthafter,
CASE
WHEN DATEDIFF(MONTH, joinedat, lastshipment) > 5 then 1
else 0
end as 5monthafter,
CASE
WHEN DATEDIFF(MONTH, joinedat, lastshipment) > 6 then 1
else 0
end as 6monthafter
from (
select Account,
MIN(CreatedAt) AS JoinedAt,
MAX(LastShipmentAt) AS LastShipment,
from dbo.x
where type = 'A'
AND CreatedAt >= DATEADD(YEAR, @2 - 1900, DATEADD(MONTH, @3 - 1, 0))
group by Account
) AS d
order by Account


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-19 : 19:16:48
Sorry...Yeah, that. Problem was, I kept leaving a comma on the last one.
Any good way to weed out people who are new for a specific case? For example, let's say the customer came in December 2006. I would rather just put NULL when excecuting

 WHEN DATEDIFF(MONTH, joinedat, lastshipment) > 12 then 1
else 0
end as 1yrafter


,so that I can tell at a glance that I just have incomplete data, and because I want to turn this into a pivot table (I should ahve said that earlier also - sorry.) Thanks for all your help.

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-19 : 19:18:37
Just. Just remove the "ELSE 0" lines and you will get NULL when criteria is not satisfied.

But why is NULL better than 1/0?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-19 : 19:19:09
I should say how I plan on making that pivot table - I'm turning "still customer" into 1, and "not customer" into 0. When I make a pivot table, I'll take the average of the 0s and 1s, after I make the table out of Year+Month on the left, and MonthsLater on the top.
Go to Top of Page
    Next Page

- Advertisement -