InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 08:20:26 GMT20http://www.sqlservercentral.com/Forums/Topic1354351-391-1.aspx[quote][b]ScottPletcher (9/5/2012)[/b][hr]Since the table already contains the previous year's YTD totals, would a simple LEFT JOIN suffice to get the prior year's YTD total?[code="sql"]SELECT t1curr.cod, t1curr.[year], t1curr.[month], t1curr.value, t1prev.valueFROM dbo.Table_1 t1currLEFT OUTER JOIN dbo.Table_1 t1prev ON t1prev.cod = t1curr.cod AND t1prev.[year] = t1curr.[year] - 1 AND t1prev.[month] = t1curr.[month][/code][/quote]Assuming "cod" - whatever it is - doesn't change from year to year. If it's a stock ID then the assumption is optimistic.Thu, 06 Sep 2012 02:36:15 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1354351-391-1.aspxCREATE TABLE #Table([cod] [nchar](10) NULL,[year] [int] NULL,[month] [tinyint] NULL,[value] [float] NULL) ON [PRIMARY]insert into #Table values ('cod1',2011,1,100)insert into #Table values ('cod1',2011,2,150)insert into #Table values ('cod1',2011,3,200)insert into #Table values ('cod1',2012,1,100)insert into #Table values ('cod1',2012,2,180)--insert into #Table values ('cod1',2012,3,180)--insert into #Table values ('cod1',2012,4,180)select cod,year,month,sum(value) valinto #temp1 from(select * from #Table t2UNIONselect t2.cod,T2.year+1,t2.month,'' ppy from #Table t2WHERE T2.month not in(select isnull(t5.month,0) as month from #Table t5 where (T2.year-1)=T5.year))kgroup by cod,year,monthselect cod,year,month,CASE WHEN val<>0 THEN val END val,(select SUM(t1.val) from #temp1 t1 WHERE (T2.year-1)=T1.year AND T2.month>=T1.month) AS PPY from #temp1 T2 where t2.year in(select year from #Table)Thu, 06 Sep 2012 00:30:00 GMTsubbareddy542http://www.sqlservercentral.com/Forums/Topic1354351-391-1.aspx[quote][b]ScottPletcher (9/5/2012)[/b][hr]To be fair, higher precision in intermediate calculations in complex equations can in fact cause errors rather than solving them.[/quote]yes, but here we are looking at a YTD calculation, which should involve only addition and no rounding.[quote]I'm certainly no authority on currency laws, so I can't speak to that.[/quote]Neither am I, but I have come across some financial regulations and I am sure there was no ban on using floating point in any of it. Of course I don't know US rules, they may be different.[quote]But, from a mathematics standpoint, suppose all intermediate results are supposed to be rounded to 4 decimal places, but instead you carry them out further, say to 8 places. You've changed the intermediate results, which with large multiples of iteration -- such as for interest calculations, etc. -- could change the final result from what it would have been with different rounding of intermediate calcs.[/quote]Yes, cases where rounding of intermediate results is required mean that if that rounding isn't automatic the flow has to be interrupted to allow it to be done; however, it's usually true that a point at which rounding is required is also a point for which a permanent record is required anyway, so that this is no big deal - flow is interrupted to create a permanent record, so the forced rounding doesn't add any extra interruptions. There may be cases where this isn't the case, but I haven't come across any such case. An interest calculation with large multiples of iteration is an interesting case - interest is usually defined over some fairly long period (a month, or a quarter, or half a year, or a year) and the rules for calculation and documentation generally mean that the number of interest steps applied in between recorded points is one, ie it's exactly the general case pointed out as usual above; if interest has to be calculated for less that a period, the relevant fraction of the base period is used, not iterations over some smaller unit than that fraction, so that doesn't involve any iteration either. So it seems odd for a case of large multiple iterations to happen in interest calculation - except of course where one is producing an illustration of total cost over a long period, where the neccessity for doing the rounding at each step can be a pain.Wed, 05 Sep 2012 18:43:06 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1354351-391-1.aspxTo be fair, higher precision in intermediate calculations in complex equations can in fact cause errors rather than solving them.I'm certainly no authority on currency laws, so I can't speak to that.But, from a mathematics standpoint, suppose all intermediate results are supposed to be rounded to 4 decimal places, but instead you carry them out further, say to 8 places. You've changed the intermediate results, which with large multiples of iteration -- such as for interest calculations, etc. -- could change the final result from what it would have been with different rounding of intermediate calcs.Wed, 05 Sep 2012 15:12:56 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1354351-391-1.aspx[quote][b]CELKO (9/5/2012)[/b][hr]I want to obtain an YTD value for every year and on the same row I have to put the ytd value of previous year .Your DDL is not a table because it has no key, your split the date into parts, we do not use FLOAT in SQL because of rounding errors and the laws concerning currency. Here is teh DDL for a valid table and your query. CREATE TABLE Foobar(cod CHAR(10) NOT NULL, foo_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, PRIMARY KEY (cod, foo_date), vague_value DECIMAL (12,5) NOT NULL);[/quote]I've only rarely seen such drivel in my life. Float(1), Float(2),....Floaqt(52), and Float(53) are all part of the ISO SQL standard. The default precision for Float is 53. DECIMAL(12,5) has precision poorer than 40, so we can expect the rounding behaviour of FLOAT(53) to be around 4 decimal orders of magnitude better than the rounding behaviour of DECIMAL(12,5) even if DECIMAL(12,5) manages to avoid overflow (ie all results are less than 100,000,000, since that overflows DECIMAL(12,5)) which is perhaps unlikely. Non-mathematicians should not talk about rounding errors without first doing a little research to find out how rounding errors actually behave, and should try to avoid making elementary school errors about whether a particular type is capable of representing a useful range of values.There are of course representation issues with early versions of the floating point standard, but if I'm working only with numbers that can be represented exactly in floating point form those representation issues have no impact (so for example I should record euro cents, not euros, pence, not pounds, and so on, and then I'm safe as long as I don't need to do division by numbers with non-representable reciprocals - which is almost always the case in finance). Life would of course be much more pleasant if the SQL standards committee members could get their heads around the latest version of the floating point standard (it's been available for years now, but I understand it's not yet been looked at for SQL) and add FP with decimal-based exponent to the SQL type repertoire; that would eliminate at a stroke every representation issue that is not suffered also by the decimal types. Of course they should add a 128 bit form at the same time, and introduce full support of the standard's exception handling and error signalling. If my previous experience of SQL standardisation is anything to go by, it will take another couple of decades before any of this useful stuff gets into the standard (and I would guess, from the tone of your anti-float rant, that if the committees have members like you it will never happen, and we will be stuck with the criminally bizarre decimal types, a relic of COBOL days, for ever).Since most large banks, most merchant banks, and most really big companies do their finance using float rather than decimal, I believe that the laws to which you refer are a product of your misunderstanding of various financial regulations and have no real existence. Incidentally, who are the "we" to whom you refer? I'd like to know so that I will be aware, if I run into any of them, that I must totally distrust any pronouncement they make that has any matematical content beyond third grade, and remember to carefully check any assertions they make about laws and regulations.Wed, 05 Sep 2012 14:56:06 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1354351-391-1.aspx[quote][b]Brandie Tarvin (9/5/2012)[/b][hr][quote][b]CELKO (9/5/2012)[/b][hr]we do not use FLOAT in SQL because of rounding errors and the laws concerning currency. [/quote]You keep saying this, but I work (and have worked) with multiple companies that do use FLOAT (and use it quite well) because the other data types don't give them the information they need. The waste management industry, for instance, needs it to calculate tonnage and other items. Some people even use FLOAT for financial transactions.So I have two questions for you.1) Who is "we"?2) What currency laws are you referring to? Because if I'm breaking laws by supporting these databases, I'd really like to know before I end up in jail for the rest of my life.[/quote][quote]1) Who is "we"?[/quote]Mr. Celko and his pet mouse (the one he keeps in his shirt pocket, you know, the one with the pocket protector).[quote]2) What currency laws are you referring to? Because if I'm breaking laws by supporting these databases, I'd really like to know before I end up in jail for the rest of my life.[/quote]You have me on this one. My Google-fu fails on finding anything.Wed, 05 Sep 2012 13:20:22 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1354351-391-1.aspx[quote][b]CELKO (9/5/2012)[/b][hr]we do not use FLOAT in SQL because of rounding errors and the laws concerning currency. [/quote]You keep saying this, but I work (and have worked) with multiple companies that do use FLOAT (and use it quite well) because the other data types don't give them the information they need. The waste management industry, for instance, needs it to calculate tonnage and other items. Some people even use FLOAT for financial transactions.So I have two questions for you.1) Who is "we"?2) What currency laws are you referring to? Because if I'm breaking laws by supporting these databases, I'd really like to know before I end up in jail for the rest of my life.Wed, 05 Sep 2012 12:31:20 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic1354351-391-1.aspxSince the table already contains the previous year's YTD totals, would a simple LEFT JOIN suffice to get the prior year's YTD total?[code="sql"]SELECT t1curr.cod, t1curr.[year], t1curr.[month], t1curr.value, t1prev.valueFROM dbo.Table_1 t1currLEFT OUTER JOIN dbo.Table_1 t1prev ON t1prev.cod = t1curr.cod AND t1prev.[year] = t1curr.[year] - 1 AND t1prev.[month] = t1curr.[month][/code]Wed, 05 Sep 2012 12:29:21 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1354351-391-1.aspx[quote][b]Lynn Pettis (9/5/2012)[/b][hr][quote][b]Andy Hyslop (9/5/2012)[/b][hr][quote][b]CELKO (9/5/2012)[/b][hr]I want to obtain an YTD value for every year and on the same row I have to put the ytd value of previous year .Your DDL is not a table because it has no key, your split the date into parts, we do not use FLOAT in SQL because of rounding errors and the laws concerning currency. Here is teh DDL for a valid table and your query. CREATE TABLE Foobar(cod CHAR(10) NOT NULL, foo_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, PRIMARY KEY (cod, foo_date), vague_value DECIMAL (12,5) NOT NULL); SELECT cod, SUM(vague_value) OVER (PARTITION BY cod ORDER BY foo_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS vague_value_runtot FROM Foobar;[/quote]Mr C this is a 2008 forum.........[/quote]Actually, he doesn't care.[/quote]Yeah I know Lynn ;-)Wed, 05 Sep 2012 10:31:36 GMTAndy Hyslophttp://www.sqlservercentral.com/Forums/Topic1354351-391-1.aspx[quote][b]Andy Hyslop (9/5/2012)[/b][hr][quote][b]CELKO (9/5/2012)[/b][hr]I want to obtain an YTD value for every year and on the same row I have to put the ytd value of previous year .Your DDL is not a table because it has no key, your split the date into parts, we do not use FLOAT in SQL because of rounding errors and the laws concerning currency. Here is teh DDL for a valid table and your query. CREATE TABLE Foobar(cod CHAR(10) NOT NULL, foo_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, PRIMARY KEY (cod, foo_date), vague_value DECIMAL (12,5) NOT NULL); SELECT cod, SUM(vague_value) OVER (PARTITION BY cod ORDER BY foo_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS vague_value_runtot FROM Foobar;[/quote]Mr C this is a 2008 forum.........[/quote]Actually, he doesn't care.Wed, 05 Sep 2012 10:27:31 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1354351-391-1.aspx[quote][b]CELKO (9/5/2012)[/b][hr]I want to obtain an YTD value for every year and on the same row I have to put the ytd value of previous year .Your DDL is not a table because it has no key, your split the date into parts, we do not use FLOAT in SQL because of rounding errors and the laws concerning currency. Here is teh DDL for a valid table and your query. CREATE TABLE Foobar(cod CHAR(10) NOT NULL, foo_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, PRIMARY KEY (cod, foo_date), vague_value DECIMAL (12,5) NOT NULL); SELECT cod, SUM(vague_value) OVER (PARTITION BY cod ORDER BY foo_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS vague_value_runtot FROM Foobar;[/quote]Mr C this is a 2008 forum.........Wed, 05 Sep 2012 09:47:03 GMTAndy Hyslophttp://www.sqlservercentral.com/Forums/Topic1354351-391-1.aspxI want to obtain an YTD value for every year and on the same row I have to put the ytd value of previous year .Your DDL is not a table because it has no key, your split the date into parts, we do not use FLOAT in SQL because of rounding errors and the laws concerning currency. Here is teh DDL for a valid table and your query. CREATE TABLE Foobar(cod CHAR(10) NOT NULL, foo_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, PRIMARY KEY (cod, foo_date), vague_value DECIMAL (12,5) NOT NULL); SELECT cod, SUM(vague_value) OVER (PARTITION BY cod ORDER BY foo_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS vague_value_runtot FROM Foobar;Wed, 05 Sep 2012 09:36:29 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1354351-391-1.aspxThank you everybody!Wed, 05 Sep 2012 08:08:54 GMTantonelahttp://www.sqlservercentral.com/Forums/Topic1354351-391-1.aspx[quote][b]subbareddy542 (9/5/2012)[/b][hr]use below code:...[/quote]After correcting [val].Wed, 05 Sep 2012 05:51:49 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1354351-391-1.aspxKoen's nailed all the good reasons for not persisting this information. It's easy enough to calculate on the fly:[code="sql"]-- This running total uses a triangular join.-- The performance of TJ's usually sucks. Depending -- on the maximum number of rows which are -- aggregated when month = 12, you may have to change -- this part of the query to a running totals rCTE-- or a Quirky Update;WITH RunningTotals AS (SELECT t1.cod, t1.year, t1.month, [value] = x.value FROM Table_1 t1CROSS APPLY ( SELECT value = SUM(value) FROM Table_1 t2 WHERE t2.cod = t1.cod AND t2.year = t1.year AND t2.month <= t1.month) x),-- construct a matrix containing all cod/year/month valuesAllCodPeriods AS ( SELECT AllCods.cod, AllYears.year, AllMonths.month FROM (SELECT month FROM RunningTotals GROUP BY month) AllMonths CROSS JOIN (SELECT year FROM RunningTotals GROUP BY year) AllYears CROSS JOIN (SELECT cod FROM RunningTotals GROUP BY cod) AllCods )SELECT matrix.cod, matrix.year, matrix.month, ty.value, [value pp] = ly.value FROM AllCodPeriods matrixLEFT JOIN RunningTotals ty ON ty.cod = matrix.cod AND ty.year = matrix.year AND ty.month = matrix.monthLEFT JOIN RunningTotals ly ON ly.cod = matrix.cod AND ly.year+1 = matrix.year AND ly.month = matrix.month-- here's an alternative using APPLY, which carries down-- the running total from 2012/2 to 2012/3.SELECT AllCods.cod, AllYears.year, AllMonths.month, ty.Value, [value pp] = ly.value FROM (SELECT month FROM Table_1 GROUP BY month) AllMonthsCROSS JOIN (SELECT year FROM Table_1 GROUP BY year) AllYearsCROSS JOIN (SELECT cod FROM Table_1 GROUP BY cod) AllCodsOUTER APPLY ( SELECT value = SUM(value) FROM Table_1 t2 WHERE t2.cod = AllCods.cod AND t2.year = AllYears.year AND t2.month <= AllMonths.month) tyOUTER APPLY ( SELECT value = SUM(value) FROM Table_1 t2 WHERE t2.cod = AllCods.cod AND t2.year+1 = AllYears.year AND t2.month <= AllMonths.month) ly[/code]Wed, 05 Sep 2012 05:44:08 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1354351-391-1.aspxuse below code:CREATE TABLE #Table([cod] [nchar](10) NULL,[year] [int] NULL,[month] [tinyint] NULL,[value] [float] NULL) ON [PRIMARY]GOinsert into #Table values ('cod1',2011,1,100)insert into #Table values ('cod1',2011,2,150)insert into #Table values ('cod1',2011,3,200)insert into #Table values ('cod1',2012,1,100)insert into #Table values ('cod1',2012,2,180)select cod,year,month,sum(value) val into #temp1 from (select * from #Table t2 UNION select t2.cod,T2.year+1,t2.month,'' ppy from #Table t2 WHERE T2.month not in(select isnull(t5.month,0) as month from #Table t5 where (T2.year-1)=T5.year) )k group by cod,year,monthselect cod,year,month,CASE WHEN val<>0 THEN val END val,(select SUM(t1.val) from #temp1 t1 WHERE (T2.year-1)=T1.year AND T2.month>=T1.month) AS PPY from #temp1 T2Wed, 05 Sep 2012 03:35:20 GMTsubbareddy542http://www.sqlservercentral.com/Forums/Topic1354351-391-1.aspxIt seems odd you want to store this values in the table.This means values of a row are dependant on other rows. Not sure this is good design.For example, if you add a record for a year, you have to update other records of the same year to reflect the YTD and you need to update one record of the next year.Usually these values are calculated for reporting and are thus calculated in a SELECT query and passed on to the report (or even calculated in the report itself).If you really want to store it in the table, you can issue two update statements against the table that will calculate the YTD and the previous period value from scratch.An example YTD query can be found here:[url]http://stackoverflow.com/questions/3480247/sql-how-to-find-ytd-amount[/url]Note: it will definately not be easier in SSIS.Wed, 05 Sep 2012 03:19:08 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1354351-391-1.aspxI want to obtain an YTD value for every year and on the same row I have to put the ytd value of previous year .I have this table:[i]CREATE TABLE [dbo].[Table_1]( [cod] [nchar](10) NULL, [year] [int] NULL, [month] [tinyint] NULL, [value] [float] NULL) ON [PRIMARY]GOinsert into Table_1 values ('cod1',2011,1,100)insert into Table_1 values ('cod1',2011,2,150)insert into Table_1 values ('cod1',2011,3,200)insert into Table_1 values ('cod1',2012,1,100)insert into Table_1 values ('cod1',2012,2,180)[/i]I must obtain a new table :cod|year|month|value|value pp|cod1|2011|1|100|Nullcod1|2011|2|250|Nullcod1|2011|3|450|Nullcod1|2012|1|100|100cod1|2012|2|280|250cod1|2012|3|Null|450Anybody can help me?Is it a simpler way to do this in SSIS too?Thank youWed, 05 Sep 2012 01:29:17 GMTantonela