Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

help with adjusting the amount in sql and rerunnability of query

Posted on 2011-03-01
8
Medium Priority
?
261 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

618 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