Most Frequent Allocations

Question

Most Frequent Allocations

Mahathi

SSC Veteran

Points: 270
More actions
September 19, 2008 at 10:51 pm

#191201

Hi
I am having requirement that based on a client id i should get the most frequently visited allocations for a day.
I should get the maximum count individual for each ClientId only for the current day for each AllocId.
For example,
I am having two clients a and b and they have AllocId's c and d respectively. If c and d have a count values of 8 and 9
when i have to show the max count for Client 'a', i should get the AllocId 'c' and for Client 'b' I should get the AllocId as 'd' on;y for the present day.
Hope you understood the problem.
Thanks in advance for any help provided.
Regards
Mahathi.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

Please check out the links in my signature line for tips on how to present a question so that it cna be easily understood and answers provided.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Mahathi SSC Veteran Points: 270 More actions · Answer 2

I have a table named Allocation Visit and the schema of the table is as follows:

CREATE TABLE [dbo].[AllocationVisit](

[VisitId] [int] IDENTITY(1,1) NOT NULL,

[AllocId] [bigint] NOT NULL,

[VisitTime] [datetime] NOT NULL,

[Cnt] [money] NOT NULL,

[LastUpdate] [datetime] NOT NULL

) ON [PRIMARY]

And the data is

AllocId Visit Time

1 9/22/2008 9:43:03 AM 1.00009/22/2008 9:43:03 AM

6 9/20/2008 9:43:46 AM 0.00009/20/2008 9:43:46 AM

5 9/18/2008 11:03:16 AM 1.0000 9/18/2008 11:03:16 AM

2 9/12/2008 12:45:30 PM 3.0000 9/12/2008 12:45:30 PM

3 9/19/2008 10:29:45 AM 0.00009/19/2008 10:29:45 AM

4 9/22/2008 11:30:32 AM 2.00009/22/2008 11:30:32 AM

7 9/19/2008 10:31:08 AM 5.00009/19/2008 10:31:08 AM

8 9/18/2008 11:13:32 AM 1.00009/18/2008 11:13:32 AM

mpeters-878100 Right there with Babe Points: 716 More actions · Answer 3

Hows this

select ALLocID,count(*)as 'No Of times visited' from dbo.allocationvisit

--where visittime = 'Enter date here'

group by AlLocID

Mahathi SSC Veteran Points: 270 More actions · Answer 4

Sorry the previous post is not yet complete. I unknowingly clicked the enter button.

The count varaibe increments when any user visits a page called ViewAllocation and AllocId is the parameter.

VisitTime is the time at which the page is visited and Last Update is the time at which the data has been modified last.

The count becomes '0' at the start of each day and then it increases each time the page is visited.

The requirement is i need to get the AllocId with the maximum count only for this day and also for a particular Client where the ClientId is send as a parameter. The ClientId is from another table.

Mahathi SSC Veteran Points: 270 More actions · Answer 5

I got the solution for the requirement. Th following is the query used to get the most Frequent Allocations per day per client.

ALTER procedure [dbo].[mostFrequentAllocations](@ClientId bigint)

as

begin

select av.AllocId,a.AllocName,convert(varchar,getdate(),101) as VisitTime from AllocationVisit av,Allocations A,Clients C

where

av.AllocId=A.AllocId and A.ClientId=C.ClientId and C.ClientId=@ClientId and convert(varchar,getdate(),101)=convert(varchar,VisitTime,101) and

av.Cnt=(select max(Cnt) from AllocationVisit av,Clients C,Allocations A where convert(varchar,getdate(),101)=convert(varchar,VisitTime,101) and A.AllocId=av.AllocId and A.ClientId=C.ClientId and C.ClientId=@ClientId)

end

where ClientId is sent as a parameter from another table called 'Clients'.