You're absolutely right.

Even with the window function removed, the original code is...

Anatoly Ukhvanov (6/13/2012)

---

Thanks, I'll «store it in my mind». 🙂

P. S. Did I choose the correct English idiom? Do American/English people say like this? (I'm...

No, it's spam, reported.

/* If you're not concerned about persisting the results, then a recursive CTE works just fine */

/* Working with more than a few thousand rows? Then spool "OrderedData" into...

SELECT TOP 1

CASE WHEN GroupCount = 1 THEN SumGroup / CountInGroup ELSE 1 END

FROM (

SELECT a,

CountInGroup= COUNT(*),

SumGroup= SUM(a),

GroupCount= COUNT(*) OVER(PARTITION BY @@spid)

FROM ( SELECT...

I can't see the need for dynamic sql here.

;WITH MainServices AS (

SELECT m.AccountKey, m.DateCompleted, m.[Service], m.AmountCompleted, m.TaxCompleted

FROM tblMainServices m

WHERE m.DateCompleted >= @MinDate

AND (m.PriceAdjustmentDescription IS NULL OR m.PriceAdjustmentDescription...

Function isn't mandatory. Don't try it. Follow Gail's advice and do the updates...

francois.vandecan (6/12/2012)

---

Tks for your quick reaction.

To explain you

Input table : several columns + 1 column containing 5 <> col (string based) in 1 (separated by |).

Output table : same several...

Are you attempting to update/insert two different tables within one merge statement? It's possible to do this with composible DML, but there's not enough information in your post to determine...

No problem. Left pad the text slice to 7 characters:

DECLARE @dbo_F_TRACKING_TK_FLIGHT VARCHAR(20)

SET @dbo_F_TRACKING_TK_FLIGHT = 'VBD55201/1MAY12'

-- Using RIGHT() is a little cleaner than SUBSTRING(). Then use STUFF to insert the spaces...

Use the correct style when using convert. The first part of your code is nearly there, albeit missing the spaces between the date elements:

DECLARE @dbo_F_TRACKING_TK_FLIGHT VARCHAR(20)

SET @dbo_F_TRACKING_TK_FLIGHT = 'VBD55201/12MAY12'

-- Using...

Lynn Pettis (6/11/2012)

---

Using the formatted data from Lowell's post (thank you, Lowell), here is one way to accomplish your task:

With tenant_history(hmy, dtdate, htent, sevent, dtoccurred, dtmovein, dtmoveout )

AS

(

SELECT '176340','2011-08-26 00:00:00.000','55496','Notice...

Good grief, this thread reads like a traincrash.

Three sample tables are presented because they are all required. No pivoting is necessary. The business rules are a little nonstandard because...

nehaCS (6/11/2012)

---

This gives the perfect result. how can I store the result set in a temp table. I want to store these values in temp table and use that temp...

SELECT [Output] = STUFF(

(SELECT ', ' + CONVERT(VARCHAR(10),DATEADD(dd,0-(7*n),LastFridayOfThisMonth),101)

FROM (

SELECT

FirstDayOfFirstMonth,

LastFridayOfThisMonth = DATEADD(dd,0-(DATEDIFF(dd,-3,x.LastDayOfLastMonth)%7),x.LastDayOfLastMonth)

FROM (

SELECT

FirstDayOfFirstMonth = DATEADD(month,DATEDIFF(month,0,GETDATE())-2,-0), -- April

LastDayOfLastMonth = DATEADD(month,DATEDIFF(month,0,GETDATE())+0,-1) -- May

) x

) y

CROSS JOIN (VALUES (0), (1),(2),(3),(4),(5),(6),(7),(8),(9)) d...