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

cross tab Expand / Collapse
Author
Message
Posted Wednesday, January 14, 2009 6:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 9:38 AM
Points: 1,525, Visits: 4,071
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 =).


Seth Phelabaum
Consistency is only a virtue if you're not a screwup.

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #636190
Posted Wednesday, January 14, 2009 7:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 37,105, Visits: 31,662
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #636780
Posted Thursday, January 15, 2009 3:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,556, Visits: 2,593

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)


....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

Post #636931
Posted Thursday, January 15, 2009 6:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 37,105, Visits: 31,662
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. :D


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #637022
Posted Thursday, January 15, 2009 6:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,556, Visits: 2,593
It's actually much simple than you are thinking....

here is the simplified version of it...

SELECT	Earn.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 (
SELECT ROW_NUMBER() OVER( PARTITION BY SummarySalaryID ORDER BY SummarySalaryID, Sequence, SalaryHeadID ) AS RowID,
SummarySalaryID, SalaryHeadID, SalaryHeadCode, SalaryHeadName, SalaryHeadAlias,
HeadType, Sequence, SalaryRate, SalaryAmount
FROM #tmpMonthlySalaries
WHERE PaymentType = 'Earning'
AND ArrearPayment = 0
AND HeadType != @strOtherPayments
) Earn
FULL OUTER JOIN
(
SELECT ROW_NUMBER() OVER( PARTITION BY SummarySalaryID ORDER BY SummarySalaryID, Sequence, SalaryHeadID ) AS RowID,
SummarySalaryID, SalaryHeadID, SalaryHeadCode, SalaryHeadName, SalaryHeadAlias,
HeadType, Sequence, SalaryRate, SalaryAmount
FROM #tmpMonthlySalaries
WHERE PaymentType = '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

Post #637058
Posted Thursday, January 15, 2009 9:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 37,105, Visits: 31,662
That's pretty much like what Seth has and both are pretty much the way I'd do it...

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #637269
Posted Thursday, January 15, 2009 9:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 37,105, Visits: 31,662
Jeff Moden (1/15/2009)
But, aren't you executing the column merge code once for each "employee"?


Never mind... not enough coffee... :P


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #637271
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse