Solved

SQL Query Syntax Difference

Posted on 2014-11-25
7
191 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 48

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 13

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 48

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server How-To Show Notes In First Row of Results 4 31
SQL Server / Update DB? 22 38
Acces SQL Insert Problem 6 32
Need to find substring in SQL 5 15
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.
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

830 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