1

I am trying to select 4 Columns from temp tables I have created, I am struggling to get one of the columns to display its data.

I have written the statement differently to try and get the correct data however an Union is needed in order to subtract the Quantity from second table


Create table #temp110
(
ItemCode nvarchar(50),
[Quantity 6 months ago] int
)
Create table #temp6to12
(
ItemCode nvarchar(50),
[Quantity 6 to 12 Months Ago] int
)

GO
select top 20
    oi.[Itemcode], 
    sum(qu.[Quantity]) as [Quantity 6 months ago]
    into #temp110
from miro.dbo.oitm oi 
join miro.dbo.qut1 qu on oi.ItemCode = qu.ItemCode
WHERE DATEDIFF( MONTH, qu.docdate, GETDATE()) < 64
group by oi.[Itemcode], qu.Quantity
order by qu.quantity DESC

-- qty of itms between 6 & 12 months back, lowest 10 !!All Items
select top 20
    oi.[Itemcode], 
    sum(qu.[Quantity]) as [Quantity 6 to 12 Months Ago]
    into #temp6to12
from miro.dbo.oitm oi 
join miro.dbo.qut1 qu on oi.ItemCode = qu.ItemCode
WHERE DATEDIFF( MONTH, qu.docdate, GETDATE()) > 6 
and DATEDIFF( MONTH, qu.docdate, GETDATE()) < 12
group by oi.[Itemcode], qu.Quantity
order by qu.quantity DESC






SELECT 
UN.ItemCode,
UN.[Quantity 6 To 12 Months Ago],
UN.[Quantity Differance] 
FROM(

select 
t1.Itemcode,
t1.[Quantity 6 to 12 Months Ago],
t1.[Quantity 6 To 12 Months Ago] as [Quantity Differance]
from #temp6to12 t1
join #temp110 t2 on t1.[ItemCode] = t2.[ItemCode] 

UNION

select 
t2.Itemcode,
t2.[Quantity 6 months ago],
- t2.[Quantity 6 Months Ago] as [Quantity Differance] 
from #temp110 t1
join #temp110 t2 on t1.[ItemCode] = t2.[ItemCode]

) as Un

order by un.[Quantity Differance] ASC

Currently This code works and however it does not display a value for [Quantity 6 months ago], Which is what I ultimately need.

If I try to call it from the Union as I have with [Quantity 6 to 12 Months Ago] It does not pick it up, I suspect this is because [Quantity 6 to 12 Months Ago] is used to subtract from [Quantity 6 months ago] in the Union. The fact that it is used mathematically mean I cannot call it from the Union?

Expect to be able to add UN.[Quantity 6 months ago] to the select statement

Dale K
  • 25,246
  • 15
  • 42
  • 71
KavenM
  • 7
  • 4
  • It seems that you want to display two columns, and use conditional aggregations with the condition in the `WHERE` clause – Ilyes Sep 16 '19 at 09:10
  • You are selecting the 20 top items for two different time periods each. Then you inner join them. This join will only work for items that were among the top 20 in both periods. Otherwise you'd need a full outer join. – Thorsten Kettner Sep 16 '19 at 09:49
  • By the way: `group by oi.[Itemcode], qu.Quantity`? You don't want the top 20 items, but the top 20 single quantities? That doesn't seem likely. It would also render `sum(qu.[Quantity])` weird, because you'd kinda look up how often a quantity for an item occurs. I suppose you want `group by oi.Itemcode`and `order by sum(qu.quantity) desc` instead. – Thorsten Kettner Sep 16 '19 at 10:05
  • Hi Thorsten. You are correct about the order/group by as well as the join, I thanks for helping me think about this. I think I need to rewrite most of this code – KavenM Sep 16 '19 at 10:24

1 Answers1

0

Note that when you are using UNION it evaluates the results against each other, leaving out any duplicates. In your code you don’t seem to have any information that indicates if the quantity stems from either the first or the second part.

By using UNION ALL you force it to keep both results even if they are the same.

Alternatively, you can use some sort of indicator (eg a string/text) to indicate if the quantity relates to 6 months or 6-12 months.

ssn
  • 509
  • 3
  • 12