Solved

SQL Query Syntax Difference

Posted on 2014-11-25
7
185 Views
Last Modified: 2014-11-26
Hello all,

I have the following.   I have a table called Customer that has the following data for example:

Customer
CustId        CustDate       CustOrdAmount      CustGrpAmount
1                 9/2/2014      1000.00                  900.00
1                 10/1/2014      800.00                  800.00
1                 11/2/2014      900.00                  100.00
2                  8/4/2014       990.00                  990.00
2                  9/5/2014       700.00                  800.00
I then have another temp table I will create that will have the following:

#TempCustomer
CustId         CustDate      CustOrdAmount      CustGrpAmount
1                 11/29/2014     800.00                     500.00
2                 10/30/2014     700.00                     800.00

What I need to do is check based on the CustId if the temp table CustOrdAmount delta is different OR the CustGrpAmount is different than the Max(CustDate) record in Customer for that CustId then Insert a new record into the Customer Table otherwise ignore the insert from the Temp table to the Customer table for that CustId.

So in this case above I would be comparing the 3rd record in Customer table CustId = 1 to the TempTable CustId = 1 and inserting a record into the Customer table.  For CustId = 2 I would be ignoring that insert they are the same both columns.
0
Comment
Question by:sbornstein2
7 Comments
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40464349
This should returns the records that you need insert:
WITH Cust_CTE (CustId, CustDate) 
AS (
     SELECT CustId, MAX(CustDate)
     FROM Customer
)
SELECT c.CustId, c.CustDate
FROM Cust_CTE c
INNER JOIN #TempCustomer tc ON tc.CustId=c.CustId
WHERE tc.CustOrdAmount <> c.CustOrdAmount OR tc.CustGrpAmount=c.CustGrpAmount

Open in new window

0
 
LVL 33

Expert Comment

by:ste5an
ID: 40464350
Delta? What delta?
0
 
LVL 32

Expert Comment

by:awking00
ID: 40464975
If you're using sql server 2008 or later, you can use merge.
merge customer as c
using #tempCustomer as t
on c.custid = t.custid and c.custordamount = t.custordamount and c.custgrpamount = t.custgrpamount
when not matched then
insert values (t.custid, t.custdate, t.custordamount, t.custgrpamount);
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:sbornstein2
ID: 40465411
I need to look at every record in the #TempCustomer then compare it against the max(custdate) record in Customer where CustId = the same CustID.   So the records I would be comparing for the 1st temp table record is:

#TempCustomer
 CustId         CustDate      CustOrdAmount      CustGrpAmount
 1                 11/29/2014     800.00                     500.00

Then getting the Max cust date record in Customer for that same CustId
Customer
 CustId        CustDate       CustOrdAmount      CustGrpAmount
 1                 11/2/2014      900.00                  100.00

Then if CustOrdAmount is different OR CustGrpAmount is different I want to insert the #TempCustomer table record into the Customer table.   In this case they are different so my Customer table would now look like this:

Customer
 CustId        CustDate       CustOrdAmount      CustGrpAmount
 1                 9/2/2014      1000.00                  900.00
 1                 10/1/2014      800.00                  800.00
 1                 11/2/2014      900.00                  100.00
 1                 11/29/2014    800.00                  500.00
 2                  8/4/2014       990.00                  990.00
 2                  9/5/2014       700.00                  800.00

Then for the 2nd record in the temp table I would be comparing:
#TempCustomer
 CustId         CustDate      CustOrdAmount      CustGrpAmount
 2                 10/30/2014     700.00                     800.00

Customer
 CustId        CustDate       CustOrdAmount      CustGrpAmount
 2                  9/5/2014       700.00                  800.00

In this case they are the same so I would not insert anything into Customer.

Then last scenario if the Temp table record did not have a matching CustId record at all in the Customer table I would insert that into the Customer table.
0
 
LVL 12

Accepted Solution

by:
Koen Van Wielink earned 500 total points
ID: 40466235
This should do the trick:

With CTE
(	CustId
,	CustDate
,	CustOrdAmount
,	CustGrpAmount
)

as
(
Select		CustId
		,	CustDate
		,	CustOrdAmount
		,	CustGrpAmount
from	(select		ROW_NUMBER() over (partition by custID order by CustDate desc) as 'RowNum'
				,	CustId
				,	CustDate
				,	CustOrdAmount
				,	CustGrpAmount
		From	customer) as a
where	a.RowNum = 1
)

-- Insert records with different amount
Insert into customer
select		t.CustId
		,	t.CustDate
		,	t.CustOrdAmount
		,	t.CustGrpAmount
from	#Tempcustomer t
		inner join CTE
			on t.CustId = CTE.CustId
where	(CTE.CustId = t.CustId
and		(CTE.CustOrdAmount <> t.CustOrdAmount
or		CTE.CustGrpAmount <> t.CustGrpAmount))

-- Insert records without custId in customer table
Insert into customer
select		t.CustId
		,	t.CustDate
		,	t.CustOrdAmount
		,	t.CustGrpAmount
from	#Tempcustomer t
where	not exists
		(select 1
		from	customer c
		where	t.CustId = c.CustId)

Open in new window


The CTE is populated with the record of the customer table containing the maximum order date for each customer.
The first insert statement joins that CTE on the temp table and selects only the orders where the customer ID is the same, but either the Grpamount or OrdAmount is different, and inserts those into the customer table.
The second insert statement handles the records in the temp table for which no customer ID exists in the customer table.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40466303
Did you test my script?
0
 

Author Closing Comment

by:sbornstein2
ID: 40466762
awesome thanks I ended up using the NOT EXISTS as well
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Solution for warm standby SQL server 20 40
email about the whoisactive result 7 27
Azure SQL Insert not working suddenly 11 22
ms sql last 8 weeks as columns 5 28
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

867 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

18 Experts available now in Live!

Get 1:1 Help Now