Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BIGGY
Starting Member
17 Posts |
 Posted - 2012-03-14 : 13:27:47
|
| Good afternoon!I have two tables that I'm trying to coordinate between. Matter and Invoice which I'm trying to incorporate into a Crystal Report, however I believe that the calculation needs to happen in the SQL rather than in the VB of the report.The report must ultimately GROUP1 by YEAR(InvoiceDate) and GROUP2 by MatterNumber. Each matter may have one or many invoices. What I need to do in SQL is to select the MATTER.MATTERNUMBER, INVOICE.INVOICEAMOUNT for a given date range and other various select criteria.As the query is pulling in the INVOICEAMOUNT field for each matter, I need it to cut off once the invoices hit the $1,000,000 mark regardless of the date range or anything else. As soon as an invoice dollar amount causes a matter to go over 1 million, the dollar amount should be capped at $1,000,000 and the rest of the invoice and future invoices disregarded. Any suggestions on how to accomplish this would be greatly appreciated.Thanks,Mike |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-14 : 15:41:41
|
| so how should be your output? can you post sample?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
BIGGY
Starting Member
17 Posts |
Posted - 2012-03-14 : 16:09:27
|
| Sure here's a sample of what it would look like...Currently if I just run the basic SELECT query then I will see three fields.MatterNumber | InvoiceDate | InvoiceAmount | InvoiceID1000 3-1-2012 $500,000 11000 3-3-2012 $400,000 21000 3-5-2012 $500,000 31000 3-6-2012 $100,000 4Totaling the invoice amounts up, the matter number 1000 would go over $1 million for a grand total of $1.5 mil. What should be displayed and passed into the Crystal report is...MatterNumber | InvoiceDate | InvoiceAmount | InvoiceID1000 3-1-2012 $500,000 11000 3-3-2012 $400,000 21000 3-5-2012 $100,000 3So two things happened...Invoice #3 was cut so that the grand total does not exceed $1 mil and Invoice #4 was dropped all together (this would apply to any invoices after #4 as well).Thanks!Mike |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-03-14 : 21:05:51
|
[code];with cte as( select MatterNumber, InvoiceDate, InvoiceAmount, InvoiceID, InvAmt = case when InvoiceAmount < MatterNumber then InvoiceAmount else 0 end, CummAmt = case when InvoiceAmount < MatterNumber then InvoiceAmount else MatterNumber end from yourtable where InvoiceID = 1 union all select d.MatterNumber, d.InvoiceDate, d.InvoiceAmount, d.InvoiceID, InvAmt = case when c.CummAmt + d.InvoiceAmount < d.MatterNumber then d.InvoiceAmount else d.MatterNumber - c.CummAmt end, CummAmt = case when c.CummAmt + d.InvoiceAmount < d.MatterNumber then c.CummAmt + d.InvoiceAmount else d.MatterNumber end from cte c inner join yourtable d on c.InvoiceID + 1 = d.InvoiceID )select MatterNumber, InvoiceDate, InvAmt, InvoiceIDfrom ctewhere InvAmt <> 0[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Grouping in SQL to find a maximum cutoff