Hey,
So i'm relatively new to using SQL, or at least haven't used it since my college days and therefor can't seem to get something pretty basic to work.
I have ONE table called TRACKING_TRAIN that tracks all activities from one user. Each activity is assigned a code. The table captures the transaction code, the time it was done, and the username of who did the transaction.
I'm trying to pull bits and pieces of this information into 1 table as a means of measuring someones productivity by the number of actions on a certain day.
Example of what I want my table to look like
Name | Action 1 | Action 2 | Action 3 |
------------------------------------------
John | 34 | 2 | 4 |
Sarah | 2 | 2 | 3 |
Because all actions are a different I'm using 3 different queries. The first pulls the user_ID and counts the number of items that were touched using Action 1, grouping it by User_ID. That part works fine. Once I add my additional queries I don't really know how to merge them so I get duplicate results of the name field.
Here's an example of my code: -
-
SELECT User_ID, count(DISTINCT object_Id) as Action 1, null as Action 2, null as Action 3
-
FROM TABLE 1
-
WHERE tran_type = '010'
-
AND tran_code = '004'
-
AND TRUNC(MOD_DATE_TIME) = TRUNC(SYSDATE -6)
-
Group by user_id
-
UNION
-
SELECT User_ID, null as Action 1, count(DISTINCT object_Id) as Action 2, null as Action 3
-
FROM TABLE 1
-
WHERE tran_type = '500'
-
AND tran_code = '003'
-
AND TRUNC(MOD_DATE_TIME) = TRUNC(SYSDATE -6)
-
Group by user_id
-
UNION
-
SELECT User_ID, null as Action 1, null as Action 2, count(DISTINCT object_Id) as Action 3
-
FROM TABLE 1
-
WHERE tran_type = '100'
-
AND tran_code = '090'
-
AND TRUNC(MOD_DATE_TIME) = TRUNC(SYSDATE -6)
-
Group by user_id
-
-
I'm probably making a pretty dumb mistake, but it's been years since college and I never really paid attention in my SQL class. Be gentle.
11 1650
You should union them all and then do the count. Not count each separately and then union them.
But they're all from the same table and going to the same table. The action id numbers all come from the same column. Does that make sense? I'm missing something pretty obvious somewhere.
I'm just confused now. It would help to see sample input data and resulting output data.
I'll try. The table i'm getting info from will look something like this:
User_ID.|.Transaction_Type.|.Transaction_Code.|... Date...|
----------------------------------------------------------
ROBERT..|........800.......|.......004........|201 1-12-29|
GOLLUM..|........700.......|.......009........|201 1-12-29|
GOLLUM..|........500.......|.......001........|201 1-12-28|
GOLLUM..|........500.......|.......001........|201 1-12-29|
ROBERT..|........800.......|.......004........|201 1-12-29|
GOLLUM..|........500.......|.......001........|201 1-12-29|
GOLLUM..|........800.......|.......004........|201 1-12-29|
GOLLUM..|........700.......|.......009........|201 1-12-29|
The Transaction_Type and Transaction_Code when combined end up being the equivalent of an action. Like an order being placed, picked, shipped, etc.
So what I want from the table above, is a breakdown of actions from a person on a given day. (as an example, the 29th of december)
Something like this:
Username |.800.004.|.700.009.|.500.001.| <--- numbers = action taken
----------------------------------------
ROBERT...|....2....|....0....|....0....|
GOLLUM...|....1....|....2....|....2....|
I'm hoping that makes more sense.
You're looking for a pivot query but that's going to be unyieldy depending on how many combinations of transaction type and code you have.
I was concerned about having to do a subquery for every group in the main query, but your solution seems to have sidestepped this. It looks like it would only be the two subqueries regardless of how many groups there were. I think that should give you what you are looking for. Use the second query as your row source, then set the first column (UserRow) width to zero. I built a table that looks just like yours (at least what you described below), and then built these two queries. They came out looking precisely the way you asked for it.
Thanks,
Jackie
@Rabbit
I'm only looking for 3 combinations.
Transaction_Type | Transaction Code
-----------------------------------
.......800.......|.........004.........
.......800.......|.........009.........
.......998.......|.........001.........
@JackieBolinsky
I had already tried doing subqueries in my initial select statement but the problem I got was that it returns the total number across all users and then slaps it under every single username. It might be that I put my subqueries in the wrong spot but I honestly don't see where else/how I can put them to get the desired result.
I forgot how rigid SQL was and suddenly remember why I stopped pursuing business programming/database management stuffs in college.
Oh, just 3? Then definitely use the pivot.
Now that you mentioned that you're looking for specific actions, that will make your life a little easier.
Try this -
-
with YourTransactionTable
-
as
-
(select cast(null as varchar(10)) as UserID,cast(null as smallint) as Transaction_Type, cast('' as varchar(5)) as Transaction_Code,cast(null as Datetime) as trandate
-
union all
-
select 'ROBERT','800','004','2011-12-29'
-
union all
-
select 'GOLLUM','700','009','2011-12-29'
-
union all
-
select 'GOLLUM','500','001','2011-12-28'
-
union all
-
select 'GOLLUM','500','001','2011-12-29'
-
union all
-
select 'ROBERT','800','004','2011-12-29'
-
union all
-
select 'GOLLUM','500','001','2011-12-29'
-
union all
-
select 'GOLLUM','800','004','2011-12-29'
-
union all
-
select 'GOLLUM','700','009','2011-12-29'
-
)
-
select userid, Act_800_004, Act_800_009, Act_998_001
-
from
-
(
-
select
-
userid, ActionTaken = 'Act_' + cast(Transaction_Type as varchar(5)) + '_' + Transaction_Code
-
from YourTransactionTable
-
where UserId is not null
-
and 'Act_' + cast(Transaction_Type as varchar(5)) + '_' + Transaction_Code in ('Act_800_004','Act_800_009','Act_998_001')
-
) as P
-
PIVOT
-
(
-
count(ActionTaken) for ActionTaken in ([Act_800_004],[Act_800_009],[Act_998_001])
-
) as YourRequestedOutput;
-
Forget about the CTE, that's just to have a sample data to present to you.
Happy Coding!!!
~~ CK
I actually got it to work the way I wanted to today using nested CASE statements with a SUM function. I'll post it tomorrow when I get access to the computer that I typed it up on again. There's a bit of fine tuning needed for little bits and pieces, but the core of it seems to work and doesn't take a huge amount of time to process.
Thanks for trying to help me get back on my coding horse.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: caro |
last post by:
Hi
I am trying to write two Select * statements to the same text file
using bcp (from a stored procedure).
But cannot find a way of appending to a file using bcp.
Does anyone know if this...
|
by: ctoth |
last post by:
I'm writing an ASP page for a project and it requires multiple queries.
However, I'm trying to combine multiple SELECT statements, but can't
figure out a way that actually works.
Basically, here...
|
by: Gord |
last post by:
I would like to create a summary report from the results of 11 queries
(based on 2 tables). All the queries have the same format and return
3 numbers (Count, Current Year Bal, Last Year Bal.)...
|
by: Dave Edwards |
last post by:
I understand that I can fill a datagrid with multiple queries, but I cannot
figure out how to fill a dataset with the same query but run against
multiple SQL servers, the query , table structure...
|
by: beretta819 |
last post by:
Ok, so I apologize in advance for the wordiness of what follows... (I am not looking for someone to make this for me, but to point me in the right direction for the steps I need to take.)
I was...
|
by: vaiism |
last post by:
I am creating a report that outputs the contact information and details about a water treatment plant, and needs to include information about people who work there.
If I tie all the information...
|
by: showmegeek |
last post by:
I have one main table and multiple queries that give back information from different distribution list, what I would like to do is create one report that is used genericly to give me the information...
|
by: Akhenaten |
last post by:
I am currently using enterprise manager to run multiple queries on a
single table in a DB. I refresh these queries every few minutes. Due
to the huge number of them I was looking for a better way...
|
by: DigiLife |
last post by:
Greetings all,
I am trying to create a form in access 2007 that will return multiple queries (count). I have an appointment table and based off of the date selection I want a grid similar to "Cheap...
|
by: dougancil |
last post by:
I have multiple queries that I need to run when a user clicks a button but they are all separate queries. Can this be done?
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |