August 25, 2015 at 7:37 am
cory.bullard76 (8/25/2015)
the datatype for that field is a Varchar
Yes, but how many characters?
VARCHAR(n)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 25, 2015 at 7:48 am
Ok, got it. Varchar(5)
August 25, 2015 at 9:48 am
cory.bullard76 (8/24/2015)
The values from labels.bagdisp range from 2-60. Is that what you are asking for?
Half. Can you confirm there are *no* non-numeric values? (no 'abc', no '_', nothing except the digits 0-9)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2015 at 9:48 am
ChrisM@Work (8/25/2015)
cory.bullard76 (8/24/2015)
The values from labels.bagdisp range from 2-60. Is that what you are asking for?Gail's asking you to look at the table definition.
No, I wanted the range of data in the column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2015 at 10:22 am
Gila, there are only numeric values....thanks again.
August 25, 2015 at 10:33 am
In that case, this should work.
case when parts.ndc = '50242-0138-01' then CAST(labels.BAGSDISP AS Numeric(6,2)) *0.9 ELSE -1 end [Units Dispensed]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2015 at 2:48 pm
Gila...that worked great. Thanks again! Quick question. Some of the results from that statement are 7.8400 (for example). I'd like to round that. But, when I put Round() around the multiplication portion...it does nothing. How would I round that?
August 26, 2015 at 3:05 pm
Round it to what?
7.84 should become:
8?
7.8?
7.84?
7.840?
Something else?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2015 at 3:20 pm
Sorry...I'd like to round it to 8
August 27, 2015 at 1:17 pm
cory.bullard76 (8/26/2015)
Gila...that worked great. Thanks again! Quick question. Some of the results from that statement are 7.8400 (for example). I'd like to round that. But, when I put Round() around the multiplication portion...it does nothing. How would I round that?
An FYI for Cory: You have the pleasure of working with one of the most well known and respected SQL experts on the planet. Her name is Gail, btw. I don't see many people calling her Gila but maybe she doesn't find it offensive.
August 27, 2015 at 1:26 pm
Oh, I'm sorry. I didn't know her name and just called her by her screen name. Wasn't trying to be rude or anything.
August 27, 2015 at 1:39 pm
I don't mind, and lots of people here call me "Gila". Couple people here call me "Monster", which I find a little weird. "Sir" I stopped getting annoyed at years ago.
btw, my name's in my sig.
CAST(ROUND(case when parts.ndc = '50242-0138-01' then CAST(labels.BAGSDISP AS Numeric(6,2)) *0.9 ELSE -1 end [Units Dispensed],0) AS INT)
Above should work. The CAST is to get rid of the .00.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2015 at 1:52 pm
Gail, I appreciate all of the help you've given me.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply