Solved

# VB.Bet SQL

Posted on 2015-02-12

I got this from one of the experts and with a few changes gives me what I wanted BUT

with cte as (

SELECT distinct Id, Code

CASE WHEN start < '2014-01-01' then '2014-01-01' else start end as Start

CASE WHEN finish < '2014-04-01' then finish else

CASE WHEN finish > '2014-04-01' then '2014-04-01' else

CASE WHEN finish IS NULL then '2014-04-01' END END END as finish

FROM CodeFile

WHERE ((start <= 2014-01-01' ) and (Finish >= '2014-04-01')) or

((Start < '2014-04-01') and (finish > 2014-01-01')) or

((Start > '2014-01-01') and (finish < '2014-04-01')) or

((Start < '2014-04-01') and (finish is NULL))

)

,n as (select 0 as d union all select 1 union all select 2 union all select 3

union all select 4 union all select 5 union all select 6 union all select 7

union all select 8 union all select 9)

,n3 as (select (a.d*100)+(b.d*10)+c.d as d from n as a,n as b,n as c)

Select id

,Dateadd(dd,case when start between '20140101' and '20140401'

then n3.d else 0 end,start) as start

,Dateadd(dd,case when start between '20140101' and '20140401'

and dateadd(dd,n3.d,start)<finish

then n3.d else 0 end,

case when start between '20140101' and '20140401'

and dateadd(dd,n3.d,start)<finish

then start else finish end)as finish

,code

*** add ,cd.Supplier after adding Left Join ***

from cte cross join n3

*** Add Extra Left Join Here ***

where n3.d between 0 and datediff(d,Start,finish)-1

order by id,start,finish

Gives me the correct Records

If I add a left Join at *** Add Extra Left Join Here ***

Left Join CodeFileDates as cd on (cte.id = cd.CodeId) and

(cd.StartDate between '2014-01-01' and '2014-04-01')

or I Tried

Left Join CodeFileDts('2014-01-01','2014-04-01') as cd on (cte.id = cd.CodeId)

I Get multiple records how do I stop the date range from the left join adding

extra records meaning if the Left Join is met only return supplier for each date

created before the Left Join

With the origional SQL With out the left join I get 77 records - Correct

adding the Left Join to get the cd.supplier I get 385 records 5 Times 77

I only want the 77

Thanks