| Author |
 Topic  |
|
mole999
Starting Member
United Kingdom
17 Posts |
 Posted - 01/11/2011 : 08:10:37
|
Struggling to see the wood for the trees
I'm Using
GetUTCDate() -
IsNull(jbhinv_rec.work_started + +jbhinv_rec.work_started_2,
jbhinv_rec.booked_in_2 + +jbhinv_rec.booked_in) As Day,
to get a date from the server time and then use either the combined start date with start time or the booked start date with start time, all that works perfectly for me
what I can't manage to do is to take the resulting
06/05/1900 04:25:16 value and (A) present as Days, and then (B) repeating with the same query as [h]:mm
its exporting to excel and i can convert it visually no problem, but it feeds a pivot table query which when drilled down produces the 1900 date system
I've looked at CAST, and CONVERT and DATEDIFF, but frankly totally lost at this time, and no longer can construct a good keyword search
gentle pointers much appreciated
Mole |
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 01/11/2011 : 08:18:36
|
Maybe my brain is fried or something but I can't seem to understand your requirements.
Can you write which columns you have, which datatype they are and what you expect the output to look like...??
- Lumbago
My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com |
 |
|
|
mole999
Starting Member
United Kingdom
17 Posts |
Posted - 01/11/2011 : 08:37:03
|
quote:
Originally posted by Lumbago
Maybe my brain is fried or something but I can't seem to understand your requirements.
Can you write which columns you have, which datatype they are and what you expect the output to look like...??
- Lumbago
My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
The relevant columns are
jbhinv_rec.work_started Varchar(5)
jbhinv_rec.work_started_2, Smalldatetime
jbhinv_rec.booked_in_2 Varchar(5)
jbhinv_rec.booked_in Smalldatetime
now if I have this right
GetUTCDate produces 11/01/2011 13:31:23
and
jbhinv_rec.booked_in_2 + +jbhinv_rec.booked_in
will produce a dd/mmm/yyyy hh:mm:ss value
I want to take the built value from the GetUTCDate which gives me a result like 06/01/1900 04:54:23
That result, I want to convert to
(A) whole days (output example 2)
and also where necessary be able to convert to
(B) total hours and minutes (output example 25:33)
before it arrives on the spreadsheet
Mole |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/11/2011 : 10:47:50
|
sorry didnt understand how you got 06/01/1900 04:54:23. can you elaborate?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
mole999
Starting Member
United Kingdom
17 Posts |
Posted - 01/11/2011 : 12:52:33
|
quote:
Originally posted by visakh16
sorry didnt understand how you got 06/01/1900 04:54:23. can you elaborate?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Can't give you an exact value as I'm now at home, but if I take my derived value from the GetUTCDate
GetUTCDate() - jbhinv_rec.work_started + +jbhinv_rec.work_started_2
i.e. 11/1/2011 09:00:00 - 10/1/2011 08:00:00 I get a value that looks like 06/05/1900 04:25:16 as the result.
If i format it in excel I get the hours or days as i need them, so I'm looking to apply the format before it gets to the spreadsheet
Mole |
|
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 01/12/2011 : 03:26:14
|
I feel kinda stupid here but I'm still not clear about the requirements. Can you provide insert statements to this table and also the *exact* format of the output you want? ->
DECLARE @jbhinv_rec table (
work_started Varchar(5)
work_started_2, Smalldatetime
booked_in_2 Varchar(5)
booked_in Smalldatetime
)
- Lumbago
My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com |
|
|
|
Sachin.Nand
Flowing Fount of Yak Knowledge
2937 Posts |
Posted - 01/12/2011 : 04:00:37
|
quote:
The relevant columns are
jbhinv_rec.work_started Varchar(5)
jbhinv_rec.work_started_2, Smalldatetime
jbhinv_rec.booked_in_2 Varchar(5)
jbhinv_rec.booked_in Smalldatetime
Why some columns have varchar datatypes to save the datetime values ?
PBUH
|
Edited by - Sachin.Nand on 01/12/2011 04:01:12 |
|
|
|
mole999
Starting Member
United Kingdom
17 Posts |
Posted - 01/12/2011 : 07:01:42
|
quote:
Originally posted by Sachin.Nand
quote:
The relevant columns are
jbhinv_rec.work_started Varchar(5)
jbhinv_rec.work_started_2, Smalldatetime
jbhinv_rec.booked_in_2 Varchar(5)
jbhinv_rec.booked_in Smalldatetime
Why some columns have varchar datatypes to save the datetime values ?
PBUH
I have no idea, thats what SQLDrill shows exists when i look at the structure. Its as the structure has been put together, and what i have to work with
Mole |
Edited by - mole999 on 01/12/2011 07:12:45 |
|
|
|
mole999
Starting Member
United Kingdom
17 Posts |
Posted - 01/12/2011 : 07:11:51
|
quote:
Originally posted by Lumbago
I feel kinda stupid here but I'm still not clear about the requirements. Can you provide insert statements to this table and also the *exact* format of the output you want? ->
DECLARE @jbhinv_rec table (
work_started Varchar(5)
work_started_2, Smalldatetime
booked_in_2 Varchar(5)
booked_in Smalldatetime
)
- Lumbago
My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
I have READ ONLY access to the data
as for output if it can show total hours and minutes Like (67:34) also i want to be able show the 67 hours as 3 days, but obvious seperately,
some records have been open for 87 thousand hours and I need to focus in on those so we can get them closed
looking at Insert dosent seem to be what I want, I'm taking 4 columns,, if A and B are Empty then I will use C and D as in this part of the statement
IsNull(jbhinv_rec.work_started + +jbhinv_rec.work_started_2,
jbhinv_rec.booked_in_2 + +jbhinv_rec.booked_in) As Day
producing ddmmmyyyy hh:mm:ss
that is a time in the past,
so taking the current time and removing the previous from that I get the offset in hours and minutes or days since the job began,
therefore at this moment in time I know how long we have been doing the job, so even if i could store that value, it would need to be updated constantly until the work is finished
Mole |
|
|
|
MIK_2008
Aged Yak Warrior
Pakistan
822 Posts |
Posted - 01/12/2011 : 07:18:50
|
Well Mole my brain is fried too ... after looking all details ..
However I think you are finding it difficult to move the data into Excel Sheet with the format as it looks in SQL?
|
|
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 01/12/2011 : 07:27:47
|
I'm about to give up...is it really that hard to provide some SAMPLE data in a table format? You have read access to the database so it should be fairly simple to write some simple insert statements for some of the rows so we know what we are dealing with.
Read this article -> http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server
A good ways down it has a section called "DateTime Durations That Span Multiple Days" that will show you how do display the difference between two datetimes in days, hours, minutes and seconds. Unless you come up with some sample data this will be my last say on this matter!
- Lumbago
My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com |
Edited by - Lumbago on 01/12/2011 07:29:02 |
|
|
|
mole999
Starting Member
United Kingdom
17 Posts |
Posted - 01/12/2011 : 07:46:15
|
Maybe I should state this differently
I know that
DateDiff(d, jbhinv_rec.work_started_2, GetUTCDate())
Will give me Days between the current time and the midnight of the day, but i am missing the hours, hence combining
jbhinv_rec.work_started + +jbhinv_rec.work_started_2
Can anyone tell me how to combine those two into a single date / time and name it so i can reuse that result
I still need to crack the IF one is set ISNULL then use the other, and the extra comma in DATEDIFF, is stopping that from working
I'm trying to avoid anything that is a write statement to the server as I don't have those priveldges hences doing this on the fly
Mole |
|
|
|
mole999
Starting Member
United Kingdom
17 Posts |
Posted - 01/12/2011 : 08:03:50
|
quote:
Originally posted by Lumbago
I'm about to give up...is it really that hard to provide some SAMPLE data in a table format? You have read access to the database so it should be fairly simple to write some simple insert statements for some of the rows so we know what we are dealing with.
Read this article -> http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server
A good ways down it has a section called "DateTime Durations That Span Multiple Days" that will show you how do display the difference between two datetimes in days, hours, minutes and seconds. Unless you come up with some sample data this will be my last say on this matter!
- Lumbago
My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
Booked_In Booked_in_2 Work_started work_Started_2
12:00 20/05/2010 08:00 20/05/2010
12:00 30/04/2010 08:00 30/04/2010
11:47 07/01/2011
09:08 18/12/2010 09:08 18/12/2010
18:00 10/01/2011 17:30 10/01/2011
07:00 11/01/2011 17:00 10/01/2011
10:00 11/12/2010
12:18 12/01/2011 12:18 12/01/2011
12:21 12/01/2011
11:05 09/07/2010 11:00 09/07/2010
12:40 10/07/2010
07:00 14/10/2010 07:00 14/10/2010
08:00 14/10/2010
19:31 21/10/2010 19:31 21/10/2010
11:30 22/10/2010 11:30 22/10/2010
some of th data has moved over, not all the booked are completed, and should workstarted
I have no idea how to put a table in here
I will go of and read that link
Mole |
|
|
|
mole999
Starting Member
United Kingdom
17 Posts |
Posted - 01/12/2011 : 08:44:45
|
quote:
Originally posted by Lumbago
I'm about to give up...is it really that hard to provide some SAMPLE data in a table format? You have read access to the database so it should be fairly simple to write some simple insert statements for some of the rows so we know what we are dealing with.
Read this article -> http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server
A good ways down it has a section called "DateTime Durations That Span Multiple Days" that will show you how do display the difference between two datetimes in days, hours, minutes and seconds. Unless you come up with some sample data this will be my last say on this matter!
- Lumbago
My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
I guess I'm not comprehending INSERT. To me that would place data in the SQL datatable on the Server, thereby changing the records ?? I can't find it meaning anything else
Mole |
|
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 01/12/2011 : 09:05:16
|
What he wants is for you to provide insert statements so that he can run them (not you) to put data into test tables so that he can then test out queries for you.
--
Gail Shaw
SQL Server MVP |
|
|
|
mole999
Starting Member
United Kingdom
17 Posts |
Posted - 01/12/2011 : 09:06:17
|
quote:
Originally posted by GilaMonster
What he wants is for you to provide insert statements so that he can run them (not you) to put data into test tables so that he can then test out queries for you.
--
Gail Shaw
SQL Server MVP
Ah that makes sense to me now, much obliged for the explanation
Mole |
|
|
|
mole999
Starting Member
United Kingdom
17 Posts |
Posted - 01/12/2011 : 09:35:24
|
Is this right ?
insert into table_name (Booked_In, Booked_In_2,, Work_started, Work_started_2)
08:00,20/05/2010,12:00,20/05/2010
08:00,30/04/2010,12:00,30/04/2010
11:47,07/01/2011,,
09:08,18/12/2010,09:08,18/12/2010
17:30,10/01/2011,18:00,10/01/2011
17:00,10/01/2011,07:00,11/01/2011
10:00,11/12/2010,,
12:18,12/01/2011,12:18,12/01/2011
12:21,12/01/2011,,
11:00,09/07/2010,11:05,09/07/2010
,,12:40,10/07/2010
07:00,14/10/2010,07:00,14/10/2010
08:00,14/10/2010,,
19:31,21/10/2010,19:31,21/10/2010
11:30,22/10/2010,11:30,22/10/2010
12:00,30/12/2010,12:15,30/12/2010
10:00,30/12/2010,10:00,30/12/2010
07:00,11/01/2011,,
14:36,11/01/2011,14:36,11/01/2011
06:00,12/01/2011,06:30,12/01/2011
12:30,23/12/2010,12:40,23/12/2010
13:52,14/12/2010,13:52,14/12/2010
15:15,09/01/2011,,
20:45,11/01/2011,,
07:00,12/01/2011,,
07:00,12/01/2011,15:00,12/01/2011
14:00,31/12/2010,14:00,31/12/2010
07:51,12/01/2011,,
07:00,12/01/2011,08:00,12/01/2011
08:00,12/01/2011,09:00,12/01/2011
16:20,17/11/2010,16:20,17/11/2010
,,,
15:00,09/12/2010,,
08:45,12/01/2011,08:50,12/01/2011
08:53,12/01/2011,08:53,12/01/2011
08:30,12/01/2011,08:30,12/01/2011
10:00,12/01/2011,10:30,12/01/2011
10:30,12/01/2011,,
08:30,12/01/2011,08:30,12/01/2011
12:00,06/01/2011,,
07:00,09/12/2010,12:00,09/12/2010
08:00,11/01/2011,08:45,11/01/2011
08:30,11/01/2011,08:45,11/01/2011
13:36,12/01/2011,13:36,12/01/2011
13:44,12/01/2011,13:44,12/01/2011
12:00,12/01/2011,12:00,12/01/2011
16:45,06/01/2011,,
08:00,10/01/2011,08:00,10/01/2011
13:30,30/11/2010,13:30,30/11/2010
15:00,08/01/2011,15:00,10/01/2011
08:00,10/01/2011,,
14:00,07/01/2011,14:00,07/01/2011
08:37,06/01/2011,,
,,08:30,07/08/2010
06:30,31/08/2010,10:00,31/08/2010
14:44,02/09/2010,,
07:52,07/09/2010,07:52,07/09/2010
08:24,08/09/2010,08:24,08/09/2010
08:00,08/09/2010,08:09,08/09/2010
07:00,21/09/2010,,
06:00,22/09/2010,06:00,22/09/2010
11:27,22/09/2010,11:28,22/09/2010
10:16,24/09/2010,10:17,24/09/2010
06:00,24/09/2010,06:00,24/09/2010
08:00,10/01/2011,,
11:30,10/01/2011,,
10:00,28/09/2010,10:00,28/09/2010
10:00,07/12/2010,11:00,07/12/2010
16:00,08/01/2011,16:30,08/01/2011
07:00,05/10/2010,07:30,05/10/2010
08:30,06/10/2010,08:30,06/10/2010
01:00,02/01/2011,,
12:00,02/12/2010,12:00,08/12/2010
07:00,11/01/2011,07:00,11/01/2011
11:30,11/01/2011,11:30,11/01/2011
12:00,11/01/2011,,
19:30,11/01/2011,11:58,12/01/2011
09:00,14/12/2010,09:00,14/12/2010
09:00,11/01/2011,11:00,11/01/2011
,,10:13,09/12/2010
08:00,04/01/2011,,
11:30,30/12/2010,11:45,30/12/2010
09:00,07/01/2011,,
08:00,20/12/2010,11:30,20/12/2010
11:00,12/01/2011,11:00,12/01/2011
Mole |
|
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 01/12/2011 : 10:02:32
|
No. Open up Books Online (SQL help) and look at the syntax of an insert.
The first line is right, the second needs the keyword VALUES, some brackets and some quotes, and there can only be one set of values per insert statement.
--
Gail Shaw
SQL Server MVP |
|
|
|
mole999
Starting Member
United Kingdom
17 Posts |
Posted - 01/12/2011 : 11:27:05
|
quote:
Originally posted by GilaMonster
No. Open up Books Online (SQL help) and look at the syntax of an insert.
The first line is right, the second needs the keyword VALUES, some brackets and some quotes, and there can only be one set of values per insert statement.
--
Gail Shaw
SQL Server MVP
OK, I will do that, sorry about learning on the fly
Mole |
|
|
|
mole999
Starting Member
United Kingdom
17 Posts |
Posted - 01/12/2011 : 11:43:24
|
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 ("","","","")
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")
Mole |
|
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 01/12/2011 : 13:54:46
|
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 |
|
|
|
Topic |
|
|
|
Hours And Also Days
