SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Retrieving + dividing two Rowcounts
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 07/19/2007 :  16:59:00  Show Profile  Reply with Quote
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?

Edited by - Arithmomaniac on 07/19/2007 17:04:47

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 07/19/2007 :  17:03:38  Show Profile  Reply with Quote
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

Sweden
30114 Posts

Posted - 07/19/2007 :  17:04:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 07/19/2007 17:05:01
Go to Top of Page

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 07/19/2007 :  17:15:31  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 07/19/2007 :  17:51:31  Show Profile  Reply with Quote
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 - 07/19/2007 :  18:06:15  Show Profile  Reply with Quote
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

Sweden
30114 Posts

Posted - 07/19/2007 :  18:08:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Sigh...
Sample data?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 07/19/2007 :  18:08:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Sigh...
Expected output?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 07/19/2007 :  18:14:32  Show Profile  Reply with Quote
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

Sweden
30114 Posts

Posted - 07/19/2007 :  18:20:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 07/19/2007 :  18:27:24  Show Profile  Reply with Quote
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

Sweden
30114 Posts

Posted - 07/19/2007 :  18:28:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 07/19/2007 :  18:38:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 07/19/2007 :  18:44:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 07/19/2007 :  18:45:28  Show Profile  Reply with Quote
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

Sweden
30114 Posts

Posted - 07/19/2007 :  18:49:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 07/19/2007 18:50:22
Go to Top of Page

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 07/19/2007 :  19:03:29  Show Profile  Reply with Quote
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

Edited by - Arithmomaniac on 07/19/2007 19:04:19
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 07/19/2007 :  19:09:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 07/19/2007 :  19:16:48  Show Profile  Reply with Quote
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

Sweden
30114 Posts

Posted - 07/19/2007 :  19:18:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 07/19/2007 :  19:19:09  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.34 seconds. Powered By: Snitz Forums 2000