May 5, 2015 at 1:00 pm
Hello All,
In my new job , I am looking at some tables in a database at one of the columns "Time" .
It SURPRISINGLY has 5 digit integers as the data and the datatype is "FLoat" in few tables and "varchar" in others. I am cracking my head around this.
No one around me (my colleagues) know anything bt databases , so I left to figure out by myself.
Any suggestion pls!!!
Thanks in advance
May 5, 2015 at 1:07 pm
Not enough information to answer very well, but do you have a date or a time dimension/table in your database?
May 5, 2015 at 1:12 pm
To elaborate a little :
My current task is to append a csv file (sent by another dept) to existing tables in sql server.
One of the column in the SQL server tables is "Time" and the existing data in these columns are integers .
example : they have values like 34567, 87658
When I look at the design of the table, to view the datatype of the column : It is "float" in some tables and "varchar" in few others. I cant make sense out of this !!!
I am trying to append the columns from the csv file to the columns in sql server table and this is giving me a hiccup!! I am unable to proceed any further
Please help
Thanks
May 5, 2015 at 1:36 pm
what are the max and min values of column "Time"?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 5, 2015 at 1:39 pm
In one of the tables I am looking at :
MAX Value: 82860
MIN Value : 24480
May 5, 2015 at 1:41 pm
anusha.guntupalli (5/5/2015)
In one of the tables I am looking at :MAX Value: 82860
MIN Value : 24480
could this be seconds past midnight?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 5, 2015 at 1:45 pm
J Livingston SQL (5/5/2015)
anusha.guntupalli (5/5/2015)
In one of the tables I am looking at :MAX Value: 82860
MIN Value : 24480
could this be seconds past midnight?
My thoughts too. 86400 seconds in a day.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 5, 2015 at 1:48 pm
This is the first time I am hearing of any such thing!!!!.
But if it is so...then 82860 is closer to the midnight and usually, no transactions occur at that time
May 5, 2015 at 2:32 pm
You can do a simple analysis of how many rows per hour to see if that metric makes sense.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 5, 2015 at 4:21 pm
Ohk .Thanks.
If it makes sense that way,how do I do the math to convert say a 8.00 AM (from 'Time' column in csv file) to seconds after midnight in SQL server tables?
Thank you in advance.
May 5, 2015 at 9:15 pm
Quick question, could it be seconds after midnight UTC?
😎
To convert to a time value, use DATEADD(SECOND....
DECLARE @TTIME TIME = CONVERT(TIME,'00:00:00');
DECLARE @SECONDS_AFTER_MIDNIGHT FLOAT = 75644;
SELECT DATEADD(SECOND,@SECONDS_AFTER_MIDNIGHT,@TTIME);
May 5, 2015 at 9:23 pm
This would be in Mountain Standard Time (MST)
Thank you
May 6, 2015 at 1:43 pm
I assume the tables are used directly instead of a view. Later, you could possibly add a view that combines date and time columns as one value. Any future uses of this information would utilise the view instead of the table at the least. I have seen where dates and times are stored as integers, they can come from a old mainframe system that had no concept of datetime. Just to make a note on the inconsistent data type you are seeing with <Time>, I also assume these are not staging tables either, correct?
----------------------------------------------------
May 6, 2015 at 5:58 pm
TheSQLGuru (5/5/2015)
You can do a simple analysis of how many rows per hour to see if that metric makes sense.
Here's a query that makes this suggestion pretty simple:
WITH SampleTimes (t) AS
(
SELECT CAST(82860 AS FLOAT)
UNION ALL SELECT 24480
)
SELECT TimeSlot
,DistributionByHour=COUNT(
CASE WHEN t BETWEEN TimeSlotOffsetSeconds AND TimeSlotOffsetSeconds + 3600
THEN 1
END)
FROM SampleTimes a
CROSS APPLY dbo.CalculateTimeSlots('hour', 1, NULL, NULL) b
GROUP BY TimeSlot;
Substitute the SampleData CTE with your table of interest. The CalculateTimeSlots function can be found here: Time Slots - An Essential Extension to Calendar Tables[/url]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 6, 2015 at 10:12 pm
Thank you .will definitely give this a try
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply