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
