June 4, 2008 at 10:45 am
I am trying to use a derived table to get record count for the entire year and divide it by a subset of the data.
How do I use a derived table to get the total count for 2007.
I want to add a column that will divide count(a.personNo)/Total2007Records.
The total 2007 records would come from a derived table. I will use this derived information in other field calculations as well so if there is a better way to do this please let me know.
any help would be greatly appreciated!
Select left(b.Hierarchy,3) ThreeDotHierarchy, count(a.PersonNo)
from dbo.tbl_FW_AppTracking A
JOIN [AssociateRepository].[dbo].[Associates] b ON (b.PersonNumber = a.PersonNo)
where VisitDate between '2007-01-01' and '2007-12-31'
and left(b.Hierarchy,1) = 'E'
group By left(b.Hierarchy,3)
June 4, 2008 at 11:15 am
Instead of a standard derived table, you might want to look into using a CTE (Common Table Expression). They have some advantages in terms of readability, etc.
As far as using either one, it would be very helpful if you would copy your table structure into the forum. It's hard to help write a query without seeing the structure of the underlying table(s). There's a link on the front page here to a "Best Practices for Posting Questions" article.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 4, 2008 at 11:51 am
Yes table structure would make it a bit easier.
(Database that contains associate details)
Database = [AssociateRepository]
Table Name [dbo].[Associates]
AssociateId PersonNumber Hierarchy
1329838xxxxxxxEBBGB...AD
1329840xxxxxxxEBBGB...AD
1333796xxxxxxxEUNKCFH.AB
1333797xxxxxxxEBGIB...AD
1329988xxxxxxxEBJD....AL
1329989xxxxxxxEBGIB...AD
1329991xxxxxxxEBBGB...AD
1330350xxxxxxxEZEFKB..AB
1330351xxxxxxxEZJDX...AK
1330352xxxxxxxEZHBI...AA
1361963xxxxxxxEBGIB...AD
1330898xxxxxxxEZEBBB..BD
1331108xxxxxxxEZJDX...AD
1331109xxxxxxxEBGIB...AD
1331110xxxxxxxEBGIB...AD
1331113xxxxxxxEBGIB...AD
1331114xxxxxxxEBGIB...AD
1326643xxxxxxxELBFABB.AB
1331439xxxxxxxEZHBH...AF
1331441xxxxxxxEBJD....AL
Database that contains recorded website hit counts PersonNumber and PersonNo used to link data.
Database = [University]
Table = [tbl_FW_AppTracking]
PersonNo VisitDate
xxxxxxxxx 2006-05-31 16:52:40.000
xxxxxxxxx 2006-05-31 17:00:07.313
xxxxxxxxx 2006-05-31 17:02:01.610
xxxxxxxxx 2006-05-31 17:02:23.780
xxxxxxxxx 2006-05-31 17:07:24.657
xxxxxxxxx 2006-06-01 10:35:30.187
xxxxxxxxx 2006-06-01 10:46:40.017
xxxxxxxxx 2006-06-01 10:58:56.563
xxxxxxxxx 2006-06-01 11:00:50.750
xxxxxxxxx 2006-06-01 11:14:15.657
xxxxxxxxx 2006-06-01 11:14:22.203
xxxxxxxxx 2006-06-01 11:19:48.327
xxxxxxxxx 2006-06-01 11:41:12.877
xxxxxxxxx 2006-06-01 13:32:44.030
xxxxxxxxx 2006-06-01 13:53:02.280
June 4, 2008 at 12:41 pm
That helps.
Are you looking for the number of visits per year per person, or total visits per year divided by total people, or total visits per year divided by people who visited that year?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply