SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Hours And Also Days
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

mole999
Starting Member

United Kingdom
17 Posts

Posted - 01/12/2011 :  14:08:59  Show Profile  Reply with Quote
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



The typos are all my own work

insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('08:00','20/05/2010','12:00','20/05/2010')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('08:00','30/04/2010','12:00','30/04/2010')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('11:47','07/01/2011','','')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('09:08','18/12/2010','09:08','18/12/2010')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('17:30','10/01/2011','18:00','10/01/2011')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('17:00','10/01/2011','07:00','11/01/2011')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('10:00','11/12/2010','','')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('12:18','12/01/2011','12:18','12/01/2011')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('12:21','12/01/2011','','')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('11:00','09/07/2010','11:05','09/07/2010')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('','','12:40','10/07/2010')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('07:00','14/10/2010','07:00','14/10/2010')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('08:00','14/10/2010','','')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('19:31','21/10/2010','19:31','21/10/2010')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('11:30','22/10/2010','11:30','22/10/2010')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('12:00','30/12/2010','12:15','30/12/2010')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('10:00','30/12/2010','10:00','30/12/2010')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('07:00','11/01/2011','','')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('14:36','11/01/2011','14:36','11/01/2011')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('06:00','12/01/2011','06:30','12/01/2011')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('12:30','23/12/2010','12:40','23/12/2010')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('13:52','14/12/2010','13:52','14/12/2010')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('15:15','09/01/2011','','')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('20:45','11/01/2011','','')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('07:00','12/01/2011','','')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('07:00','12/01/2011','15:00','12/01/2011')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('14:00','31/12/2010','14:00','31/12/2010')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('07:51','12/01/2011','','')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('07:00','12/01/2011','08:00','12/01/2011')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('08:00','12/01/2011','09:00','12/01/2011')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('16:20','17/11/2010','16:20','17/11/2010')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('15:00','09/12/2010','','')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('08:45','12/01/2011','08:50','12/01/2011')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('08:53','12/01/2011','08:53','12/01/2011')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('08:30','12/01/2011','08:30','12/01/2011')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('10:00','12/01/2011','10:30','12/01/2011')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('10:30','12/01/2011','','')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('08:30','12/01/2011','08:30','12/01/2011')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('12:00','06/01/2011','','')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('07:00','09/12/2010','12:00','09/12/2010')
insert into table_name (Booked_In, Booked_In_2, Work_started, Work_started_2)
VALUES ('08:00','11/01/2011','08:45','11/01/2011')

As a first cut I need the left two columns to make a usable date time and the right two columns to make a usable date time, as noted elsewhere I need to avoid the overhead of just a date so it will be like ddmmmyyyy hh:mm:ss

when I have the two times i then intend to get to one master time i.e.

IsNull(TimeA,TimeB)

I then need to use that master result in

DATEDIFF(hh,result,GETUTCDate) which should give me whole hours

as i tried to demonstrate above I was trying to do that in one movement

DATEDIFF(hh,ISNULL(DateA++TimeA,DateB++TimeB),GETUTCDate)

which gives me whole hours from start of job, whether booked or started until the current check by running the SQL



Mole
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/12/2011 :  14:27:11  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Not an explanation, you've already posted that. An example.

You've given us sample data. Now, for that sample data (or at least a subset of it) what are the exact values that you want back

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 01/13/2011 :  03:21:57  Show Profile  Reply with Quote
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
Go to Top of Page

mole999
Starting Member

United Kingdom
17 Posts

Posted - 01/13/2011 :  06:23:13  Show Profile  Reply with Quote
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




Thank you for your foreberance, I used the routine as described and have indeed seen values for the live data

I then crafted your teachings into the routine

Select
jbhinv_rec.workshop_2 As Workshop,
jbhinv_rec.repair_class_2,
jbhinv_rec.job_number As Job_Ref,
jbhinv_rec.fleet_noaccoun As Fleet,
jbhinv_rec.reg_no As Reg,
jbhinv_rec.job_title As Repair,
jbhinv_rec.repair_cause_2,
jbhinv_rec.supplier_2,
DateDiff(hh,
IsNull(a.BookedIn, a.WorkStarted), GetUTCDate()) As Live
From (
Select
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
jbhinv_rec.work_complete_2 Is Null
Order By
jbhinv_rec.workshop_2,
jbhinv_rec.repair_class_2 Desc

which is throwing the error "The multi-part identifier ... could not be bound" where the reference is "jbhinv_rec.work_complete_2" as it appears to me I am using a full reference to the table, i'm not sure which bit needs tweaking

Mole
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 01/13/2011 :  06:37:20  Show Profile  Reply with Quote
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
Go to Top of Page

mole999
Starting Member

United Kingdom
17 Posts

Posted - 01/13/2011 :  06:57:49  Show Profile  Reply with Quote
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




Wasn't trying to mislead with complexity, as you could see I was asking for a lot doing the equation, and I didn't want to add irrelevancies to the problem I was trying to solve

I've only been making minor queries for about three months and don't have any sql programmes on my system to make and test tables from, I've learnt by adapting and the running from excel, and a very simple SQL tool for reading the result (and crafting queries).

A Russian History Teacher friend of mine suggested this and it works
DATEDIFF(d, CONVERT(VARCHAR, ISNULL(jbhinv_rec.booked_in + +jbhinv_rec.booked_in_2,jbhinv_rec.work_started + +jbhinv_rec.work_started_2), 120), GETUTCDATE())
which fits my needs for a Day level report

Though I appreciate i will have to learn more flexible routines and will be using yours very shortly

Mole

Edited by - mole999 on 01/13/2011 07:33:15
Go to Top of Page

mole999
Starting Member

United Kingdom
17 Posts

Posted - 01/13/2011 :  09:01:10  Show Profile  Reply with Quote
Just to share a solution (this is what i had been asking for in my original post (I accept other ways are probably better))

Select jbhinv_rec.workshop_2 As Workshop, jbhinv_rec.repair_class_2,
jbhinv_rec.job_number As Job_Ref, jbhinv_rec.fleet_noaccoun As Fleet,
jbhinv_rec.reg_no As Reg, jbhinv_rec.job_title As Repair,
jbhinv_rec.repair_cause_2, jbhinv_rec.supplier_2, DateDiff(hh,
Convert(VARCHAR,IsNull(DateAdd(MINUTE, (Left(jbhinv_rec.booked_in, 2) * 60) +
Right(jbhinv_rec.booked_in, 2), jbhinv_rec.booked_in_2), DateAdd(MINUTE,
(Left(jbhinv_rec.work_started, 2) * 60) + Right(jbhinv_rec.work_started, 2),
jbhinv_rec.work_started_2)),120), GetUTCDate())
As Hrs
From jbhinv_rec
Where jbhinv_rec.work_complete_2 Is Null
Order By jbhinv_rec.workshop_2, jbhinv_rec.repair_class_2 Desc

It looks correct when i run it and gives a good Hour return, I can sacrifice the minutes portion for my needs if I wish to via this route

Mole
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 01/13/2011 :  09:21:29  Show Profile  Reply with Quote
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
Go to Top of Page

mole999
Starting Member

United Kingdom
17 Posts

Posted - 01/13/2011 :  10:13:11  Show Profile  Reply with Quote
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




I thought I had started off easily, but knowing what to say is much the same as knowing the keywords to search for. But now i have a further stepping stone to branch further out on, and for that i am much obliged

Mole
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.15 seconds. Powered By: Snitz Forums 2000