Need help bracketing counts

I need to create a view that shows the number of times that clients made payments, how many clients, and how much they paid over a period of time. I'm not sure if I can use a case for this. How can I put something like this together?

I have a tblClients with a clientid field

I have a tblPayments with the clientid, pmtdate, and pmtamount

For example:

1 Payment -- 23 Clients -- $16000

2 Payments -- 12 Clients -- $32000

3 Payments -- 4 Clients -- $13000

etc...

If you the the "how many clients" value you must also have such a field in the tblPayments table, or is it one purchased client per payment? If so, then

SELECT c.clientid, count(p.clientid) as clientCount,sum(p.pmtamount) as pmtSum
FROM tblClients c
LEFT JOIN tblPayments p ON c.clientid=p.clientid
WHERE p.pmtdate BETWEEN @.startdate AND @.enddate
GROUP BY c.clientid

|||

SELECTCOUNT(t.clientid)as clients,t.tblPaymentsCount,SUM(t.pmtamount)as pmtamountFROM(SELECT clientidas clientid,COUNT(tblPaymentsID)as tblPaymentsCount,SUM(pmtamount)as pmtamountFROM tblPaymentsGROUP BY clientid) tGROUP BY tblPaymentsCountORDER BY tblPaymentsCount

|||

Now that I saw BradFMJ's suggestion I realize that I quite possibly misunderstood the task

Maybe this is what you're after:

SELECTcount(*)as paymentCount,count(DISTINCT clientid)as clientCount,SUM(pmtamount)as pmtSumWHERE pmtdateBETWEEN @.startDateAND @.endDate

need help asap with 404 http://localhost/reports error

Can some one help me out? I have a bunch of reports created with sql reports. The problem I am having is I can view the reports fine from within the vs 2005 business int. designer. But when I run thehttp://localhost/reports or thehttp://localhost/reportserver I get the page can not be found HTTP 404 I have verified that IIS is loaded and there is a virtual directory for both the reports and and reports services. I also did verified that the sql reports services is running. I have gone into my reporting service configuration and checked it out everything is green and the setting are the same as I have on a working computer. I am at the end of my rope with this can some one please assist me with this. I need to get these reports up asap. Any and all assistance would be greatly appreciated.have u deployed ur reports to ur report server|||it will not allow me|||it will not allow me|||

what error message are u getting

|||a connection coul not be made to the report serverhttp://localhost/reports

Need help adding 3rd table to a Join

Hi can someone help me I'm brainless, I need to add a field from a third database to this view

CREATE View mProduct_Search

As

Select

mProduct.*,

(IsNull(mProduct.PRICE,0) * 2) as [DISPLAY_PRICE],

(IsNull(mProduct.LINER_PRICE,0) * 2) as [DISPLAY_LINER_PRICE],

(mProduct.ITEM_ID + ' ' +IsNull(mProduct.[SHORT_DESCRIPTION],' ') + ' ' + IsNull(mProduct.[CAT_PG],' ') + ' '+ IsNull(mProduct.[CATEG],' ') + ' ' + IsNull(mProduct.[DESCRIPTION1],' ') + ' ' + IsNull(mProduct.[DESCRIPTION2],' ') + ' '

+ IsNull(mProduct.[DESCRIPTION3],' ')) as [GENARAL_SERACH],

A.PERC as [PERC], A.START_DATE AS [START_DATE], A.END_DATE AS [END_DATE], A.DESCR as [DESCR]

from mProduct Left Outer Join (Select * from mSpecialPricing ) A

ON mProduct.ITEM_ID = A.ITEM_ID

The field I need to add is COLLECTION, with Left Outer Join

mCollection
ITEM_ID
COLLECTIONAnd COLLECTION is a member of which table?|||I need the COLLECTION field from the mCollection Table to be included in the mProduct_Search view|||Nevermind my brain came back to life, I have answered my own question...
CREATE View mProduct_Search

As

Select

mProduct.*,

(IsNull(mProduct.PRICE,0) * 2) as [DISPLAY_PRICE],

(IsNull(mProduct.LINER_PRICE,0) * 2) as [DISPLAY_LINER_PRICE],

(mProduct.ITEM_ID + ' ' +IsNull(mProduct.[SHORT_DESCRIPTION],' ') + ' ' + IsNull(mProduct.[CAT_PG],' ') + ' '+ IsNull(mProduct.[CATEG],' ') + ' ' + IsNull(mProduct.[DESCRIPTION1],' ') + ' ' + IsNull(mProduct.[DESCRIPTION2],' ') + ' '

+ IsNull(mProduct.[DESCRIPTION3],' ')) as [GENARAL_SERACH], B.COLLECTIONS as [COLLECTIONS],

A.PERC as [PERC], A.START_DATE AS [START_DATE], A.END_DATE AS [END_DATE], A.DESCR as [DESCR]

from mProduct Left Outer Join (Select * from mSpecialPricing ) A

ON mProduct.ITEM_ID = A.ITEM_ID

Left Outer Join (Select * from mCollections) B

on mProduct.ITEM_ID = B.ITEM_ID

Navigation of reports

Is it possible to use tree view to display all available reports.
Is it possible to navigate to another report on click event of tree node.Hi,
It is not available, but you can make it using some technique. i.e you can
simulate the tree view using the catalog table where table names are stored
and give an "Action" for jumping to other reports.
Amarnath
"Venkatesh srikanth" wrote:
> Is it possible to use tree view to display all available reports.
> Is it possible to navigate to another report on click event of tree node.|||How to attach Action to tree view.
I tried many option to navigate, Iam not finding any option to navigate to
another Report, Please suggest me
"Amarnath" wrote:
> Hi,
> It is not available, but you can make it using some technique. i.e you can
> simulate the tree view using the catalog table where table names are stored
> and give an "Action" for jumping to other reports.
> Amarnath
> "Venkatesh srikanth" wrote:
> > Is it possible to use tree view to display all available reports.
> > Is it possible to navigate to another report on click event of tree node.|||You can have a dataset using the catalog table which displayes all the report
names.
then on the report layout you can use "action" from the properties of the
table cell.
give "Jump to report". Ofcourse lot of formatting should be done for tree
view.
Amarnath
"Venkatesh srikanth" wrote:
> How to attach Action to tree view.
> I tried many option to navigate, Iam not finding any option to navigate to
> another Report, Please suggest me
> "Amarnath" wrote:
> > Hi,
> > It is not available, but you can make it using some technique. i.e you can
> > simulate the tree view using the catalog table where table names are stored
> > and give an "Action" for jumping to other reports.
> >
> > Amarnath
> >
> > "Venkatesh srikanth" wrote:
> >
> > > Is it possible to use tree view to display all available reports.
> > > Is it possible to navigate to another report on click event of tree node.