January 26, 2013 at 4:13 am
Hi,
To understand my problem, I have created two tables with their sample data for you guys:
CREATE TABLE [dbo].[tblAccount](
[AccountId] [int] IDENTITY(1,1) NOT NULL,
[AccountTitle] [varchar](50) NULL,
[AccountCreateDate] [datetime] NULL,
[AccountExpiryDate] [datetime] NULL,
CONSTRAINT [PK_tblAccount] PRIMARY KEY CLUSTERED
(
[AccountId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblAccount] ADD CONSTRAINT [DF_tblAccount_AccountCreateDate] DEFAULT (getdate()) FOR [AccountCreateDate]
GO
CREATE TABLE [dbo].[tblUser](
[UserId] [int] IDENTITY(1,1) NOT NULL,
[UserEmail] [varchar](50) NULL,
[AccountId] [int] NULL, -- FKey from tblAccount
[addedDatetime] [datetime] NULL,
[LastLogon] [datetime] NULL,
CONSTRAINT [PK_tblUser] PRIMARY KEY CLUSTERED
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblUser] ADD CONSTRAINT [DF_tblUser_UserCreateDate] DEFAULT (getdate()) FOR [addedDatetime]
GO
insert into tblAccount (AccountTitle, AccountCreateDate, AccountExpiryDate)
select 'AccountNo1', '2013-01-01 14:26:20.490', '2013-02-25 00:00:00.000'
union all
select 'AccountNo2', '2012-08-25 13:44:51.350', '2013-07-01 00:00:00.000'
union all
select 'AccountNo3', '2012-10-01 14:27:42.327', '2013-01-21 00:00:00.000'
--------
insert into tblUser (UserEmail, AccountId, addedDatetime, LastLogon)
select 'User1@Account1.com', 1, '2013-01-01 14:26:20.490', '2013-01-01 14:26:20.490'
union
select 'User2@Account1.com', 1, '2013-01-02 14:29:17.260', '2013-01-17 14:29:17.260'
union
select 'User3@Account1.com', 1, '2013-01-03 14:29:44.543', '2013-01-15 14:29:44.543'
union
select 'User1@Account2.com', 2, '2012-08-25 14:26:51.350', '2012-08-25 14:26:51.350'
union
select 'User2@Account2.com', 2, '2012-09-21 14:27:54.850', '2012-09-26 14:27:54.850'
union
select 'User3@Account2.com', 2, '2012-09-21 14:28:40.310', '2012-08-27 14:30:19.820'
union
select 'User1@Account3.com', 3, '2012-10-01 14:27:42.327', '2012-12-01 14:27:42.327'
union
select 'User2@Account3.com', 3, '2012-10-02 17:21:51.860', '2012-12-02 17:21:51.860'
union
select 'User3@Account3.com', 3, '2012-11-15 12:27:22.327', '2012-12-15 12:27:22.327'
There are two tables, (Account and User). Each user belongs to an account we have given to our client. We want to automatically notify customers that since how long number of days they are not using our system. The email should be sent on whole account bases not individual user bases. In our system there are lots of accounts which are not using it anymore and many new. Each customer account has an expiry date. We are writing a service or a job in sql which will run on daily bases but send email to customer/Account on weekly bases. Customer should get once email per week (These emails should be sent to all users incase their account is not used by any user), not daily but job runs day. If an account/customer has more than 7 days passed since no one has logged/used our system. we will send them email. similarly if a user has 14 days passed and they are not logged in we send them email with no of days they are not logged in and so on till the account get expired. I tried to make a query which prints number of days since an account has no activity but could not see how to make slices of days 7, 14, 21, 28, 35, 42, till max (Expirydate) and get only those account which has these number of days passed since no one user using our system.
-- this query will let you know the accounts which has more than 7, 14, 21, 28, 35, 42, 49, 56 days passed and but no user from their account has logon to our sysrtem ...
select max(Case when lastLogon is null then addedDatetime else lastLogon end) as LastLogin,
datediff(d, max(Case when lastLogon is null then addedDatetime else lastLogon end), Getdate()) as 'No. of days since user not login',
a1.* from tbluser u inner join (
select accountId from tblAccount where accountExpiryDate > GetDate()) a1 on a1.accountId = u.accountId
-- where t1.isActive = 1
group by a1.accountId
having datediff(d, max(Case when lastLogon is null then addedDatetime else lastLogon end), Getdate()) in (7, 14, 21, 28, 35, 42, 49, 56)
order by 1 desc
The hard coded values in above query (7, 14, 21, 35 etc. till max of an account expiry) should be dynamic to a maximum of a customers expiry date.
Please let me know if you understand my requirement and help me solve it.
The result should be, accountId, No. of days the account is not using system. i will get list of users and then send email etc. but the email requires no. of days if 7 or 14 or 21 etc.
Shamshad Ali
January 26, 2013 at 4:38 am
May be this solves my problem if it is correct way - (Performance wise/logic wise)?
select max(Case when lastLogon is null then addedDatetime else lastLogon end) as LastLogin,
datediff(d, max(Case when lastLogon is null then addedDatetime else lastLogon end), Getdate()) as 'No. of days since user not login',
a1.* from tbluser u inner join (
select accountId from tblAccount where accountExpiryDate > GetDate()) a1 on a1.accountId = u.accountId
-- where t1.isActive = 1
group by a1.accountId
having datediff(d, max(Case when lastLogon is null then addedDatetime else lastLogon end), Getdate()) in
(select number*7 from master.dbo.spt_values
where name is null and number between 1 and (select datediff(day, GetDate(), max(AccountExpiryDate))/7 as dt from tblaccount))
order by 1 desc
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply