Select data from one table base on selection from another table in SQL-CodePudding

I have these 3 table

First contain the item with price on given dates

2nd is the table of items

3rd is the table of dates in which we want to show the price of 2nd table item on every date

if duration is not available on first table it should be 0

with myTable ( item,startdate,enddate,price) as 
(  
  select  'AAAA' ,'16-3-2020','19-3-2020','50' union all
  select  'AAAA' ,'16-4-2020','19-4-2020','70' union all
  select  'BBB' ,'16-3-2020','19-3-2020','20' union all
  select  'BBB' ,'16-4-2020','19-4-2020','90' union all
  select  'CCC' ,'16-3-2020','29-3-2020','45' union all
  select  'CCC' ,'16-4-2020','19-4-2020','120'
)
select  item,startdate,enddate,price  from myTable
GO

with itemTable ( item) as 
(  
  select  'AAAA'  union all
  select  'BBB'  union all
   select  'CCC' 
)
select  item  from itemTable

GO

with DateTable ( dateItem) as 
(  
  select  '16-3-2020'  union all
  select  '19-4-2020'  union all
  select  '20-3-2020' 
)
select  dateItem  from DateTable

GO

and my desire result should be like this (above is dynamic data)

with mydesireTable (item, [16-3-2020],[19-4-2020],[20-3-2020]) as 
(  
  select  'AAAA' ,'50','70','0' union all ---0 as its not on above  data in duration 
  select  'BBB' ,'20','90','0' union all
  select  'CCC' ,'45','120','45' 
)
select  item, [16-3-2020],[19-4-2020],[20-3-2020]  from mydesireTable

I am not sure what to search for :) as i want to write query for it which return my desire table as data ( or as in temporary table )

CodePudding user response：

One of many ways to do this. This is a static crosstab. You need to list out all the columns explicitly (twice)

If your columns are dynamic, you need to use a dynamic crosstab. You should also consider doing this in your "presentation" layer, i.e. excel or whatever you are handing this over in.

You should consider what you want when something in mytable appears against a bucket twice (this solution will add the prices)

with myTable ( item,startdate,enddate,price) as 
(  
  select  'AAAA' ,CAST('2020-03-16' AS DATE),CAST('2020-03-19' AS DATE),50 union all
  select  'AAAA' ,'2020-04-16','2020-04-19',70 union all
  select  'BBB' ,'2020-03-16','2020-03-19',20 union all
  select  'BBB' ,'2020-04-16','2020-04-19',90 union all
  select  'CCC' ,'2020-03-16','2020-03-29',45 union all
  select  'CCC' ,'2020-04-16','2020-04-19',120
),

itemTable ( item) as 
(  
  select  'AAAA'  union all
  select  'BBB'  union all
   select  'CCC' 
)
,DateTable ( dateItem) as 
(  
  select  CAST('2020-03-16' AS DATE)  union all
  select  '2020-04-19'  union all
  select  '2020-03-20' 
)


SELECT item,
[2020-03-16],[2020-04-19], [2020-03-20]
FROM
(
select item, dateitem, price from myTable 
inner join datetable on datetable.dateItem between mytable.startdate and myTable.enddate
) As Src
PIVOT 
( 
SUM(price)
FOR
dateitem IN ([2020-03-16],[2020-03-20],[2020-04-19])
) as P