Counting reoccurrence of a Client in a View

Dear Experts,

Below is my SQL view code to return the information I need to create a page

SELECT        TOP (100) PERCENT dbo.tblEntry.EntryID, dbo.tblClient.ClientForeName, dbo.tblClient.ClientSurName, dbo.tblClient.ClientID, dbo.tblHorse.HorseID, dbo.tblHorse.HorseName, dbo.tblEntry.EntryBridleNumber, 
                         dbo.tblEntry.EntryStartTime, dbo.tblClass.ClassID, dbo.tblShow.ShowID, dbo.tblEntry.EntryApproved, dbo.tblEntry.EntrySectionPlace, dbo.tblEntry.EntryPercentageMark, dbo.tblEntry.EntryResultsMark, 
                         dbo.tblEntry.EntryResultsPlace, dbo.tblEntry.Section, dbo.tblEntry.EntryCancelled, dbo.tblEntry.EntryCollectiveMark, dbo.tblEntry.EntryResultsMoney, dbo.tblEntry.EntryNotes, dbo.tblClass.ClassNumber, 
                         dbo.tblTest.TestName, dbo.tblShow.ShowStartDate, dbo.tblClient.ClientBDNumber, dbo.tblClient.ClientBSJANumber, dbo.tblHorse.HorseBSJANumber, dbo.tblHorse.HorseBDNumber
FROM            dbo.tblEntry INNER JOIN
                         dbo.tblClient ON dbo.tblEntry.RiderID = dbo.tblClient.ClientID INNER JOIN
                         dbo.tblHorse ON dbo.tblEntry.HorseID = dbo.tblHorse.HorseID INNER JOIN
                         dbo.tblClass ON dbo.tblEntry.ClassID = dbo.tblClass.ClassID INNER JOIN
                         dbo.tblShow ON dbo.tblClass.ShowID = dbo.tblShow.ShowID INNER JOIN
                         dbo.tblTest ON dbo.tblClass.TestID = dbo.tblTest.TestID
WHERE        (dbo.tblEntry.EntryApproved <> 'Hold') AND (dbo.tblEntry.EntryCancelled <> 1) AND (dbo.tblEntry.EntryApproved <> 'No')
ORDER BY dbo.tblEntry.EntryStartTime, dbo.tblEntry.EntryBridleNumber, dbo.tblClass.ClassID

Open in new window


It relates to entries in a competition for horses and riders - and it works very nicely.

I would like to add the additional functionality of counting the number of times a particular ClientID occurs in the results of this view and adding an additional column in the data entitled "CompReacc" to hold the number of times.

It is important to note that this must ONLY be linked to the occurrence of the ClientID as they may be riding multiple horses in the competition.

For instance, if a client is only entered once on one horse then column CompReacc would return 1

If they were entered twice (either on the same horse or another horse) then that column would return 2 - this would of course return the value twice in effect - once for each row where they were.

The reason behind needing this is that when I print a list of entries in a show I would like to quickly see those clients with multiple entries.

Many thanks in advance
roblickleyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Please try this-

Added below in the code
,COUNT(*) OVER (PARTITION BY dbo.tblClient.ClientID) CompReacc

SELECT        TOP (100) PERCENT dbo.tblEntry.EntryID, dbo.tblClient.ClientForeName, dbo.tblClient.ClientSurName, 
	dbo.tblClient.ClientID, dbo.tblHorse.HorseID, dbo.tblHorse.HorseName, dbo.tblEntry.EntryBridleNumber, 
                         dbo.tblEntry.EntryStartTime, dbo.tblClass.ClassID, dbo.tblShow.ShowID, dbo.tblEntry.EntryApproved, dbo.tblEntry.EntrySectionPlace, dbo.tblEntry.EntryPercentageMark, dbo.tblEntry.EntryResultsMark, 
                         dbo.tblEntry.EntryResultsPlace, dbo.tblEntry.Section, dbo.tblEntry.EntryCancelled, dbo.tblEntry.EntryCollectiveMark, dbo.tblEntry.EntryResultsMoney, dbo.tblEntry.EntryNotes, dbo.tblClass.ClassNumber, 
                         dbo.tblTest.TestName, dbo.tblShow.ShowStartDate, dbo.tblClient.ClientBDNumber, 
						 dbo.tblClient.ClientBSJANumber, dbo.tblHorse.HorseBSJANumber, dbo.tblHorse.HorseBDNumber
				,COUNT(*) OVER (PARTITION BY dbo.tblClient.ClientID) CompReacc
FROM            dbo.tblEntry INNER JOIN
                         dbo.tblClient ON dbo.tblEntry.RiderID = dbo.tblClient.ClientID INNER JOIN
                         dbo.tblHorse ON dbo.tblEntry.HorseID = dbo.tblHorse.HorseID INNER JOIN
                         dbo.tblClass ON dbo.tblEntry.ClassID = dbo.tblClass.ClassID INNER JOIN
                         dbo.tblShow ON dbo.tblClass.ShowID = dbo.tblShow.ShowID INNER JOIN
                         dbo.tblTest ON dbo.tblClass.TestID = dbo.tblTest.TestID
WHERE        (dbo.tblEntry.EntryApproved <> 'Hold') AND (dbo.tblEntry.EntryCancelled <> 1) AND (dbo.tblEntry.EntryApproved <> 'No')
ORDER BY dbo.tblEntry.EntryStartTime, dbo.tblEntry.EntryBridleNumber, dbo.tblClass.ClassID

Open in new window

0
roblickleyAuthor Commented:
It works but now I notice a problem which is probably because I am lazy.
It is counting the incidents of the clientID across the whole database rather than just from the showID I have selected the data from from this view.
How should I make this specific to just the ShowID I restrict the view to returning the data from.

So  ClientID = 12 has competed in 120 classes in total but is only present in 2 classes from this particular show.

I call the view as follows : SELECT * FROM dbo.qry_Entries_HorseRiderView WHERE ShowID = ? ORDER BY CLASSNUMBER, ENTRYSTARTTIME

Is there any way to achieve this or do I need to rewrite the view?
0
Pawan KumarDatabase ExpertCommented:
>>It works but now I notice a problem which is probably because I am lazy.
Glad that it works.

>>Is there any way to achieve this or do I need to rewrite the view?
yes it is possible.

Please try this -

SELECT        TOP (100) PERCENT dbo.tblEntry.EntryID, dbo.tblClient.ClientForeName, dbo.tblClient.ClientSurName, 
	dbo.tblClient.ClientID, dbo.tblHorse.HorseID, dbo.tblHorse.HorseName, dbo.tblEntry.EntryBridleNumber, 
                         dbo.tblEntry.EntryStartTime, dbo.tblClass.ClassID, dbo.tblShow.ShowID, dbo.tblEntry.EntryApproved, dbo.tblEntry.EntrySectionPlace, dbo.tblEntry.EntryPercentageMark, dbo.tblEntry.EntryResultsMark, 
                         dbo.tblEntry.EntryResultsPlace, dbo.tblEntry.Section, dbo.tblEntry.EntryCancelled, dbo.tblEntry.EntryCollectiveMark, dbo.tblEntry.EntryResultsMoney, dbo.tblEntry.EntryNotes, dbo.tblClass.ClassNumber, 
                         dbo.tblTest.TestName, dbo.tblShow.ShowStartDate, dbo.tblClient.ClientBDNumber, 
						 dbo.tblClient.ClientBSJANumber, dbo.tblHorse.HorseBSJANumber, dbo.tblHorse.HorseBDNumber
				,COUNT(*) OVER (PARTITION BY dbo.tblClient.ClientID,dbo.tblShow.ShowID) CompReacc
FROM            dbo.tblEntry INNER JOIN
                         dbo.tblClient ON dbo.tblEntry.RiderID = dbo.tblClient.ClientID INNER JOIN
                         dbo.tblHorse ON dbo.tblEntry.HorseID = dbo.tblHorse.HorseID INNER JOIN
                         dbo.tblClass ON dbo.tblEntry.ClassID = dbo.tblClass.ClassID INNER JOIN
                         dbo.tblShow ON dbo.tblClass.ShowID = dbo.tblShow.ShowID INNER JOIN
                         dbo.tblTest ON dbo.tblClass.TestID = dbo.tblTest.TestID
WHERE        (dbo.tblEntry.EntryApproved <> 'Hold') AND (dbo.tblEntry.EntryCancelled <> 1) AND (dbo.tblEntry.EntryApproved <> 'No')
ORDER BY dbo.tblEntry.EntryStartTime, dbo.tblEntry.EntryBridleNumber, dbo.tblClass.ClassID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
roblickleyAuthor Commented:
Thank you:-)
0
Pawan KumarDatabase ExpertCommented:
welcome. glad to help as always.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.