January 20, 2012 at 10:08 am
I have a table like this:
CREATE TABLE #Test (
[InstNumber]VARCHAR(4)
,[GLLineNumber]VARCHAR(10)
,[GLLineDescription]VARCHAR(100)
,[NovemberBalance]DECIMAL(23,2)
,[DecemberBalance]DECIMAL(23,2)
,[JanuaryBalance]DECIMAL(23,2)
,[FebruaryBalance]DECIMAL(23,2)
,[MarchBalance]DECIMAL(23,2)
,[AprilBalance]DECIMAL(23,2)
,[MayBalance]DECIMAL(23,2)
,[JuneBalance]DECIMAL(23,2)
,[JulyBalance]DECIMAL(23,2)
,[AugustBalance]DECIMAL(23,2)
,[SeptemberBalance]DECIMAL(23,2)
,[OctoberBalance]DECIMAL(23,2)
,[CurrentBalance]DECIMAL(23,2)
)
The column CurrentBalance is supposed to be the value contained in the appropriate column for LAST month. For example, CurrentBalance as of January should be the value in the column DecemberBalance. It could be, I suppose, done with a trigger or an update statement, or maybe a computed column. Any ideas?
January 20, 2012 at 10:19 am
we'd really need to see the DDL of the table that the data gets extracted from to offer more than a suggestion or prototype.
I'd create a view instead that joins the table and the calculation for last months data. that way the static data of the table never needs to be updated.
SELECT
MainTable.InstNumber,
MainTable.ColumnList,
January2011.LastBalance
February2011.LastBalance
FROM MainTable
Left Outer Join ( SELECT InstNumber,SUM(Balance) AS LastBalance From MainTable WHERE TransactionDate BETWEEN @date1 and dateadd(milliseconds,-3,dateadd(month,1,@date)) January2011
ON MainTable.InstNumber = January2011.InstNumber
Left Outer Join ( SELECT InstNumber,SUM(Balance) AS LastBalance From MainTable WHERE TransactionDate BETWEEN DATEADD(month,1,@date1 and dateadd(milliseconds,-3,dateadd(month,2,@date)) February2011
ON MainTable.InstNumber = February2011.InstNumber
Lowell
January 20, 2012 at 10:27 am
Ehh, why not sum(case) ???
January 20, 2012 at 11:00 am
Ninja's_RGR'us (1/20/2012)
Ehh, why not sum(case) ???
duh...err...no excuse. that's the right solution.
not thinking straight.
Lowell
January 20, 2012 at 11:31 am
Thank you for your reply. Actually, I know I should have included the input table. It happens to be an import table that has an identical structure. BUT, I actually wanted to know how I did it even if I didn't have that table. For example, if the last column was blank I wanted to be able to update the column from this table itself simply grabbing the existing value from the column itself. For example, if December had $159.34 in it, that would become the figure in the CurrentBalance. Next month it would be the value from January, etc. Your solution looks good. I am going to start experimenting with it now.
January 20, 2012 at 11:49 am
Actually, the more I think about it and look at it, maybe I was unclear about it. The column names, JanuarBalance, etc. are the only things we have to go on. It is a 12 month rolling "chart". All I can do is evaluate the column NAME (find out what month is in the name), or maybe a column index number to find which column to get the value out of. It is not really computed at all but transferred to CurrentBalance. Let's say for a given row or InstNumber, that NovemberBalance = $1,000, DecemberBalance is $159.34, and DecemberBalance = $229.11. If the column gets inserted/updated in January, then CurrentBalance will be cleared and set to $159.34. Next month, in February, it will be set to $229.11. LAST month (in December, it would have been set to $1,000, the value in the NovemberColumn. No sums or other calculations. Just a copy of the value in the appropriate Month column to CurrentBalance. Thanks again.
January 20, 2012 at 1:27 pm
Actually, I figured it out and it works. Let me know if you see any pitfalls or improvements.
DECLARE @strReportDate VARCHAR(10)
SET @strReportDate = CAST(DATENAME(MONTH,GETDATE()) AS VARCHAR(10))
SELECT [InstNumber]
,[GLLineNumber]
,[GLLineDescription]
,[NovemberBalance]
,[DecemberBalance]
,[JanuaryBalance]
,[FebruaryBalance]
,[MarchBalance]
,[AprilBalance]
,[MayBalance]
,[JuneBalance]
,[JulyBalance]
,[AugustBalance]
,[SeptemberBalance]
,[OctoberBalance]
,[CurrentBalance]
,CASE @strReportDate
WHEN 'January'THEN DecemberBalance
WHEN 'Febuary'THEN JanuaryBalance
WHEN 'March'THEN FebruaryBalance
WHEN 'April'THEN MarchBalance
WHEN 'May'THEN AprilBalance
WHEN 'June'THEN MayBalance
WHEN 'July'THEN JuneBalance
WHEN 'August'THEN JulyBalance
WHEN 'September' THEN AugustBalance
WHEN 'October'THEN SeptemberBalance
WHEN 'November'THEN OctoberBalance
WHEN 'December'THEN NovemberBalance
ELSE Null
END
AS CurrentBalance
FROM #Test
Thanks for the help above. It gave me some hints.
January 23, 2012 at 6:59 am
CELKO (1/20/2012)
I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL.
This strikes me as very hypocritical. You have repeatedly said, "Don't use dialect," yet here you are suggesting the use of dialect. Furthermore, it's the WRONG dialect. MySQL conventions don't do any good in this forum if they're not also supported by T-SQL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 23, 2012 at 8:24 am
CELKO (1/20/2012)
Look up the term "repeated groups" and "Normalization" so you will not create tables this bad again.
Perhaps the OP has read Ted Codd's "A Relational Model of Data for Large Shared Data Banks" which is where "normal form" (now called "first normal form") was originally defined. From what I've just quoted it seems clear that you haven't. Try to explain in what way that table violates Codd's requirement for normal form, and you'll find it very difficult indeed.
Tom
January 23, 2012 at 8:51 am
Sorry to disagree with you Tom but I also believe it violates 1st NF. It’s a case of ‘Repeating groups across columns’.
According to Date's definition of 1NF, a table is in 1NF if and only if it is "isomorphic to some relation", which means, specifically, that it satisfies the following five conditions:
1.There's no top-to-bottom ordering to the rows.
2.There's no left-to-right ordering to the columns.
3.There are no duplicate rows.
4.Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).
5.All columns are regular [i.e. rows have no hidden components such as row IDs, object IDs, or hidden timestamps].
—Chris Date, "What First Normal Form Really Means", pp. 127–8[4]
January 23, 2012 at 9:10 am
Thank you for these replies. They all give me some hints for my future endeavors. However, much I have to do is already scripted for me and I am just filling in the holes. I did not conceive the idea of 23 decimal places. And I did not care for the construction of the table with repeating months. It was here before I got here and I was not tasked with nor hired for a full redesign. If I was, this would have been one of the things I would have changed. Furthermore, to redesign one thing often has severe ramifications for the rest of the database so I have to be careful what I do change or even what I ask for. I often get more than I bargained for when I do that. I don't mind your comments though. I learn a little more from each one.
January 23, 2012 at 9:19 am
Please don’t take it otherwise or discourage yourself. We just severed few options in front of you. I very well understand that schema design changes will have severe ramifications, if not done thoughtfully. Also, it’s sometimes out-of-available options (in case of third party application).
I am happy you are able to solve your issue. 🙂
January 23, 2012 at 10:24 am
Dev (1/23/2012)
Sorry to disagree with you Tom but I also believe it violates 1st NF. It’s a case of ‘Repeating groups across columns’.According to Date's definition of 1NF, a table is in 1NF if and only if it is "isomorphic to some relation", which means, specifically, that it satisfies the following five conditions:
1.There's no top-to-bottom ordering to the rows.
2.There's no left-to-right ordering to the columns.
3.There are no duplicate rows.
4.Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).
5.All columns are regular [i.e. rows have no hidden components such as row IDs, object IDs, or hidden timestamps].
—Chris Date, "What First Normal Form Really Means", pp. 127–8[4]
You apparently haven't noticed that Date's definition, which you quote, agrees with mine (which you can find in this article[/url]) on this particular topic. He forbids a single row-column intersection from containing more than one value; he doesn't forbid having more than one columns which contain numeric values, which you (and Celko) appear to think constitute a "repeating group" and want to forbid.
Don't put too much trust in wikipedia; some of the contributors take their information from pretty awful textbooks or from lectures given by junior academics who don't really understand the subject themselves, instead of from preperly peer-reviewed articles in technical journals (which is where normalisation theory generally evolves).
Even Date's definition is slightly controversial. If we assume that to be in 1NF a table has to be isomorphic to some relation, can the columns be unordered, as required by his clause 2? Clearly not if it's the ordinary set-theoretic definition of relation that's used in mathematics (those columns are ordered). Also not if it's a relational calculus in the sense of Codds relational model (those columns are ordered too). Maybe Date has some other defintion (presumably mathematical, as he talks about an isomorphism) which has unordered columns, but he hasn't yet published such a definition, as far as I can discover (he's been asked to); I tend to think in terms of a set of maps over column identifiers (names, if you like) to attribute domains, which sort of allows columns to not be ordered, but what was Date thinking of? We have no means of knowing, which makes the requirement for an isomorphism meaningless. Of course no SQL table can ever satisfy Dates 1NF definition, because SQL tables have explicitly ordered columns (I can use a number in the order to identify a column, instead of its name).
Tom
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply