quote:

---

Originally posted by GilaMonster

Single quotes, not double quotes, like for all strings in SQL. Plus you have ,, in the insert. Is there a column name missing or did you just put an extra ,?

Now, based on the sample data you've provided, what do you want as an output from this query?


--
Gail Shaw
SQL Server MVP

---

DECLARE @jbhinv_rec table (
	booked_in Varchar(5),
	booked_in_2 Smalldatetime,
	work_started Varchar(5),
	work_started_2 Smalldatetime
  )

INSERT INTO @jbhinv_rec (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('08:00','20100520','12:00','20100520')
INSERT INTO @jbhinv_rec (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('08:00','20100430','12:00','20100430')
INSERT INTO @jbhinv_rec (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('11:47','20110107',NULL,NULL)

--> See what the actual table contents look like
SELECT * FROM @jbhinv_rec

--> Provide output in the (hopefully) right format
SELECT 
	DATEDIFF(hh,ISNULL(BookedIn, WorkStarted), GETUTCDate())
FROM (
	SELECT 
		BookedIn = DATEADD(MINUTE, (LEFT(booked_in, 2) * 60) + RIGHT(booked_in, 2), booked_in_2),
		WorkStarted = DATEADD(MINUTE, (LEFT(work_started, 2) * 60) + RIGHT(work_started, 2), work_started_2)
	FROM @jbhinv_rec) AS a

quote:

---

Originally posted by Lumbago

Excellent! Now we're getting somewhere :)

I had to change some of the sample data to create valid inserts but by looking at the example below you'll get the idea. Now if the data types you specified were correct (varchar(5) and smalldatetime) the following should be a plug'n'play example of what you need. Run the entire code below by itself to see if it produces the desired result:

DECLARE @jbhinv_rec table (
	booked_in Varchar(5),
	booked_in_2 Smalldatetime,
	work_started Varchar(5),
	work_started_2 Smalldatetime
  )

INSERT INTO @jbhinv_rec (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('08:00','20100520','12:00','20100520')
INSERT INTO @jbhinv_rec (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('08:00','20100430','12:00','20100430')
INSERT INTO @jbhinv_rec (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('11:47','20110107',NULL,NULL)

--> See what the actual table contents look like
SELECT * FROM @jbhinv_rec

--> Provide output in the (hopefully) right format
SELECT 
	DATEDIFF(hh,ISNULL(BookedIn, WorkStarted), GETUTCDate())
FROM (
	SELECT 
		BookedIn = DATEADD(MINUTE, (LEFT(booked_in, 2) * 60) + RIGHT(booked_in, 2), booked_in_2),
		WorkStarted = DATEADD(MINUTE, (LEFT(work_started, 2) * 60) + RIGHT(work_started, 2), work_started_2)
	FROM @jbhinv_rec) AS a

Now IF this is correct all you have to do is to remove the "@" in the table name in the select (change @jbhinv_rec to jbhinv_rec) and you can run it against your real production table.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

---

Select 
a.workshop_2 As Workshop, 
a.repair_class_2,
...
DateDiff(hh,
IsNull(a.BookedIn, a.WorkStarted), GetUTCDate()) As Live
From (
  Select
    workshop_2,
    repair_class_2,
    work_complete_2,
    BookedIn = DateAdd(MINUTE, (Left(jbhinv_rec.booked_in, 2) * 60) +
      Right(jbhinv_rec.booked_in, 2), jbhinv_rec.booked_in_2), 
    WorkStarted = DateAdd(MINUTE, (Left(jbhinv_rec.work_started, 2) * 60) +
      Right(jbhinv_rec.work_started, 2), jbhinv_rec.work_started_2)
  From jbhinv_rec) As a
Where 
  a.work_complete_2 Is Null
Order By 
...

quote:

---

Originally posted by Lumbago

Ok, this adds some more complexity to the equation but the solution is simple. The select statement in the from-clause ("...FROM (SELECT ... ) AS a") is called a derived table and is sort of an inline view and is to be treated exactly as a table with the name "a" (notice the "AS a" at the end, you can call it whatever you want...this is just an alias). In order to output any columns from the outer query you need to explicitly name them inside the derived table first. That means that you will have to do something like this:

Select 
a.workshop_2 As Workshop, 
a.repair_class_2,
...
DateDiff(hh,
IsNull(a.BookedIn, a.WorkStarted), GetUTCDate()) As Live
From (
  Select
    workshop_2,
    repair_class_2,
    work_complete_2,
    BookedIn = DateAdd(MINUTE, (Left(jbhinv_rec.booked_in, 2) * 60) +
      Right(jbhinv_rec.booked_in, 2), jbhinv_rec.booked_in_2), 
    WorkStarted = DateAdd(MINUTE, (Left(jbhinv_rec.work_started, 2) * 60) +
      Right(jbhinv_rec.work_started, 2), jbhinv_rec.work_started_2)
  From jbhinv_rec) As a
Where 
  a.work_complete_2 Is Null
Order By 
...

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

---

quote:

---

Originally posted by Lumbago

Ok, great that you finally got something working! :) Next time you want to ask a similar (or completely different question for that matter) make sure you make it easy for us to help you...it will save both you and us a lot of time.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

---