| Author |
Topic |
|
D.ep
Starting Member
10 Posts |
 Posted - 2010-01-29 : 13:22:14
|
| Hi,I have a problem here, below is my sample query from a table called tblTicketSummary. This table contains "tickets" which are essentially sales reciepts - each ticket has its own "[ticketID]" which could include many rows (rows are line items, such as products or services). The problem is that there is a separate line item for coupons. So the "[Line Totals]" for each sale item is not in fact the total paid for each item but rather the total paid BEFORE any coupons have been applied. So what i now need is another column added for the actual total paid AFTER the coupon has been applied. Basically i need to take each ticket and calculate the [Line total] - ([Line total] * sum([discount%])) for each [ticketID] and put that value into another column column so i can see how much each line item actually sold for.[CODE]PK TicketID Type Service Discount% Discount$ Line Total1 1024 S Item 1 $40.00 2 1024 S Item 2 $30.00 3 1024 S Item 3 $45.00 4 1024 U coupon1 10.00% ($11.50)5 2190 S Item 1 $35.00 6 2190 S Item 2 $35.00 7 2190 U coupon1 10.00% ($7.00)8 2190 U coupon2 10.00% ($7.00) [/CODE]Sorry i know i probably explained it poorly. But i reaaally need help :( Thanks so much! |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2010-01-29 : 13:37:55
|
| Post the query that produced those results and add the new desired column values to the expected output.Be One with the OptimizerTG |
 |
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-29 : 13:46:03
|
Yes you are going to have to produce some data and expected results...IMO it sounds like you only needSelect TicketID, Sum(Discount$) as Discounts, Sum([Line Total]) as Costs, Sum(Discount$ + [Line Total]) as Costs_After_Discount from your_TableGroup by TicketID |
|
|
|
D.ep
Starting Member
10 Posts |
Posted - 2010-01-29 : 13:59:23
|
Hi, sorry i should have included this in the first place. Here is the basic query I used to get my results - I used 2 tables the tblTicketsSummary contains information about the client and date and such (which i have removed to condense the query) the tblTicketsRow is the one with all the line item information:tblTicketsRow.fldPK AS [PK],tblTicketsRow.fldTicketID AS [Ticket ID],tblTicketsRow.fldPS AS [Service Type],tblTicketsRow.fldId AS [Service ID],tblTicketsRow.fldDiscountPercent AS [Discount %],tblTicketsRow.fldDiscountCurrency AS [Discount $],tblTicketsRow.fldRowTotal AS [Ticket Sub Total],tblTicketsRow.fldPrice AS [Price],tblTicketsRow.fldRowTotal AS [Total Before Discount]FROM tblTicketsSummary INNER JOIN tblTicketsRow ON tblTicketsSummary.fldTicketID = tblTicketsRow.fldTicketIDWHERE NOT (tblTicketsSummary.fldDateClosed IS NULL) I have BOLDED what I would like the result to be.PK TicketID Type Service Discount% Discount$ Line Total TOTAL 1 1024 S Item 1 $40.00 $36.002 1024 S Item 2 $30.00 $27.00 3 1024 S Item 3 $45.00 $40.50 4 1024 U coupon1 10.00% ($11.50)5 2190 S Item 1 $35.00 $28.00 6 2190 S Item 2 $35.00 $28.00 7 2190 U coupon1 10.00% ($7.00)8 2190 U coupon2 10.00% ($7.00) Thanks again. |
|
|
|
D.ep
Starting Member
10 Posts |
Posted - 2010-01-29 : 14:03:21
|
| I wish i could edit my post above - i left two columns that arnt shown in the output so please ignore:"tblTicketsRow.fldRowTotal AS [Ticket Sub Total]""tblTicketsRow.fldPrice AS [Price]" |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-29 : 14:08:20
|
| You have some structure issues here...1. for ID 1024 the 10% applies to each item irregardless, for the 2nd the is an addition of the 10%, if this is working correctly then fine.2. When you start combining % discounts with savings you need to be careful... Original total = $70 apply 10% discount -> $70 - ( $70 * .10 ) = $63 New Total = $63 apply another 10% discount -> $63 - ( $63 * .10 ) = $56.70Note this is NOT what you would have got with $28 + $28 = $56So am I to assume for 2190 if you had a discount of 10% and then another for 20% you would just assume 30% (which as proved above may be wrong depending on which is applied first) |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-29 : 14:16:56
|
If the Discounts are addative then here you go, you might have to take care of NULLS yourself if no discount is applied to an ID.declare @t table(ID int, type varchar(20), Service Varchar(20), Discount_Perc float, Discount_Dollar float, Line_Total Float)insert @tselect 1024, 'S', 'Item 1', NULL ,NULL , 40.00 UNION ALLselect 1024, 'S', 'Item 2', NULL ,NULL , 30.00 UNION ALLselect 1024, 'S', 'Item 3', NULL ,NULL , 45.00 UNION ALLselect 1024, 'U', 'Coupon 1', .10 , 11.50 , NULL UNION ALLselect 2190, 'S', 'Item 1', NULL ,NULL , 35.00 UNION ALLselect 2190, 'S', 'Item 2', NULL ,NULL , 35.00 UNION ALLselect 2190, 'U', 'Coupon 1', .10 ,NULL , NULL UNION ALLselect 2190, 'U', 'Coupon 2', .10 ,NULL , NULL Select * from @tSelect *, Line_Total - (Line_Total * (Select Sum(b.Discount_Perc) from @t b where b.ID = a.ID)) as Final_Cost From @t a EDITS: For correctness |
|
|
|
D.ep
Starting Member
10 Posts |
Posted - 2010-01-29 : 14:17:26
|
| Thanks for the comment. I did not design the database, Its a point of sales software, i just need to create a report to output the values i need. I know that i had a ticket which contained 2 coupons of 10%, and each line total for the coupon = the same value. meaning, each was calculated identically on the total. so the $70 - ($7 + $7) = $56 is how they have set it up. I suppose they figured that would complicate things further to have to apply a coupon in the same order each time for every customer. (if the discount % were varied) |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-29 : 14:19:44
|
| See my above, it should work for your needs. I had to change an ID that I did not copy over correctly. |
|
|
|
D.ep
Starting Member
10 Posts |
Posted - 2010-01-29 : 14:28:55
|
| Thanks for your quick replies DP978!It seems looking at your code it is very specific to the example i have. (I am an SQL newb) Will it work on a database of 100+ tickets of varying TicketID's, discounts and line items? (i have to try it later, i don't have access to the said database right now) |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-29 : 15:04:53
|
| It will, you may have to write exceptions for nulls where there is not coupon, unless 0's are there already. |
|
|
|
D.ep
Starting Member
10 Posts |
Posted - 2010-01-29 : 15:09:39
|
| ohh man, if this works I'm so excited! i fought with this for days, "googleing" to no avail. No they are null values, so i will have to write an exception. but i can probably find out how to do that. Thank you very much!PS go bruins! from Canada, but always loved the bruins. |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-29 : 15:11:28
|
| You welcome.Haha, agreed! go Bruins! Although they are on like a 7 game losing streak right now... best of luck, if it fails for some reason (most likely nulls getting passed through the select) let us know and we can revise. |
|
|
|
D.ep
Starting Member
10 Posts |
Posted - 2010-01-29 : 20:41:09
|
Hey,I got the code giving me an output, it doesn't spit out any errors messages or anything but it doesn't create the "Final_Cost" column unless i remove the "SELECT * FROM @t" and then the values it gives me are like -4000+ (which i would assume is because i removed that line of code) so it seems only the first 2 select statements are working for me... I also double checked and it doesn't use NULL values, it actually puts in 0.00 so i shouldn't need to have exceptions.declare @t table(ID int, type varchar(20), Service Varchar(100), Discount_Perc float, Discount_Dollar float, Line_Total Float) insert @t SELECT tblTicketsRow.fldTicketID, tblTicketsRow.fldPS, tblTicketsRow.fldId, tblTicketsRow.fldDiscountPercent, tblTicketsRow.fldDiscountCurrency, tblTicketsRow.fldRowTotal FROM tblTicketsSummary INNER JOIN tblTicketsRow ON tblTicketsSummary.fldTicketID = tblTicketsRow.fldTicketID WHERE NOT (tblTicketsSummary.fldDateClosed IS NULL) SELECT * FROM @t SELECT *, Line_Total - (Line_Total * (Select Sum(b.Discount_Perc) FROM @t b where b.ID = a.ID)) AS Final_Cost FROM @t a Best regards,Craig |
|
|
|
D.ep
Starting Member
10 Posts |
Posted - 2010-01-30 : 02:47:30
|
Nevermind i added a UNION ALL and I get the "final_cost" now. and i just had to divide the sum of discount% by 100 because it was an int not a percent (ie , not 0.1 but 10) its working now!! DECLARE @t table(ID int, type varchar(20), Service Varchar(100), Discount_Perc float, Discount_Dollar float, Line_Total Float) INSERT @t SELECT tblTicketsRow.fldTicketID, tblTicketsRow.fldPS, tblTicketsRow.fldId, tblTicketsRow.fldDiscountPercent, tblTicketsRow.fldDiscountCurrency, tblTicketsRow.fldRowTotal FROM tblTicketsSummary INNER JOIN tblTicketsRow ON tblTicketsSummary.fldTicketID = tblTicketsRow.fldTicketID WHERE NOT (tblTicketsSummary.fldDateClosed IS NULL) AND (tblTicketsSummary.fldDateVoided IS NULL) AND (tblTicketsSummary.fldIndependentID IS NULL) GetUserDate('AND','tblTicketsSummary.fldDateClosed','') UNION ALL SELECT * FROM @t a SELECT *, Line_Total - (Line_Total * (Select Sum(b.Discount_Perc)/100 FROM @t b WHERE b.type = 'U' AND b.ID = a.ID)) AS Final_Cost FROM @t a WHERE Type = 'S'But i had another question. How would i also pull the Line_Totals into Final_Cost even if they didn't have a discount to apply?ID type Service Discount_Perc Discount_Dollar Line_Total Final_Cost1029 S 80069 0 0 38.24 01033 S 70001 0 0 45.49 0 Thanks, |
|
|
|
D.ep
Starting Member
10 Posts |
Posted - 2010-02-01 : 10:35:47
|
Still stumped, i assume its not grabbing the line_total and putting it into the final_cost when that ticket doesn't have a type 'U' coupon (b.type='U' AND b.ID = a.ID) as stated in the "where clause" but i couldn't get a CASE or IF statement to copy over each line_total when that happened. is that the wrong approach? SELECT *, Line_Total - (Line_Total * (Select Sum(b.Discount_Perc)/100 FROM @t b WHERE b.type = 'U' AND b.ID = a.ID)) AS Final_Cost FROM @t a WHERE Type = 'S' |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-02-01 : 12:24:33
|
SELECT *, Line_Total - (Line_Total * ISNULL((Select Sum(b.Discount_Perc)/100 FROM @t b WHERE b.type = 'U' AND b.ID = a.ID),0)) AS Final_Cost FROM @t a WHERE Type = 'S'Try thatIf that doesn't work doCase When (Line_Total - (Line_Total * (Select Sum(b.Discount_Perc)/100 FROM @t b WHERE b.type = 'U' AND b.ID = a.ID) = 0 then Line_Total Else (Line_Total - (Line_Total * (Select Sum(b.Discount_Perc)/100 FROM @t b WHERE b.type = 'U' AND b.ID = a.ID) |
|
|
|
D.ep
Starting Member
10 Posts |
Posted - 2010-02-02 : 10:42:40
|
| I used your first suggestion and everything works perfectly now. Thanks! |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-02-02 : 11:29:47
|
| Your welcome, glad we got it all to work for ya! |
|
|
|
|
Query calculation? help!