June 29, 2017 at 1:40 pm
I'm trying to pull sales for current year and last year for a list of customers. Sales are coming from 2 tables and the key is the customer ID. However the customer may not have data for both years so the ID may not exist in both tables. I could use a FULL OUTER but I don't get a cust id for last years sales. I also tried a UNION ALL but I want each years sales to be in it's own column. With a UNION I get two rows and I need three.
CREATE TABLE CYsales (cust_id varchar(5), sales decimal(4,0))
CREATE TABLE LYsales (cust_id varchar(5), sales decimal(4,0))
INSERT INTO CYsales (cust_id, sales)
VALUES ('Cust1', 4000)
, ('Cust2', 3000)
, ('Cust5', 1500)
INSERT INTO LYsales (cust_id, sales)
VALUES ('Cust1', 2000)
, ('Cust3', 1200)
, ('Cust4', 5000)
, ('Cust5', 3500)
SELECT CYsales.cust_id
, CYsales.sales
, LYsales.sales
FROM CYsales
FULL OUTER JOIN LYsales ON LYsales.cust_id = CYsales.cust_id
---------------------------
SELECT CYsales.cust_id
, CYsales.sales
FROM CYsales
UNION ALL
SELECT LYsales.cust_id
, LYsales.sales
FROM LYsales
What I hope to get is:
Cust1 4000 2000
Cust2 3000 NULL
Cust3 NULL 1200
Cust4 NULL 5000
Cust5 1500 3500
June 29, 2017 at 2:33 pm
I got this one:SELECT CASE WHEN [CYsales].[cust_id] IS NULL THEN [LYsales].[cust_id] ELSE [CYsales].[cust_id] END AS [cust_ID]
, [CYsales].[sales]
, [LYsales].[sales]
FROM [CYsales]
FULL OUTER JOIN [LYsales] ON [LYsales].[cust_id] = [CYsales].[cust_id]
ORDER BY cust_ID
Does that suit your needs? I ran the above and I getcust_ID sales sales
Cust1 4000 2000
Cust2 3000 NULL
Cust3 NULL 1200
Cust4 NULL 5000
Cust5 1500 3500
EDIT - edited for formatting and to change the temp tables to real tables. I used temp tables while working on this to ensure things worked before posting.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 30, 2017 at 7:02 am
That's perfect. Thanks so much. I can mold this into a CTE and combine into my other six tables to complete the report. Thanks again.
July 3, 2017 at 3:17 pm
Perfect case for the sexy COALESCE function:
SELECT
COALESCE([CYsales].[cust_id], [LYsales].[cust_id]) AS [cust_ID]
, [CYsales].[sales]
, [LYsales].[sales]
FROM [CYsales]
FULL OUTER JOIN [LYsales] ON [LYsales].[cust_id] = [CYsales].[cust_id]
ORDER BY cust_ID
July 3, 2017 at 3:37 pm
autoexcrement - Monday, July 3, 2017 3:17 PMPerfect case for the sexy COALESCE function:
SELECT
COALESCE([CYsales].[cust_id], [LYsales].[cust_id]) AS [cust_ID]
, [CYsales].[sales]
, [LYsales].[sales]
FROM [CYsales]
FULL OUTER JOIN [LYsales] ON [LYsales].[cust_id] = [CYsales].[cust_id]
ORDER BY cust_ID
Yes. COALESCE (or even ISNULL) is more pretty in the code than that big ugly CASE... My brain just went for the first solution I could think of. Which is also how I usually end up with cursors. Thankfully with production code, I tweak it before it goes live so I don't have to worry about cursors hitting a live server.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply