drew.allen - Tuesday, January 2, 2018 1:25 PM
OK, I am not getting the results so I will post some data.
The end result should look similar to:
fvendno fcompany POItem PORelsDate........TotStdCost
001275 B&R Industried.............................. 1.00
001275 B&R Industried.............................. 1.00 (total Row)
002119 Roberson Tool................................ 2.00
002119 Roberson Tool................................ 3.00
002119 Roberson Tool................................ 5.00 (total Row)
002302 Unique Automation.......................... 4.00 ( and so on).
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
Create table #mytable(
fvendno char(6),
fcompany char(50),
POItemKey char(12),
PORelsdate datetime,
totstdcost numeric(15,4),
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED)
--===== All Inserts into the IDENTITY column
Set Identity_Insert #mytable ON
--===== Insert the test data into the test table
Insert into #mytable (fvendno, fcompany, POitemKey, PORelsDate, totstdcost, ID)
Select '000027', 'Albany Steel', '123456 1', Cast('01/01/2018' as datetime), 1.00, 1 Union all
Select '000027', 'Albany Steel', '123459 1', Cast('01/01/2018' as datetime), 2.00, 2 Union all
Select '000060', 'Apple Rubber', '123455 1', Cast('01/01/2018' as datetime), 3.00, 3 Union all
Select '000060', 'Apple Rubber', '123467 1', Cast('01/01/2018' as datetime), 4.00, 4 Union all
Select '000080', 'Century Spring', '189456 1', Cast('01/01/2018' as datetime), 5.00, 5 Union all
Select '000127', 'Everett Manufacturing', '223456 1', Cast('01/01/2018' as datetime), 6.00, 6
--===== Set the identity insert back to normal
Set Identity_Insert #mytable OFF
Select * from #mytable