Is it possible with SQL to combine 2 Select statements results together in one temp table.

I have 2 Select statements on a View (vReportData). I've done them separately as I thought it would be too complex to do them together.  However, I need the results of both SELECT statements to be combined for my report.  Is there a way to execute both these select statements but displaying the results in one temporary table so I can use the temp table for my report (in ASP.Net).  Both my SELECT statements are as follows:

SELECT
    Port, Boat,
    SUM(CASE WHEN Product_Name = 'Lobsters' THEN ProductKiloQty ELSE 0 END) AS [LobstersKG],
    SUM(CASE WHEN Product_Name LIKE '%Crabs' THEN ProductKiloQty ELSE 0 END) AS [CrabsKG],
    SUM(CASE WHEN Product_Name LIKE 'Velvet Crabs%' THEN ProductKiloQty ELSE 0 END) AS [VelvetsKG],
    SUM(CASE WHEN Product_Name LIKE 'Langoustine%' THEN ProductKiloQty ELSE 0 END) AS [LangKG],
    SUM(CASE WHEN Product_Name = 'Crabs (Cut)' THEN ProductKiloQty ELSE 0 END) AS [CutCrKG]
FROM vReportData
WHERE Landing_Day = 5
AND DateOfCatch BETWEEN DATEADD(day, - 8, @EndDate) AND DATEADD(day, - 1, @EndDate)
GROUP BY
    Port, Boat
ORDER BY --optional, of course
    Port, Boat
      RETURN

Then the same except Landing_Day = 6:

      SELECT
    Port, Boat,
    SUM(CASE WHEN Product_Name = 'Lobsters' THEN ProductKiloQty ELSE 0 END) AS [LobstersKG],
    SUM(CASE WHEN Product_Name LIKE '%Crabs' THEN ProductKiloQty ELSE 0 END) AS [CrabsKG],
    SUM(CASE WHEN Product_Name LIKE 'Velvet Crabs%' THEN ProductKiloQty ELSE 0 END) AS [VelvetsKG],
    SUM(CASE WHEN Product_Name LIKE 'Langoustine%' THEN ProductKiloQty ELSE 0 END) AS [LangKG],
    SUM(CASE WHEN Product_Name = 'Crabs (Cut)' THEN ProductKiloQty ELSE 0 END) AS [CutCrKG]
FROM vReportData
WHERE Landing_Day = 6
AND DateOfCatch BETWEEN DATEADD(day, - 8, @EndDate) AND DATEADD(day, - 1, @EndDate)
GROUP BY
    Port, Boat
ORDER BY --optional, of course
    Port, Boat
      RETURN
Lorna70Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
why not just add the Landing_Day criteria to the group by ?

   SELECT
    Port, Boat, Landing_Day,
    SUM(CASE WHEN Product_Name = 'Lobsters' THEN ProductKiloQty ELSE 0 END) AS [LobstersKG],
    SUM(CASE WHEN Product_Name LIKE '%Crabs' THEN ProductKiloQty ELSE 0 END) AS [CrabsKG],
    SUM(CASE WHEN Product_Name LIKE 'Velvet Crabs%' THEN ProductKiloQty ELSE 0 END) AS [VelvetsKG],
    SUM(CASE WHEN Product_Name LIKE 'Langoustine%' THEN ProductKiloQty ELSE 0 END) AS [LangKG],
    SUM(CASE WHEN Product_Name = 'Crabs (Cut)' THEN ProductKiloQty ELSE 0 END) AS [CutCrKG]
FROM vReportData
WHERE Landing_Day in (5, 6)
AND DateOfCatch BETWEEN DATEADD(day, - 8, @EndDate) AND DATEADD(day, - 1, @EndDate)
GROUP BY
    Port, Boat, Landing_Day
ORDER BY --optional, of course
    Port, Boat, Landing_Day

Open in new window

Lee SavidgeCommented:
Use a union.

SELECT
    Port, Boat,
    SUM(CASE WHEN Product_Name = 'Lobsters' THEN ProductKiloQty ELSE 0 END) AS [LobstersKG],
    SUM(CASE WHEN Product_Name LIKE '%Crabs' THEN ProductKiloQty ELSE 0 END) AS [CrabsKG],
    SUM(CASE WHEN Product_Name LIKE 'Velvet Crabs%' THEN ProductKiloQty ELSE 0 END) AS [VelvetsKG],
    SUM(CASE WHEN Product_Name LIKE 'Langoustine%' THEN ProductKiloQty ELSE 0 END) AS [LangKG],
    SUM(CASE WHEN Product_Name = 'Crabs (Cut)' THEN ProductKiloQty ELSE 0 END) AS [CutCrKG]
FROM vReportData
WHERE Landing_Day = 5
AND DateOfCatch BETWEEN DATEADD(day, - 8, @EndDate) AND DATEADD(day, - 1, @EndDate)
GROUP BY
    Port, Boat

UNION ALL

      SELECT
    Port, Boat,
    SUM(CASE WHEN Product_Name = 'Lobsters' THEN ProductKiloQty ELSE 0 END) AS [LobstersKG],
    SUM(CASE WHEN Product_Name LIKE '%Crabs' THEN ProductKiloQty ELSE 0 END) AS [CrabsKG],
    SUM(CASE WHEN Product_Name LIKE 'Velvet Crabs%' THEN ProductKiloQty ELSE 0 END) AS [VelvetsKG],
    SUM(CASE WHEN Product_Name LIKE 'Langoustine%' THEN ProductKiloQty ELSE 0 END) AS [LangKG],
    SUM(CASE WHEN Product_Name = 'Crabs (Cut)' THEN ProductKiloQty ELSE 0 END) AS [CutCrKG]
FROM vReportData
WHERE Landing_Day = 6
AND DateOfCatch BETWEEN DATEADD(day, - 8, @EndDate) AND DATEADD(day, - 1, @EndDate)
GROUP BY
    Port, Boat
ORDER BY --optional, of course
    Landing_Day, Port, Boat

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lorna70Author Commented:
Oops sorry Geert_Gruwez my code for Landing_Day = 6 should have read:

AND DateOfCatch BETWEEN DATEADD(day, - 8, @EndDate) AND @EndDate) so they need to display different date ranges.  I think Union should work so thanks lsavidge - will try that now.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Lorna70Author Commented:
dbo.TESTReport
      (
      @StartDate date,
      @EndDate date      )
AS
SELECT
    Port, Boat,
    SUM(CASE WHEN Product_Name = 'Lobsters' THEN ProductKiloQty ELSE 0 END) AS [LobstersKG],
    SUM(CASE WHEN Product_Name LIKE '%Crabs' THEN ProductKiloQty ELSE 0 END) AS [CrabsKG],
    SUM(CASE WHEN Product_Name LIKE 'Velvet Crabs%' THEN ProductKiloQty ELSE 0 END) AS [VelvetsKG],
    SUM(CASE WHEN Product_Name LIKE 'Langoustine%' THEN ProductKiloQty ELSE 0 END) AS [LangKG],
    SUM(CASE WHEN Product_Name = 'Crabs (Cut)' THEN ProductKiloQty ELSE 0 END) AS [CutCrKG]
FROM vReportData
WHERE Landing_Day = 5
AND DateOfCatch BETWEEN DATEADD(day, - 7, @EndDate) AND DATEADD(day, - 1, @EndDate)
GROUP BY
    Port, Boat

UNION ALL

      SELECT
    Port, Boat,
    SUM(CASE WHEN Product_Name = 'Lobsters' THEN ProductKiloQty ELSE 0 END) AS [LobstersKG],
    SUM(CASE WHEN Product_Name LIKE '%Crabs' THEN ProductKiloQty ELSE 0 END) AS [CrabsKG],
    SUM(CASE WHEN Product_Name LIKE 'Velvet Crabs%' THEN ProductKiloQty ELSE 0 END) AS [VelvetsKG],
    SUM(CASE WHEN Product_Name LIKE 'Langoustine%' THEN ProductKiloQty ELSE 0 END) AS [LangKG],
    SUM(CASE WHEN Product_Name = 'Crabs (Cut)' THEN ProductKiloQty ELSE 0 END) AS [CutCrKG]
FROM vReportData
WHERE Landing_Day = 6
AND DateOfCatch BETWEEN DATEADD(day, - 8, @EndDate) AND @EndDate
GROUP BY
    Port, Boat
ORDER BY --optional, of course
    Landing_Day, Port, Boat

RETURN

Can someone please tell me what's wrong with this syntax??
Lorna70Author Commented:
Meant to say, I'm using SQL Server
Geert GOracle dbaCommented:
What does this give ?

SELECT Landing_Day, Port, Boat, 
    SUM(CASE WHEN Product_Name = 'Lobsters' THEN ProductKiloQty ELSE 0 END) AS [LobstersKG],
    SUM(CASE WHEN Product_Name LIKE '%Crabs' THEN ProductKiloQty ELSE 0 END) AS [CrabsKG],
    SUM(CASE WHEN Product_Name LIKE 'Velvet Crabs%' THEN ProductKiloQty ELSE 0 END) AS [VelvetsKG],
    SUM(CASE WHEN Product_Name LIKE 'Langoustine%' THEN ProductKiloQty ELSE 0 END) AS [LangKG],
    SUM(CASE WHEN Product_Name = 'Crabs (Cut)' THEN ProductKiloQty ELSE 0 END) AS [CutCrKG]
FROM 
  (
  SELECT
      Landing_Day, Port, Boat, ProductKiloQty, Product_Name
  FROM vReportData
  WHERE (Landing_Day = 5 AND DateOfCatch BETWEEN DATEADD(day, - 8, @EndDate) AND DATEADD(day, - 1, @EndDate)
    OR (Landing_Day = 6 AND DateOfCatch BETWEEN DATEADD(day, - 8, @EndDate) AND @EndDate)
  ) As X
GROUP BY
    Landing_Day, Port, Boat
ORDER BY 
  Landing_Day, Port, Boat

Open in new window

Lorna70Author Commented:
Thanks - that looks like it would work too but I'm getting an error near 'AS x'.

The 'UNION ALL' method works if I take out the first 'ORDER BY' statement :-)
Chris__WCommented:
Hello Lorna70,

From what I've always seen in TSQL, your 2 select statements are basically tables in the FROM clause.

Put a Select statement above:

SELECT field1, field2, field3 FROM
(
{Your first select statement}
UNION ALL
{Your second select statement}
)
GROUP BY
ORDER BY


Also, remove all GROUP BY and ORDER BY clauses that are within the UNION section and put them at the end as shown above.

Thanks,
Chris
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.