HELP! Query not properly displaying data
-
I have prepared a query which produces results as such:
STORE_ID DBR_DATE TAXABLE_SALES NON_TAXABLE_SALES FUEL_SALE FUEL_TAX FUEL_MAN
-------- --------------------------- ----------------- ----------------------------------
7700 2003-03-27 1709.01 1183.70 14282.966025 1199.923975 164.90
7703 2003-03-27 .00 2252.53 10373.088150 871.451850 164.90
7704 2003-03-27 .00 2800.21 9528.410250 800.489750 164.90
7707 2003-03-27 .00 2946.54 17307.068625 1453.981375 164.90
7708 2003-03-27 1594.05 1025.45 16051.850550 1348.529450 164.90
7709 2003-03-27 .00 3537.52 20195.102475 1696.607525 164.90
7710 2003-03-27 1894.27 1097.28 9127.307250 766.792750 164.90
7712 2003-03-27 .00 2368.38 7105.030425 596.899579 164.90
7717 2003-03-27 1157.23 877.61 12598.886925 1058.443075 164.90
7723 2003-03-27 1147.08 1050.09 12218.992200 1026.527800 164.90
7724 2003-03-27 .00 2059.71 21845.593350 1835.266650 164.90
7728 2003-03-27 1966.40 693.69 10109.400750 849.299250 164.90
(12 row(s) affected)
However, the final column (Fuel_Man) should only have data (164.90)from one store, 7704.
I can't figure out what's wrong with my query...The final column is a renamed field called 'Amount'. I am pulling information from two tables, and I am trying to get the final column to populate correctly. Hopefully, even if I don't get any help, this problem will be of use to others. Here is my query so far...
-- Set the dateformat to ignore the time
Set dateformat mdy
Declare @Start_Date datetime,
@End_Date datetime,
@Store_ID varchar(10)
set @start_date = '03/27/2003'
set @End_date = '03/27/2003'
set @Store_ID = '77%'
SELECT a.STORE_ID,
a.DBR_DATE,
a.TAXABLE_SALES,
SUM(a.DEPT_SALES-a.TAXABLE_SALES) AS NON_TAXABLE_SALES,
SUM(a.FUEL_SALES-(a.FUEL_SALES*.0775)) AS FUEL_SALE,
SUM(a.FUEL_SALES*.0775) AS FUEL_TAX,
b.AMOUNT AS 'FUEL_MAN'
FROM CSCRDTTL b RIGHT OUTER JOIN CSCSHBAL a
ON a.DBR_DATE=b.DBR_DATE
WHERE (b.CARD_NAME = 'FUELMAN' AND a.STORE_ID LIKE @STORE_ID AND a.DBR_DATE>=@START_DATE
AND a.DBR_DATE<=@END_DATE)
GROUP BY a.STORE_ID, a.DBR_DATE, a.TAXABLE_SALES, b.AMOUNT
Thanks in advance for any help that anyone can offer.
Yours in db...
-
Should'nt a.STORE_ID be part of the join between the tables
-
Thanks for replying...when I join by store ID and by busines date, all I get is the one store (7704) that has the (Fuelman) sales...I need all the stores, plus whichever stores that have sales in Fuelman...
-
TO be clear - if you change:
...
FROM CSCRDTTL b RIGHT OUTER JOIN CSCSHBAL a
ON a.DBR_DATE=b.DBR_DATE
WHERE ...
to:
...
FROM CSCRDTTL b RIGHT OUTER JOIN CSCSHBAL a
ON (a.DBR_DATE=b.DBR_DATE and a.STORE_ID = b.STORE_ID)
WHERE ...
you don't get all the [a] records?
R David Francis -
STORE_ID DBR_DATE TAXABLE_SALES NON_TAXABLE_SALES FUEL_SALE FUEL_TAX FUEL_MAN
-------- --------------------------- ----------------- ----------------------------------
7704 2003-03-27 .0 2800.21 9528.410250 800.489750 164.90
(1 row(s) affected)
This is what I see when I run this code:
-- Set the dateformat to ignore the time
Set dateformat mdy
Declare @Start_Date datetime,
@End_Date datetime,
@Store_ID varchar(10)
set @start_date = '03/27/2003'
set @End_date = '03/27/2003'
set @Store_ID = '%77%'
SELECT a.STORE_ID,
a.DBR_DATE,
a.TAXABLE_SALES,
SUM(a.DEPT_SALES-a.TAXABLE_SALES) AS NON_TAXABLE_SALES,
SUM(a.FUEL_SALES-(a.FUEL_SALES*.0775)) AS FUEL_SALE,
SUM(a.FUEL_SALES*.0775) AS FUEL_TAX,
b.AMOUNT AS FUEL_MAN
FROM CSCSHBAL a RIGHT OUTER JOIN CSCRDTTL b ON
(a.DBR_DATE=b.DBR_DATE AND a.STORE_ID = b.STORE_ID)
WHERE (b.CARD_NAME ='FUELMAN' AND a.STORE_ID LIKE @STORE_ID AND a.DBR_DATE>=@START_DATE
AND a.DBR_DATE<=@END_DATE)
GROUP BY a.STORE_ID, a.DBR_DATE, a.TAXABLE_SALES, b.AMOUNT
-
What happens if you switch it to a LEFT OUTER JOIN? I always get a bit dyslexic with these things....
R David Francis -
Quick note: you swapped the sides of the join between your first chunk of code and your second. The CSCSHBAL table should be the one the join direction referes to (on the right for RIGHT, on the left for LEFT)
R David Francis -
The code changed because I was trying out left and right joins, and I was swapping the table names back and forth. I'm getting desperate! 🙂 I wanted to try all the different combinations of joins and table name layouts. But the results stay the same as my last posting...only one row displaying which store had the Fuelman sales, or all the stores, but all showing the sales posted for store number 7704.
What about a Union? Is there a way to use that? Someone at work suggested that I use temp tables...is there a way to use temp tables to get my desired results?
-
I think the problem is here:
WHERE (b.CARD_NAME = 'FUELMAN' AND ...
For all of the rows of a that don't have a matching b row, b.CARD_NAME is NULL. So, you might try:
WHERE ((b.Amount IS NULL OR b.CARD_NAME = 'FUELMAN') AND ...
This may not work, depending on the possible options for the values in table . You might need to tinker with the "pass" test (where you let a rows that weren't matched with a b row pass), or you might add the b.CARD_NAME = 'FUELMAN' test into the join condition.
See if that helps, and let us know!
R David Francis -
SUM(a.FUEL_SALES*.0775) AS FUEL_TAX,
b.AMOUNT AS FUEL_MAN
FROM CSCSHBAL a LEFT OUTER JOIN CSCRDTTL b ON
(b.CARD_NAME='FUELMAN' AND a.DBR_DATE=b.DBR_DATE AND a.STORE_ID = b.STORE_ID)
WHERE (a.STORE_ID LIKE @STORE_ID AND a.DBR_DATE>=@START_DATE
AND a.DBR_DATE<=@END_DATE)
This code got me this result:
STORE_ID DBR_DATE TAXABLE_SALES NON_TAXABLE_SALES FUEL_SALE FUEL_TAX FUEL_MAN
-------- --------------------------- ----------------- ----------------------------------
7700 2003-03-27 1709.01 1183.70 14282.966025 1199.923975 NULL
7703 2003-03-27 .00 2252.53 10373.088150 871.451850 NULL
7704 2003-03-27 .00 2800.21 9528.410250 800.489750 164.90
7707 2003-03-27 .00 2946.54 17307.068625 1453.981375 NULL
Thanks everyone, for your help!!!
I'll definitely be back to help others the way you all saved my ass, I mean, helped me!!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply