Calcaluting total nu of people based on their name field

shamen

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

Adam Tappis - IMGroup

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.

shamen

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

Adam Tappis - IMGroup

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.

shamen

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

shamen

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

Thanks lot

shamen

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.

shamen

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

Adam Tappis - IMGroup

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.

shamen

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

Adam Tappis - IMGroup

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.

shamen

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.

Adam Tappis - IMGroup

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.

shamen

I'm sorry CDOrderFK should be replaced with AccountID..Now it should be clear.

Adam Tappis - IMGroup

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