Based on initial sample data and results i assumed that only date part of #Status.EffectiveEndDate is meanigful. That is any of

#Status VALUES ('1','Same','2009-07-01','2009-09-30',0)

('1','Same','2009-07-01','2009-09-30 10:03:17',0) etc

must be treated as...

AFAIU the problem is that ERP's "SQL command window" can get only string in the form

'date1','date2'..

while SQL proc needs

'date1,date2..'

and those "command window" provides no tools...

Try this

select

f.ID ,

f.Fee ,

f.Bill ,

f.A_Date ,

f.B_Date ,

EffectiveStartDate = case when f1.EffectiveStartDate < s.EffectiveStartDate

then cast(s.EffectiveStartDate as datetime)

else f.EffectiveStartDate end,

EffectiveEndDate = case when f1.EffectiveEndDate >= s.EffectiveEndDate

then dateadd(second,...

aleksandra 81219 (9/4/2015)

No, I have n variables from erp system and these n variables (for example 21 different separate dates, not one string!) have to be passed by ONE parameter...

Mikael Eriksson SE (8/18/2015)

---

I think I have mentioned this before on these forums but it is worth mentioning again.

Specifying the text() node is semantically different from not specifying the...

Your business logic needs some clarification.

First, i can hardly belive the company and its competitors have shared inventory database to compare prices Itemid by Itemid. Shouldn't it be some goods...

So you need dynamic pyvoting. See http://www.sqlservercentral.com/scripts/pivot/110353/

Probably it can provided this join joins no more then one row with the same dep.Departure_Code from ana subquery (see below, move it to WHERE) but it...

Looks like the whole table need to be converted into single row rather then transposed. Is it what you need really?

Based on this...

Thanks for sharing.

10% i guess.

I've checked the first query text using query diagrammer, namely off-line version of https://snowflakejoins.com/ and i see that

1) Product p is never used but in this join

JOIN...

COUNT(*) OVER (PARTITION BY docnome

...

Please examin query plans for all three queries above. Most probably you will not find the difference concerning perfomance.

Optimizer always tries its best to utilize PK index, you...

Have you compared execution plans for those queries ?