Query Results problem, at my wits end....

Question

Post reply

Query Results problem, at my wits end....

TimParker

Ten Centuries

Points: 1023
More actions
July 3, 2012 at 1:48 pm

#260793

I have a query that I am trying to finish up for a report. We track different actions by our consultants, if a consultant hasn't done a specific action during the time frame, it naturally doesn't show up. I need there to be a zero for the entry.
At first, I thought it was an issue with the joins and or the fact that it was dumping the null/zero fields due to aggregates.
We have 3 tables that are being used.
ActionLog - where the data for the actions is kept. One entry per action done.
Events - has the Event names that we are pulling for the report (6 values)
People - Get the Employee Name based on their team and type
I have tried changing the order of the tables queried, the action log has the eventID and the PersonID that gets em to the other two tables. I have an inner join on the People table and was doing a left outer for the events so that I thought I would get all 6 for each user with Zeroes for entries that don't exist.
The result should be:
User 1 Event 1
Event 2
Event 3
Event 4
Event 5
Event 6
User 2 ...
I hope I am just mising something simple. Help? TIA!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Please provide some sample data, table structure, business rules and expected result, like this format:

Data:

IF OBJECT_ID('tempdb..#t') IS NOT NULL

DROP TABLE #t

CREATE TABLE #T

(

ColA INT

,ColB INT

,ColC INT

)

INSERT INTO #T

SELECT 1 , 1, 10

UNION ALL SELECT 1 , 2, 11

UNION ALL SELECT 1 , 3, 12

UNION ALL SELECT 2 , 6, 20

UNION ALL SELECT 2 , 8, 21

UNION ALL SELECT 2 , 7, 22

UNION ALL SELECT 3 , 4, 31

UNION ALL SELECT 3 , 4, 32

UNION ALL SELECT 3 , 3, 33

Business Rlule : Get all the rows that has the MAX of ColB for each value of ColA.

Expected Result:

ColAColBColC

1312

2821

3431

3432

Lowell SSC Guru Points: 323518 More actions · Answer 2

the trick is you are missing a couple of tables, i bet.

you have to get all possible combinations before you join for the totals.

you have to select from something like a calendar table,

left outer join that to a table of all consulktants, and finally left outer join to the data that ahs the time worked;

if you do it like that, you can calculate the time spent, even when zero.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Not really enough information to provide concrete advice. We can't see what you see, so it is difficult from your vague description to help you with the necessary joins between the tables. Please read the first article I reference below in my signature block as it will walk you through what you need to post and how to do it.

Be sure to provide your expected results based on the sample data you should also be providing.

TimParker Ten Centuries Points: 1023 More actions · Answer 4

Thanks for the info so far. I will gather up what is requested and reply again soon. Thought I gave enough info/description. Still learning, appreciate the help!!