asked on

help with adjusting the amount in sql and rerunnability of query

Hi experts,
Pls find attached results.xlsx file and the sql file, this script is supposed to only run once but if I accidently ran twice or thrice the AdjTotal amount is doubled or tripled for the dates I run. I have highlighted the problem areas with yellow and imp columns are marked red.
I need help with modifying the query for reusability and also should fix the problem areas
now.
For ex: want to AdjTotal to be -9.74 instead of -19.48 for the first row and the Adj column should be corrected to 0
sql.txt
results.xlsx

derekkromm

If you just run this query in SSMS, does it return the same values as the spreadsheet or the correct values?

SELECT            [WebOrderID],
                                          sum([SubTotal])*-1                  as      [SubTotal],
                                          sum([ShippingTotal])*-1            as      [ShippingTotal],
                                          sum([TaxTotal])*-1                  as      [TaxTotal],
                                          (sum([SubTotal])+sum([ShippingTotal])+sum([TaxTotal]))*-1      as      [Total],
                                          min(TransCode)            as      [TransactionType]
                        FROM      (      SELECT
                                                      WebOrderID,
                                                      SubTotal,
                                                      ShippingTotal,
                                                      TaxTotal,
                                                      Total,
                                                      TransCode
                                    FROM            datawarehousedbo.tbACT_Adj_Trans)            as      d
WHERE WebOrderID = 312540
                                    GROUP BY      [WebOrderID]

sqlcurious

ASKER

yes same but fewer columns, please see result below:
WebOrderID SubTotal ShippingTotal TaxTotal Total TransactionType
312540 -18.00 0.00 -1.48 -19.48 8001

thanks

derekkromm

I'm guessing this returns a RecordCount of 2?

If so, do the other offending records have RecordCounts that correspond to the amount that its off? If so, you could do sum(<col>)*-1 / RecordCount as a workaround.

SELECT		[WebOrderID],
							sum([SubTotal])*-1			as	[SubTotal],
							sum([ShippingTotal])*-1		as	[ShippingTotal],
							sum([TaxTotal])*-1			as	[TaxTotal],
							(sum([SubTotal])+sum([ShippingTotal])+sum([TaxTotal]))*-1	as	[Total],
							min(TransCode)		as	[TransactionType],
							COUNT(*) as [RecordCount]
				FROM	(	SELECT	
									WebOrderID,
									SubTotal,
									ShippingTotal,
									TaxTotal,
									Total,
									TransCode
						FROM		datawarehousedbo.tbACT_Adj_Trans)		as	d 
						WHERE [WebOrderId] = 312540
						GROUP BY	[WebOrderID]

Open in new window

sqlcurious

ASKER

But if how can I add it in the final select statement as there is no group by, if I make the above suggested change way above the select statements for example find the attached query, it still doesnt give me the result. Please help
Thanks
RptAged-With-BillFloat-1.txt

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sqlcurious

ASKER

Thanks for the efforts but I am not getting the result I want.
The requirement was made a little easier for me. I would like to see the results avoiding the results for dates of column adjdate - 01/26/2011, 02/22/2011 , 01/31/2011
So, I am trying use this condition in the where clause at the end but the result set doesnt pick the null valued adjdate, see near -- bandage for bad data:

WHERE      convert(decimal(18,2),(GoodShipped+ShipFrt+Tax)+((AdjGood+AdjShipFrt+AdjTax))+ (Pmtcr+Adj)+(NCC_Adjtax+NCC_AdjGood+NCC_AdjShipFrt) ) <>0
      AND WebOrderID>50000
      AND Name is not NULL
      -- (Bandage for bad data)
      AND CONVERT(nvarchar(8),(adjdate),112) NOT IN (20110126, 20110222, 20110131)
      AND CONVERT(nvarchar(8),AdjDate,112) IS NULL
      ORDER BY      OrderDate

sqlcurious

ASKER

actually changed to and it worked:
AND (CONVERT(nvarchar(8),(adjdate),112) NOT IN (20110126, 20110222, 20110131) OR
CONVERT(nvarchar(8),AdjDate,112)IS NULL)

sqlcurious

ASKER

thanks!