Monthly billing system - query to get list of defaulters who have not paid their dues by today

this is the customers table, column 'C_BillingDay' indicates on which day the bill are due monthly for example if value is 4 thn billing due date of that customer is 4rth of each month. he has to pay his dues on 4rth ..

CREATE TABLE [dbo].[Customers](
 [CID] [int] IDENTITY(1,1) NOT NULL,
 [C_Name] [varchar](50) NULL,
 [C_EmailID] [varchar](20) NULL,
 [C_MobileNo] [varchar](20) NULL,
 [C_PhoneNo] [varchar](20) NULL,
 [C_HomeAddress] [varchar](max) NULL,
 [C_ServiceArea] [int] NULL,
 [C_AccountStatus] [int] NULL,
 [C_IPAdress] [varchar](50) NULL,
 [C_MACAddress] [varchar](50) NULL,
 [C_Package] [int] NULL, 
 [C_BillingDay] [int] NULL,
 [Balance] [float] NULL, 
 [C_AccountCreated] [datetime] NULL,
 [C_AccountModified] [datetime] NULL,

payments' table:

TABLE [dbo].[Payments](
    [PID] [int] IDENTITY(1,1) NOT NULL,
    [CID] [int] NULL,
    [Amount] [int] NULL,
    [PaymentType] [int] NULL,
    [Details/Comments] [varchar](max) NULL,
    [DateTimeRecieved] [datetime] NULL,
    [DateTimeModified] [datetime] NULL,

please help me quering all the defaulters who have not paid their dues on time by today ...

i have been trying with similar functions like DATEADD(MONTH,-1,GETDATE()) but they are not giving desired results :S

select * from Customers,payments 
 where Payments.DateTimeRecieved 
NOT BETWEEN GETDATE() AND DATEADD(MONT开发者_如何转开发H,-1,GETDATE())

From the limited system information given, I suspect there would be two relevant processes.

The first a daily job which does an UPDATE on the [Balance] column for all records in [Customers] based on [C_Package] (perhaps this indicates a monthly fee stored elsewhere), [C_AccountStatus] (perhaps indicating whether they are active), [C_BillingDay], and todays date.

Once that is done all you need to do is

SELECT [CID] FROM [dbo].[Customers] WHERE [Balance] > 0

Hope this helps.

Edit ...

To select Customers who haven't made a payment (though the logic seems flawed):

SELECT c.[CID] 
  FROM [Customers] c
WHERE c.[CID] NOT IN 
  (SELECT p.[CID] FROM [Payments] p 
    WHERE p.[DateTimeRecieved] BETWEEN GETDATE() AND DATEADD(MONTH,-1,GETDATE()))

(There's probably a way more elegant query than that!)

What about checking if the balance is not 0 and that today is the date of their payment. Something like:

select * from customers, payments where customers.balance > 0 and todays_date >= billdate;

its a bit abstract because i dont really know what language we are dealing with or what form your dates are in. I am also making an assumption that they pay off their entire balance when they pay their dues