Sheldon Livingston
asked on
Trying to SUM non-duplicate records.
Good morning,
I am trying, with the query below, to grab "scanning" data. From the Transactions table I'm grabbing the user and sales order (SO). I then grab quantities from the soitem table (looking for when SO = fsono).
The query works fine unless someone scans an SO more than once (thus resulting in the record being added again into the transaction table).
How can I grab distinct records from the transaction table and then left outer join the other tables to get me my results?
Thank you in advance.
I am trying, with the query below, to grab "scanning" data. From the Transactions table I'm grabbing the user and sales order (SO). I then grab quantities from the soitem table (looking for when SO = fsono).
The query works fine unless someone scans an SO more than once (thus resulting in the record being added again into the transaction table).
How can I grab distinct records from the transaction table and then left outer join the other tables to get me my results?
Thank you in advance.
Select
Description, sum(FnAltQty) as totalDoorCount, userID, fsono
from
DoorTrak.dbo.Transactions
left outer join DoorTrak.dbo.Entities on DoorTrak.dbo.Transactions.userID = DoorTrak.dbo.Entities.entityID
left outer join M2MDATA01.dbo.soitem on DoorTrak.dbo.Transactions.SO = M2MDATA01.dbo.soitem.fsono
where
userID >= 1000
and
month(dateCreated) = 6
and
day(dateCreated) = 21
and
year(dateCreated) = 2017
and
fprodcl in ('SD', 'SM', 'SR','LV','SL','SS','ST')
group by
userid, description, fsono
order by
userid, fsono
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Provided solution.