How do I write a Union between table AccessDetails and PosSalesWithAggregation

Transact-SQL
1

AccessDetails have these 16 columns.
DayClosureId,
DeviceGroupId,
DeviceId,
DeviveReaderId,
SoldPoolId,
FromPoolId,
TicketId,
CategoryId,
DateTime,
Passages
PassagesStateId,
SerialNumber,
DataCarrierNumber,
Date,
Hour,
poolId

Här is the column for table PosSalesWithAggregation
DayClosureId,
DeviceId,
TicketId,
CategoryId,
Datetime,
SerialNumber,
Date,
Hour,
PoolId,
TransactionId,
Indetid,
ArticleId,
Currencyid,
IsSuccsessful,
Quantity,
Turnover,
TurnoverTarget,
SalesTransactionTypeId,
ValidFrom,
ValidTo

Note: I convert Alteryx flow to stored procedure
So I must have SUM(Quantity), SUM(Turnover), SUM(TurnoverTarget)
and because of the aggregation I must have group by on these columns
TransactionsId, Indetid, ArticleId, Currencyid, IsSuccsessful, SalesTransactionTypeId, ValidFrom, ValidTo.

I tried with this sql clause

SELECT t.poolID
, t.DeviceId
, t.DayClosureId
, t.TransactionId
, t.IndetId
, t.TicketId
, t.CategoryId
, t.ArticleId
, t.CurrencyId
, t.Datetime
, t.IsSuccessful
,sum(t.Quantity) as Quantity
,sum(t.Turnover) as Turnover
,sum(t.TurnoverTarget) as TurnoverTarget
,t.SalesTransactionTypeId
,t.ValidFrom,t.ValidTo,t.SerialNumber,t.Date,t.Hour,t.DeviceGroupId,t.DeviceReaderId,t.SoldPoolId,t.FromPoolId,t.Passages,t.PassageStateID,t.DataCarrierNumber from
(SELECT poolID,
DeviceId,
DayClosureId,
TransactionId,
IndetId,
TicketId,
CategoryId,
ArticleId,
Datetime,
IsSuccessful,
CurrencyId,
Quantity,
Turnover,
TurnoverTarget,
SalesTransactionTypeId,
ValidFrom,
ValidTo,
Date,
Hour,
SerialNumber,
NULL as DeviceGroupId,
0 as DeviceReaderId, -- Because we have union this DeviceReaderId should always be 0
NULL as SoldPoolId,
NULL AS FromPoolId,
NULL as Passages,
NULL as PassageStateID,
NULL as DataCarrierNumber
FROM [Transform].PosSalesWithAggregation
UNION ALL
SELECT PoolId,
DeviceId,
DayClosureId,
NULL as TransactionId,
NULL, --IndetId
TicketId,
CategoryId,
NULL, --ArticleId
Datetime,
NULL, --IsSuccessful
NULL, --CurrencyId
NULL, --Quantity
NULL,
NULL,
NULL, --SalesTransactionTypeId
NULL, --ValidFrom
NULL, --ValidTo
Date,
Hour,
SerialNumber,
DeviceGroupId,
DeviceReaderId,
SoldPoolId,
FromPoolId,
Passages,
PassageStateID,
DataCarrierNumber
FROM [Transform].AccessDetails) AS t
where t.TransactionId is not null
GROUP BY t.poolID
, t.DeviceId
, t.DayClosureId
, t.TransactionId
, t.IndetId
, t.TicketId
, t.CategoryId
, t.ArticleId
, t.CurrencyId
, t.Datetime
, t.IsSuccessful
,t.SalesTransactionTypeId
,t.ValidFrom,t.ValidTo,t.SerialNumber,t.Date,t.Hour,t.DeviceGroupId,t.DeviceReaderId,t.SoldPoolId,t.FromPoolId,t.Passages,t.PassageStateID,t.DataCarrierNumber

But this result produced the wrong answer. If I used the same input and compared the number of rows the Alteryx produced with my result it was a huge difference. Alteryx produced 97 054 rows and the sql clause above produced 3262

2

hi

please check your "thinking"
sometimes "thinking errors" cause problems
i mean that maybe the SQL is doing correctly but you are not able to understand

do you have any idea ?
how to DEBUG

3
Your Code Formatted 
SELECT t.poolid
       ,t.deviceid
       ,t.dayclosureid
       ,t.transactionid
       ,t.indetid
       ,t.ticketid
       ,t.categoryid
       ,t.articleid
       ,t.currencyid
       ,t.datetime
       ,t.issuccessful
       ,Sum(t.quantity)       AS Quantity
       ,Sum(t.turnover)       AS Turnover
       ,Sum(t.turnovertarget) AS TurnoverTarget
       ,t.salestransactiontypeid
       ,t.validfrom
       ,t.validto
       ,t.serialnumber
       ,t.date
       ,t.hour
       ,t.devicegroupid
       ,t.devicereaderid
       ,t.soldpoolid
       ,t.frompoolid
       ,t.passages
       ,t.passagestateid
       ,t.datacarriernumber
FROM   
     (
	     SELECT poolid
               ,deviceid
               ,dayclosureid
               ,transactionid
               ,indetid
               ,ticketid
               ,categoryid
               ,articleid
               ,datetime
               ,issuccessful
               ,currencyid
               ,quantity
               ,turnover
               ,turnovertarget
               ,salestransactiontypeid
               ,validfrom
               ,validto
               ,date
               ,hour
               ,serialnumber
               ,NULL AS DeviceGroupId
               ,0    AS DeviceReaderId
               ,-- Because we have union this DeviceReaderId should always be 0
               NULL  AS SoldPoolId
               ,NULL AS FromPoolId
               ,NULL AS Passages
               ,NULL AS PassageStateID
               ,NULL AS DataCarrierNumber
        FROM   [Transform].possaleswithaggregation
        UNION ALL
        SELECT poolid
               ,deviceid
               ,dayclosureid
               ,NULL AS TransactionId
               ,NULL
               ,--IndetId
               ticketid
               ,categoryid
               ,NULL
               ,--ArticleId
               datetime
               ,NULL
               ,--IsSuccessful
               NULL
               ,--CurrencyId
               NULL
               ,--Quantity
               NULL
               ,NULL
               ,NULL
               ,--SalesTransactionTypeId
               NULL
               ,--ValidFrom
               NULL
               ,--ValidTo
               date
               ,hour
               ,serialnumber
               ,devicegroupid
               ,devicereaderid
               ,soldpoolid
               ,frompoolid
               ,passages
               ,passagestateid
               ,datacarriernumber
        FROM   [Transform].accessdetails
		
		) AS t
WHERE  t.transactionid IS NOT NULL
GROUP  BY t.poolid
          ,t.deviceid
          ,t.dayclosureid
          ,t.transactionid
          ,t.indetid
          ,t.ticketid
          ,t.categoryid
          ,t.articleid
          ,t.currencyid
          ,t.datetime
          ,t.issuccessful
          ,t.salestransactiontypeid
          ,t.validfrom
          ,t.validto
          ,t.serialnumber
          ,t.date
          ,t.hour
          ,t.devicegroupid
          ,t.devicereaderid
          ,t.soldpoolid
          ,t.frompoolid
          ,t.passages
          ,t.passagestateid
          ,t.datacarriernumber