SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


cross tab


cross tab

Author
Message
Garadin
Garadin
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7025 Visits: 4107
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216754 Visits: 41986
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...Wink, That's what I've done in our payroll system....SmileSmile


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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Ramesh Saive
Ramesh Saive
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6786 Visits: 2643

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


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

--Ramesh


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216754 Visits: 41986
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. BigGrin

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Ramesh Saive
Ramesh Saive
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6786 Visits: 2643
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


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216754 Visits: 41986
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216754 Visits: 41986
Jeff Moden (1/15/2009)
But, aren't you executing the column merge code once for each "employee"?


Never mind... not enough coffee... Tongue

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search