How to add data rows to fit date range 2

[fishchi]

Hi,

Below is simple representation a table_A.

table_A

Company Volume Begtime Endtime
-------------------------------------
ABC 1000 03/01/2009 03/05/2009
ABC 3500 03/05/2009 03/08/2009

My desired output are records that are duplicated for each of the 2 date ranges as shown below. Note the Company and Volume replicate for the date range. Any help would be appreciated. Thanks!

Desired SQL Output

Company Volume Date
---------------------------
ABC 1000 03/01/2009
ABC 1000 03/02/2009
ABC 1000 03/03/2009
ABC 1000 03/04/2009
ABC 3500 03/05/2009
ABC 3500 03/06/2009
ABC 3500 03/07/2009

 

[bborissov]

NOT TESTED AT ALL!!!

DECLARE @Test TABLE (aNumber int)
DECLARE @i int
SET @i = 0
WHILE @i < 1000
   BEGIN
       INSERT INTO @Test VALUES (@i)
       SET @i = @i + 1
   END


SELECT Company,
       Volume,
       DATEADD(dd, Test.aNumber, TableName.DateField) AS Date,
FROM TableName
INNER JOIN @Test Test ON DATEADD(dd, Test.aNumber,TableName.Begtime) <= TableName.Endtime

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[fishchi]

Thank you bborissov. That worked!

Have a great weekend!

 

[fishchi]

Hi bborissov,

I just got a request to make the suggested query into a sql view so it can be called from a Crystal Report.
When I put your suggested code into a CREATE VIEW, I get an error message that the DECLARE has invalid syntax. The same code runs fine from Query Analyzer, so I'm guessing it isn't the syntax. Probably DECLARE statements not allowed in views? If so is there a workarond?

I can't use stored procedure due to limitations on system since another system is calling the runtime version of Crystal and the main system cannot handle stored procedures.

Thanks again

 

[gmmastros]

Create View WhateverYouWantToCallIt
As
Select T.Company, T.Volume, T.BegTime + Nums.Number 
From   [!]YourTableNameHere[/!] As T
       Inner Join (
         Select Number 
         From   Master..spt_Values 
         Where  Type = 'P'
         ) As NUMS
         On DateAdd(Day, Nums.Number, T.BegTime) < T.EndTime

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fishchi]

It's not creating the view that I'm having an issue with, it is that the DECLARE statement is throwing an error within the CREATE VIEW. It states there is a syntax error, but it works fine if I just run it straight in query analyzer. I suppose the question is, can you have a DECLARE statement within a sql view?

 

[fishchi]

Oh, I didn't see the inner join part. I thought you were just showing me how to create a view. Let me give this a try. Thanks!

 

[fishchi]

Hi gmmastros,

that worked just fine. Thank you.

I'm not familiar with the "Master..spt_Values" table.
What is this table for and what does it mean when screening on Type='P'?

 

[gmmastros]

can you have a DECLARE statement within a sql view?

No.

That's why I provided an alternative query that does not require a declare statement. There are limitations with the code I provide. Specifically, your date ranges cannot be larger than (256 days with SQL2000) or (2048 days with SQL2005). I suspect this will not be a problem (based on your sample data), but it is something you should be aware of.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fishchi]

Yes, 256 should be fine. The date range should never span more than 31 days. I got everything to work.

Can you explain what is the "Master..spt_Values"? It doesn't seen to exist as a real table or a system table. Is it a virtual table and what is it for?
Also what does it mean when screening on Type='P'?

Thanks.

 

[gmmastros]

spt_values is a table that exists in the master database to store configuration information. When you filter the rows on type = 'P', you essentially get a result set that has a number column with 256 rows with sequential numbers. Think of it as a 'poor mans' numbers table.

In my database, I have a user table named Numbers with values ranging from 1 to 1,000,000. There are several queries that benefit from a numbers table like this. Very handy indeed.

If this were my database, I would have used my Numbers table instead.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fishchi]

Thank you for explanation. Now I understand.