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.
Author |
Topic |
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2013-04-18 : 12:20:36
|
Hi AllI'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 duplicateThe requests are compared by CustomerID,Reportnumberexampleif 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 foundHow do i accomplish the aboveCustomer_request and customer_request_drafts tables are exact replicas.Below is the DDLCREATE 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 NULLThanks |
|
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' ENDFROM Customer_request_drafts dLEFT JOIN Customer_Request rON r.CustomerId = d.CustomerIdAND r.reportnumber = d.reportnumberAND r.receiveddate > = DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2013-04-18 : 16:31:16
|
Not getting the results rightThough 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 foundquote: Originally posted by visakh16
SELECT d.*,CASE WHEN r.CustomerId IS NULL THEN 'no match found' ELSE 'Match found' ENDFROM Customer_request_drafts dLEFT JOIN Customer_Request rON r.CustomerId = d.CustomerIdAND r.reportnumber = d.reportnumberAND r.receiveddate > = DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
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. |
|
|
|
|
|
|
|