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

user_3432034 0
Viewed 71

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 )

sql-server sql
2 Answers

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
user_16901930 
IF OBJECT_ID('tempdb..#myTable', 'U') IS NOT NULL 
DROP TABLE #myTable;
IF OBJECT_ID('tempdb..#itemTable', 'U') IS NOT NULL 
DROP TABLE #itemTable;
IF OBJECT_ID('tempdb..#DateTable', 'U') IS NOT NULL 
DROP TABLE #DateTable;

CREATE TABLE #myTable (
    item VARCHAR(MAX) NOT NULL,
    startdate DATE NOT NULL,
    enddate DATE NOT NULL,
    price INT NOT NULL DEFAULT(0)
    );

INSERT #myTable (item, startdate, enddate, price) VALUES
    ('AAAA' ,CAST('2020-03-16' AS DATE),CAST('2020-03-19' AS DATE),50),
    ('AAAA' ,'2020-04-16','2020-04-19',70), 
    ('BBB' ,'2020-03-16','2020-03-19',20),
    ('BBB' ,'2020-04-16','2020-04-19',90),
    ('CCC' ,'2020-03-16','2020-03-29',45),
    ('CCC' ,'2020-04-16','2020-04-19',120)


CREATE TABLE #itemTable ( 
    item VARCHAR(MAX) NOT NULL
)

INSERT #itemTable (item) VALUES
    ('AAAA'), 
    ('BBB'),
    ('CCC')

CREATE TABLE #DateTable (  
    dateItem DATE NOT NULL
)

INSERT #DateTable (dateItem) VALUES
    (CAST('2020-03-16' AS DATE)), 
    (CAST('2020-04-19' AS DATE)),
    (CAST('2020-03-20' AS DATE)),
    (CAST('2020-03-21' AS DATE)),
    (CAST('2021-03-21' AS DATE)),
    (CAST('2022-03-21' AS DATE))


Declare @DynamicCol nvarchar(max),@DynamicColNull nvarchar(max)
        ,@Sql nvarchar(max)

SELECT @DynamicColNull=STUFF((SELECT DISTINCT ', '+'ISNULL('+QUOTENAME(dateItem),','+'''0'''+') As '+QUOTENAME(dateItem)
                        FROM #DateTable FOR XML PATH ('')),1,2,'')

SELECT @DynamicCol=STUFF((SELECT DISTINCT ', '+QUOTENAME(dateItem) FROM #DateTable FOR XML PATH ('')),1,2,'')

SET @Sql='SELECT [item], '+@DynamicColNull+' 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 ('+@DynamicCol+')
            )AS Pvt'
PRINT @Sql
EXEC(@Sql)
user_191331830
Related