How To Select All The Dates Of of a given period Even Though There Are No Records?

[harshworkingsql]

Hi,

I have one SQL Table with 3 columns as below


Column1: ProductionDate - DateTime - Not NULL
Column2: Quantity - Int - Not NULL
Column3: ProductName -string - not null
Now There are 5 Records in Table

1-1-2007, 5,Pen
1-3-2007, 7,Stabler
1-3-2007,4,pen
1-5-2007,9,Pencil
1-5-2007,2,stabler


Output of Result should be as below

1-1-2007 5,pen
1-1-2007 0,stabler
1-1-2007 0,pencil
1-2-2007 5,pen
1-2-2007 0,stabler
1-2-2007 0,pencil
1-3-2007 4,pen
1-3-2007 7,stabler
1-3-2007 0,pencil
1-4-2007 4,pen
1-4-2007 7,stabler
1-4-2007 0,pencil
1-5-2007 4,pen
1-5-2007 2,stabler
1-5-2007 9,pencil

Means Query should return all the dates of Month with Quantity and productName and if there is no entry in Table then the previous days quantity for Quantity.

How to Do it? Please suggest with Query

[code green]

A query is possible with a sub-query that gets MAX date less than returned date.
But I think this is a database design flaw.
Well it isn't actually a database, it is simply a flat file.
It doesn't store the data you need in a format that can be retrieved without doing SQL handsprings and will get worse.

Can't think what the design should at the moment

[ck9663]

Do you have a table that contains all the Product? Does it has Product ID that can be used to link the two tables?

~~ CK

[NoClueReally]

It's been a long while since I've done much SQL, but if I remember correctly you'll need to use concepts such as an outer and inner join. It looks like it will end up being a very expensive operation as a query. Programattically (is that even a word?), the solution would be relatively simple and straight forward.

[Sandeep M]

try This.... enjoy knowledge freedom ......

I've used one table,table20, and a function,test12() for this.

CREATE TABLE [dbo].[test20](
    [productionDate] [datetime] NULL,
    [Quantity] [int] NULL,
    [ProductName] [varchar](50) NULL
) 
GO
 
insert into test20 values('1-1-2007', 5,'Pen')
insert into test20 values('1-3-2007', 7,'Stabler')
insert into test20 values('1-3-2007',4,'pen')
insert into test20 values('1-5-2007',9,'Pencil')
insert into test20 values('1-5-2007',2,'stabler')
 
GO
CREATE FUNCTION test12 ()
RETURNS @Productdates TABLE (Productiondate DATETIME NOT NULL)
AS
BEGIN
DECLARE @cnt INT;
DECLARE @StartDate DATETIME;
SET @cnt=0;
SELECT @cnt= DATEDIFF(DAY,MIN(Productiondate),MAX(Productiondate)),@StartDate=MIN(Productiondate) FROM test20 
WHILE (@cnt>0)
    BEGIN
    INSERT INTO @Productdates VALUES(DATEADD(DAY,@cnt,@StartDate))
    SET @cnt=@cnt-1
    END
INSERT INTO @Productdates VALUES(@StartDate)
RETURN
END;
 
GO
 
-- Sql Query which returns Resultset
 
select    d.productiondate,
        d.productname, 
        isnull(quantity,isnull((select quantity from test20 where productname=d.productname and productiondate =(select max(productiondate) from test20 where productiondate<d.productiondate)) ,0) ) as Qty
FROM test20 c
right join 
(
    select a.productiondate,b.productname from test12() a
    cross join 
    (select distinct productname from test20) b 
) d 
    on         c.productiondate=d.productiondate
         and c.productname=d.productname
order by d.productiondate