SQL Calculations With Multi-Group Affiliations
I'm attempting to have a function or view that is able to calculate and roll up various counts while being able to search on a many to many affiliation.
Here is an example data set:
Invoice Table: InvoiceID LocationID StatusID 1 5 1 2 5 1 3 5 1 4 5 2 5 7 2 5 7 1 5 7 2 Group Table: GroupID GroupName 1 Group 1 2 Group 2 GroupToLocation Table: GroupToLocationID GroupID LocationID 1 1 5 2 2 5 3 2 7
I have gotten to the point where I could sum up the various statuses per location and get this:
LocationID Status1 Status2 5 3 1 7 1 2
Location 5 has 3 Invoices with a status of 1, and 1 invoice with a status of 2 while Location 7 has 1 status 1 and 2 status 2
There are two groups, and Location 5 is in both, while Location 7 is only in the second. I need to be able to set it up where I can append a where statement like this:
select * from vw_GroupCounts where GroupName = 'Group 2'
select Invoice, SUM(*) from vw_GroupCounts where GroupName = 'Group 2'
And that result in only getting Location 7. Whenever I do this, as I have to use left joins or something along those lines, the counts are duplicating for each group the the Location is affiliated with. I know I could do something along the lines of a subquery and pass in the GroupName into that, but the system I am working with uses a dynamic query builder that appends WHERE statements based on user input.
I don't mind using view, or functions, or any number of functions inside of functions, but I hope there is a way to do what I'm looking for.
Since locations 5 and 7 are in Group 2, if you search for group 2 in the where clause after joining all the tables, then you would get all records in this case, this isn't duplication, just the way the data is. A different join wouldn't change this, only changing the data. Let me know if I am misunderstanding something though.
Here is how you would join them to do that search.
Here it is with your first example of the location and status count.