Credit Card expiration date checking

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

SQL Server 2005 Forums

Transact-SQL (2005)

Credit Card expiration date checking

Author

Topic

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2007-12-17 : 12:42:42

Hi friends,

I am new SQL developement ( am .Net developer). I need to implement a stored proc in sql 2005 on CC expiration task.

I have customer information and his CC exiration date.
By using expiration date (like '01/08'),

Conditions are:

1. If CC expiring within 30 or 31 days. Stored proc will write the Customer data in EmailNotification table with message type =1. (Customer will not get second notification until 14 days before expiring)

2. If CC expiring within 14 days. Stored proc will write the customer data in EmailNotification table with message type =2. (Customer will not get second notification until 11 days before expiring)

3. If CC expiring within 11 days. Stored proc will write the customer data in EmailNotification table with message type =3. (Customer will not get second notification until 2 days before expiring)

4. If CC expiring within 2 days. Stored proc will write the customer data in EmailNotification table with message type and update orderStatusID to On Hold CC Expired in order table.

Please letme basic atleast basic structure of stored proc code. It will greatful to me.

Thanks in advance

srikanth

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-17 : 13:18:45

suppose your main table is CreditCustomer and let structure be
(
CreditCustomerId PK IDENTITY(),
CreditCardId int,
CustomerID int,
ExpiryDate datetime
...)

now procedure:-

CREATE PROC CreditExpirationCheck
AS

INSERT INTO EmailNotify(CustomerID,CreditCardID,...,Message_Type)
SELECT CustomerID,
other cust deatils,
CreditCardID,
other fields....,
CASE
WHEN DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,cc.ExpiryDate),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0))<=31 AND
DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,cc.ExpiryDate),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0))>14
THEN 1
WHEN DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,cc.ExpiryDate),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0))<=14 AND DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,cc.ExpiryDate),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0))>11
THEN 2
WHEN DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,cc.ExpiryDate),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0))<=11 AND DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,cc.ExpiryDate),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0))>2
THEN 3
WHEN DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,cc.ExpiryDate),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0))<=2
THEN 4
END AS 'Msg queue'
FROM CreditCustomer cc
INNER JOIN Customer c
ON c.CustomerID=cc.CustomerID
WHERE DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,cc.ExpiryDate),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0)) <=31

INSERT INTO Orders
SELECT CustomerID,
CreditCardID,
...,
'On Hold CC Expired '
FROM EmailNotify en
INNER JOIN CreditCustomer cc
ON cc.CustomerID=en.CustomerID
AND cc.CreditCardID=en.CreditCardID
WHERE en.Message_Type=4

GO

hope this will provide you a stub to work on...

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2007-12-17 : 14:08:55

Hi,

Thanks for you reply,

I am sending my table list.

1.

TABLE [dbo].[Order](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [int] NOT NULL,
[CustomerCode] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateCreated] [datetime] NOT NULL,
[DateOrdered] [datetime] NOT NULL,
[OrderTypeID] [int] NULL,
[OrderStatusID] [int] NULL,
[LastProcessDate] [datetime] NULL,

2. CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[CustomerCode] AS ([dbo].[udf_FormatCode]('RS',[CustomerID])),
[Email] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FirstName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MiddleName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateCreated] [datetime] NOT NULL,
[DateModified] [datetime] NOT NULL,
[MembershipTypeID] [int] NULL

3. CREATE TABLE [dbo].[Payment](
[PaymentID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NOT NULL,
[OrderPaymentType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PaymentType] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreditCardNumber] [varbinary](max) NULL,
[Last4DigitsOfCreditCardNumber] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ExpirationDate] [varbinary](max) NULL,
[CVV2] [varbinary](max) NULL,
[Amount] [numeric](18, 2) NOT NULL
-------------------------
I need to retrive

[CustomerID] from Order Where [OrderStatusID] =1, [OrderStatusID] Not = 7
[FirstName],[LastName],[Email] From Customer Table Where [CustomerID] =Order.[CustomerID]
[ExpirationDate] from Payment Where [OrderID] = order.[OrderID]

After that I have the my previous post Condtions with [ExpirationDate]

Actually my job schedule run daily.So people should not get 2 times in a peroid (like in between 31 to 14 days). Please make a stored proc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-18 : 01:07:17

CREATE PROC CreditExpirationCheck
AS

CREATE TABLE #temp
(
CustomerID int,
FirstName varchar(20),
LastName varchar(20),
Email varchar(50),
ExpirationDate datetime
)

--get deatils of customers
INSERT INTO #temp
SELECT o.CustomerID,
c.FirstName,
c.LastName,
c.Email,
p.ExpirationDate
FROM Order o
INNER JOIN Customer c
ON c.CustomerID=o.CustomerID
INNER JOIN Payment p
ON p.OrderID=o.OrderID
WHERE o.OrderStatusID =1
OR o.OrderStatusID <> 7
(correponds to
Where [OrderStatusID] =1, [OrderStatusID] Not = 7 of your post i believe you wanted records that satisfy either of these)

--populate email notify table with correct msg type
INSERT INTO EmailNotify(CustomerID,
FirstName,
LastName,
Email,
Message_Type)
SELECT t.CustomerID,
t.FirstName,
t.LastName,
t.Email,
CASE
WHEN DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,t.ExpirationDate),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0))<=31 AND
DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,t.ExpirationDate),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0))>14
THEN 1
WHEN DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,t.ExpirationDate ),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0))<=14 AND DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,t.ExpirationDate),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0))>11
THEN 2
WHEN DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,t.ExpirationDate),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0))<=11 AND DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,t.ExpirationDate),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0))>2
THEN 3
WHEN DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,t.ExpirationDate),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0))<=2
THEN 4
END AS 'Msg queue'
FROM #temp t
LEFT OUTER JOIN EmailNotify en
ON en.CustomerID=t.CustomerID
AND DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,en.ExpiryDate),0), DATEADD(d,DATEDIFF(d,0,t.ExpiryDate),0)) > 31
WHERE en.CustomerID IS NULL
AND
DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,GETDATE()),0),DATEADD(d,DATEDIFF(d,0,cc.ExpiryDate),0)) <=31

UPDATE o
SET o.OrderStatusID={ID for status 'On Hold CC Expired'. Might be in Status table which you have not given}
FROM Orders o
INNER JOIN EmailNotify en
ON o.CustomerID=en.CustomerID
AND en.Message_Type=4

GO

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-18 : 01:24:49

you two need to learn the [code] tag.

elsasoft.org

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2007-12-18 : 10:41:42

Hi Visakh,

Thanks for your reply,

I have question for you

DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,t.ExpirationDate),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0))

What is it returns ? In my case t.ExpirationDate is '12/07' in nvarchar datatype? How Do I convert that to date?

And Also

My store proc will run daily night. So If one customer data insert in EmailNotification table on 1st day. It should not insert the same data on 2nd day.

If any new customer register on 2nd day and his CC expiring in the same month then his data has to insert on the table on that night.

Thanks in advance

Thanks
Srikanth

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-18 : 11:04:00

DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,t.ExpirationDate),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0))
this gives difference in days between ExpirationDate and todays date.
i.e 12 days in case of 12/07
I have used DATEADD,DATEDIFF functions to strip off time part if you have any in ur fields. But this requires both values to be of datetime type. If its nvarchar then use CONVERT() to first cast it to datetime type and then use inside this. please refer to books online for usage example.

LEFT OUTER JOIN EmailNotify en
ON en.CustomerID=t.CustomerID
AND DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,en.ExpiryDate),0), DATEADD(d,DATEDIFF(d,0,t.ExpiryDate),0)) > 31
WHERE en.CustomerID IS NULL

this will ensure the same customer wont be picked until 31 days (period end) after 1st pick up.
Also all of new customers who satisfy the conditions Where [OrderStatusID] =1, [OrderStatusID] Not = 7
in order table will be picked up automatically each night.

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2007-12-18 : 11:18:31

I am sorry,

Same question I am asking you. '12/07' is the 'mm/yy'. I tried to convert(datetime,'12/07). its giving error.

this will ensure the same customer wont be picked until 31 days (period end) after 1st pick up.
Also all of new customers who satisfy the conditions Where [OrderStatusID] =1, [OrderStatusID] Not = 7
in order table will be picked up automatically each night.

Customer should not pickedup from 31 to 14 days.
1. 14th day again we have to insert same customer data with messagetype =2. (one time in between 14 to 11)

2. 11th day again we have to insert same customer data with messagetype =2. (one time in between 11 to 2)

3. 2nd day again we have to insert same customer data with messagetype =2. (one time in between 2 to 1) and set OrderstatusID=7 (CC -OnHold)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-18 : 11:36:00

Are you not storing dd part of date at all?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-18 : 11:44:37

As CC expiry dates are usually the beginning of the month, just use:


set dateformat dmy
select DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,'01/' + t.ExpirationDate),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0))

You will have to put the dateformat part at the beginning of the code to ensure you get the correct result though as otherwise the results of the datediff can be a bit screwy.

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2007-12-18 : 11:47:26

Yes, I am not storing dd part at all. I have only mm/yy only in my expiration date.

quote:
Originally posted by visakh16

Are you not storing dd part of date at all?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-18 : 12:04:12

Append 01/ to your nvarchar fields as suggested by RickD.put set dateformat dmy also on top.
Also change the join condition as follows:-

LEFT OUTER JOIN EmailNotify en
ON en.CustomerID=t.CustomerID
WHERE en.CustomerID IS NULL
OR (DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,'01/'+en.ExpiryDate),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0)) = 14
OR (DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,'01/'+en.ExpiryDate),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0)) = 11
OR (DATEDIFF(d,DATEADD(d,DATEDIFF(d,0,'01/'+en.ExpiryDate),0), DATEADD(d,DATEDIFF(d,0,GETDATE()),0)) =2

this ensures the Customers will be put in EmailNotify only if they are not already put (when 31 days to expire) or already put but 14/11/2 days remaining . Hope this is your requirement.

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2007-12-18 : 15:45:59

Thank you to visakh16,RickD,jezemine.

My task got gr8 success.

Thank you very much every one

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources