Alan, I believe that you missed the important part of the sample data. 😀

-- sample data

DECLARE @yourtable TABLE (Clt char(5), Part char(5), Date_a datetime, Val_b decimal(8,2), percent_b varchar(6));

-- only did...

I think that I found the problem. Is easy to notice it if you format your strings.

I'm tempted to post the corrected code but I'd love to have the opportunity...

I wouldn't say that it's completely right, but it's a nice try.

- The main problem (IMO) is the DVD table. It isn't normalized. I suggest you to create a...

Alan.B (4/7/2015)

---

Brandie Tarvin (4/3/2015)

---

Koen Verbeeck (4/3/2015)

---

SqlSanctum (4/3/2015)

---

No, it was never rescinded after Vatican II, almost everyone thinks it was though. Even my mother didn't believe me when I told her....

Just adding a solution that goes a little bit further than Don's.

WITH

E(n) AS(

SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E(n) --10 rows

),

E2(n) AS(

SELECT...

The "DDL" is not good as we can't use it.

Some questions:

Why are you concatenating values instead of parametrizing your queries?

Are you aware of SQL injection?

Why are you using RBAR in...

That's why it simulates LEAD...

It's easy to test this formula, but that's just the tip of the iceberg on this process. 🙂

Maybe changing the splitter function with the DelimitedSplit8K which is available in here:

http://www.sqlservercentral.com/articles/Tally+Table/72993/

I'll also go with the recommendation of using RECOMPILE or using dynamic sql as this seems like a...

If there are less than 5 digits, it means that there are bigger problems than a formula.

However, I'll stay with the original because I was trying to improve the process...

Thank you both for your solutions.

Using DB_NAME() is an easy option.

SELECT DBname= DB_NAME()

This might surprise you, but Holidaydate column is now part of the pivot columns.

SELECT DutyRosterPeriodID,

StartDate,

EndDate,

pvt.HolidayDate,...

Eirikur Eiriksson (4/6/2015)

---

Luis Cazares (4/6/2015)

---

Or maybe you might need a LIKE in your JOIN clause.

SELECT

TB.DiaryID

,TA.MedicalCodeID

FROM dbo.TableA TA

JOIN dbo.TableB TB ON TB.EmisCode LIKE...

I'd completely remove the table variable (unless you use it somewhere else). And I would use the sys.databases view.

SET NOCOUNT ON

SELECT NAME,

recovery_model_desc AS 'RECOVERY...