|
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
Combining queries
I need help combining two queries. Doing a 'UNION' displays the data as follows:
1 4786.54 1 78371.00 2 1082.53 2 11695.65 Instead, i want my data displayed as 1 4786.54 78371.00 2 1082.53 11695.65 Here are the queries SELECT DailyTran.dlt_locfk AS 'Location', SUM(DailyTran.dlt_amt) AS 'Total Gas Sales' FROM DailyTran INNER JOIN .GLedger ON DailyTran.dlt_glfk = GLedger.gld_pk INNER JOIN Locations ON DailyTran.dlt_locfk = Locations.loc_pk AND GLedger.gld_locfk = Locations.loc_pk WHERE ((GLedger.gld_glno = '33101') OR (GLedger.gld_glno = '33111') OR (Gledger.gld_glno = '33121')) AND (DailyTran.dlt_date = '7/20/2004') GROUP BY DailyTran.dlt_locfk UNION SELECT DailyTran.dlt_locfk AS 'Location', SUM(DailyTran.dlt_amt) AS 'Total Credit Card Sales' FROM DailyTran INNER JOIN .GLedger ON DailyTran.dlt_glfk = GLedger.gld_pk INNER JOIN Locations ON DailyTran.dlt_locfk = Locations.loc_pk AND GLedger.gld_locfk = Locations.loc_pk WHERE (GLedger.gld_glno = '11080') AND (DailyTran.dlt_date BETWEEN '5/01/2004' AND getDATE()) GROUP BY DailyTran.dlt_locfk |
|
#2
|
||||
|
||||
|
Which values in the query are passed in?
|
|
#3
|
|||
|
|||
|
I have 3 tables:
DailyTran that has daily transactions. (everything that rings up at the retail register). It contains 'date', 'amt', 'gl no'..glno is the category number for the item. So, this table may contain multiple items with same date and glnos. The next table is the GLedger which contains 'glno' , 'locid', 'Item Description'. e.g. 33121 1 Gas Sales -Unleaded'. Last table is the location that contains 'locid', 'loc_no', 'loc_description'...different location ids for different retail outlets. I have to display say for eg. Total Gas Sales, Total Gas Gallons, YTD for the day for locations 1-7. To do this i pass gl_no and date to the DailyTran table and the GLedger table. However a union of the 2 sql statements displays results as i described before. Instead i want the specified display. Hope this helps understanding my question better. |
![]() |
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > Combining queries |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|