I have two Tables containing Sales Data. I want to read the Table a sort by date and accumulate dollars by order date. Then I want to read the second table and accumulate these dollar amounts by date and then merge the records together so that I gave 1 row with amounts for type A and amounts for type b.

Here are the tables I am looking at.

Select Cast(J.Order_Date As Varchar(11))) As [Order Date]
          ,Sum(Case when Sales_Code like '%Comm%' then (J.Order_Quantity * J.Unit_Price) Else 0 end) As Decimal(11,2) As [Job Comm]
          ,Sum(Case when Sales_Code = '5-Day' then (J.Order_Quantity * J.Unit_Price) Else 0 end) As Decimal(11,2) As [Job Auto]
          ,Sum(Case when Sales_Code like '%Auto%" then (J.Order_Quantity * J.Unit_Price) Else 0 end) As Decimal(11,2) As [Job Auto]
          ,Sum(Case when Sales_Code = '' then (J.Order_Quantity * J.Unit_Price) Else 0 end) As Decimal(11,2) As [Job Fixed]
          ,Sum(Case when Sales_Code = 'XX' then (J.Order_Quantity * J.Unit_Price) Else 0 end) As Decimal(11,2) As [SO Comm)
          ,Sum(Case when Sales_Code = 'YY' then (J.Order_Quantity * J.Unit_Price) Else 0 end) As Decimal(11,2) As [SO Auto)
          ,Sum(Case when Sales_Code = 'ZZ' then (J.Order_Quantity * J.Unit_Price) Else 0 end) As Decimal(11,2) As [SO Fixed)
from [PRODUCTION].dbo.Job As J
union all
Select Cast(SH.Order_Date As Varchar(11))) As [Order Date]
          ,Sum(Case when Sales_Code like '%Comm%' then SD.Ext_Amt Else 0 end) As Decimal(11,2) As [SO Comm]
          ,Sum(Case when Sales_Code = '5-Day'     then SD.Ext_Amt Else 0 end) As Decimal(11,2) As [SO Auto]
          ,Sum(Case when Sales_Code like '%Auto%" then SD.Ext_Amt Else 0 end) As Decimal(11,2) As [SO Auto]
          ,Sum(Case when Sales_Code = ''          then SD.Ext_Amt Else 0 end) As Decimal(11,2) As [SO Fixed]
          ,Sum(Case when Sales_Code = 'XX' then SD.Ext_Amt Else 0 end) As Decimal(11,2) As [Job Comm)
          ,Sum(Case when Sales_Code = 'YY' then SD.Ext_Amt Else 0 end) As Decimal(11,2) As [Job Auto)
          ,Sum(Case when Sales_Code = 'ZZ' then SD.Ext_Amt Else 0 end) As Decimal(11,2) As [Job Fixed)
from [PRODUCTION].dbo.SO_Detail As SD
Inner Join [PRODUCTION].dbo.SO_Header As SH
    on SD.Sales_Order = SH.Sales_Order
Group by J.Order_Date
Order by J.Order_Date Desc


Looking for output like

Order Date   Job Comm   Job AUto   Job Fixed    SO Comm  SO AUto  SO Fixed

Mar-11-2014    100.00     250.00       50.00     200.00   300.00    400.00
Mar-10-2014    500.00     340.00        0.00     110.00   400.00    500.00
Mar-09-2014    600.00     333.00       56.00     210.00   500.00    300.00

Thanks for your help

Could you please provide sample DDL+DML and your desired output with sample data?

This would help us to help you better.

Try the below query. I am unable to test.

SELECT [Order Date],[Job Comm],[Job Auto],[Job Fixed],[So Comm],[So Auto],[So Fixed] FROM (
Select CONVERT(CHAR(10),J.Order_Date,101)  As [Order Date]
          ,Sum(Case when Sales_Code like '%Comm%' then (J.Order_Quantity * J.Unit_Price) Else 0 end)  As [Job Comm]
          ,Sum(Case when Sales_Code = '5-Day' then (J.Order_Quantity * J.Unit_Price) Else 0 end)  As [Job Auto]
          ,Sum(Case when Sales_Code like '%Auto%' then (J.Order_Quantity * J.Unit_Price) Else 0 end)  As [Job Auto]
          ,Sum(Case when Sales_Code = '' then (J.Order_Quantity * J.Unit_Price) Else 0 end)  As [Job Fixed]
          ,Sum(Case when Sales_Code = 'XX' then (J.Order_Quantity * J.Unit_Price) Else 0 end)  As [SO Comm]
          ,Sum(Case when Sales_Code = 'YY' then (J.Order_Quantity * J.Unit_Price) Else 0 end)  As [SO Auto]
          ,Sum(Case when Sales_Code = 'ZZ' then (J.Order_Quantity * J.Unit_Price) Else 0 end)  As [SO Fixed]
from [PRODUCTION].dbo.Job As J
GROUP BY CONVERT(CHAR(10),J.Order_Date,101)
UNION ALL
Select CONVERT(CHAR(10),SH.Order_Date,101) As [Order Date]
          ,Sum(Case when Sales_Code like '%Comm%' then SD.Ext_Amt Else 0 end)  As [SO Comm]
          ,Sum(Case when Sales_Code = '5-Day'     then SD.Ext_Amt Else 0 end)  As [SO Auto]
          ,Sum(Case when Sales_Code like '%Auto%' then SD.Ext_Amt Else 0 end)  As [SO Auto]
          ,Sum(Case when Sales_Code = ''          then SD.Ext_Amt Else 0 end)  As [SO Fixed]
          ,Sum(Case when Sales_Code = 'XX' then SD.Ext_Amt Else 0 end)  As [Job Comm]
          ,Sum(Case when Sales_Code = 'YY' then SD.Ext_Amt Else 0 end)  As [Job Auto]
          ,Sum(Case when Sales_Code = 'ZZ' then SD.Ext_Amt Else 0 end)  As [Job Fixed]
from [PRODUCTION].dbo.SO_Detail As SD
Inner Join [PRODUCTION].dbo.SO_Header As SH
    on SD.Sales_Order = SH.Sales_Order
Group by CONVERT(CHAR(10),J.Order_Date,101)
) X
Order by CAST([Order Date] AS DATETIME) Desc

Regards, RSingh

• Edited by RSingh()Moderator 18 hours 2 minutes ago

Seeing the output it looks like what you need is this

select COALESCE(p.[Order Date],q.[Order Date]) AS [Order Date],
COALESCE([Job Comm],0) AS [Job Comm],
COALESCE([Job AUto],0) AS [Job AUto],
COALESCE([Job Fixed],0) AS [Job Fixed],
COALESCE([SO Comm],0) AS [SO Comm],
COALESCE([SO AUto],0) AS [SO AUto],
COALESCE([SO Fixed],0) AS [SO Fixed]
from
(
Select Cast(J.Order_Date As Varchar(11))) As [Order Date]
          ,Sum(Case when Sales_Code like '%Comm%' then (J.Order_Quantity * J.Unit_Price) Else 0 end) As Decimal(11,2) As [Job Comm]
          ,Sum(Case when Sales_Code = '5-Day' then (J.Order_Quantity * J.Unit_Price) Else 0 end) As Decimal(11,2) As [Job Auto]
          ,Sum(Case when Sales_Code like '%Auto%" then (J.Order_Quantity * J.Unit_Price) Else 0 end) As Decimal(11,2) As [Job Auto]
          ,Sum(Case when Sales_Code = '' then (J.Order_Quantity * J.Unit_Price) Else 0 end) As Decimal(11,2) As [Job Fixed]
          ,Sum(Case when Sales_Code = 'XX' then (J.Order_Quantity * J.Unit_Price) Else 0 end) As Decimal(11,2) As [SO Comm)
          ,Sum(Case when Sales_Code = 'YY' then (J.Order_Quantity * J.Unit_Price) Else 0 end) As Decimal(11,2) As [SO Auto)
          ,Sum(Case when Sales_Code = 'ZZ' then (J.Order_Quantity * J.Unit_Price) Else 0 end) As Decimal(11,2) As [SO Fixed)
from [PRODUCTION].dbo.Job As J
)p
full join
(
Select Cast(SH.Order_Date As Varchar(11))) As [Order Date]
          ,Sum(Case when Sales_Code like '%Comm%' then SD.Ext_Amt Else 0 end) As Decimal(11,2) As [SO Comm]
          ,Sum(Case when Sales_Code = '5-Day'     then SD.Ext_Amt Else 0 end) As Decimal(11,2) As [SO Auto]
          ,Sum(Case when Sales_Code like '%Auto%" then SD.Ext_Amt Else 0 end) As Decimal(11,2) As [SO Auto]
          ,Sum(Case when Sales_Code = ''          then SD.Ext_Amt Else 0 end) As Decimal(11,2) As [SO Fixed]
          ,Sum(Case when Sales_Code = 'XX' then SD.Ext_Amt Else 0 end) As Decimal(11,2) As [Job Comm)
          ,Sum(Case when Sales_Code = 'YY' then SD.Ext_Amt Else 0 end) As Decimal(11,2) As [Job Auto)
          ,Sum(Case when Sales_Code = 'ZZ' then SD.Ext_Amt Else 0 end) As Decimal(11,2) As [Job Fixed)
from [PRODUCTION].dbo.SO_Detail As SD
Inner Join [PRODUCTION].dbo.SO_Header As SH
    on SD.Sales_Order = SH.Sales_Order
Group by J.Order_Date
)q
on p.[Order Date] = q.[Order Date]
Order by COALESCE(p.[Order Date],q.[Order Date]) Desc