Add varchar columns with colon in between to show time

Question

Add varchar columns with colon in between to show time

Viewing 15 posts - 31 through 45 (of 52 total)

You must be logged in to reply to this topic. Login to reply

pwalter83 SSChampion Points: 14575 More actions · Answer 1

Jeff Moden (3/29/2012)
Eugene Elutin (3/29/2012)
Just looking into this thread again...
What I can say, crap data structure design quickly leads to crap and stupid coding you need to do to achieve something good!
Calculating VARCHARs - Plain stupid!
Can some someone come up with one good reason why would you want to store time as varchar in HH:MM format? Just one!
And don't tell me it's because of nice formatting as it it's not even that! It's not "HH:MM" it is: "#00:00" !
Verdict: REDESIGN!
Heh... calm down ol' friend. It doesn't sound good when Celko does that. What makes you think you'll sound better? I think Celko makes a real *** out of himself when he loses it like this. We've all had to deal with "crap data" before and it's usually out of our control. 😉

In my case, you are right, it IS out of my control. When I requested the creator of the table to modify the datatype, he simply said he could'nt as he had his own restrictions.

But the point is I have changed my query that does'nt at all involve what Eugene referred to. I have requirement now to SUM the time columns and then ADD them up. Anders was able to answer part of the query which works out correctly.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Are you all set then or do you still need some help to pull all of this off?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Thank you all for defending me...

Sorry, I wasn't right. I didn't read all posts through, what I've seen was just the first one, asking to sum HH:MM's and continues discussion about it, growing and growing from converting HH:MM back to calculable INT's to summing something UDF's. I didn't see the change of topic nature...

Would be a bit easier if OP open separate thread, but again it's my fault only of lazy-reading...

Sorry again. (the last thing I would want for myself is to be compared to J.C.:w00t:)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

pwalter83 SSChampion Points: 14575 More actions · Answer 4

Jeff Moden (3/29/2012)
Are you all set then or do you still need some help to pull all of this off?

Jeff,

Actually I would need some help at this point. Anders provided a solution but I still need to SUM up the rows and then ADD up the columns.

Do you want me to point you to my query or if its possible I can create a new thread.

Thanks.

pwalter83 SSChampion Points: 14575 More actions · Answer 5

Eugene Elutin (3/29/2012)
Thank you all for defending me...
Sorry, I wasn't right. I didn't read all posts through, what I've seen was just the first one, asking to sum HH:MM's and continues discussion about it, growing and growing from converting HH:MM back to calculable INT's to summing something UDF's. I didn't see the change of topic nature...
Would be a bit easier if OP open separate thread, but again it's my fault only of lazy-reading...
Sorry again. (the last thing I would want for myself is to be compared to J.C.:w00t:)

I am sorry for the harsh words too, Eugene. When you are stuck (and frustrated) on a query, the last thing you need is criticism for the wrong reasons.

Anders Pedersen SSChampion Points: 11410 More actions · Answer 6

pwalter83 (3/29/2012)
Jeff Moden (3/29/2012)
Are you all set then or do you still need some help to pull all of this off?
Jeff,
Actually I would need some help at this point. Anders provided a solution but I still need to SUM up the rows and then ADD up the columns.
Do you want me to point you to my query or if its possible I can create a new thread.
Thanks.

I tested it and it works, you have everything you need to do what I think you need to do. But will leave it as a learning opportunity to figure out how 🙂 I will hint that the function fcn_ConvertMinutesToHours needs to be altered a little bit due to the number being too high. I get the sum of the record set given earlier to be 139 hours.

If that doesn't match what you think the result should be from the data given, then please provide the query you have.

pwalter83 SSChampion Points: 14575 More actions · Answer 7

Anders Pedersen (3/29/2012)
pwalter83 (3/29/2012)
Jeff Moden (3/29/2012)
Are you all set then or do you still need some help to pull all of this off?
Jeff,
Actually I would need some help at this point. Anders provided a solution but I still need to SUM up the rows and then ADD up the columns.
Do you want me to point you to my query or if its possible I can create a new thread.
Thanks.
I tested it and it works, you have everything you need to do what I think you need to do. But will leave it as a learning opportunity to figure out how 🙂 I will hint that the function fcn_ConvertMinutesToHours needs to be altered a little bit due to the number being too high. I get the sum of the record set given earlier to be 139 hours.
If that doesn't match what you think the result should be from the data given, then please provide the query you have.

Hi Anders,

I tried to modify your code but could'nt get it to show 139 hours, its showing different weird values with an asterisk in the end something like 37.* (I am trying on a different database) and I know I am doing something wrong.

pwalter83 SSChampion Points: 14575 More actions · Answer 8

Anders Pedersen (3/29/2012)
pwalter83 (3/29/2012)
Jeff Moden (3/29/2012)
Are you all set then or do you still need some help to pull all of this off?
Jeff,
Actually I would need some help at this point. Anders provided a solution but I still need to SUM up the rows and then ADD up the columns.
Do you want me to point you to my query or if its possible I can create a new thread.
Thanks.
I tested it and it works, you have everything you need to do what I think you need to do. But will leave it as a learning opportunity to figure out how 🙂 I will hint that the function fcn_ConvertMinutesToHours needs to be altered a little bit due to the number being too high. I get the sum of the record set given earlier to be 139 hours.
If that doesn't match what you think the result should be from the data given, then please provide the query you have.

Hi Anders,

Have you got any ideas on this one ? I have been trying a few things but does'nt work out correctly..

pwalter83 SSChampion Points: 14575 More actions · Answer 9

Anders Pedersen (3/29/2012)
pwalter83 (3/29/2012)
Jeff Moden (3/29/2012)
Are you all set then or do you still need some help to pull all of this off?
Jeff,
Actually I would need some help at this point. Anders provided a solution but I still need to SUM up the rows and then ADD up the columns.
Do you want me to point you to my query or if its possible I can create a new thread.
Thanks.
I tested it and it works, you have everything you need to do what I think you need to do. But will leave it as a learning opportunity to figure out how 🙂 I will hint that the function fcn_ConvertMinutesToHours needs to be altered a little bit due to the number being too high. I get the sum of the record set given earlier to be 139 hours.
If that doesn't match what you think the result should be from the data given, then please provide the query you have.

Hi Anders,

I tried to amend the function as you mentioned but could'nt get it to show the sum of 139 that you got. The sum you got is correct but I am still clueless about how to bring it to that total. Could you please disclose what you changed in the function ? 🙂

pwalter83 SSChampion Points: 14575 More actions · Answer 10

Anders Pedersen (3/29/2012)
pwalter83 (3/29/2012)
Jeff Moden (3/29/2012)
Are you all set then or do you still need some help to pull all of this off?
Jeff,
Actually I would need some help at this point. Anders provided a solution but I still need to SUM up the rows and then ADD up the columns.
Do you want me to point you to my query or if its possible I can create a new thread.
Thanks.
I tested it and it works, you have everything you need to do what I think you need to do. But will leave it as a learning opportunity to figure out how 🙂 I will hint that the function fcn_ConvertMinutesToHours needs to be altered a little bit due to the number being too high. I get the sum of the record set given earlier to be 139 hours.
If that doesn't match what you think the result should be from the data given, then please provide the query you have.

Hi Anders,

I am still stuck on this query, could you please let me know the amendment you did to the function ?

Thanks,

Paul

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

Dang it... that's the second time I lost track of this thread. My apologies. I'll try to take a look tonight.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pwalter83 SSChampion Points: 14575 More actions · Answer 12

Jeff Moden (4/18/2012)
Dang it... that's the second time I lost track of this thread. My apologies. I'll try to take a look tonight.

No Problem, thanks Jeff

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

I've been looking at this thread for a couple of days now and have decided that I don't know what the requirements are anymore. First, it start off with a column of weekly hour totals and a request for sum of the hours and further down, there's a table with weekly hours by day and no week identifier (starting date or ending date, usually). Looking at the code that you're having a problem with, I'm not even sure what the desired output should be anymore.

Can we start over on this and put all of the requirements and sample data/tables in one post along with what the output should look like so I can take a shot at it? Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pwalter83 SSChampion Points: 14575 More actions · Answer 14

Jeff Moden (4/22/2012)
I've been looking at this thread for a couple of days now and have decided that I don't know what the requirements are anymore. First, it start off with a column of weekly hour totals and a request for sum of the hours and further down, there's a table with weekly hours by day and no week identifier (starting date or ending date, usually). Looking at the code that you're having a problem with, I'm not even sure what the desired output should be anymore.
Can we start over on this and put all of the requirements and sample data/tables in one post along with what the output should look like so I can take a shot at it? Thanks.

Thanks, Jeff !

The following fields in the table have a time datatype-

MonHours

TueHours

WedHours

ThuHours

Frihours

The requirement is to add the values for all the fields above after doing a SUM for each of them.

It should be something like this but I am not sure about the exact code and that is the reason for my query-

SUM(MonHours) + SUM(TueHours) + SUM(WedHours) + SUM(ThuHours) + SUM(Frihours).

In other words, SUM all the individual rows - MonHours, TuesHours, WedHours, ThuHours,FriHours first and then add all the resultant 5 columns.

For example, for the sample data attached, the SUM should be 174 hours (34.5 + 33 + 36.5 + 35 + 35)

It would have been easy to just SUM the Weekhours column, however, it has a varchar datatype and cant be used to SUM up the values.

I am not sure if this seems to be clear enough. Please find the table DDL and the sample data attached.

This has to be used in a report using a query so please also find the sample query attached and 2 functions that may be helpful.

Thanks,

Paul

pwalter83 SSChampion Points: 14575 More actions · Answer 15

pwalter83 (4/23/2012)
Jeff Moden (4/22/2012)
I've been looking at this thread for a couple of days now and have decided that I don't know what the requirements are anymore. First, it start off with a column of weekly hour totals and a request for sum of the hours and further down, there's a table with weekly hours by day and no week identifier (starting date or ending date, usually). Looking at the code that you're having a problem with, I'm not even sure what the desired output should be anymore.
Can we start over on this and put all of the requirements and sample data/tables in one post along with what the output should look like so I can take a shot at it? Thanks.
Thanks, Jeff !
The following fields in the table have a time datatype-
MonHours
TueHours
WedHours
ThuHours
Frihours
The requirement is to add the values for all the fields above after doing a SUM for each of them.
It should be something like this but I am not sure about the exact code and that is the reason for my query-
SUM(MonHours) + SUM(TueHours) + SUM(WedHours) + SUM(ThuHours) + SUM(Frihours).
In other words, SUM all the individual rows - MonHours, TuesHours, WedHours, ThuHours,FriHours first and then add all the resultant 5 columns.
For example, for the sample data attached, the SUM should be 174 hours (34.5 + 33 + 36.5 + 35 + 35)
It would have been easy to just SUM the Weekhours column, however, it has a varchar datatype and cant be used to SUM up the values.
I am not sure if this seems to be clear enough. Please find the table DDL and the sample data attached.
This has to be used in a report using a query so please also find the sample query attached and 2 functions that may be helpful.
Thanks,
Paul

Can somebody kindly help me on this, I am stuck for quite a while now.

Thanks,

Paul