Hi Ahmad,

Investigate the COUNT aggregate function first. When comfortable with that , take a look at the PIVOT operator.

Personally i prefer to make the PIVOT'ing a frontend/disply issue...

Sounds to me like a solution using CDC (Change data capture) or CT (Change Tracking) used in conjuction with ssis would be a good alternative here.

Pivot will 'convert' rows to columns , un-pivot will go the other way columns to rows

This link contains some full examples

http://msdn.microsoft.com/en-us/library/ms177410.aspx

Sure , investigate the use of heirarchyId.

Hi ,

I think you are going to have to go into a bit more detail about what you dont understand.

TimeToShine (1/19/2012)

---

Does this sound like a logical solution, or more like a hack? If the latter, what would be the best hierarchy for the situation, assuming we are starting...

They are "Table Variables".

Ok , so Brad says

"Another way to use Profiler is to monitor live,

production activity on a 24/7 basis. Wait just a moment. Did I just recommend using Profiler 24/7?

Isn't this...

Hmmm , that is quite a convuluted mess.

Start by doing a proper join rather than sub-selects

so

FROM [NADECORP\SNjenga].ACTUAL_HDD_DAILY nolock

WHERE WEATHER_STATION =

(SELECT WEATHER_STATION FROM StagingVolume WHERE BillDate_ID = @intRowNo)

AND Market_ID =...

Grant has done a good right up of the reasons why, http://www.scarydba.com/2008/12/18/profiler-research/

Given that there are easy and viable alternatives, I wouldnt want to take the risk with my production...

So something like this ?

Create table #Cust

(

CustomerId integer

)

go

Create Table #Orders

(

OrderId integer,

CustomerId integer,

DueDate date)

go

insert into #Cust(CustomerId) values(1)

go

insert into #Orders(OrderId,CustomerId,DueDate)

values(1,1,'20120101')

insert into #Orders(OrderId,CustomerId,DueDate)

values(2,1,'20120201')

insert into #Orders(OrderId,CustomerId,DueDate)

values(2,1,'20120301')

go

select * from

#Cust Outer apply (Select top(1) #Orders.*

...

Hmmm , that does sound slow. Could point to errors further down the stack ??

Tried setting up Instant File Initialization ? http://sqlblog.com/blogs/tibor_karaszi/archive/2009/03/09/do-you-have-instant-file-initialization.aspx

Yes, the one-to-many is what you should be aiming for. Its always better to over-engineer than under-engineer.

Try this article http://www.sqlservercentral.com/articles/Tally+Table/72993/ on how to split a comma delimeted string. Then its just a simple join.

agreed sp_executesql should *always* be used. But quite why dynamic sql is even being used here is anyones guess 🙂