Get row from date interval even if it doesnt exist from one table

Question

I have this query

SELECT Date,  IFNULL(Price, '------') AS Price
       FROM productHistory
WHERE Date between '2012-08-15' and '2012-08-19' AND Company='AAA' AND Product='PPP'
GROUP BY Date

and the result is:

Date.......Price
-------------------
2012-08-15...100,00 
2012-08-19...110,00

and it should be like this:

Date.........Price
-------------------------------------
2012-08-15......100,00   
2012-08-16......--------
2012-08-17......--------
2012-08-18......--------
2012-08-19......110,00

I am working with only one table, I have checked similar quiestions in this forum but I could not find a solution.

When I get this, what I want to do is to add more Companies as columns in the query to get a result like this. well for this I have to change the query..

Date.........PriceCompany1.....PriceCompany2.....PriceCompany3 
----------------------------------------------------------  
2012-08-15......100,00................................100,00..................................100,0
2012-08-16......---------...............................100,00...................................---------
2012-08-17......---------..............................----------.................................110,00
2012-08-19......110,00..............................100,00..................................----------

What do I need to do?

Consider tagging your question for the specific DBMS you're using. — Nicola Musatti, Sep 05 '12 at 10:22
possible solution for your problem you could find here: [Get a list of dates between two dates](http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates) — Andreas Rohde, Sep 05 '12 at 11:22

Lajja Thaker · Answer 1 · 2012-09-05T11:19:13.820

0

Create StoredProcedure for that

CREATE PROCEDURE [dbo].[GetMasterData]
   @startDate DATETIME,
   @endDate DATETIME
AS    
BEGIN
SET NOCOUNT ON;

WITH dates(Date) AS 
(
    SELECT @startdate as Date
    UNION ALL
    SELECT DATEADD(d,1,[Date])
    FROM dates 
    WHERE DATE < @enddate
)
SELECT Date as Date1  into #tmp1  FROM dates
OPTION (MAXRECURSION 0) 


Select Date1,IsNULL(Price, '------') AS Price  from #tmp1 left outer join
productHistory on  #tmp1.Date1 = productHistory.Date
and Company='AAA' AND Product='PPP'

Drop table #tmp1

END

and execute sp using below line

exec GetMasterData '08/15/2012','08/19/2012'

edited Sep 05 '12 at 11:19

answered Sep 05 '12 at 10:01

Lajja Thaker

2,031
8
33
53

@thaker: this will give company price in a different row not in the same row right? – Sankara Sep 05 '12 at 11:11
nope, it will give on same row, have you tried it ?? or , could you please give me script of your table , so I will check and tell you ? – Lajja Thaker Sep 05 '12 at 11:17
1

Are you sure ???? It is pretty obvious from your query right . you have only 2 columns Date1 and Price. – Sankara Sep 05 '12 at 11:19
Yes I am sure but I insist you to give me script or structure of your table – Lajja Thaker Sep 05 '12 at 11:40
table structure is irrelevant in this case :( – Sankara Sep 05 '12 at 11:57

wildplasser · Answer 2 · 2012-09-05T13:41:19.587

No need for procedures, just plain SQL will do:

-- generate "pseudo calendar table" from the data:
WITH dt AS (
        SELECT DISTINCT zdate FROM producthistory
        WHERE zdate between '2012-08-10' and '2012-08-20'
        )
-- a poor man's PIVOT:
SELECT dt.zdate
        , pa.price AS price_a
        , pb.price AS price_b
        , pc.price AS price_c
FROM dt
LEFT JOIN producthistory pa ON pa.zdate = dt.zdate AND pa.company='AAA' AND pa.product='ppp'
LEFT JOIN producthistory pb ON pb.zdate = dt.zdate AND pb.company='BBB' AND pb.product='ppp'
LEFT JOIN producthistory pc ON pc.zdate = dt.zdate AND pc.company='CCC' AND pc.product='ppp'

ORDER BY zdate
;

Since the OP does not show any table definition or data, I'll have to invent my own:

CREATE TABLE producthistory
        ( company varchar
        , product varchar
        , zdate Date NOT NULL
        , price INTEGER
        );

INSERT INTO producthistory ( company, product, zdate, price ) VALUES
  ( 'AAA', 'ppp', '2012-08-15', 100)
, ( 'AAA', 'ppp', '2012-08-15', 110)
, ( 'AAA', 'ppp', '2012-08-15', 120)
, ( 'BBB', 'ppp', '2012-08-16', 200)
, ( 'BBB', 'qqq', '2012-08-16', 210)
, ( 'BBB', 'ppp', '2012-08-16', 220)
, ( 'CCC', 'ppp', '2012-08-15', 300)
;

RESULTS:

CREATE TABLE
INSERT 0 7
   zdate    | price_a | price_b | price_c 
------------+---------+---------+---------
 2012-08-15 |     100 |         |     300
 2012-08-15 |     110 |         |     300
 2012-08-15 |     120 |         |     300
 2012-08-16 |         |     200 |        
 2012-08-16 |         |     220 |        
(5 rows)

adding the IFNULL/COALESCE function (or other formatting trivia) is left as an exercise to the reader

Get row from date interval even if it doesnt exist from one table

2 Answers2