Union on a join

jkirkerx

I got myself in a pickle here. I started letting customers delete items in the store. But I copied the item into a separate table. I should of added a column status instead. But anyways, on my report for total items sold in the year, I did a join on the product table, to get the current cost, images, etc. So now that some items have been deleted, the record is no longer in the product table, but in the recycle table. I tried a union all, but it results in 2 records, in which the 2nd is null. I tried just union as well, but it produces an reader error. I'm not sure how to proceed on this, or what to words to use to do a search here. I'm stumped! [EDIT] Maybe I should of done a if exist around the join, and just switch tables.

    Dim query As String = \_
    "DECLARE @startDate AS DATE; " & \_
    "DECLARE @stopDate AS DATE; " & \_
    "SET @startDate = DATETIMEFROMPARTS(@Year, 1, 1, 0, 0, 0, 0); " & \_
    "SET @stopDate = DATETIMEFROMPARTS(@Year, @Month, @Day, 23, 59, 59, 999); "

    query +=
    "SELECT " & \_
    " coH.PartNumber, " & \_
    " coH.ManPartNumber, " & \_
    " SUM(coH.Qty) as TotalQty, " & \_
    " CAST(SUM(coH.Qty \* coh.Cost) AS Decimal(10,2)) as TotalCost, " & \_
    " CAST(SUM(coH.Qty \* coh.Price) AS Decimal(10,2)) as TotalPrice, " & \_
    " pI.Cost, " & \_
    " pI.Price, " & \_
    " pI.ShortDescription, " & \_
    " pI.LongDescription, " & \_
    " pI.PostageImage, " & \_
    " pI.Thumbnail, " & \_
    " pI.VendorID, " & \_
    " pI.VendorName, " & \_
    " pI.Weight, " & \_
    " pI.ShipHeight, " & \_
    " pI.ShipWidth, " & \_
    " pI.ShipDepth, " & \_
    " pI.LimitedItem, " & \_
    " vI.Address1, " & \_
    " vI.Address2, " & \_
    " vI.City, " & \_
    " vI.StateCode, " & \_
    " vI.CountryCode, " & \_
    " vI.ZipCode, " & \_
    " vI.ContactName, " & \_
    " vI.VendorPhone " & \_
    " FROM CompletedOrdersCartHistory as coH " & \_
    " LEFT JOIN PRODUCTINFO AS pI " & \_
    " ON coH.PartNumber = pI.PartNumber " & \_
    " LEFT JOIN VendorInfo AS vI " & \_
    " ON pI.VendorID = vI.VendorID " & \_
    " WHERE coh.OrderDate > @startDate " & \_
    " AND coh.OrderDate < @stopDate " & \_
    " AND coh.PartNumber = @PartNumber " & \_
    " GROUP BY " & \_
    " coH.PartNumber, " & \_
    " coH.ManPartNumber,  " & \_
    " pI.Cost,  " & \_
    " pI.Price, " & \_
    " p

Richard Deeming

Since you're using a relatively recent version of MS SQL Server, something like this should work:

DECLARE @startDate AS DATE;
DECLARE @stopDate AS DATE;

SET @startDate = DATETIMEFROMPARTS(@Year, 1, 1, 0, 0, 0, 0);
SET @stopDate = DATETIMEFROMPARTS(@Year, @Month, @Day, 23, 59, 59, 999);

WITH cteProductInfo As
(
SELECT
PartNumber,
Cost,
Price,
ShortDescription,
LongDescription,
PostageImage,
Thumbnail,
VendorID,
VendorName,
Weight,
ShipHeight,
ShipWidth,
ShipDepth,
LimitedItem
FROM
PRODUCTINFO

UNION ALL

SELECT
    PartNumber,
    Cost,
    Price,
    ShortDescription,
    LongDescription,
    PostageImage,
    Thumbnail,
    VendorID,
    VendorName,
    Weight,
    ShipHeight,
    ShipWidth,
    ShipDepth,
    LimitedItem
FROM
    PRODUCTINFO\_RECYCLEBIN

),
cteOrders As
(
SELECT
PartNumber,
ManPartNumber,
SUM(coH.Qty) as TotalQty,
CAST(SUM(coH.Qty * coh.Cost) AS decimal(10, 2)) as TotalCost,
CAST(SUM(coH.Qty * coh.Price) AS decimal(10, 2)) as TotalPrice
FROM
CompletedOrdersCartHistory
GROUP BY
PartNumber,
ManPartNumber
)
SELECT
coH.PartNumber,
coH.ManPartNumber,
coH.TotalQty,
coH.TotalCost,
coH.TotalPrice,
pI.Cost,
pI.Price,
pI.ShortDescription,
pI.LongDescription,
pI.PostageImage,
pI.Thumbnail,
pI.VendorID,
pI.VendorName,
pI.Weight,
pI.ShipHeight,
pI.ShipWidth,
pI.ShipDepth,
pI.LimitedItem,
vI.Address1,
vI.Address2,
vI.City,
vI.StateCode,
vI.CountryCode,
vI.ZipCode,
vI.ContactName,
vI.VendorPhone
FROM
cteOrders as coH
LEFT JOIN cteProductInfo AS pI
ON coH.PartNumber = pI.PartNumber
LEFT JOIN VendorInfo AS vI
ON pI.VendorID = vI.VendorID
WHERE
coh.OrderDate > @startDate
AND
coh.OrderDate < @stopDate
AND
coh.PartNumber = @PartNumber
ORDER BY
coH.PartNumber
;

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

jkirkerx

That looks interesting! Ohhhhh, so that's how you union all the productinfo and productinfo_recyclebin together. Yes I do understand the SQL now. Wow, thanks for taking the time to look at it. I know it was a lot of code to look at and could get confusing. I'll give it a spin tomorrow. Thanks Richard!

jkirkerx

Worked like a charm! Thanks! Had to adjust a couple of things, but that got my mind into better understanding the SQL sample