Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

create YTD in a Table Expand / Collapse
Author
Message
Posted Wednesday, September 5, 2012 1:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 23, 2014 4:44 AM
Points: 52, Visits: 91
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 .

I have this table:
CREATE TABLE [dbo].[Table_1](
[cod] [nchar](10) NULL,
[year] [int] NULL,
[month] [tinyint] NULL,
[value] [float] NULL
) ON [PRIMARY]

GO


insert 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 must obtain a new table :
cod|year|month|value|value pp|
cod1|2011|1|100|Null
cod1|2011|2|250|Null
cod1|2011|3|450|Null
cod1|2012|1|100|100
cod1|2012|2|280|250
cod1|2012|3|Null|450

Anybody can help me?
Is it a simpler way to do this in SSIS too?

Thank you

Post #1354351
Posted Wednesday, September 5, 2012 3:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 13,636, Visits: 11,509
It 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:
http://stackoverflow.com/questions/3480247/sql-how-to-find-ytd-amount

Note: it will definately not be easier in SSIS.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1354397
Posted Wednesday, September 5, 2012 3:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, December 8, 2014 7:35 AM
Points: 888, Visits: 672
use below code:

CREATE TABLE #Table(
[cod] [nchar](10) NULL,
[year] [int] NULL,
[month] [tinyint] NULL,
[value] [float] NULL
) ON [PRIMARY]

GO


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)
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,month

select 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
Post #1354402
Posted Wednesday, September 5, 2012 5:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
Koen's nailed all the good reasons for not persisting this information. It's easy enough to calculate on the fly:

-- 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 t1
CROSS 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 values
AllCodPeriods 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 matrix
LEFT JOIN RunningTotals ty
ON ty.cod = matrix.cod
AND ty.year = matrix.year
AND ty.month = matrix.month
LEFT 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) AllMonths
CROSS JOIN (SELECT year FROM Table_1 GROUP BY year) AllYears
CROSS JOIN (SELECT cod FROM Table_1 GROUP BY cod) AllCods
OUTER APPLY (
SELECT value = SUM(value)
FROM Table_1 t2
WHERE t2.cod = AllCods.cod
AND t2.year = AllYears.year
AND t2.month <= AllMonths.month
) ty
OUTER 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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1354448
Posted Wednesday, September 5, 2012 5:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
subbareddy542 (9/5/2012)
use below code:

...


After correcting [val].


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1354451
Posted Wednesday, September 5, 2012 8:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 23, 2014 4:44 AM
Points: 52, Visits: 91
Thank you everybody!
Post #1354560
Posted Wednesday, September 5, 2012 9:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:12 PM
Points: 1,945, Visits: 3,180

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;


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1354681
Posted Wednesday, September 5, 2012 9:47 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 7:14 AM
Points: 692, Visits: 2,815
CELKO (9/5/2012)

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;


Mr C this is a 2008 forum.........


==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Post #1354694
Posted Wednesday, September 5, 2012 10:27 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 20,859, Visits: 32,882
Andy Hyslop (9/5/2012)
CELKO (9/5/2012)

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;


Mr C this is a 2008 forum.........


Actually, he doesn't care.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1354732
Posted Wednesday, September 5, 2012 10:31 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 7:14 AM
Points: 692, Visits: 2,815
Lynn Pettis (9/5/2012)
Andy Hyslop (9/5/2012)
CELKO (9/5/2012)

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;


Mr C this is a 2008 forum.........


Actually, he doesn't care.


Yeah I know Lynn


==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Post #1354736
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse