We help IT Professionals succeed at work.

SQL Query Syntax Difference

sbornstein2
sbornstein2 asked
on
212 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.
Comment
Watch Question

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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

ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Delta? What delta?
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
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);

Author

Commented:
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.
Business Intelligence Specialist
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Did you test my script?

Author

Commented:
awesome thanks I ended up using the NOT EXISTS as well

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.