Table Join with Grouping problem

• gjoelson 29755

  SSC Eights!

  Points: 848

  More actions

  March 2, 2019 at 10:51 am

  #379540

  Morning,
  I'm looking for some help on my query, it really a grouping problem.
  I've joined 2 unsimilar tables (different db's)  to get a value not available in the 1st table. 

  The 2nd table has different values per product which when grouped together splits my records...
  
select y.Destination, count(*) Loads , sum(y.NetTons) Tons,   t.product
,CASE DATEPART(day ,GETDATE())
 when 1 then t.[1st]
 when 2 then t.[2nd]
 when 3 then t.[3rd]
end EXPT
FROM [SZ2611308].[dbo].[RSILoadoutTransaction] y
inner Join [AZUREDB].[SSSUTIL].[dbo].[SA-Releases] t on y.Destination = t.customer
WHERE [TransactionDate] > DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
and t. customer is not null
and t. [1st] is not null
and t. [2nd] is not null
and t. [3rd] is not null

  GROUP BY y.Destination, t.[1st], t.[2nd], t.[3rd], t.product

  

  This next image is the correct format I need but without the [expt] value
  

  Thanks for your help !
  Greg

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  March 7, 2019 at 2:03 pm

  #2023880

  Your GROUP BY includes product, and your picture shows two different values for product for destination BJS.   That would cause your problem.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)