June 7, 2015 at 11:13 am
Dear All,
I am creating a query that shows the consumption of stock against Manf Orders (M/O) and struggling on the last hurdle. I am having difficulties calculating a running total based on an Opening Balance. The first line returns the correct results but the following lines do not. I have tried other variants of the "Over Partition" but still no joy?
SELECT CASE WHEN ROWNUMBER > 1 THEN ''
ELSE A.Component
END AS Component ,
CASE WHEN ROWNUMBER > 1 THEN ''
ELSE A.SKU
END AS SKU ,
CASE WHEN ROWNUMBER > 1 THEN ''
ELSE SUBSTRING(A.[Order Date], 7, 2) + SUBSTRING(A.[Order Date],5, 2) + SUBSTRING(A.[Order Date], 3, 2)
END AS [Order Date] ,
CASE WHEN ROWNUMBER > 1 THEN 0
ELSE A.[Current Balance]
END AS [Opening Balance] ,
A.[Qty Req,d ] ,
A.[Issued to M/O] ,
'Running Total'=SUM([Current Balance] - abs(A.[Qty Req,d] + ABS(A.[Issued to M/O])) OVER (PARTITION BY a.Component ORDER BY A.Component ROWS BETWEEN UNBOUNDED PRECEDING AND current row)
FROM [M3_PackaginAndMaterials] A
Please see attachment to view output.
Any assistance would be appreciated.
Kind regards
June 7, 2015 at 12:45 pm
will be probably easier if you provided create table / sample insert data statements and expected results ....this makes it much easier to provide you with a tested solution.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 7, 2015 at 1:59 pm
Hi,
Please find Sample Table c/w records:-
CREATE TABLE #Temp(
[ROWNUMBER] [float] NULL,
[Component] [float] NULL,
[M/O No] [float] NULL,
[Opening Balance] [float] NULL,
[M/O Qty Req,d (MWOMAT)] [float] NULL,
[Issued to M/O] [float] NULL,
[Running Total] [float] NULL
) ON [PRIMARY]
insert into #temp
([ROWNUMBER],[Component],[M/O No],[Opening Balance],[M/O Qty Req,d (MWOMAT)],[Issued to M/O])
VALUES
(1,'60000015','2365659','295.95','12.79','0'),
(1,'60000055','2564674','19140','-6012','0'),
(2,'60000055','2564675','19140','-6012','0'),
(3,'60000055','2567321','19140','-1058.112','0'),
(4,'60000055','2567322','19140','-1058.112','0'),
(5,'60000055','2567323','19140','-1058.112','0'),
(6,'60000055','2567324','19140','-1058.112','0'),
(7,'60000055','2567325','19140','-1058.112','0'),
(8,'60000055','2567326','19140','-1058.112','0'),
(1,'60000060','2565686','-2404.8','-6012','0'),
(2,'60000060','2565687','-6012','-2404.8','0'),
(3,'60000060','2565688','-6012','-2404.8','0')
select * from #temp
SELECT
ROWNUMBER
,CASE WHEN ROWNUMBER > 1 THEN ''
ELSE A.Component
END AS Component ,
CASE WHEN ROWNUMBER > 1 THEN 0
ELSE A.[Opening Balance]
END AS [Opening Balance] ,
A.[M/O Qty Req,d (MWOMAT)] ,
A.[Issued to M/O] ,
SUM(A.[Opening Balance]) - ([M/O Qty Req,d (MWOMAT)]),
'Running Total'=SUM(A.[Opening Balance] - abs(A.[M/O Qty Req,d (MWOMAT)] - ABS(A.[Issued to M/O]))) OVER (PARTITION BY a.Component ORDER BY A.Component ROWS BETWEEN UNBOUNDED PRECEDING AND current row)
FROM #Temp A
group by ROWNUMBER,a.Component,[M/O No],[opening balance],[M/O Qty Req,d (MWOMAT)],a.[Issued to M/O]
drop table #Temp
GO
Thanks in advance
June 7, 2015 at 2:04 pm
thanks for script...what are the expected results from the sample data?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 7, 2015 at 2:21 pm
Hi,
Please find attached spread sheet showing the correct Running Total values .
Kind regards
June 7, 2015 at 9:51 pm
That's not the kosher way of posting expected results, but anyway... here's the answer:
SELECT Component
, [Opening Balance]
, [M/O Qty Req,d (MWOMAT)]
, [Opening Balance] - ABS(SUM([M/O Qty Req,d (MWOMAT)]) OVER (PARTITION BY Component
ORDER BY rownumber
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)) as RunSum
FROM #Temp;
Why ABS() should be necessary I don't know, but it appears to solve the problem. BTW, removing/replacing the spaces in your column names would make coding a LOT easier. =)
June 8, 2015 at 1:17 am
Same as pietlinden's, adjusted to the required output
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#Temp') IS NOT NULL DROP TABLE #Temp;
CREATE TABLE #Temp(
[ROWNUMBER] [float] NULL,
[Component] [float] NULL,
[M/O No] [float] NULL,
[Opening Balance] [float] NULL,
[M/O Qty Req,d (MWOMAT)] [float] NULL,
[Issued to M/O] [float] NULL,
[Running Total] [float] NULL
) ON [PRIMARY]
INSERT INTO #temp
([ROWNUMBER],[Component],[M/O No],[Opening Balance],[M/O Qty Req,d (MWOMAT)],[Issued to M/O])
VALUES
(1,'60000015','2365659','295.95','12.79','0'),
(1,'60000055','2564674','19140','-6012','0'),
(2,'60000055','2564675','19140','-6012','0'),
(3,'60000055','2567321','19140','-1058.112','0'),
(4,'60000055','2567322','19140','-1058.112','0'),
(5,'60000055','2567323','19140','-1058.112','0'),
(6,'60000055','2567324','19140','-1058.112','0'),
(7,'60000055','2567325','19140','-1058.112','0'),
(8,'60000055','2567326','19140','-1058.112','0'),
(1,'60000060','2565686','-2404.8','-6012','0'),
(2,'60000060','2565687','-6012','-2404.8','0'),
(3,'60000060','2565688','-6012','-2404.8','0')
SELECT
[ROWNUMBER]
,CASE
WHEN [ROWNUMBER] = 1 THEN [Component]
ELSE 0
END AS [Component]
,CASE
WHEN [ROWNUMBER] = 1 THEN [Opening Balance]
ELSE 0
END AS [Opening Balance]
,[M/O Qty Req,d (MWOMAT)]
,[Issued to M/O]
,ROUND([Opening Balance] - ABS(SUM([M/O Qty Req,d (MWOMAT)]) OVER
(
PARTITION BY [Component]
ORDER BY [ROWNUMBER]
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
)),3) AS [Running Total]
FROM #temp;
Results
ROWNUMBER Component Opening Balance M/O Qty Req,d (MWOMAT) Issued to M/O Running Total
----------- ----------- ----------------- ---------------------- --------------- --------------
1 60000015 295.95 12.79 0 283.16
1 60000055 19140 -6012 0 13128
2 0 0 -6012 0 7116
3 0 0 -1058.112 0 6057.888
4 0 0 -1058.112 0 4999.776
5 0 0 -1058.112 0 3941.664
6 0 0 -1058.112 0 2883.552
7 0 0 -1058.112 0 1825.44
8 0 0 -1058.112 0 767.328
1 60000060 -2404.8 -6012 0 -8416.8
2 0 0 -2404.8 0 -14428.8
3 0 0 -2404.8 0 -16833.6
June 8, 2015 at 1:22 am
Hi,
Thank you to all that have helped me resolve this.
Apologies for the long winded post's. Will attempt to use more screenshots in the future.
Kindest regards to All:-)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply