April 20, 2010 at 3:41 pm
Hello all,
Hopefully you can help me with this query:
SELECT C.LOCATION_NAME,SUM(A.SALE_AMOUNT)
FROM SALES A, ACCOUNT B, LOCATION C, OPER_DAY D
WHERE D.DATE_ID = A.DATE_ID
AND A.SALES_ID = B.SALES_ID
AND B.LOCATION_ID = C.LOCATION_ID
AND C.LOCATION_ID IN (1, 2)
AND D.DATE IN ('04/19/2009', '04/20/2009')
This is currently outputing:
LOCATION_NAME SALES
---------------- -------
LOCATION1 $100
Basically the output I want will be something like this
LOCATION_NAME SALES
---------------- -------
LOCATION1 $100
LOCATION2 $0
But location 2 is not showing up when there's no sale for that date, however I would like to display $0 if this is the case. Hopefully this could be done without a T-SQL procedure. Any help will be appreciated on this!!
Thanks
April 20, 2010 at 4:08 pm
just use the ISNULL function to convert the null, i would think should work just fine:
SELECT
C.LOCATION_NAME,
SUM(ISNULL(A.SALE_AMOUNT,0.00)) AS SALE_AMOUNT
FROM SALES A,
LEFT OUTER JOIN ACCOUNT B ON A.SALES_ID = B.SALES_ID
LEFT OUTER JOIN LOCATION C B.LOCATION_ID = C.LOCATION_ID
LEFT OUTER JOIN OPER_DAY D ON D.DATE_ID = A.DATE_ID
WHERE C.LOCATION_ID IN (1, 2)
AND D.DATE IN ('04/19/2009', '04/20/2009')
EDIT: because the joins are all INNER joins due to the old ansi syntax, you need to change it to outer joins and alos join against a master table of locations; so if there are no sales at all, but you need a total, you have to make the first/prime table LOCATION, and join to that:
SELECT
C.LOCATION_NAME,
SUM(ISNULL(A.SALE_AMOUNT,0.00)) AS SALE_AMOUNT
FROM LOCATION C
LEFT OUTER JOIN ACCOUNT B B.LOCATION_ID = C.LOCATION_ID
LEFT OUTER JOIN SALES A ON A.SALES_ID = B.SALES_ID
LEFT OUTER JOIN OPER_DAY D ON D.DATE_ID = A.DATE_ID
WHERE C.LOCATION_ID IN (1, 2)
AND D.DATE IN ('04/19/2009', '04/20/2009')
Lowell
April 20, 2010 at 8:37 pm
Hello Lowell,
Thanks for the prompt reply.
I forgot to mention that I have already tried the ISNULL function earlier and it still didn't work (as I was looking for the MS SQL equivalent of Oracle's NVL function).
It is still showing only one record. I have even tried with WHERE EXISTS but no luck.
FYI I also added to the end of the statement: GROUP BY C.LOCATION_NAME
BY the way this is SQL 2005.
Thanks
April 21, 2010 at 1:46 am
I think the script below will solve your problem.
By the way, next if you time post CREATE TABLE and INSERT statements it will be much easier for people to help.
DROP TABLE SALES
DROP TABLE ACCOUNT
DROP TABLE LOCATION
DROP TABLE OPER_DAY
GO
CREATE TABLE SALES(SALES_ID int, DATE_ID int, SALE_AMOUNT numeric(12,2))
CREATE TABLE ACCOUNT(LOCATION_ID int, SALES_ID int)
CREATE TABLE LOCATION(LOCATION_ID int, LOCATION_NAME varchar(50))
CREATE TABLE OPER_DAY(DATE_ID int, [DATE] datetime)
INSERT INTO LOCATION
VALUES (1, 'Loc 1'), (2, 'Loc 2'), (3, 'Loc 3')
INSERT INTO ACCOUNT
VALUES (1,1),(2,2),(3,3)
INSERT INTO OPER_DAY
VALUES (1,'20090419'),(2,'20090420'),(3,'20090421')
INSERT INTO SALES
VALUES
(1, 1, 10),
(1, 2, 20),
(1, 3, 30),
(2, 3, 200),
(3, 1, 1000)
;WITH Result
AS (
SELECT C.LOCATION_ID, C.LOCATION_NAME, SUM(A.SALE_AMOUNT) AS SALE_AMOUNT
FROM SALES A, ACCOUNT B, LOCATION C, OPER_DAY D
WHERE D.DATE_ID = A.DATE_ID
AND A.SALES_ID = B.SALES_ID
AND B.LOCATION_ID = C.LOCATION_ID
AND C.LOCATION_ID IN (1, 2)
AND D.DATE IN ('04/19/2009', '04/20/2009')
GROUP BY C.LOCATION_ID, C.LOCATION_NAME
)
SELECT LOCATION_NAME, SALE_AMOUNT
FROM Result
UNION ALL
SELECT LOCATION_NAME, 0
FROM LOCATION L
WHERE
L.LOCATION_ID NOT IN (SELECT LOCATION_ID FROM Result)
AND LOCATION_ID IN (1,2)
By the way, this problem smells like a school problem. Are you using us to do your homework ? 😉
Also, the Oracle NVL function is the same as the SQL Server ISNULL function, but it wont help you with this problem.
/SG
April 21, 2010 at 8:21 am
Stefan_G,
Thanks a bunch, your solution works. I did not not drop my tables though as they have production data.
By the way I am not a student, whish I could think of forums to do my homework when I was in school, 🙂
Even for work stuff, this is the first time I had to post a problem. (Usually google results were enough)
Thanks again to all of you
April 21, 2010 at 8:26 am
MB_DB (4/21/2010)
I did not not drop my tables though as they have production data.
Good. I probably should not have included those DROP TABLE statements in my script. Glad you did not just run it blindly 😛
/SG
April 21, 2010 at 11:42 am
Just thought about this, what if I wanted to add a field called OLD_SALES_AMOUNT which will be another select for the prior year sales?
The output fields will be LOCATION_NAME, SALES_AMOUNT, OLD_SALES_AMOUNT and their respectives values.
April 21, 2010 at 12:53 pm
MB_DB (4/21/2010)
Just thought about this, what if I wanted to add a field called OLD_SALES_AMOUNT which will be another select for the prior year sales?The output fields will be LOCATION_NAME, SALES_AMOUNT, OLD_SALES_AMOUNT and their respectives values.
I would run two queries like the one I posted before, one for the current year, and another for the previous year. I would save the output of these queries to two temp tables, and I would then join these temp tables. Something like this:
SELECT LOCATION_NAME, SALES_AMOUNT
INTO #t1
FROM ...
WHERE date = this year AND ...
SELECT LOCATION_NAME, SALES_AMOUNT
INTO #t2
FROM ...
WHERE date = last year AND ...
SELECT t1.LOCATION_NAME, t1.SALES_AMOUNT, t2_SALES_AMOUNT as OLD_SALES_AMOUNT
FROM #t1 t1, #t2 t2
WHERE t1.LOCATION_NAME = t2.LOCATION_NAME
/SG
April 21, 2010 at 1:10 pm
Cool, thanks!!!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply