Thanks Steve. Something along these lines then. Not sure where two of the tables (MG_COUNTRY,MG_OFFICE) fit into this but it's worth a try:

SELECT *

INTO #Offices

FROM (VALUES

('KBE ANR','BELGIUM'),('KBE ZEE','BELGIUM'),('KEU...

TomThomson (8/14/2014)

---

The same here - Cheshire is not famous...

This is worth a try:

SELECT *

INTO #Offices

FROM (VALUES

('KBE ANR','BELGIUM'),('KBE ZEE','BELGIUM'),('KEU ANR','BELGIUM'),

('KDE VIE','AUSTRIA'),

('KDE BRE','GERMANY'),('KDE BRV','GERMANY'),('KDE DUS','GERMANY'),('KDE FRA','GERMANY'),('KDE HAM','GERMANY'),('KDE MUC','GERMANY'),('KDE STR','GERMANY'),

('KFR BOD','FRANCE'),('KFR DKK','FRANCE'),('KFR FOS','FRANCE'),('KFR LEH','FRANCE'),('KFR LIO','FRANCE'),('KFR MRS','FRANCE'),

('KFR PAR','FRANCE'),('EXAF PARIS','FRANCE'),('FRBAL BOD','FRANCE'),('KEU...

;WITH Dates AS (

SELECT [date]

FROM (VALUES

(GETDATE()-7.7),(GETDATE()-6.6),(GETDATE()-5.5),(GETDATE()-4.4),(GETDATE()-3.3),

(GETDATE()-2.2),(GETDATE()-1.1),(GETDATE()-0),(GETDATE()+1.1),(GETDATE()+2.2),

(GETDATE()+3.3),(GETDATE()+4.4),(GETDATE()+5.5),(GETDATE()+6.6),(GETDATE()+7.7)

) d ([date])

)

SELECT

[date],

[Dayname] = DATENAME(dw, [date]),

LatestSunday = DATEADD(DAY,-1+DATEDIFF(DAY,-1,[date])/7*7,0),

LatestMonday = DATEADD(DAY,DATEDIFF(DAY,0,[date])/7*7,0),

LatestTuesday = DATEADD(DAY,1+DATEDIFF(DAY,1,[date])/7*7,0)

FROM Dates

Shadab Shah (8/6/2014)

---

Thanks Chris for the reply and your time.

Since yesterday i am trying to improvised on the solution given by you.

Actually there is one more condition , Consider the...

TomThomson (8/13/2014)

---

GilaMonster (8/13/2014)

---

TomThomson (8/13/2014)

---

I wish I could give you some of ours. ...

pwalter83 (8/13/2014)

---

ChrisM@home (8/8/2014)

---

pwalter83 (8/8/2014)

---

Any ideas, Chris ??[/quote

Hi Paul, I'm on break in Normandy with no access to SQL Server until tuesday....

You may also wish to correct the error. Your function returns...

Phil Parkin (8/13/2014)

---

Not by me! Or anyone else,...

-- Your function returns incorrect values when the time of the from-date is later than the time of the to-date.

-- Also, it's likely to be far faster written as an...

SELECT

e1.Place,

e1.Objective,

e1.[Year],

e1.[Month],

e1.Exam1,

e2.Exam2,

e3.Exam3

FROM #tmpExam1 e1

LEFT JOIN #tmpExam2 e2

ON e2.Place = e1.Place

AND e2.[Year] = e1.[Year]

AND e2.Objective = e1.Objective

AND e2.[Month] = e2.[Month]

INNER JOIN #tmpExam3 e3

ON e3.Place...

rajsin7786 (8/11/2014)

---

Hi guys,

thanks for all your help, i have updated statistics on all the columns, I ran the query suggested by ChrisM and also the original query , both...

ScottPletcher (8/7/2014)

---

ChrisM@Work (8/7/2014)

---

Each reply posted to your question implies "maybe", "probably" or "definitely". The average of these isn't...

I don't see any parameters in your code, Paul.

Each reply posted to your question implies "maybe", "probably" or "definitely". The average of these isn't "definitely not"!