Here's an alternative way to do it:

;WITH cte AS

(

SELECT reportqid,incidenttype,sortorder,CONVERT(CHAR(10),enddate,112) AS enddate

FROM

( SELECT *

FROM HHSDataTest

) p

UNPIVOT (sortorder FOR incidenttype IN

(

[NumFoodSvcInsPerformed],

[NumRoutineFoodSvcInsPerformed],

[NumFollowupFoodSvcInsPerformed] ,

[NumCompliantFoodSvcInsPerformed] ,

[NumTouristAccomInspections],

[NumSolidWasteInsPerformed],

[NumFacilitiesClosed]

)

) AS unpvt

),

transform AS

(

SELECT...

My pleasure 😀

As a side note: the article I referenced in my signature is not mine....

TheSQLGuru (5/14/2010)

---

Unfortunately I don't have the time to work this today, but shouldn't this be doable with simple CASE statements in a single pass? Hopefully someone else can work...

If you define your sour columns as character you might be able to use the following:

SELECT CAST('2010'+'-'+'1'+'-'+'15' AS DATETIME)

The way you've arranged your tables you probably need a RIGHT OUTER JOIN to get all values from Staff.

No, since the column definition is missing. There are no ready to use sample data either...

Please...

I would use ROW_NUMBER() OVER(PARTITION BY names, address, [basic contact info] ORDER BY hopefully_existing_rowid) as RowNmbr to identify the separate rows.

The ROW_NUMBER would be used within a CTE in order...

Would the following help?

I'm using OVER (PARTITION BY) to get the total per idName.

Side note: Since you already using dynamic SQL you might want to have a look at the...

Tara,

did you add any indexes after populating #EmployeeDur and Empgig1?

If not, I guess the following indexes might help:

Empgig1: EmpID,InDt include(EmpCode)

#EmployeeDur: EmpID,StDate,EnDate

Side note: Are you sure you need the DISTINCT clause...

Excellent job, Grant!

The only question that remains open is: Where to draw the line?

But I guess it's almost impossible to answer...

Interview questions, test/homework, almost complete projects, several hundred lines of...

Jeff Moden (5/12/2010)

---

Yes and no. You're certainly on the correct tract with making a table but it should be a permanent table instead of one built on the fly....

Jeff Moden (5/12/2010)

---

...

I'm still believe that the use of the Tally table here would be slower across a million rows than if the codes were stored in a nice...

I would quote the related msdn website.

AFAIK, websites are allowed as references.

e.g. http://msdn.microsoft.com/en-US/library/ms186734(v=SQL.90).aspx as a reference for the ROW_NUMBER() syntax.

I would copy the website together with a date information for...

I second Brandie and Elliot regarding the sandbox db.

Once that person "designed" a query he/she wants to run in production, that person should send the query to one of the...

Please re-read my previous post: I asked for your expected result based on your sample data. I can't find it...

All I see is

ROW # FIELD NAME 1 ... FIELD NAME...