Solved

help with adjusting the amount in sql and rerunnability of query

Posted on 2011-03-01
8
240 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:sqlcurious
  • 5
  • 2
8 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 35012312
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]

0
 

Author Comment

by:sqlcurious
ID: 35012421
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
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 35012715
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

0
 

Author Comment

by:sqlcurious
ID: 35040584
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
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35040641
Add a condition before inserting the data. So that you can skip the insert/update in the sub-sequent runs on same day.
 IF NOT EXISTS (SELECT 1 FROM datawarehouse.[dbo].[RptAged] WHERE RptDate = CONVERT(DATE,GETDATE())) 

Open in new window

Here is the complete code.
----DELETE datawarehouse.[dbo].[thistCybersource] where [DateandTime] >'2010-10-29 00:00:00.000' 


	TRUNCATE TABLE 
			datawarehouse.[dbo].[RptAged]
       IF NOT EXISTS (SELECT 1 FROM datawarehouse.[dbo].[RptAged] WHERE RptDate = CONVERT(DATE,GETDATE()))
       BEGIN

	/*Get all orders along with other attributes to create Balance report*/
	INSERT INTO 
				datawarehouse.[dbo].[RptAged]
				(
					[WebOrderID]
				  ,[FulfillmentID]
				  ,[MarketCode]
				  ,[OrderDate]
				  ,[Name]
				  ,[OrderQTY]
				  ,[GoodShipped]
				  ,[ShipFrt]
				  ,[Tax]
				  ,[TotalOrd]
				  ,[Status]
				 )                     
	SELECT		PO.TrackingNumber, ORF.BrightpointOrderNumber,
				ISNULL(CASE ORF.[RegionCodeShip]
						WHEN '1002' THEN '131'
						WHEN '1003' THEN '131'  
						WHEN '1004' THEN '131'
						WHEN '2001' THEN '161' 
						WHEN '3001' THEN '151' 
						WHEN '3002' THEN '152' 
						WHEN '3003'	THEN '153' 
						WHEN '3004' THEN '154' 
						WHEN '3005' THEN '154' 
						WHEN '4001' THEN '112' 
						WHEN '4002' THEN '113' 
						WHEN '4003' THEN '111' 
						WHEN '4004' THEN '111' 
						WHEN '5001' THEN '121' 
						WHEN '6001' THEN '141'
						WHEN '6002' THEN '141' 
						WHEN '9997' THEN '181' 
						WHEN '7001' THEN '171' 
						WHEN '8001' THEN '191'
						ELSE NULL 		END,CASE ORF.[RegionCode]
						WHEN '1002' THEN '131'
						WHEN '1003' THEN '131'  
						WHEN '1004' THEN '131'
						WHEN '2001' THEN '161' 
						WHEN '3001' THEN '151' 
						WHEN '3002' THEN '152' 
						WHEN '3003'	THEN '153' 
						WHEN '3004' THEN '154' 
						WHEN '3005' THEN '154' 
						WHEN '4001' THEN '112' 
						WHEN '4002' THEN '113' 
						WHEN '4003' THEN '111' 
						WHEN '4004' THEN '111' 
						WHEN '5001' THEN '121' 
						WHEN '6001' THEN '141'
						WHEN '6002' THEN '141' 
						WHEN '9997' THEN '181' 
						WHEN '7001' THEN '171' 
						WHEN '8001' THEN '191'
						ELSE NULL 		END) AS OracleCoCode,
						PO.Created, 
						CC.CustomerNameOnPayment,
						PO.LineItemCount,
						PO.SubTotal,
						PO.ShippingTotal,
						PO.TaxTotal, 
						PO.Total, PO.Status
	FROM				[172.20.16.51].metrocart_transactions.dbo.OrderForms ORF 
	INNER JOIN          [172.20.16.51].metrocart_transactions.dbo.PurchaseOrders  PO ON ORF.OrderGroupId = PO.OrderGroupId 
	LEFT JOIN           [172.20.16.51].metrocart_transactions.dbo.CreditCardPayments CC ON ORF.OrderFormId = CC.OrderFormId
	WHERE				CONVERT(nvarchar(8), ORF.Created,112) < CONVERT(nvarchar(8),GETDATE(),112) 
	


	/*Get Order that have transaction in cybersource, but not in DW*/
	INSERT INTO datawarehouse.[dbo].[RptAged]
				([WebOrderID],
				[Name],
				PmtCr,
				OrderDate)
	SELECT      [MerchantReferenceNumber],
				[FirstName]+' '+ [LastName],
				[Amount]*-1,
				[DateandTime]
	FROM		datawarehouse.[dbo].[thistCybersource]
	WHERE		[MerchantReferenceNumber] not like'1%' 
	AND			Applications 
	IN			('Credit Card Authorization(Accept),Credit Card Settlement(Accept),Decision Manager(Accept)',
				'Credit Card Settlement(Accept)')
	AND			[DateandTime]		<	GETDATE()-1
	AND			[MerchantReferenceNumber] 
	NOT IN		(SELECT		weborderid 
				 FROM		metrocart_datawarehouse.dbo.RptAged)
				 
	/*
	/*Get Order that have transaction in BillFloat */				 
	INSERT INTO datawarehouse.[dbo].[RptAged]
				([WebOrderID],
				[Name],
				PmtCr,
				OrderDate)
	SELECT      [MerchantReferenceNumber],
				[FirstName]+' '+ [LastName],
				[Amount]*-1,
				[DateandTime]
	FROM		datawarehouse[dbo].[thistBillFloat]
	WHERE		[MerchantReferenceNumber] 
				NOT IN		
				(SELECT		weborderid 
				 FROM		metrocart_datawarehouse.dbo.RptAged)
				 
*/
			 

	UPDATE dbo.RptAged
	SET MarketCode = '131',GoodShipped=0,ShipFrt=0,Tax=0,TotalOrd=0
	WHERE WebOrderID ='8252009'

	/*
	UPDATE OrderFormHeader
	SET OrderFormHeader.RegionCode =ORF.RegionCode
	FROM        OrderFormHeader INNER JOIN	OrderGroup ON OrderFormHeader.ordergroup_id = OrderGroup.ordergroup_id 
				inner join 172.20.16.51.metrocart_transactions.dbo.PurchaseOrders  PO on PO.TrackingNumber = OrderGroup.order_number
				inner join	[156625-SQLCLUS].metrocart_transactions.dbo.OrderForms ORF on ORF.OrderGroupId = PO.OrderGroupId 
	WHERE     (OrderFormHeader.RegionCode IS NULL) and ORF.RegionCode IS NOT NULL


	UPDATE OrderFormHeader
	SET OrderFormHeader.RegionCodeShip =ORF.RegionCodeShip
	FROM        OrderFormHeader INNER JOIN	OrderGroup ON OrderFormHeader.ordergroup_id = OrderGroup.ordergroup_id 
				inner join 172.20.16.51.metrocart_transactions.dbo.PurchaseOrders  PO on PO.TrackingNumber = OrderGroup.order_number
				inner join	[156625-SQLCLUS].metrocart_transactions.dbo.OrderForms ORF on ORF.OrderGroupId = PO.OrderGroupId 
	WHERE    (OrderFormHeader.RegionCodeShip IS NULL) and 
	ORF.RegionCodeShip IS NOT NULL

	SELECT *
	FROM  dbo.RptAged
	WHERE MarketCode is null*/


	DELETE	dbo.RptAged
	WHERE	WebOrderID like'1%' and   ({ fn LENGTH(WebOrderID) } = 5)


	DELETE	dbo.RptAged
	WHERE	WebOrderID <50000


	UPDATE  dbo.RptAged
	SET		[Name] =a.FirstName +' '+a.LastName
	FROM	dbo.RptAged 
	INNER JOIN (SELECT		OrderGroup.order_number,
							Address.FirstName,
							Address.LastName
				FROM		Address 
				INNER JOIN	OrderGroupAddresses 
				ON			Address.AddressID = OrderGroupAddresses.OrderGroupAddressesID 
				INNER JOIN  OrderGroup 
				ON			OrderGroupAddresses.OrderGroup_id = OrderGroup.ordergroup_id
				WHERE		Address.[AddressName]='Billing'
				GROUP BY	OrderGroup.order_number, Address.FirstName, Address.LastName) a 
	ON	 dbo.RptAged.WebOrderID=a.order_number

	UPDATE  dbo.RptAged
	set [Name]=upper([Name])


	/*Update CyberSource Credit Adjustment*/
	UPDATE		metrocart_datawarehouse.dbo.RptAged
	SET         Adj		=	b.Amount, 
				AdjDate =	b.AdjDate
	FROM		metrocart_datawarehouse.dbo.RptAged  
	INNER JOIN	(SELECT		[MerchantReferenceNumber]				as	WebOrderID,
							sum(thistCyberSource.Amount)			as	[Amount], 
							max(thistCyberSource.[DateandTime])	as	AdjDate
				FROM        metrocart_datawarehouse.dbo.thistCyberSource
				WHERE		(metrocart_datawarehouse.dbo.thistCyberSource.Applications = N'Credit Card Credit(Accept)')
				GROUP BY	[MerchantReferenceNumber])			b
	ON			b.WebOrderID	=	RptAged.WebOrderID


	/*Update CyberSource Payment Charge*/

	UPDATE		metrocart_datawarehouse.dbo.RptAged
	SET         Pmtcr	=	CS.TotalCharge*-1
	FROM		(SELECT [MerchantReferenceNumber],SUM(Amount) as TotalCharge
	FROM metrocart_datawarehouse.dbo.thistCyberSource 
	WHERE		thistCyberSource.Applications IN		('Credit Card Authorization(Accept),Credit Card Settlement(Accept),Decision Manager(Accept)',
				'Credit Card Settlement(Accept)')		
	GROUP BY [MerchantReferenceNumber]	) CS
	INNER JOIN	metrocart_datawarehouse.dbo.RptAged 
	ON			CS.[MerchantReferenceNumber]		=	RptAged.WebOrderID

	/*Update Adjustment detail transactions */
	UPDATE		datawarehouse.[dbo].RptAged
	SET         AdjGood			=	c.SubTotal, 
				AdjShipFrt		=	c.ShippingTotal, 
				AdjTax			=	c.TaxTotal,
				TransactionType =	c.TransactionType
	FROM		datawarehouse.[dbo].RptAged 
	INNER JOIN	(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 
						GROUP BY	[WebOrderID])										as	c 
	ON		c.WebOrderID	=	datawarehouse.[dbo].RptAged.WebOrderID
	
/********	 New Code Added */
	--Update Amounts for BillFloat
	UPDATE		datawarehouse.[dbo].RptAged
	SET			AdjGood			=	(BF.Amount * -1)
	FROM		datawarehouse.[dbo].RptAged AS RptA
	INNER JOIN	datawarehouse.[dbo].thistBillFloat AS BF
	ON			RptA.WebOrderID = BF.MerchantReferenceNumber



	/*Update BrightPoint Shipping Number and ShipDate from thistBPShipment*/
	UPDATE		datawarehouse.[dbo].RptAged
	SET			FulfillmentID		=	thistBPShipment.ShipNum, 
				ShipDate			=	thistBPShipment.ShipDate, 
				Status				=	N'Shipped'
	FROM        datawarehouse.[dbo].RptAged 
	INNER JOIN	datawarehouse.[dbo].thistBPShipment 
	ON			RptAged.WebOrderID	=	thistBPShipment.PONum
	WHERE	--	(RptAged.FulfillmentID IS NULL) AND		
	datawarehouse.[dbo].thistBPShipment.SKU		<>	'286104EU-RTNLBL'


	/*Update Status to Non-fulfillment when orders are older thatn 10 days*/
	UPDATE		datawarehouse.[dbo].[RptAged] 
	SET			[Status]			=		'Non-fulfillment' 
	WHERE		[FulfillmentID]		is		NULL 
	AND			[OrderDate]			<		GETDATE()-10


	/*Update NCC Adjustment*/
	UPDATE    RptAged
	SET             NCC_AdjTax = NCC.TTTaxAmount,
					NCC_AdjGood = NCC.TTamount, 
					NCC_AdjShipFrt = NCC.TTFrieght
	FROM         RptAged INNER JOIN
					   (SELECT WebOrderID, sum(TaxAmount)as TTTaxAmount, 
					   sum(Frieght) as TtFrieght,sum(amount) as TTAmount
					   FROM  thistNCCAdjustment
					   GROUP BY WebOrderID) NCC
					   ON RptAged.WebOrderID = NCC.WebOrderID


	SELECT		GETDATE()					as  RptDate,
				WebOrderID, 
				FulfillmentID, 
				MarketCode,
				OrderDate, 
				ShipDate, 
				[Name], 
				GoodShipped, 
				ShipFrt, 
				Tax, 
				TotalOrd, 
				AdjGood						as	[AdjGood], 
				AdjShipFrt					as	[AdjShipFrt], 
				AdjTax						as	[AdjTax], 
				(AdjGood+AdjShipFrt+AdjTax) as AdjTotal,
				NCC_AdjGood,
				NCC_AdjTax,
				NCC_AdjShipFrt,
				(NCC_Adjtax+NCC_AdjGood+NCC_AdjShipFrt) as [NCC_AdjTotal],
				PmtCr, 
				Adj, 
				([PmtCr] + [Adj])			as	[NetPmts], 
				Status, 
				convert(decimal(18,2),(GoodShipped+ShipFrt+Tax)+((AdjGood+AdjShipFrt+AdjTax))+ (Pmtcr+Adj)+(NCC_Adjtax+NCC_AdjGood+NCC_AdjShipFrt) ) as [BalanceAmount],
				CASE	convert(decimal(18,2),(GoodShipped+ShipFrt+Tax)+((AdjGood+AdjShipFrt+AdjTax))+ (Pmtcr+Adj)+(NCC_Adjtax+NCC_AdjGood+NCC_AdjShipFrt) )  
				WHEN	0 
				THEN	NULL 
				ELSE	datediff(day, OrderDate, getdate())  
				END		as	BalanceAge, 
				adjdate,
				TransactionType
	FROM        datawarehouse.[dbo].RptAged
	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
	ORDER BY	OrderDate

	SELECT		GETDATE()					as  RptDate,
				WebOrderID, 
				FulfillmentID, 
				MarketCode,
				OrderDate, 
				ShipDate, 
				[Name], 
				GoodShipped, 
				ShipFrt, 
				Tax, 
				TotalOrd, 
				AdjGood						as	[AdjGood], 
				AdjShipFrt					as	[AdjShipFrt], 
				AdjTax						as	[AdjTax], 
				(AdjGood+AdjShipFrt+AdjTax) as AdjTotal,
				NCC_AdjGood,
				NCC_AdjTax,
				NCC_AdjShipFrt,
				(NCC_Adjtax+NCC_AdjGood+NCC_AdjShipFrt) as [NCC_AdjTotal],
				PmtCr, 
				Adj, 
				([PmtCr] + [Adj])			as	[NetPmts], 
				Status, 
				convert(decimal(18,2),(GoodShipped+ShipFrt+Tax)+((AdjGood+AdjShipFrt+AdjTax))+ (Pmtcr+Adj)+(NCC_Adjtax+NCC_AdjGood+NCC_AdjShipFrt) ) as [BalanceAmount],
				CASE	convert(decimal(18,2),(GoodShipped+ShipFrt+Tax)+((AdjGood+AdjShipFrt+AdjTax))+ (Pmtcr+Adj)+(NCC_Adjtax+NCC_AdjGood+NCC_AdjShipFrt) )  
				WHEN	0 
				THEN	NULL 
				ELSE	datediff(day, OrderDate, getdate())  
				END		as	BalanceAge, 
				adjdate,
				TransactionType
	FROM        datawarehouse.[dbo].RptAged
	--where WebOrderID = 365824 
	--and	convert(decimal(18,2),(GoodShipped+ShipFrt+Tax)+((AdjGood+AdjShipFrt+AdjTax))+ (Pmtcr+Adj)+(NCC_Adjtax+NCC_AdjGood+NCC_AdjShipFrt) ) <>0
	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 NULL
	ORDER BY OrderDate
    END
 --select top 100* from datawarehouse.[dbo].RptAged

Open in new window

0
 

Author Comment

by:sqlcurious
ID: 35072531
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  
0
 

Author Comment

by:sqlcurious
ID: 35072784
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)
0
 

Author Closing Comment

by:sqlcurious
ID: 37540166
thanks!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now