Why?

marc.eilander 13301 (7/31/2013)

---

Hi, Thank you for your response. However the goal is to only retrieve those lines starting with Int.nr.TXT:. I checked the data, all lines have a valid...

Charlottecb (7/30/2013)

---

Hi Chris,

Firstly, thanks again for taking the time to help me...

The final script you wrote doesn't quite give me what I need - If I add an order for...

Have a look at the two queries you are UNIONing. The WHERE clauses are the exact opposite (bar a small oversight, WO.ORDER_DATE >= @vLAST_ORDER_DATE should be WO.ORDER_DATE > @vLAST_ORDER_DATE). That...

Spoot

Ventolin

;WITH Source_table (VENDID, [TYPE]) AS

(SELECT 'ABC', 1 UNION ALL

SELECT 'ABC', 0 UNION ALL

SELECT 'ABC', 0 UNION ALL

SELECT 'XYZ', 1 UNION ALL

SELECT 'XYZ', 0 UNION ALL

SELECT 'XYZ', 0 UNION ALL

SELECT 'XYZ',...

Crofter

Charlottecb (7/30/2013)

---

Thanks ChrisM, that's great - it fixed my issue. Many thanks for your help.

Final question, any idea how to modify this to group by the week instead of the...

Your calendar function contains a recursive CTE. You can use option(MAXRECURSION 0) or something to get around your error message. Here's the definition of my calendar iTVF:

ALTER FUNCTION [dbo].[IF_Calendar]

(

@StartDate...

thunderousity (7/30/2013)

---

ChrisM@Work (7/30/2013)

---

Gotcha. I think an efficient TSQL version might use EXISTS, something like this:

SELECT *

FROM MyTable

WHERE EXISTS (

...

Try this, Ryan.

Usage is like this:

SELECT *

FROM dbo.IF_Calendar ('2000-01-01','2013-12-31','monday')

It's hardcoded for your fiscal year.

CREATE FUNCTION [dbo].[IF_Calendar]

(

@StartDate DATE,

@EndDate DATE,

@FirstWeekDay VARCHAR(10) -- e.g. 'monday'

)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN...

Gotcha. I think an efficient TSQL version might use EXISTS, something like this:

SELECT *

FROM MyTable

WHERE EXISTS (

...

I use an inline tally table calendar generator, otherwise everything is the same as your environment:

;WITH Agg AS (

SELECT

s.SalesmanID,

s.Name,

c.DateRange,

o.OrderTotal,

seq = ROW_NUMBER() OVER(PARTITION BY s.SalesmanID ORDER BY c.DateRange)

FROM...

crmitchell (7/30/2013)

---

ChrisM@Work (7/29/2013)

---

The OP is now selling real estate in Argentina, 3 1/2 years later.

Margarett, if the leftmost part of the...