January 13, 2009 at 6:28 am
IF OBJECT_ID('TempDB..#tblInv','U') IS NOT NULL
DROP TABLE #tblInv
CREATE TABLE #tblInv
(
ID INT ,
PartNo VARCHAR(10) NOT NULL,
TranType VARCHAR(7) NOT NULL,
Quantity INT NOT NULL
)
insert into #tblInv(id,partno, TranType,quantity)
select 1, 'A', 'buy', 50 union all
select 2,'A', 'buy', 50 union all
select 3,'A', 'buy', 50 union all
select 4,'A', 'buy', 50 union all
select 5,'A', 'buy', 100 union all
select 6,'A', 'sell', 150 union all
select 7,'A', 'sell', 100 union all
select 8,'A', 'buy', 500 union all
select 9,'A', 'sell', 450 union all
select 10,'A' ,'buy', 450 union all
select 11,'A', 'sell', 550 union all
select 12,'B', 'buy', 300 union all
select 13,'B', 'sell', 200 union all
select 14,'B' ,'buy', 200 union all
select 15,'B', 'sell', 250 union all
select 16, 'B', 'sell', 250
select * from #tblInv
Base from this...how can I get something like this:
idbIDPartNoTranTypeQuantitysIDsQuantity
11Abuy506150
22Abuy507100
33Abuy509450
44Abuy5011550
55Abuy10000
68Abuy50000
710Abuy45000
812Bbuy30013200
914Bbuy20015250
100B-016250
thanks
January 13, 2009 at 6:47 am
Actually you don't need a cross tab/pivot....
IF OBJECT_ID('TempDB..#tblInv','U') IS NOT NULL
DROP TABLE #tblInv
CREATE TABLE #tblInv
(
ID INT ,
PartNo VARCHAR(10) NOT NULL,
TranType VARCHAR(7) NOT NULL,
Quantity INT NOT NULL
)
insert into #tblInv(id,partno, TranType,quantity)
select 1, 'A', 'buy', 50 union all
select 2,'A', 'buy', 50 union all
select 3,'A', 'buy', 50 union all
select 4,'A', 'buy', 50 union all
select 5,'A', 'buy', 100 union all
select 6,'A', 'sell', 150 union all
select 7,'A', 'sell', 100 union all
select 8,'A', 'buy', 500 union all
select 9,'A', 'sell', 450 union all
select 10,'A' ,'buy', 450 union all
select 11,'A', 'sell', 550 union all
select 12,'B', 'buy', 300 union all
select 13,'B', 'sell', 200 union all
select 14,'B' ,'buy', 200 union all
select 15,'B', 'sell', 250 union all
select 16, 'B', 'sell', 250
select * from #tblInv
SELECTCOALESCE(b.id,s.id) AS id, COALESCE(b.id,0) AS bid, COALESCE(b.partno,s.partno) AS partno,
COALESCE(b.TranType,s.TranType) AS TranType, COALESCE(b.quantity,0) AS bquantity,
COALESCE(s.id,0) AS [sid], COALESCE(s.quantity,0) AS squantity
FROM(
SELECTROW_NUMBER() OVER( PARTITION BY partno ORDER BY id ) AS rowid, *
FROM#tblInv
WHERETranType = 'buy'
) b
FULL OUTER JOIN
(
SELECTROW_NUMBER() OVER( PARTITION BY partno ORDER BY id ) AS rowid, *
FROM#tblInv
WHERETranType = 'sell'
) s ON b.partno = s.partno AND b.rowid = s.rowid
Edit:
Aaaah, I think I had too much coffee in the day:w00t:, posting 2K5 solutions in 2K forums....
Just wait for few minutes, I'll be back with 2K solution...
--Ramesh
January 13, 2009 at 9:26 am
Ramesh,
thank you very much for looking into this...It would be easy if the database is sql2005, but unfortunately, I'm dealing with sql2k...which make a bit more difficult. I had try the using Case statement, but not getting the anywhere.
thanks
January 13, 2009 at 1:14 pm
Try this
http://www.sqlservercentral.com/articles/T-SQL/63681/
It should answer most of your questions.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 13, 2009 at 10:16 pm
First of all, a disclaimer:
The report you asked for is something that is likely best handled in the front end. This really is not a cross tab report (at least not the way I define it.) This is combining 2 separate data sets into one. The data you are trying to display in the same row has no true relation other than the fact that they're transactions on the same product ordered by their date.
That said, you can get the output you're requesting like this:
[font="Courier New"]IF OBJECT_ID('TempDB..#tblInv','U') IS NOT NULL
DROP TABLE #tblInv
CREATE TABLE #tblInv
(
ID INT,
PartNo VARCHAR(10) NOT NULL,
TranType VARCHAR(7) NOT NULL,
Quantity INT NOT NULL,
PRIMARY KEY CLUSTERED(PartNo, TranType, ID),
RN INT,
BSOrder INT
)
INSERT INTO #tblInv(id,partno, TranType,quantity)
SELECT 1, 'A', 'buy', 50 UNION ALL
SELECT 2,'A', 'buy', 50 UNION ALL
SELECT 3,'A', 'buy', 50 UNION ALL
SELECT 4,'A', 'buy', 50 UNION ALL
SELECT 5,'A', 'buy', 100 UNION ALL
SELECT 6,'A', 'sell', 150 UNION ALL
SELECT 7,'A', 'sell', 100 UNION ALL
SELECT 8,'A', 'buy', 500 UNION ALL
SELECT 9,'A', 'sell', 450 UNION ALL
SELECT 10,'A' ,'buy', 450 UNION ALL
SELECT 11,'A', 'sell', 550 UNION ALL
SELECT 12,'B', 'buy', 300 UNION ALL
SELECT 13,'B', 'sell', 200 UNION ALL
SELECT 14,'B' ,'buy', 200 UNION ALL
SELECT 15,'B', 'sell', 250 UNION ALL
SELECT 16, 'B', 'sell', 250
DECLARE @RN INT,
@BSOrder INT,
@ID INT,
@LastPart VARCHAR(10),
@LastType VARCHAR(7)
SET @RN = 0
SET @BSOrder = 1
UPDATE #tblInv
SET @RN = RN = @RN + 1,
@BSOrder = BSOrder = CASE WHEN @LastPart = PartNo AND @LastType = TranType THEN @BSOrder + 1 ELSE 1 END,
@LastPart = PartNo,
@LastType = TranType,
@ID = [ID] -- Anchor
FROM #tblInv WITH (INDEX(0))
SELECT COALESCE(B.RN, S.RN) RN,
ISNULL(B.[ID],0) BID,
COALESCE(B.PartNo,S.PartNo) PartNo,
ISNULL(B.TranType,'-') TranType,
ISNULL(B.Quantity,0) Quantity,
ISNULL(S.[ID],0) SID,
ISNULL(S.Quantity,0) sQuantity
FROM (SELECT * FROM #tblInv WHERE TranType = 'buy') B
FULL OUTER JOIN
(SELECT * FROM #tblInv WHERE TranType = 'sell') S
ON B.PartNo = S.PartNo AND B.BSOrder = S.BSOrder
ORDER BY COALESCE(B.RN, S.RN)[/font]
January 14, 2009 at 3:13 am
I don't get it... why does the SQuantity drop to 0 for rows 5 and 8? Not enough quantity has been sold off for that to happen on what appears to be a simple running total problem. I know the problem has been solved, but can someone explain why? Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 14, 2009 at 3:15 am
Never mind... not enough coffee... I see it. And, I agree with Seth... unless you have some additional information, this is definitely not the way I'd output from an inventory system.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 14, 2009 at 3:24 am
Jeff Moden (1/14/2009)
Never mind... not enough coffee... I see it. And, I agree with Seth... unless you have some additional information, this is definitely not the way I'd output from an inventory system.
Aaaah...;), That's what I've done in our payroll system....:):)
--Ramesh
January 14, 2009 at 3:41 am
Ramesh (1/14/2009)
Jeff Moden (1/14/2009)
Never mind... not enough coffee... I see it. And, I agree with Seth... unless you have some additional information, this is definitely not the way I'd output from an inventory system.Aaaah...;), That's what I've done in our payroll system....:):)
Heh... payroll, inventory... doesn't matter. I don't understand the business rules for matching two seemingly unrelated rows.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 14, 2009 at 3:55 am
Jeff Moden (1/14/2009)
Ramesh (1/14/2009)
Jeff Moden (1/14/2009)
Never mind... not enough coffee... I see it. And, I agree with Seth... unless you have some additional information, this is definitely not the way I'd output from an inventory system.Aaaah...;), That's what I've done in our payroll system....:):)
Heh... payroll, inventory... doesn't matter. I don't understand the business rules for matching two seemingly unrelated rows.
I guess it just like showing data in columnar manner, that you see in payslips. And also there are some not so good reporting tools (which i use) that doesn't do what it should be doing...
--Ramesh
January 14, 2009 at 6:40 am
If the amounts sold were directly related to the amounts bought, this would be a good report. If your payroll does the same with hours worked and amount paid, good stuff, there's a direct relation. Here, you've really got 2 completley separate data sets, your ordering and your sales. If you were ordering specifically for a sale order, fine. Otherwise... I don't see much value in putting it all in the same column. But hey, if it helps you do something, that's good.
And yeah, I had to stare at that output for several minutes before I figured out what he was actually asking for as well Jeff =).
January 14, 2009 at 7:10 pm
Ramesh (1/14/2009)
Jeff Moden (1/14/2009)
Ramesh (1/14/2009)
Jeff Moden (1/14/2009)
Never mind... not enough coffee... I see it. And, I agree with Seth... unless you have some additional information, this is definitely not the way I'd output from an inventory system.Aaaah...;), That's what I've done in our payroll system....:):)
Heh... payroll, inventory... doesn't matter. I don't understand the business rules for matching two seemingly unrelated rows.
I guess it just like showing data in columnar manner, that you see in payslips. And also there are some not so good reporting tools (which i use) that doesn't do what it should be doing...
Ah! Ok... I get it... the data being presented is for a single "employee" (or whatever), all the rows in the example are for a single employee, and you don't really give a hoot what the order is so long as all the deductions (for example) are on one side. Heh... that also means, this is RBAR on steriods because you don't have a decent reporting tool to do it for you and no one wants to (or can't) write an app to do it for you.
How is the current solution working so far a performance goes? I ask because I can just see someone thinking they need a While loop to loop through each "employee" (or whatever) and that's patently not the case.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 15, 2009 at 3:17 am
Heh... that also means, this is RBAR on steriods....
Actually, I think its not RBAR, as its like merging two resultsets on COLUMNS...so its more like CBAR(Column-By-Agonizing-Row):hehe:
....because you don't have a decent reporting tool to do it for you and no one wants to (or can't) write an app to do it for you.
And I don't have decent reporting guys too...:D:D:D
How is the current solution working so far a performance goes? I ask because I can just see someone thinking they need a While loop to loop through each "employee" (or whatever) and that's patently not the case.
Performance-wise, I am very satisfied (and the clients too...) with what I've done, the procedure gives me the payslips of 4000+ employees within 15-20 seconds on an average 10 executions on an average server. And I know, Jeff, you could do even better than this...:)
--Ramesh
January 15, 2009 at 6:10 am
But, aren't you executing the column merge code once for each "employee"?
And, if you're happy with the speed, then that's what matters. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
January 15, 2009 at 6:45 am
It's actually much simple than you are thinking....
here is the simplified version of it...
SELECTEarn.RowID AS EarnRowID, Earn.SalaryHeadName AS EarnSalaryHeadName, Earn.HeadType AS EarnHeadType,
Earn.SalaryAmount AS EarnSalaryAmount, Dedn.RowID AS DednRowID,
Dedn.SalaryHeadName AS DednSalaryHeadName, Dedn.HeadType AS DednHeadType,
Dedn.SalaryAmount AS DednSalaryAmount
FROM(
SELECTROW_NUMBER() OVER( PARTITION BY SummarySalaryID ORDER BY SummarySalaryID, Sequence, SalaryHeadID ) AS RowID,
SummarySalaryID, SalaryHeadID, SalaryHeadCode, SalaryHeadName, SalaryHeadAlias,
HeadType, Sequence, SalaryRate, SalaryAmount
FROM#tmpMonthlySalaries
WHEREPaymentType= 'Earning'
AND ArrearPayment = 0
AND HeadType != @strOtherPayments
) Earn
FULL OUTER JOIN
(
SELECTROW_NUMBER() OVER( PARTITION BY SummarySalaryID ORDER BY SummarySalaryID, Sequence, SalaryHeadID ) AS RowID,
SummarySalaryID, SalaryHeadID, SalaryHeadCode, SalaryHeadName, SalaryHeadAlias,
HeadType, Sequence, SalaryRate, SalaryAmount
FROM#tmpMonthlySalaries
WHEREPaymentType= 'Deduction'
AND ArrearPayment = 0
AND HeadType != @strOtherPayments
) Dedn ON Earn.SummarySalaryID = Dedn.SummarySalaryID AND Earn.RowID = Dedn.RowID
Though I am happy with what I've got, but still I am very much open to your suggestions...
--Ramesh
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply