So after weeks of torture, this things is near completion. We're using SSRS to display the report on the .NET side. However, I'm piecing together the last bits of the SQL query. So, I have three tables. One has names of all these buildings, one has contains reason_codes, and the last one has three fields: building, reason code, extra_pay.
The users need the report in a certain format. For instance, on the left side, top to bottom, they want ALL the reason codes, on top from left to right, they want all the buildings (think of an excel type report). Then, the extra_pay is shown where a reason code and building meet.
Let's call the tables tblBuildings, tblReasonCodes, and tblMain.
What would be the best way to return all this data? Keep in mind that reason codes don't relate to buildings. For instance, there is no mapping of building a to certain reason codes. Any reason code can be applied to any building, and vice versa.
If I do a simple query like A (not complete), then for each building, I get every work reason code. So if I have 10 buildings and 15 reason codes, then 10 * 15 = 150 rows. I just need the ten buildings, the 15 codes, and my final data from tblMain mapped to it.
Make sense? Best way to do this, thanks :)