September 3, 2014 at 1:43 am
Hi,
Need to multiply the following by 12 then divide this by 52.
,COALESCE(CTAX.[NET-AMT],0)+COALESCE(HEAT.[NET-AMT],0)+COALESCE(FURNIT.[NET-AMT],0)+COALESCE(SERVCH.[NET-AMT],0)+COALESCE(SEWER.[NET-AMT],0)+COALESCE(WATER.[NET-AMT],0) as 'weekly rent'
Everywhere I try and put the *12/52, I get errors.
September 3, 2014 at 1:50 am
What are the errors?
This should work (but of course I can't test it, since you didn't provide any table DDL and sample data).
,[Weekly Rent] = ((COALESCE(CTAX.[NET-AMT],0)+COALESCE(HEAT.[NET-AMT],0)+COALESCE(FURNIT.[NET-AMT],0)+COALESCE(SERVCH.[NET-AMT],0)+COALESCE(SEWER.[NET-AMT],0)+COALESCE(WATER.[NET-AMT],0)) * 12) / 52.0
Be aware of integer division. 12/52 equals to 0 with integer division. 12/52.0 equals 0.230769.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 3, 2014 at 3:15 am
Thanks. That worked
September 3, 2014 at 12:38 pm
CELKO (9/3/2014)
You doing everything wrong. Where is the DDL? That is minimal Netiquette in SQL forums. Is this all INTEGER math? I hope not! But that is what your coalesce to zero implies. If it were a DECIMAL() value, then you would have written COALESCE(x, 0.00) instead.You do not know that square brackets and single quotes are not SQL; they are T-SQL dialect. In violation of ISO standards and common sense, you put punctuation in a data element name. We do not do display formatting in a query!
Putting each element of Rents into its own table is a design flaw called “attribute splitting” and laugh at it.
There is no such crap as “amt” in RDBMS; it is “<something in particular>_amt” instead. My guess is that you should had a talbe like this:
CREATE TABLE Rents
(property_id CHAR(15)NOT NULL,
rental_month CHAR(10) NOT NULL
CHECK (rental_month LIKE '[12],[0-9][0-9][0-9]-[01][0-9]-00'),
PRIMARY KEY (property_id, rental_month),
ctax_amt DECIMAL (10,2) DEFAULT 0.00 NOT NULL,
heat_amt DECIMAL (10,2) DEFAULT 0.00 NOT NULL,
furniture_amt DECIMAL (10,2) DEFAULT 0.00 NOT NULL,
servch_amt DECIMAL (10,2) DEFAULT 0.00 NOT NULL, -- what is this?
sewer_amt DECIMAL (10,2) DEFAULT 0.00 NOT NULL,
water_amt DECIMAL (10,2) DEFAULT 0.00 NOT NULL,
etc);
Then we do a VIEW:
SELECT property_id, rental_month,
(ctax_amt + heat_amt + furniture_amt
+ servch_amt + sewer_amt + water_amt + etc)
AS weekly_rent_amt
FROM Rents;
Celko, you could really use a charm school course.
September 3, 2014 at 1:15 pm
CELKO (9/3/2014)
You doing everything wrong.
Well, at least his English wasn't that bad 😉
And he didn't use ISNULL, seeing you have quite the aversion for T-SQL.
CELKO (9/3/2014)
You do not know that square brackets and single quotes are not SQL; they are T-SQL dialect.
This is a SQL Server forum. Hence the name, SQLServerCentral. T-SQL is perfectly accepted here, it is even encouraged.
CELKO (9/3/2014)
We do not do display formatting in a query!
Please refrain from using "we" in sentences. Speak for yourself. Unless you insist on using the royal we.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 3, 2014 at 1:45 pm
SQL Server has a feature called computed columns that could be very useful here. Basically you can create a virtual column based on a computation, and it becomes effectively another column in the table when you reference it. This allows you do define "[Weekly Rent]" in a single place yet use it everywhere.
Here's an example of how to add a computed column to your table:
ALTER TABLE your_table_name
ADD
[Weekly Rent] AS ((COALESCE(CTAX.[NET-AMT],0)+COALESCE(HEAT.[NET-AMT],0)+COALESCE(FURNIT.[NET-AMT],0)+COALESCE(SERVCH.[NET-AMT],0)+COALESCE(SEWER.[NET-AMT],0)+COALESCE(WATER.[NET-AMT],0)) * 12) / 52.0
From then on, you can use it just like any other column; for example:
SELECT [weekly rent]
FROM your_table_name
WHERE [weekly rent] >= 500.00
ORDER BY [weekly rent] DESC
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 3, 2014 at 1:47 pm
Someone call the normalization police, we have an offender in here.
That Rents table is not normalized. What will happen when there's more concepts needed? Will you just add columns? What happens if you want to know who's the provider for any service? Will you just add rows?
The rental_month column is flawed, it will allow invalid months such as '2999-15-00'. That's why we use date data types.
You just committed one of the greatest sins on database design. You assumed things without knowing the requirements.
You should stay out of the forums if you want to keep your good reputation.
September 3, 2014 at 2:04 pm
CELKO (9/3/2014)
You doing everything wrong. Where is the DDL? That is minimal Netiquette in SQL forums. Is this all INTEGER math? I hope not! But that is what your coalesce to zero implies. If it were a DECIMAL() value, then you would have written COALESCE(x, 0.00) instead.You do not know that square brackets and single quotes are not SQL; they are T-SQL dialect. In violation of ISO standards and common sense, you put punctuation in a data element name. We do not do display formatting in a query!
Putting each element of Rents into its own table is a design flaw called “attribute splitting” and laugh at it.
There is no such crap as “amt” in RDBMS; it is “<something in particular>_amt” instead. My guess is that you should had a talbe like this:
CREATE TABLE Rents
(property_id CHAR(15)NOT NULL,
rental_month CHAR(10) NOT NULL
CHECK (rental_month LIKE '[12],[0-9][0-9][0-9]-[01][0-9]-00'),
PRIMARY KEY (property_id, rental_month),
ctax_amt DECIMAL (10,2) DEFAULT 0.00 NOT NULL,
heat_amt DECIMAL (10,2) DEFAULT 0.00 NOT NULL,
furniture_amt DECIMAL (10,2) DEFAULT 0.00 NOT NULL,
servch_amt DECIMAL (10,2) DEFAULT 0.00 NOT NULL, -- what is this?
sewer_amt DECIMAL (10,2) DEFAULT 0.00 NOT NULL,
water_amt DECIMAL (10,2) DEFAULT 0.00 NOT NULL,
etc);
Then we do a VIEW:
SELECT property_id, rental_month,
(ctax_amt + heat_amt + furniture_amt
+ servch_amt + sewer_amt + water_amt + etc)
AS weekly_rent_amt
FROM Rents;
Vastly more important, "we" do not store editing chars within the data! Even if one insists on storing dates as (var)char, why on earth would there ever be a comma and/or a dash present? YYYYMMDD is (1) shorter and (2) unambiguous. Moreover, day '00' is not valid in most SQL, nor I believe in the ANSI standard ... but you just happen to like that approach so it's OK? No, it's bizarre in a SQL Server table and no one will know wth it means.
Btw, NOT NULL seems wrong here. Why couldn't one know some amounts but not know the others yet? Until I receive the bill, they should be NULL, not "0.00", because that would/should mean I owe nothing for that item for that month.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 3, 2014 at 3:38 pm
CELKO (9/3/2014)
Vastly more important, "we" do not store editing chars within the data! Even if one insists on storing dates as (var)char, why on earth would there ever be a comma and/or a dash present? YYYYMMDD is (1) shorter and (2) unambiguous. Moreover, day '00' is not valid in most SQL, nor I believe in the ANSI standard ... but you just happen to like that approach so it's OK? No, it's bizarre in a SQL Server table and no one will know what it means.
Do you have you copy of the ANSI/ISO Standards? Read them. The only display format allowed is ISO-8601, 'yyyy-mm-dd' and not your punctuation free version. Sorry.
We also do not allow the 'T' separator between data and time fields. This hurts since a solid string without white space has some advantages in parsing
The MySQL month names I like sort with the ANSi/ISO, are language independent, and have been proposed for ISO. And thanks to MySQL, it is widely used and understood. Can you see a disadvantage or a better choice?
yyyy-nn-nn is ambiguous: perhaps you've heard of Europe, where 'yyyy-dd-mm' is common. YYYYMMDD is unambiguous. Storing dashes is also a waste of space and forces me to "unedit" the data if I prefer, say, mm/dd/yyyy as my output.
If you want a yyyy-mm-dd format, do so when SELECTing the data, but it's wasteful and can corrupt data storing it that way.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 3, 2014 at 4:10 pm
CELKO (9/3/2014)
Do you have you copy of the ANSI/ISO Standards? Read them. The only display format allowed is ISO-8601, 'yyyy-mm-dd' and not your punctuation free version. Sorry.
Seems that the ANSI/ISO standards cttee dealing with SQL doesn't like other ISO standards; YYYYMMDD is an ISO standard date format(I can't remember its number) while YYYY-MM-DD is a truncation of an ISO date-time standard, not an ISO date standard.
edit; my mistake - the truncated YYYY-MM-DD is allowed in ISO 8061. But DD is constrained to be in the range 01 to 31 so the CELKO YYYY-MM-00 is guaranteed non-conformant to the ISO standard.
Tom
September 3, 2014 at 4:21 pm
CELKO (9/3/2014)
Vastly more important, "we" do not store editing chars within the data! Even if one insists on storing dates as (var)char, why on earth would there ever be a comma and/or a dash present? YYYYMMDD is (1) shorter and (2) unambiguous. Moreover, day '00' is not valid in most SQL, nor I believe in the ANSI standard ... but you just happen to like that approach so it's OK? No, it's bizarre in a SQL Server table and no one will know what it means.
Do you have you copy of the ANSI/ISO Standards? Read them. The only display format allowed is ISO-8601, 'yyyy-mm-dd' and not your punctuation free version. Sorry.
We also do not allow the 'T' separator between data and time fields. This hurts since a solid string without white space has some advantages in parsing
The MySQL month names I like sort with the ANSi/ISO, are language independent, and have been proposed for ISO. And thanks to MySQL, it is widely used and understood. Can you see a disadvantage or a better choice?
Why would I buy anything that you might get royalties from?
Interesting, ISO8601 yyyy-mm-ddThh:mm:ss.mmm (no spaces) << Look there is a T between the date and time. I thought you "We" do not allow the 'T' separator between date and time fields? By the way, unless you have mouse in your pocket, stop using the ubiquitous "we" all the time. Makes you look schizoid as well as being a bully with your harsh tactics.
And until MS SQL Server supports the same functionality as MySQL stop confusing people using SQL Server with features/capabilities that don't exist in SQL Server. Use the dialect of the system you are working with.
September 3, 2014 at 4:23 pm
TomThomson (9/3/2014)
CELKO (9/3/2014)
Do you have you copy of the ANSI/ISO Standards? Read them. The only display format allowed is ISO-8601, 'yyyy-mm-dd' and not your punctuation free version. Sorry.Seems that the ANSI/ISO standards cttee dealing with SQL doesn't like other ISO standards; YYYYMMDD is an ISO standard date format(I can't remember its number) while YYYY-MM-DD is a truncation of an ISO date-time standard, not an ISO date standard.
I, too, had thought that YYYYMMDD was the "basic" standard format, with YYYY-MM-DD the perhaps preferred (but not exclusive) standard, "extended" format, for character display/transmission. For storage, I can't fathom why anyone would even consider storing dashes/other formatting characters: it's such a bizarre waste of space and increase in complexity using the data.
As far as formats go, I also thought 'yyyy-mm-ddThh:mm:ss.sssssss' was an always-acceptable standardized ISO (ANSI?) format.
As a practical matter, since I exchange data with England, Ireland, parts of Asia, etc, I will never use 'yyyy-nn-nn': it's just too subject to error. Every time anyone tried it we get interpretation errors.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 3, 2014 at 4:32 pm
I'm not sure if YYYY-MM-DD is the only display format, but I wouldn't care less about display formats, that's what the front-end is for. We must care to store values not formatted strings.
According to Wikipedia's article on ISO 8601 (I won't buy a document either just to validate it), there's a basic format that uses no separators. http://en.wikipedia.org/wiki/ISO_8601#cite_ref-6
September 3, 2014 at 4:36 pm
CELKO (9/3/2014)
My guess is that you should had a talbe like this:CREATE TABLE Rents
(property_id CHAR(15)NOT NULL,
rental_month CHAR(10) NOT NULL
CHECK (rental_month LIKE '[12],[0-9][0-9][0-9]-[01][0-9]-00'),
That's downright appalling. Those '-'symbols are dsplay formatting, not data, and should not be part of the month data. A month datum should not include any day designation, especially not '00' which simply makes it into an invalid date string - '01' would be less obnoxious as indicating the beginning of the month. On top of that you have used an inadequate CHECK constraint, which permits the invalid months 00, 13,....19.
You would do much better with
rental_month CHAR(6) NOT NULL
CHECK (rental_month LIKE ['[12][0-9][0-9][0-9][01][0-9]' AND substring(rental_month(5,2) BETWEEN '01' AND '13') ),
which contains only month data, no formatting characters and no meaningless day data, and imposes the correct domain constraint instead of permitting nonsense values.
If you can't be bothered to get elementary stuff like this right I suggest you change your ways: stop preaching about trivial divergences from the ISO standard (particularly on a forum where the relevant standard is the T-SQL specification anyway) and learn to concentrate on specifying data types and domain constraints correctly.
edit: I found that standard number, so now I can add that YYYYMMDD is a valid ISO 8061 value, while your YYYY-19-00 is not, and neither is YYYY-MM-00 even if MM is a valid pair. So please stop claiming that non-standard formats that you have invented conform to ISO 8061 when they certainly don't. If you want the formatting hyphen, you will find that ISO 8061 does permit YYYY-MM (no day part).
Tom
September 3, 2014 at 8:03 pm
CELKO (9/3/2014)
the truncated YYYY-MM-DD is allowed in ISO 8061. But DD is constrained to be in the range 01 to 31 so the CELKO YYYY-MM-00 is guaranteed non-conformant to the ISO standard.
Let me try this again. The SQL Standard picked one and only one display format. This means we do not string function like FORMAT, CONVERT, and other proprietary nightmares that violate the concept of a tiered architecture.
This is not my creation, but I did vote for it. 😉 And I had nothing to do with the MySQL convention. That is under consideration and a handy trick. Shall I go thru the reasons again?
Want to talk about the INTERVAL data type that T-SQL does not have yet?
Nope, don't want to talk about MySQL or the data types that MS has not yet implemented. Irrelevant to the current discussion, plus you don't discuss anything (at least in online forums), you bully and berate people who don't tow your line.
Viewing 15 posts - 1 through 15 (of 46 total)
You must be logged in to reply to this topic. Login to reply