[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Mixed Report with Weekly and Monthly Data

Posted on 2009-12-29
5
Medium Priority
?
761 Views
Last Modified: 2012-05-08
We use Access as the front end and SQL as the back end.  I like to create the queries in SQL and then just create a basic query in Access which will have all the date filters based on a form selection.

So I get several excel source spreadsheets.  Which I import into SQL Tables.  In the attached spreadsheet it gives you and idea of the structure of each table and the data within.  Basically a sales person can sell through the company (phones), through an online portal or via some third party software called Nexus.

The data I recieve comes in Monthly for the online portal, the company sales in one spreadsheet.  The nexus information also comes monthly but separately.

Also each week I recieve the weekly sales via the nexus software.

So the only common link (for all sources) is the name.  Also a sales person may have sales in the nexus software for a given month but no sales via the company or portal so they might not appear in that source.....so no link available.  Also there may be entries in the Nexus weekly source but nothing in the monthly since we only get that information at the end of the month.

The other source is a reference table which indicates each sales persons region and manager.  

So the report they want (last sheet of the attached) is a report which indicates the region and manager for each person, the last three weeks of Nexus sales and the last three months of sales activity for all possible channels.

This isnt straight forward right?  I am not completely dumb....just very tired.  But because of all these sources being almost completly independant from one another its the JOINS that are killing me.  How can the sample report be accomplished?

(Keep in mind that I have really simplified it as the reference table has 2000 sales staff and on top of that they are spelt different across data sources so I have to create an alias table for the name field)

Forgive me but I am so tired and burnt out I cant think straight but hopefully someone at EE can get me the help/advice by tomorrow :)


Example.xls
0
Comment
Question by:Wedmore
  • 3
  • 2
5 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 26144192
You could union them up instead of joining them.
SELECT Salesperson, Date, SUM(AMOUNT) As Amount
FROM (
SELECT Salesperson, Date, Amount
FROM Nexus
UNION ALL
SELECT Salesperson, Date, Amount
FROM Company
UNION ALL
SELECT Salesperson, Date, Amount
FROM Portal
) A
GROUP BY Salesperson, Date
That will combine the salespeople at the lowest data level from each source. You then amy need to outer join to a salesperson list to get missing salespeople.
That doesn't cover anywhere near all of your request but hopefully points you in the right direction...... now go and have a sleep!

 
0
 

Author Comment

by:Wedmore
ID: 26145521
I was always under the impression UNIONs should be avoided...
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 1500 total points
ID: 26163457
Is there any specific reason? Every SQL operator has advantages and disadvantages. Certainly its a common mistake to use UNION instead of UNION ALL, as UNION by itself forces a DISTINCT, which in turn generally forces a sort of data, which is an expensive operation.
You should use whatever operators solve your problem within whatever parameters you have. In this case you may find UNION is quicker then performing three full outer joins (which is the other 'direct' way of performing this 'merge' of data)
0
 

Author Comment

by:Wedmore
ID: 26280279
Not really a specific reason.  Just a rumour I heard you know - probably the worst way to hear things I guess.  

I solved it in a completely different approach with separte queries on the selected date.  So I had one query that pulled the dates for the three previous weeks and one query for the three previous months.  Then I create queries for each table to join on their respective date query and summarise the data.  
Finally I join all three queries onto the reference table.  

Long winded maybe but it works surprisingly quickly.  But your comment did provide me with new insight on UNIONs so shared points.
0
 

Author Closing Comment

by:Wedmore
ID: 31671065
Not the solution I went with but provided something I wasnt aware of.  Cant split or reduce points though.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question