April 29, 2016 at 9:35 am
Hi All,
I hope that this is in the correct place! I have a single table with the following columns of interest.
DateOfBusiness FKStoreIdCheckNumberFKItemIdQSQuickComboID
2016-04-01 00:00:00.0006366 30001 9052073064
What it is basically showing, is every single item that is sold and which check it is associated to. What i want to find, is a count for the number of items in a check, when there is a specific item on a check. It is worth noting that there may be multiple instances of the same cheque number, so the primary key for this table would be a combination of DoB, FKStoreID & CheckNumber
I can find all check numbers that contain the relevant item with (note i have added the date in just to reduce the number of results returned):
select CheckNumber
from dpvHstGndItem
where FKItemId = 200112
and DateOfBusiness = '2016-04-01'
And i am attempting to use the results of this to count the number of items on the check numbers that are returned using:
select FKStoreId, DateOfBusiness, CheckNumber, COUNT(fkitemid)
from dpvHstGndItem
where CheckNumber IN (
select CheckNumber
from dpvHstGndItem
where FKItemId = 200112
and DateOfBusiness = '2016-04-01'
)
and DateOfBusiness = '2016-04-01'
group by fkstoreid, DateOfBusiness, CheckNumber
order by fkstoreid, DateOfBusiness
This does return a count of the items on the checks, however it returns a count of the items on all checks, not just ones that contain the FKItemID 200112.
This is my first attempt at this level of complexity of sql for about 10 years since i studied it at University!
April 29, 2016 at 10:20 am
Change the IN for an EXISTS clause.
select FKStoreId, DateOfBusiness, CheckNumber, COUNT(fkitemid)
from dpvHstGndItem o
where EXISTS (
select 1
from dpvHstGndItem i
where FKItemId = 200112
and DateOfBusiness = '2016-04-01'
AND i.CheckNumber = o.CheckNumber
AND i.DateOfBusiness = o.DateOfBusiness
AND i.FKStoreId = o.FKStoreId
)
and DateOfBusiness = '2016-04-01'
group by fkstoreid, DateOfBusiness, CheckNumber
order by fkstoreid, DateOfBusiness
This code might need changes as I can't test it for correctness, but I hope you get the idea. If you don't fully understand it, ask any questions that you might have.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply