Full Outer Join Returns the correct results but the Order by is incomplete

Question

I am joining sales data source A with sales data source D. I want to order the net results by date, store and category. In some cases there is a match on store and category, in others no match on a store, etc.

Here is the desired result set:

Here is script to build the test data:

-- drop table #ASales
CREATE TABLE #ASales(
[Store] [varchar](50) NULL,
[SaleDate] [datetime] NULL,
[ACategory] [varchar](20) NULL,
[ATotalGallons] [numeric](38, 4) NULL
);

-- drop table #DSales
CREATE TABLE #DSales (
 [Store] [varchar](20) NULL,
 [SaleDate] [date] NULL,
 [DCategory] [varchar](40) NULL,
 [DTotalGallons] [decimal](38, 3) NULL
);

 INSERT #ASales ([Store], [SaleDate], [ACategory], [ATotalGallons]) VALUES (N'0009', CAST(N'2020-01-16T00:00:00.000' AS DateTime), N'D', CAST(643.0040 AS Numeric(38, 4)))
 INSERT #ASales ([Store], [SaleDate], [ACategory], [ATotalGallons]) VALUES (N'0009', CAST(N'2020-01-16T00:00:00.000' AS DateTime), N'G', CAST(4534.1350 AS Numeric(38, 4)))
 INSERT #DSales ([Store], [SaleDate], [DCategory], [DTotalGallons]) VALUES (N'10', CAST(N'2020-01-16' AS Date), N'G', CAST(1444.512 AS Decimal(38, 3)))
 INSERT #DSales ([Store], [SaleDate], [DCategory], [DTotalGallons]) VALUES (N'12', CAST(N'2020-01-16' AS Date), N'G', CAST(1417.343 AS Decimal(38, 3)))
 INSERT #DSales ([Store], [SaleDate], [DCategory], [DTotalGallons]) VALUES (N'9', CAST(N'2020-01-16' AS Date), N'G', CAST(4506.082 AS Decimal(38, 3)))
 INSERT #DSales ([Store], [SaleDate], [DCategory], [DTotalGallons]) VALUES (N'9', CAST(N'2020-01-16' AS Date), N'O', CAST(6.000 AS Decimal(38, 3)))
 INSERT #DSales ([Store], [SaleDate], [DCategory], [DTotalGallons]) VALUES (N'12', CAST(N'2020-01-16' AS Date), N'O', CAST(7.000 AS Decimal(38, 3)))
 INSERT #DSales ([Store], [SaleDate], [DCategory], [DTotalGallons]) VALUES (N'10', CAST(N'2020-01-16' AS Date), N'AD', CAST(471.373 AS Decimal(38, 3)))
 INSERT #DSales ([Store], [SaleDate], [DCategory], [DTotalGallons]) VALUES (N'12', CAST(N'2020-01-16' AS Date), N'AD', CAST(230.340 AS Decimal(38, 3)))
 INSERT #DSales ([Store], [SaleDate], [DCategory], [DTotalGallons]) VALUES (N'9', CAST(N'2020-01-16' AS Date), N'AD', CAST(617.909 AS Decimal(38, 3)))
 INSERT #DSales ([Store], [SaleDate], [DCategory], [DTotalGallons]) VALUES (N'10', CAST(N'2020-01-16' AS Date), N'O', CAST(1.000 AS Decimal(38, 3)))

 SELECT Store,
   SaleDate,
   ACategory,
   ATotalGallons
 FROM #ASales
  Order by SaleDate,cast(Store as int), ACategory;

SELECT  Store,
   SaleDate,
   DCategory,
   DTotalGallons
 FROM #DSales 
  Order by SaleDate, cast(Store as int), DCategory;

If I use the power of a FULL OUTER JOIN, I successfully bring back the rows for each store from each source, but I cannot get them to order as a unit. Here's the statement that almost returns what I want, however store 9 shows up twice, after ordering first by sales source a--then again from sales source D. Close, but not exactly what I want. I need the stores to show up in order once, inclusive of both sources. What do I need to do to fix this?

 SELECT COALESCE(a.Store,d.Store) AS store,
   COALESCE(A.SaleDate,d.SaleDate) AS saledate,
   A.ACategory,
   A.ATotalGallons,
   D.DCategory,
   D.DTotalGallons
 FROM #ASales A
  FULL OUTER JOIN #DSales D 
    ON D.Store = A.Store 
   AND D.SaleDate = A.SaleDate
   Order by COALESCE(A.SaleDate,d.SaleDate),COALESCE(a.Store,d.Store), a.ACategory,D.DCategory;

Answer 1

NOTE: This is for anyone following this thread. I've appended @VBoka's query magic by adding as part of the ON clause in the full outer join, A.AFuelCategory = D.DFuelCategory. This resolved the last part of the problem, which was to show the intersect on a shared fuel category for the same store on a single line. Also, the patindex/len work can be omitted by casting the store values as int.

    SELECT CAST(COALESCE(a.Store,d.Store) AS INT) AS store,
           COALESCE(A.SaleDate,d.SaleDate) AS saledate,
           A.AFuelCategory,
           A.ATotalGallons,
           D.DFuelCategory,
           D.DTotalGallons
     FROM #ASales A
     FULL OUTER JOIN #DSales D 
       ON CAST(D.Store AS INT) = CAST(A.Store AS INT) 
       AND D.SaleDate = A.SaleDate 
       AND a.AFuelCategory=d.DFuelCategory
      Order by COALESCE(A.SaleDate,d.SaleDate),
               CAST(COALESCE(a.Store,d.Store) AS INT), 
               a.AFuelCategory,
               D.DFuelCategory;

Answer 2

If I understand correctly, you need to handle multiple categories on the same day for the same table. For that, use row_number() to enumerate the rows:

SELECT COALESCE(a.Store,d.Store) AS store,
       COALESCE(A.SaleDate,d.SaleDate) AS saledate,
       A.ACategory,
       A.ATotalGallons,
       D.DCategory,
       D.DTotalGallons
FROM (SELECT A.*,
             ROW_NUMBER() OVER (PARTITION BY SaleDate, Store ORDER BY A.Category) as seqnum
      FROM #ASales A
     ) A FULL OUTER JOIN
     (SELECT D.*
             ROW_NUMBER() OVER (PARTITION BY SaleDate, Store ORDER BY DCategory) as seqnum
      FROM #DSales D 
     ) D
     ON D.Store = A.Store AND
        D.SaleDate = A.SaleDate AND
        D.seqnum = A.seqnum
ORDER BY COALESCE(A.SaleDate, d.SaleDate), COALESCE(a.Store, d.Store), a.ACategory, D.DCategory;

Answer 3

I hope this will be what you are looking for. In select I used SUBSTRING and PATINDEX.

In order by I have used also a CAST function.

SELECT SUBSTRING(COALESCE(a.Store,d.Store), PATINDEX('%[^0]%', COALESCE(a.Store,d.Store)+'.'), LEN(COALESCE(a.Store,d.Store))) AS store
       , COALESCE(A.SaleDate,d.SaleDate) AS saledate
       , A.ACategory
       , A.ATotalGallons
       , D.DCategory
       , D.DTotalGallons
FROM #ASales A
FULL OUTER JOIN #DSales D 
     ON D.Store = A.Store 
     AND D.SaleDate = A.SaleDate
Order by cast(SUBSTRING(COALESCE(a.Store,d.Store), PATINDEX('%[^0]%', COALESCE(a.Store,d.Store)+'.'), LEN(COALESCE(a.Store,d.Store))) as int)
         , COALESCE(A.SaleDate,d.SaleDate)
         , a.ACategory
         , D.DCategory;

Here is the DEMO of the query.