Select statement and WHERE clause problem...
-
Hello everyone.
I need to solve what seems a very simple problem but, strangely enough, I cannot
assemble the correct SQL statement that would accomplish the task.
In few words: given the sample table and sample data reported below, I need to
select only those customers that have not been billed in the past 60 days or
that have never been billed.
I am probably missing something obvious, but I have been at this for several
hours and still cannot find a satisfactory, correct solution.
Any help is greatly, greatly appreciated.
Giorgio
-------------------------------------------------------------------------------------------
-- sample table + sample data
-------------------------------------------------------------------------------------------
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'Test_LastBilledTable' AND type = 'U')
DROP TABLE Test_LastBilledTable
GO
CREATE TABLE
Test_LastBilledTable
(
Customer_Id int NOT NULL,
Job_Id int NOT NULL CONSTRAINT PK_Test_LastBilledTable PRIMARY KEY (Job_Id),
Last_Billed_Date datetime NULL
)
GO
INSERT INTO Test_LastBilledTable VALUES (100, 1, '2005-01-01')
INSERT INTO Test_LastBilledTable VALUES (100, 2, '2005-06-01')
INSERT INTO Test_LastBilledTable VALUES (200, 3, '2005-01-01')
INSERT INTO Test_LastBilledTable VALUES (200, 4, NULL)
INSERT INTO Test_LastBilledTable VALUES (300, 5, '2005-06-01')
INSERT INTO Test_LastBilledTable VALUES (300, 6, NULL)
INSERT INTO Test_LastBilledTable VALUES (400, 7, NULL)
INSERT INTO Test_LastBilledTable VALUES (400, 8, NULL)
-------------------------------------------------------------------------------
-- SCENARIO
-------------------------------------------------------------------------------
-- Today's date is 2005-07-01
-- Objective: select customers that have not been billed in the past 60 days
-- or have never been billed
-- Given above sample data, the sql statement should return the following:
-- Customer 200
-- because it has 2 jobs, and one of the job has a last billed date of 2005-01-01
-- (over 60 days), and the other job has a null last billed date
-- customer 400
-- because has 2 jobs and both jobs have a null last billed date
-- (in other words, the customer has never been billed).
-- note how customer 100 must not be returned because it has 2 jobs
-- and one of the jobs has a last billed date of 2005-06-01, and that
-- date is not over 60 days old.
-- note how customer 300 must not be returned because it has 2 jobs
-- and even if the last billed date of job # 6 is null, the last billed date
-- of job # 5 is not over 60 days.
-
First of all 60 days is not the same as 2 months, so I presumed that you needed 2 months back in the query.
Here it goes :
Select DISTINCT Customer_ID from dbo.Test_LastBilledTable LBD where not exists (Select * from Test_LastBilledTable LBDE where LBD.Customer_ID = LBDE.Customer_ID and LBDE.Last_Billed_Date > Dateadd(MM, -2, '2005-07-01')) or not exists (Select * from Test_LastBilledTable LBDE where LBD.Customer_ID = LBDE.Customer_ID and LBDE.Last_Billed_Date is not null)
Not sure you need the distinct in there, test with or without with your full data to be sure.
-
SELECT DISTINCT Customer_IdFROM Test_LastBilledTable x
WHERE Customer_Id NOT IN ( SELECT Customer_Id FROM Test_LastBilledTable WHERE Last_Billed_Date > DATEADD(d, -60, GETDATE()) )
-
I knew I was missing something... Much better solution
. -
Remi and Chris -- thank you so much... that is a great solution... and by looking at your sample code I have learned something new and valuable.
Giorgio
-
Giorgio, no problem. Glad to hear that.
@ Remi: It sometimes helps to be slow.
-
I didn't see this one... It's not because I didn't take my time
.BTW my solution works
... just not the best solution (59% to 41% on the execution plan). -
59% to 41% on the execution plan
And if you add an index on (Customer_Id, Last_Billed_Date) you are at least in a little better shape: 53.55% against 46.45%
-
Here's a sick idea :
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'Test_LastBilledTable' AND type = 'U')
DROP TABLE Test_LastBilledTable
GO
CREATE TABLE [Test_LastBilledTable] (
[Customer_Id] [int] NOT NULL ,
[Last_Billed_Date] [datetime] NULL ,
[Job_Id] [int] NOT NULL ,
CONSTRAINT [IX_Test_LastBilledTable_Unique] UNIQUE NONCLUSTERED
(
[Job_Id]
) ON [PRIMARY] ,
CONSTRAINT [Pk_Test_LastBilledTable] UNIQUE CLUSTERED
(
[Customer_Id],
[Last_Billed_Date],
[Job_Id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO Test_LastBilledTable (Customer_Id, Job_Id, Last_Billed_Date) VALUES (100, 1, '2005-01-01')
INSERT INTO Test_LastBilledTable (Customer_Id, Job_Id, Last_Billed_Date) VALUES (100, 2, '2005-06-01')
INSERT INTO Test_LastBilledTable (Customer_Id, Job_Id, Last_Billed_Date) VALUES (200, 3, '2005-01-01')
INSERT INTO Test_LastBilledTable (Customer_Id, Job_Id, Last_Billed_Date) VALUES (200, 4, NULL)
INSERT INTO Test_LastBilledTable (Customer_Id, Job_Id, Last_Billed_Date) VALUES (300, 5, '2005-06-01')
INSERT INTO Test_LastBilledTable (Customer_Id, Job_Id, Last_Billed_Date) VALUES (300, 6, NULL)
INSERT INTO Test_LastBilledTable (Customer_Id, Job_Id, Last_Billed_Date) VALUES (400, 7, NULL)
INSERT INTO Test_LastBilledTable (Customer_Id, Job_Id, Last_Billed_Date) VALUES (400, 8, NULL)
Select DISTINCT Customer_ID from dbo.Test_LastBilledTable LBD where not exists (Select * from Test_LastBilledTable LBDE where LBD.Customer_ID = LBDE.Customer_ID and LBDE.Last_Billed_Date > Dateadd(MM, -2, '2005-07-01')) or not exists (Select * from Test_LastBilledTable LBDE where LBD.Customer_ID = LBDE.Customer_ID and LBDE.Last_Billed_Date is not null)
SELECT DISTINCT Customer_Id
FROM Test_LastBilledTable x
WHERE Customer_Id NOT IN ( SELECT Customer_Id FROM Test_LastBilledTable WHERE Last_Billed_Date > DATEADD(d, -60, GETDATE()) )
DROP TABLE Test_LastBilledTable
(53.62%, 46.28%)
-
Here's a sick idea
Not quite sure what you meant with this, other than that it came slightly closer. I also tried this in SQL Server 2005, and got results 62% vs 38%! Of course all of this is just for fun, since with real data the results could be completely different.
-
I was just talking about the strange use of clusered and non-clustered indexes. We rarely see it around here.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply