• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 45
  • Last Modified:

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
0
roblickley
Asked:
roblickley
  • 3
  • 2
1 Solution
 
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
 
roblickleyAuthor Commented:
Thank you:-)
0
 
Pawan KumarDatabase ExpertCommented:
welcome. glad to help as always.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now