Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Comparing Requests from previous year

Author  Topic 

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2013-04-18 : 12:20:36
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

52326 Posts

Posted - 2013-04-18 : 13:22:43
[code]
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)
[/code]

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

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2013-04-18 : 16:31:16
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

549 Posts

Posted - 2013-04-18 : 17:04:19
Is recieveddate in 2013 ?
If yes, would you be able to post some of your data.
Go to Top of Page
   

- Advertisement -