This link might help you to answer your question.

Side note: the answer is based on the tone of the question...

Please provide table definition and sample data leading to your expected result in a ready to use format as described in the first link in my signature.

Also, please include what...

I can't reproduce the error you're getting:

create table #t (EmpID char(5), Name varchar(10), WorkDate date,Status varchar(1),WkdHrs int)

insert into #t

values

('00010', 'JOSEPH', '20140710','P', 8),

('05200', 'ANTHONY', '20140710','P', 8),

('35359', 'OMEGA', '20140710', Null, 0)

SELECT EmpID,...

Here's one way to do it:

with cte as

(

-- UNPIVOT the two columns

SELECT inside

FROM logs

UNION

SELECT outside

FROM logs

UNION

-- add the start time

SELECT DATEADD(dd,DATEDIFF(dd,0,MIN(inside)),0)

FROM logs

UNION

-- add the end time

SELECT DATEADD(dd,DATEDIFF(dd,0,MAX(inside)),1)

FROM logs

), cte2...

Please have a look at the CroosTab article referenced in my signature.

Get back to us if you still have questions.

duplicate post. No replies please.

Please see the Original post

at ZZartin:

Pleae run your code with the following line at the very beginning:

SET DATEFIRST 4

Strong hint: DATEFIRST is not deterministic...

I don't really understand why "product per lin within a plant" would be end up as a cross join.

Based on your example you have two rows provided:

INSERT INTO [dbo].[Resource_Mat] ([Plant],...

You might want to be a little more specific what you mean by

...

If you run the "outer" sproc in SSMS, any result set being...

thejordans21 (7/28/2014)

---

There is more data that that but put that just for a start.. I need to populate all the rows with the plant and plant desrc. I wanted to...

Once we have sample data to work with it should be a straight forward approach.

It looks like you're looking for the setup time per resource and product.

If that's the case...

Are all the tables used in the Join located at the Orcle server?

If so, I recommend to change it to a remote Query (at least for all tables located at...

Based on Eirikurs great solution I added a few minor "enhancements":

a) instead of using GETDATE() in a CASE statement, I've added a separat variable. This has two advantages: it makes...

Instead of

Union

If(@NPI is not null or @TIN IS Not null)

Begin

You'll need to remove the If condition as well as the BEGIN ... END.

Add the following line to the WHERE...

mallikachowdhary 98955 (7/26/2014)

---

Hi,

As I mentioned in my earlier post while running with OpenQuery gives me the following error:

The OLE DB provider "SAOLEDB.11" for linked server "ENIQSQLSERVER" supplied inconsistent metadata. The...