September 14, 2015 at 1:31 pm
I have update schema and i upload image with my desire result.
CREATE TABLE #NONAMETABLE(
sinGroup NVARCHAR(10)
,column1 INT
,column2 int
);
INSERT INTO #NONAMETABLE(sinGroup,column1,column2) VALUES
('y1',100,0),
('y2',0,60),
('z1',150,0),
('z2',0,50)
;
drop table #NONAMETABLE
September 14, 2015 at 1:42 pm
Are you doing a running total? Is this in SSRS that you need to do this? Or in T-SQL?
September 14, 2015 at 1:49 pm
T- SQL
September 14, 2015 at 3:09 pm
a starter for you to consider
CREATE TABLE #NONAMETABLE(
ID INT
,column1 INT
);
INSERT INTO #NONAMETABLE(ID,column1) VALUES
(1,100.00)
,(2,60.00);
SELECT column1, column1 - LEAD(column1,1,NULL) OVER(order by ID) as column2
FROM #NONAMETABLE
DROP TABLE #NONAMETABLE;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 14, 2015 at 3:12 pm
Slightly different approach from JLS's
😎
USE tempdb;
GO
SET NOCOUNT ON;
GO
DECLARE @SAMPLE_DATA TABLE
(
column1 NUMERIC(12,2) NOT NULL
,column2 NUMERIC(12,2) NOT NULL
);
INSERT INTO @SAMPLE_DATA(column1,column2)
VALUES ( 100.00, 40.00)
,( 90.00, 70.00)
,( 200.00, 140.00)
;
;WITH BASE_DATA AS
(
SELECT
ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) AS RID
,SD.column1
,SD.column2
FROM @SAMPLE_DATA SD
)
SELECT
BD.RID
,BD.column1
,BD.column2
FROM BASE_DATA BD
UNION ALL
SELECT
BD.RID
,BD.column1 - BD.column2
,0
FROM BASE_DATA BD
ORDER BY RID ASC
,BD.column2 DESC
;
Results
RID column1 column2
---- -------- --------
1 100.00 40.00
1 60.00 0.00
2 90.00 70.00
2 20.00 0.00
3 200.00 140.00
3 60.00 0.00
September 14, 2015 at 3:41 pm
No, this is my starting point:
CREATE TABLE #NONAMETABLE(
ID INT
,column1 INT
,column2 int
);
INSERT INTO #NONAMETABLE(ID,column1,column2) VALUES
(1,100.00,40)
;
drop table #NONAMETABLE
September 14, 2015 at 3:49 pm
kdejan87 (9/14/2015)
No, this is my starting point:
CREATE TABLE #NONAMETABLE(
ID INT
,column1 INT
,column2 int
);
INSERT INTO #NONAMETABLE(ID,column1,column2) VALUES
(1,100.00,40)
;
drop table #NONAMETABLE
Quick question, can you post a more complete sample and result set, single row sample leaves quiet few questions unanswered.
😎
September 14, 2015 at 4:23 pm
I have update schema and i upload image with my desire result. I realized I need this result.
CREATE TABLE #NONAMETABLE(
sinGroup NVARCHAR(10)
,column1 INT
,column2 int
);
INSERT INTO #NONAMETABLE(sinGroup,column1,column2) VALUES
('y1',100,0),
('y2',0,60),
('z1',150,0),
('z2',0,50)
;
drop table #NONAMETABLE
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply