Calcaluting total nu of people based on their name field

I want to create a report which should be looked like this.

Members Joined Members Borrower Depositor Members Joined Yesterday 8 4 4 Members Joined Last 7 days 39 20 19 Members Joined Last 30 Days 243 120 122 Members Borrower Depositor 2007 Members Joined to Date 1,052 510 542 Q1-07 415 200 215 Q2-07 445 210 235 Q3-07 (To date) 192 100 92 All Members Joined 1,052 510 542

In the data base, there are some tables called members, loans and deposits containing members names, joined date , etc.

Example:

MembersTable

MemberName MemberID etc

LoansTable

MemberName CommencedDate

DepositorTable

MemberName CommencedDate

I assume that I should be able to create a report using matix format. But I want to clarify few things before I start. How do I calculate nu members? Data base tables contains only names of the members but not numbers. Other thing is how can I group members by perticular time periods as shown above? Do I need to use parameters? Also In the table, members can be borrowers or a depositors? How can clarify this one?

Please Can anyone give an idea? How to start and which direction I should go etc?

Thanks

I personally prefer to do all this work at the data layer within the SQL query rather than trying to get rs to SUM things conditionally.

Looking at your definition, I would creaste multiple datasets and probably stick to returning 3 columns in my dataset and using a table for the layout rather than a matrix as they are more flexible. This makes sense as your number of columns does not need to expand dynamically which is what a matrix is really meant for.

You should also consider whether you will add a date parameter to do "as at" queries or whether you will just use GETDATE() to get the current date and time.

|||

Thanks Adam I will try with a table. I think he wants to see the report once a month without querying. Run the report and get the results end of each month.

I wrote a query something like this.

SELECT COUNT(DISTINCT(Members.Name), COUNT(DISTINCT(Loan.Name) AS Borrowers. , COUNT(DISTINCT(Depositor.Name) AS Depositors

FROM Members M

INNER JOIN Loan L ON M.Name = L.Name

INNER JOIN Depositor D ON M.Name = D.Name

WHERE ( L.CommencedDate BETWEEN (@.Startdate) AND (@.GETDATE( ) )

AND

D.CommenceDate BETWEEN (@.Startdate) AND (@.GETDATE( ) ) )

Am I going right direction?

Thanks

|||

I'd break it up a bit and firstly try to write the queries for the individual bits of infrmation and look to see if you can bring them together.

by loking at your data, borrower + depositor = member, is it safe to assume that? If so then there's no need to join to member is there? or can member potentially be both?

Assuming the former,

Dataset1:

Code Snippet

WITH yesterdayCTE AS

(

SELECT type = 'Members Joined Yesterday'

, borrowers = (select count(*)

from LoansTable

where CommencementDate >= GETDATE()-1)

, depositors = (select count(*)

from DepositorTable

where CommencementDate >= GETDATE()-1)

),

last7daysCTE AS

(

SELECT type = 'Members Joined Last 7 Days'

, borrowers = (select count(*)

from LoansTable

where CommencementDate >= GETDATE()-7)

, depositors = (select count(*)

from DepositorTable

where CommencementDate >= GETDATE()-7)

),

last30daysCTE AS

(

SELECT type = 'Members Joined Last 30 Days'

, borrowers = (select count(*)

from LoansTable

where CommencementDate >= GETDATE()-30)

, depositors = (select count(*)

from DepositorTable

where CommencementDate >= GETDATE()-30)

),

combinedCTE AS

(

SELECT * FROM yesterdayCTE

UNION ALL

SELECT * FROM last7daysCTE

UNION ALL

SELECT * FROM last30daysCTE

)

SELECT *

, members = borrowers + depositors

FROM combinedCTE

You can employ a similar startegy to construct the querters except you probably won't to have a separate CTE per quarter.

|||

Thanks lot for the codes Adam..Yes Total Number of members are sum of borrowers and depositors. But member can not be both. But from membertable we can not identify whether they are borrowers or depositors. I need to get that info from borrowers and depositors tables.

I tried with my codes. I had to change @.GETDATE as @.Enddate. Except that My code worked. But drawback is user has to input dates manually inorder to see that. I think the one you wrote does not requre user to enter dates. I will try with your code.

One more thing..what do u mean CTE?

Probably for 2007 year members statistics, I may need to make a seperate data set.

Thanks again

|||

Your code worked perfectly. Thats what exactly I want. I will write a similar code for 2007 qtrs too.

Thanks lot

|||

I wrote the code for quarters too.

I clicked new data set and then in the querry string window I wrote the query.

But when I click ok,it gives the following error message.

"Specify a CLS compliant name for the data set."

What does that mean?

Instead of GETDATE() , can I specify dates?

As a example;

commencementDate BETWEEN '01/01/2007' AND '03/31/2007'

When I do this , I get the error message.

|||well everything works well...Thanks for your help Adam|||

Glad I could help.

CTE stands for Common Table Expression and is a new addition to T-SQL 2005. I think they make code a lot more readable.

|||

ok..I have another problem which is almost similar.

CD - Table

Amount(int) StartDate(Date) AccountID(int)

I want to create a report from this table. the report should look like this.

Value No of Accounts Average Value

For Yesterday

For Last 7days

For Last 30 days

Can I use the same query..Here I have only one table. I assume I should be able to use it. I already wrote it. But there is a syntax error.

WITH yesterdayCTE AS

(

SELECT type = 'CDs Sold Yesterday'

, Values = (select sum( Amount )

from CD Table

where StartDate >= GETDATE()-1)

, No Of CDs Sold = (select count(AccountID)

from CD Table

where StartDate >= GETDATE()-1)

, AverageCD Size = (select avg(Amount)

from CD Table
where StartDate >=GETDATE()-1)

),

I get the following error message.

There is an error in the query. Incorrect syntax near the keyword 'Values'.
Incorrect syntax near ','.
Incorrect syntax near ','.
Incorrect syntax near ')'.

I dont know why? Can you please show me where I went wrong?|||

I'd like to help but your query does not resemble the above example, if you want column or table names to have spaces then you have enclose them in [] e.g. [AverageCD Size] instead of just AverageCD Size. Secondly, you should look up the syntax for a CTE and in this case you probably don't need one.

|||Yes I changed the startdate now. I tried with [ ] .But did not work. I looked at the syntax for CTE. As you said I dont need one. But still get the same error message.|||

Like I said, you're not giving me enough information to work with here.

shamen wrote:

Value(int) StartDate(Date) AccountID(int)

If these are the columns in your table then what is CDOrderFK doing in your query. Please be more explicit.|||I'm sorry CDOrderFK should be replaced with AccountID..Now it should be clear.|||

OK, I would still use the CTE, except you need to use 3 of them and the SQL can a be little simpler.

Code Snippet

WITH yesterdayCTE AS

(

SELECT type = 'CDs Sold Yesterday'

, total_sales = sum(Amount)

, num_accounts = count(DISTINCT AccountID)
, average_sales = avg(Amount)

FROM [CD Table]

WHERE StartDate >= GETDATE()-1

)

, last7days AS

(

SELECT type = 'CDs Sold Last 7 Days'

, total_sales = sum(Amount)

, num_accounts = count(DISTINCT AccountID)
, average_sales = avg(Amount)

FROM [CD Table]

WHERE StartDate >= GETDATE()-7

)

, last30days AS

(

SELECT type = 'CDs Sold Last 7 Days'

, total_sales = sum(Amount)

, num_accounts = count(DISTINCT AccountID)
, average_sales = avg(Amount)

FROM [CD Table]

WHERE StartDate >= GETDATE()-30

)

SELECT * FROM yesterdayCTE

UNION ALL

SELECT * FROM last7days

UNION ALL

SELECT * FROM last30days