December 22, 2011 at 4:05 am
Hello Friends,
I've task to make some complicated query in SQL.
Have info on Purchase Order, Plans for PO payments per week in ref codes.
Loaded paid invoices.
Loaded unpaid invoices.
Goal is.
To reflect Plan for payment by PO in 1st Lane,
Reflect total value of paid invoices in 2nd Lane,
Reflect total value of unpaid invoices in 3rd Lane.
For 2012 Y.
Please, if any body could help me with that?! Thanks a lot.
December 22, 2011 at 4:55 am
Whatever you define as a "Lane", the best way I can think of is using a calendar table.
I recommend you google for the term "SQL Server calendar table".
Then modify the code to include the columns you need.
You can also define a "week" as per your requirement (start day of a week, ISO format, or any special definition you might need).
December 22, 2011 at 5:37 am
Under "Lane" meant Lines at result section.
So idea is to be so:
Seperation by weeks in column
Week1Week2Week3Week4
Line 1PO # (Payment Plan)10000850011001000
Line 2Paid Invoices5000000
Line 3Unpaid Invoices5000000
P.S thank you for soonest reply.
December 22, 2011 at 5:44 am
Using the calendar table you could first aggregate the values.
In a second step, use the CrossTab approach to pivot the result. For details how the CrossTab concept works, see the related link in my signature.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply