February 10, 2014 at 9:38 am
Hi Everyone,
Create Procedure InvoiceReport
@StartDate Datetime
@EndDate Datetime
DECLARE @DateReportTABLE
(
DateReport Datetime,
)
I would like to create a variable/table or temporary table and populate the table with all the Date between the StartDate and EndDate Parameter
Example:
@StartDate ='2014/02/01'
@EndDate ='2014/02/10'
I want to Add date between February 1st and 10th into temp table and use the temptable for a select query with another table in the same stored procedure.
But I don't have any idea to insert multiple values from a function into a temporary/variable table...
SELECT DateReport .DateReport , Invoice.InvoiceNumber
FROM DateReport LEFT JOIN Invoice ON DateReport.DateReport = Invoice.Date;
DateReport .DateReport Invoice.InvoiceNumber
2014/02/01 NULL
2014/02/02 1000
2014/02/03 1001
2014/02/04 1002
2014/02/04 1003
2014/02/05 NULL
2014/02/06 NULL
Thanks !!
2014/02/07 1004
2014/02/08 1005
2014/02/09 1006
2014/02/10 1007
February 10, 2014 at 9:49 am
Without the DDL for invoice I can't complete the query for you , but you could use a recursive CTE to populate the dates that you need into the table variable. Like so:
declare @startdate datetime
,@Enddate datetime
DECLARE @DateReport TABLE
(
DateReport Datetime
);
select @startdate = '20140201'
,@Enddate = '20140210';
with DateCTE as
(
select @startdate as ReportDate
union all
select dateadd(day,1,ReportDate) as ReportDate
from DateCTE
where dateadd (day,1,ReportDate) < dateadd(day,1,@Enddate)
)
insert @DateReport
SELECT *
FROM DateCTE
SELECT *
FROM @DateReport
February 10, 2014 at 12:28 pm
wow just great
thanks !
I didn't know that you can use recursive cte !
February 10, 2014 at 12:46 pm
Beware of recursive CTEs that count (like this one) as they could perform worse than a cursor or while loop (even if with 10 rows you can't note a difference). Check the following article on the topic: http://www.sqlservercentral.com/articles/T-SQL/74118/
I won't leave you without an alternative, here's one for you 😉
WITH E1(N) AS(
SELECT N FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(N)
),
E2(N) AS(
SELECT a.N FROM E1 a, E1 b -- 10 * 10 = 100 lines
),
E4(N) AS(
SELECT a.N FROM E2 a, E2 b -- 100 * 100 = 10,000 lines (over 27 years max range)
),
cteTally(N) AS(
SELECT TOP(DATEDIFF(DAY, @startdate - 1, @Enddate))
DATEADD( DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @startdate)
FROM E4
)
insert @DateReport
SELECT *
FROM cteTally
February 10, 2014 at 1:11 pm
Luis Cazares (2/10/2014)
Beware of recursive CTEs that count (like this one) as they could perform worse than a cursor or while loop (even if with 10 rows you can't note a difference). Check the following article on the topic: http://www.sqlservercentral.com/articles/T-SQL/74118/
Thanks Luis for the heads up and the article.
February 10, 2014 at 1:22 pm
Anyway I can't use the recursive function for over 100...
The maximum recursion 100 has been exhausted before statement completion.
February 10, 2014 at 1:24 pm
thanks for second solution but for the moment I don't understand completely the function
February 10, 2014 at 1:40 pm
You could use the recursive query with no limits (it's part of the documented syntax).
The default limit is 100 as a safenet because you shouldn't need more than that for recursive queries or you might be doing something wrong.
February 10, 2014 at 1:43 pm
dquirion78 (2/10/2014)
thanks for second solution but for the moment I don't understand completely the function
Read the article I posted, it has a nice explanation on how it works. Maybe you could ask something more specific. What don't you understand? the CTE? the cascading CTE? The cteTally query?
February 10, 2014 at 1:49 pm
how is working the parameter (N)
mainly this part : ROW_NUMBER() OVER(ORDER BY (SELECT NULL)
row_number is from top ?
over ? never see this
select null ? why null ?
February 10, 2014 at 2:07 pm
dquirion78 (2/10/2014)
how is working the parameter (N)
E1(N) is defining the name of the CTE (E1) and its column (N). You could easily remove it but you'll have to name the columns within the CTE
mainly this part : ROW_NUMBER() OVER(ORDER BY (SELECT NULL)
row_number is from top ?
ROW_NUMBER is not from TOP. TOP is to define the maximum number of rows returned. ROW_NUMBER() will generate a number for each row returned.
over ? never see this
OVER is used for Ranking Functions
to define the order and partition to process it.
select null ? why null ?
The ORDER BY in the OVER clause doesn't allow constants, so I just used an expression that won't return anything.
Note: Check the links on the bold words.
February 10, 2014 at 2:11 pm
thanks man !
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply