Need help subtracting Money of Category from two different dates

[leo57]

I have data saved everynight like so
There are 3 columns: date, category, money.
Date1 Cat1 ExtCost1

​

Date2

​

Cat2 ExtCost2
2016-11-10 Base Material 36526.31 2016-11-11 Base Material 36522.12
2016-11-10 Encasing Parts 34523.33 2016-11-11 Encasing Parts 34345.32
I want to be able to choose two dates for the same category and have it give the difference of the money. So I would like to see:
Base Material: 36526.31 - 36522.12 = 34.88
Encasing Parts: 34523.33 - 34345.32 = 178.01

SELECT --[RMI_ID]
      CAST(i1.[DateAdded] as Date) As Date1 ,i1.[Category] as Cat1,i1.[Extended Cost] as ExtCost1,
	  CAST(i2.[DateAdded] as Date) as Date2,i2.[Category] as Cat2,i2.[Extended Cost] as ExtCost2
  FROM [FreedomSIReports].[dbo].[RMInventoryByCategory] i1
  inner join [FreedomSIReports].[dbo].[RMInventoryByCategory] i2 on i2.[Category] = i1.[Category]
  Where CAST([i1.DateAdded] as Date) = '2016-11-14' and CAST([i2.DateAdded] as Date) = '2016-11-13'
  order by i1.dateAdded desc

TIA

 

[leo57]

Dooh, sorry I see I put the i1 [i1.DateAdded] in the wrong place it should be i1.[DateAdded].
my bad

SELECT --[RMI_ID]
      CAST(i1.[DateAdded] as Date) As Date1, i1.[Category] as Cat1, i1.[Extended Cost] as ExtCost1,
	  CAST(i2.[DateAdded] as Date) as Date2, i2.[Category] as Cat2, i2.[Extended Cost] as ExtCost2,i1.[Extended Cost]-i2.[Extended Cost] as Thedifference
  FROM [FreedomSIReports].[dbo].[RMInventoryByCategory] i1
  inner join [FreedomSIReports].[dbo].[RMInventoryByCategory] i2 on i2.[Category] = i1.[Category]
  Where CAST(i1.[DateAdded] as Date) = '2016-11-14' and CAST(i2.[DateAdded] as Date) = '2016-11-13'
  order by i1.dateAdded desc

 

[Andrzejek]

Is this how your data looks like?

[pre]
Date1 Cat1 ExtCost1 Date2 Cat2 ExtCost2
2016-11-10 Base Material 36526.31 2016-11-11 Base Material 36522.12
2016-11-10 Encasing Parts 34523.33 2016-11-11 Encasing Parts 34345.32[/pre]

Have fun.

---- Andy

There is a great need for a sarcasm font.