looping through all days of the year

I like nigelrivett's answer. My contribution is less efficient, but doesn't have a defined upper limit on the datediff.

It uses the table variable, so won't work in SQL 6.5 or 7.0 (you'll have to use a temporary table there - which has more drawbacks).

declare @.startdate datetime, @.enddate datetime
select @.startdate = '1 feb 2001', @.enddate = '20 sep 2010'

declare @.max_number int
set @.max_number = datediff(d, @.startdate, @.enddate)

--create a table of numbers from 0 to @.max_number
set nocount on
declare @.t table (i int)
insert into @.t values (0)

declare @.i int
set @.i = 1
while @.i < @.max_number/2
begin
insert into @.t select i + @.i from @.t
set @.i = @.i * 2
end
insert into @.t select i + @.i from @.t where i + @.i <= @.max_number
set nocount off

--use the table
select d = dateadd(dd, i, @.startdate) from @.t order by dJust for overkill, I've created a user-defined function which takes a number and returns a table of numbers from 0 to that number. That can then be used to solve this problem and elsewhere.

This will only work in SQL 2000 (as far as I know)...

CREATE FUNCTION table_of_numbers (@.max_number int)
RETURNS @.t table (i int)
AS
begin
insert into @.t values (0)

declare @.i int
set @.i = 1
while @.i < @.max_number/2
begin
insert into @.t select i + @.i from @.t
set @.i = @.i * 2
end
insert into @.t select i + @.i from @.t where i + @.i <= @.max_number

RETURN
end

So...

SELECT * FROM table_of_numbers(10)

Gives...

i
----
0
1
2
3
4
5
6
7
8
9
10

And for this specific problem...

declare @.startdate datetime, @.enddate datetime
select @.startdate = '1 feb 2001', @.enddate = '20 sep 2001'

declare @.max_number int
set @.max_number = datediff(d, @.startdate, @.enddate)
select d = dateadd(dd, i, @.startdate) from table_of_numbers(@.max_number) order by d

Lookup with multiple hits

In a Lookup component I've defined a SQL query which returns a sorted resultset. For each Lookup component input row I want to have a single output row. Problem is that for each input row there is possibility of multiple matches in SQL query resultset. From all of the possible multiple hits I want only the first one to be returned, and if no match is found then no output row. How to implement this?

Try changing your lookup query to only return the rows you are interesting in.

Code Block

select

colA

,colB

,max or min (colC)

from

tableA

group by

colA

,colB

|||

The lookup component does this by default does it not?

Edit: that is return only the first result that it runs into. Just make sure that you return the correct row first and you should be fine...

Try the following to prove:

select 19000101 as datekey, '01/01/1900' as datename

UNION

select 19000102 as datekey, '01/02/1900' as datename

UNION

select 19000103 as datekey, '01/03/1900' as datename

UNION

select 19000104 as datekey, '01/04/1900' as datename

UNION

select 19000105 as datekey, '01/05/1900' as datename

in an oledb source

and then in the lookup

select 19000101 as datekey, 'myname11' as name

UNION

select 19000102 as datekey, 'myname21' as name

UNION

select 19000103 as datekey, 'myname31' as name

UNION

select 19000104 as datekey, 'myname41' as name

UNION

select 19000101 as datekey, 'myname12' as name

UNION

select 19000102 as datekey, 'myname22' as name

UNION

select 19000102 as datekey, 'myname23' as name

Notice, there is no match for 19000105, it will redirect.

You will return the values

19000101, '01/01/1900', 'myname11'

19000102, '01/02/1900', 'myname21'

19000103, '01/03/1900', 'myname31'

19000104, '01/04/1900', 'myname41'

|||

Eric Wisdahl wrote:

The lookup component does this by default does it not?

I believe so. The message about duplicate values is just a warning, not an error. I don't think there's a guaranteed order -- it just picks the first one it comes across.|||

Eric Wisdahl wrote:

The lookup component does this by default does it not?

Yes. Lookup is a synchronous component; hence the number of rows in the output is the same than the number of rows in the input. If your lookup query returns more than one row for an incoming row; then 'the first' one would be used; and you don't have control over which one would be used.|||SQL query returns result set of multiple contact persons for multiple companies. On output I need for each company to filter out just one of the contact persons. Result set is sorted (ORDER BY) so that if many contact persons are found per comapny one contact person that should be chosen as highest in order. On input of the Lookup component I've put OLE_SRC component which fetches all the companies.

I couldn't use just GROUP BY because I need columns in resultset which aren't used in aggregate function nor should be be group on.
|||

I would not feel comfortable relying in SSIS picking the 1st from the list. If the query in the lookup component is against SQL Server, Oracle or other RDBS where the rank function I would use a query like:

http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html

Notice that you could mimic the result set even without the rank() function; but the query could get little complex|||Great, thank you all, especially to Rafael.

In an OLE_SRC I manually entered SQL command. I had to do it manually because it seems that Query Builder doesn't (yet) support OVER construct - when I click Build Query on OLE_SRC component it says "The OVER SQL construct or statement is not supported.".
|||

Is your source sql server 2005?

If not, you'll have to use another technique to get the row number

Code Block

select

colA

,(

select count(*)

from tableA b

where b.colA <= a.colA

) as RowNum

from tableA a

|||Yes, the source is SQL Server 2005. OLE DB Source component has Query Builder GUI which doesn't support OVER construct. I entered query manually and it works.