Hi Folks
Looking for some help or guidance on creating a report or SQL query. Having a rough time getting my head around this one.
Most transactions have Items from various departments, I need to be able to find all transaction that have items ONLY from a particular department. In other words, all the items for that transaction must be from this department only.
Any help would be appreciated.
Dan
*This post is locked for comments
I used your above suggestion and it's working great thanks. Now taking it a step further and creating a view of the above query. When I create the view in SO Administrator I get result -1 but the view gets created properly. Is this normal. Thanks
Dan
Thanks Ahmed
You are a true SQL/RMS Guru - you were able to do what a lot said couldn't be done.
The query worked as requested - much appreciated. I also learned a lot from your query - so thanks again. Now I'm playing around with your query to get even more data that I required ie: all the transactional data excluding the one from the above query (transactions with all items from department = 'xxx' and between date = x and date = y).
Getting their slowly and thanks to your query I'm learning a lot in the process.
Dan
Dan,
The below query would retrieve "all transaction that have items ONLY from a particular department" as u mentioned, but it doesn't include a time filter, so plz specify exactly the columns and filters u need in the report
You will of course need to replace the xxx with your department name
**********
select * from
(select distinct transactionnumber,count(*) as TransCount
from transactionentry
group by transactionnumber) t1
inner join
(select distinct transactionnumber,count(*) as DepTransCount
from transactionentry te
inner join item on te.itemid=item.id
inner join department on item.departmentid=department.id
where department.name='xxx'
group by transactionnumber) t2
on t1.transcount=t2.deptranscount
and t1.transactionnumber=t2.transactionnumber
********
A Hassan
RMS Leaders
Help me understand the need. Why would anyone need to see a report that only shows a sales transactions that only includes _only_ one department for the entire transaction?
The solution I mentioned only show transaction(s) that include an item in a specfic department. Why can it not includes transactions that have other items on it? If you sell a Doll and a chewing gum and you only want to see transactions that have and Dolls deparment item sold on it, why does it need to be excluded?
Jeff
I was really hoping this could be done, this is a something I really need.
Thanks
Danny
If you are asking for a report that only has item sold on it that only come from one department, I don't think it can't be done.
For a list of all transactions with item(s) sold from a specfic department, the Detailed Sales Report will do it.
Date Sold Between <Dates>
AND Department = <Department Name>
You might want to see the Transaction so right click the body of the report | Show/Hide Columns | select Transaction
#1
André Arnaud de Cal...
292,516
Super User 2025 Season 1
#2
Martin Dráb
231,432
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
