July 24, 2019 at 1:34 am
Last but certainly not least, there is no way that I'd ever include spaces or other special characters in the columns so as to make them require brackets.
You're also missing FK's for things like ProjectType and ProjectClassification.
And thank you very much for your suggestions and for taking the time to write so much. I added your suggestions to my OneNote.
July 24, 2019 at 3:49 am
On that same note, I'd also change the datatype for "IsBillable" and "IsActive" to TINYINT and use a 1 for "Yes" and a 0 for "No" along with constraints to ensure that only 1 or 0 could ever be used in the column. I wouldn't use the BIT datatype here, either. The reason for the proposed change is it will make coding damned easy to determine how many projects are billable or active using a simple SUM aggregate. Y/N and BIT datatypes will require a conversion to aggregate them.
July 24, 2019 at 3:18 pm
Jeff Moden wrote:Last but certainly not least, there is no way that I'd ever include spaces or other special characters in the columns so as to make them require brackets. You're also missing FK's for things like ProjectType and ProjectClassification.
- Do you consider underscores to be special characters?
- Let’s say I used ProjectType instead of [Project Type], but I want it to display to the user as Project Type. How would I do that? I could use an alias but I would still have to use [ ] right?
- You said that I was missing foreign keys for ProjectType and ProjectClassification. Did you mean the foreign key would be defined in another table right or did you mean having these two columns being a foreign key which would reference a primary key in another table?
And thank you very much for your suggestions and for taking the time to write so much. I added your suggestions to my OneNote.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 24, 2019 at 3:43 pm
Jeff Moden wrote:Last but certainly not least, there is no way that I'd ever include spaces or other special characters in the columns so as to make them require brackets. You're also missing FK's for things like ProjectType and ProjectClassification.
- Do you consider underscores to be special characters?
- Let’s say I used ProjectType instead of [Project Type], but I want it to display to the user as Project Type. How would I do that? I could use an alias but I would still have to use [ ] right?
- You said that I was missing foreign keys for ProjectType and ProjectClassification. Did you mean the foreign key would be defined in another table right or did you mean having these two columns being a foreign key which would reference a primary key in another table?
And thank you very much for your suggestions and for taking the time to write so much. I added your suggestions to my OneNote.
SELECT [Alias Name with spaces] = SomeColumnName
... or ...
SELECT SomeColumnName AS [Alias Name with spaces]
... or, like Drew said, do it in your presentation layer (GUI or reporting software) if that's where this ends up at.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 24, 2019 at 3:45 pm
Jeff Moden wrote:On that same note, I'd also change the datatype for "IsBillable" and "IsActive" to TINYINT and use a 1 for "Yes" and a 0 for "No" along with constraints to ensure that only 1 or 0 could ever be used in the column. I wouldn't use the BIT datatype here, either. The reason for the proposed change is it will make coding damned easy to determine how many projects are billable or active using a simple SUM aggregate. Y/N and BIT datatypes will require a conversion to aggregate them.
- You would use TINYINT with 1 or 0 so you can do a SUM aggregate. If you used Y/N, couldn't you get the same result by using some function to count the number of Y's in a column?
- Y/N and BIT data types will require a conversion. A conversion to what?You YYo
I'll get back to you on these questions tonight. I have to get to work now.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 24, 2019 at 7:51 pm
You would use TINYINT with 1 or 0 so you can do a SUM aggregate. If you used Y/N, couldn't you get the same result by using some function to count the number of Y's in a column?
Y/N and BIT data types will require a conversion. A conversion to what?
Summing a TINYINT (1/0) column is the tidiest solution, but not the only one. This rather ugly hack works for a BIT column:
DROP TABLE IF EXISTS #Boris;
CREATE TABLE #Boris
(
Sometext VARCHAR(10)
,IsBillable BIT NOT NULL
);
INSERT #Boris
(
Sometext
,IsBillable
)
VALUES
('a', 1)
,('b', 1)
,('c', 0);
SELECT COUNT(NULLIF(b.IsBillable, 0))
FROM #Boris b;
A variation on this same trick should work with any string (eg, Y):
SELECT COUNT( CASE b.IsBillable
WHEN 'Y' THEN
1
ELSE
NULL
END
)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 24, 2019 at 8:34 pm
If your presentation layer is SSRS, then using underscores in your column names makes life easier, IMHO. Now I'm using 2008 SSRS, but when I drop a column name like Policy_Type into the report, SSRS is smart enough to replace the underscore with a space for the column name on the report. Now if you use the camel case PolicyType you will have to go into the column name to add the space. I don't usually disagree with Jeff, but I think the underscores are easier to read, again IMHO. I use camel case at my work because that's their 'standard', I sneak in underscores when I can. 🙂 I also disagree that you should write the SQL statement as
SELECT PolicyType AS Policy_Type
instead of
SELECT Policy_Type = PolicyType
To me I think it will make it easier for someone who is not as experienced in SQL to understand what the statement is doing. IMHO 😉
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
July 24, 2019 at 8:52 pm
If your presentation layer is SSRS, then using underscores in your column names makes life easier, IMHO. Now I'm using 2008 SSRS, but when I drop a column name like Policy_Type into the report, SSRS is smart enough to replace the underscore with a space for the column name on the report. Now if you use the camel case PolicyType you will have to go into the column name to add the space. I don't usually disagree with Jeff, but I think the underscores are easier to read, again IMHO. I use camel case at my work because that's their 'standard', I sneak in underscores when I can. 🙂 I also disagree that you should write the SQL statement as SELECT PolicyType AS Policy_Type instead of SELECT Policy_Type = PolicyType To me I think it will make it easier for someone who is not as experienced in SQL to understand what the statement is doing. IMHO 😉
You're not actually disagreeing with me. 😀 Underscores absolutely DO make things easier to read! I just hate typing the damned things. I curse at MS every time I have to type something like sys.dm_db_index_physical_stats and, yeah, things like Intelasense and SqlPrompt help but only after you get past typing the sys.dm_db_ part because there are so many objects with that prefix.
Interesting tip about SSRS... I didn't know that. If I used SSRS, I probably wouldn't squawk about typing underscores.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 24, 2019 at 8:56 pm
PolicyType is Pascal case, not camel case, which would be policyType.
I guess I'm lucky: I've been reading code without underscores for so long that I find it just as easy to read as that with. And saving on the _ typing is totally worthwhile.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 24, 2019 at 9:08 pm
below86 wrote:If your presentation layer is SSRS, then using underscores in your column names makes life easier, IMHO. Now I'm using 2008 SSRS, but when I drop a column name like Policy_Type into the report, SSRS is smart enough to replace the underscore with a space for the column name on the report. Now if you use the camel case PolicyType you will have to go into the column name to add the space. I don't usually disagree with Jeff, but I think the underscores are easier to read, again IMHO. I use camel case at my work because that's their 'standard', I sneak in underscores when I can. 🙂 I also disagree that you should write the SQL statement as SELECT PolicyType AS Policy_Type instead of SELECT Policy_Type = PolicyType To me I think it will make it easier for someone who is not as experienced in SQL to understand what the statement is doing. IMHO 😉
You're not actually disagreeing with me. 😀 Underscores absolutely DO make things easier to read! I just hate typing the damned things. I curse at MS every time I have to type something like sys.dm_db_index_physical_stats and, yeah, things like Intelasense and SqlPrompt help but only after you get past typing the sys.dm_db_ part because there are so many objects with that prefix. Interesting tip about SSRS... I didn't know that. If I used SSRS, I probably wouldn't squawk about typing underscores.
I started out using underscore, kind of like using the dash in COBOL So I used that for years and years. It's taken me almost 3 years at my current job, to not use the underscores all the time. It's also taken me awhile to get used to capitalizing each word for the camel case. I still find it easier to type policy_type than PolicyType, to many times I put policytype and had to go back and correct it. Hard teaching this Ol' dog new tricks, but I'm trying. 😉
I guess we didn't really disagree there, but I still prefer the 'AS' method instead of '='. 😀
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
July 24, 2019 at 9:12 pm
PolicyType is Pascal case, not camel case, which would be policyType. I guess I'm lucky: I've been reading code without underscores for so long that I find it just as easy to read as that with. And saving on the _ typing is totally worthwhile.
You know I had a Turbo Pascal class way back in High School, I don't even remember how I typed the code anymore. Thanks for the correction Phil, I'll try remembering that for the next discussion.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
July 24, 2019 at 9:37 pm
but I still prefer the 'AS' method instead of '='. 😀
The reason I prefer the "=" method is simply because of my habit to do vertical alignment for readability and because some of the formulas we use are terribly long. It also puts SELECTs into the same form as UPDATEs. I realized in UPDATEs that, when I'm looking for a column name, it's easier to find for me and so adopted the same method for SELECTs. I also used to use trailing commas but then found joy in leading commas for the same reason. It took a long time for me to adopt the leading comma thing until I realized that I always did leading AND's and OR's for the same reasons. After that, it was a pretty easy switch for me. Not sure how I went so long without doing both.
And, just to be sure, no... I'm absolutely not criticizing anyone for using "AS" and trailing commas. The fastest way I know of to get into a fight with someone is to tell them how to format their aliases and commas.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 25, 2019 at 1:16 am
Jeff Moden,
I didn't know you could create an alias like: [Contract Number] = ContractNo
Good to know. And thank you for all of your feedback.
July 25, 2019 at 1:28 am
Phil,
I tested the SUM aggregate function on TINYINT and it works great. Thank you.
July 25, 2019 at 1:36 am
Jeff,
What do you mean when you say doing vertical alignment. I noticed you also mentioned that in your past last night.
Viewing 15 posts - 16 through 30 (of 46 total)
You must be logged in to reply to this topic. Login to reply