SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Comparing Requests from previous year
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jim_jim
Constraint Violating Yak Guru

USA
306 Posts

Posted - 04/18/2013 :  12:20:36  Show Profile  Reply with Quote
Hi All
I'm trying to compare DraftRequests in Customer_request_drafts table that we create every yearover year for customer convenience to that of already submitted requests in 2013.The Requests already submitted are in the Customer_Request table.

We are doing this comparison so that if the customer has already submiited a new request for 2013 without using the draft that we create then we would like to remove the draft that was systematically generated as it is going to be a duplicate

The requests are compared by CustomerID,Reportnumber

example
if i give a draft requestid that was systematically generated to my query that is in the customer_request_drafts table then it should try to compare the requests out there in customer_request table The compariosn is by customerid and reportnumber feilds and limit the comparison only to 2013(receiveddate)and get the request in 2013 .If no match found then an indicator that no match found

How do i accomplish the above

Customer_request and customer_request_drafts tables are exact replicas.Below is the DDL

CREATE TABLE [dbo].[Customer_REQUEST](
[requestid] [numeric](18, 0) NOT NULL,
[receiveddate] [datetime] NULL,
[neededby] [datetime] NULL,
[billingerror] [char](1) NULL,
[billingerrtext] [varchar](255) NULL,
[reportnumber] [numeric](18, 0) NOT NULL,
[CustomerId] [varchar](25) NOT NULL


Thanks

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/18/2013 :  13:22:43  Show Profile  Reply with Quote

SELECT d.*,CASE WHEN r.CustomerId IS NULL THEN 'no match found' ELSE 'Match found' END
FROM Customer_request_drafts d
LEFT JOIN Customer_Request r
ON r.CustomerId = d.CustomerId
AND r.reportnumber = d.reportnumber
AND r.receiveddate > = DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

USA
306 Posts

Posted - 04/18/2013 :  16:31:16  Show Profile  Reply with Quote
Not getting the results right
Though there are requests with the same customerid and reportnumber in the customer_request table and customer_report_draft table the results is coming back as no match found

quote:
Originally posted by visakh16


SELECT d.*,CASE WHEN r.CustomerId IS NULL THEN 'no match found' ELSE 'Match found' END
FROM Customer_request_drafts d
LEFT JOIN Customer_Request r
ON r.CustomerId = d.CustomerId
AND r.reportnumber = d.reportnumber
AND r.receiveddate > = DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/18/2013 :  17:04:19  Show Profile  Reply with Quote
Is recieveddate in 2013 ?
If yes, would you be able to post some of your data.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.24 seconds. Powered By: Snitz Forums 2000