User list with first activated offer

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

General SQL Server Forums

New to SQL Server Programming

User list with first activated offer

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2013-11-27 : 23:37:56

Hi Friends,

I have 3 tables, please find the table structure and sample data below

Table 1 : tblUserInfo

UserID | FirstName | Email | JoinedDate

1 Testuser01 User01@User.com 10-10-2013
2 Testuser10 User10@User.com 11-10-2013
3 Testuser20 User10@User.com 11-10-2013

Table 2 : tblOffers

OfferID | OfferName | ExpiryDate

1 OfferSample1 15-10-2014
2 OfferSample2 15-9-2014
3 OfferSample3 10-07-2014

Table 3 :tblOfferActivated

ActivationID | UserID | OfferID | ActivationCode | ActivatedDate

1 2 3 ABC 11-11-2013
2 2 1 CEG 13-11-2013
3 3 1 JHG 18-11-2013
4 3 2 KIU 20-11-2013

Expected Output

I want to list out the users with the first activated offer details.The OfferName Should be based on the first activated date

UserID | FirstName | Email | JoinedDate | OfferID | OfferName |ActivatedDate | ActivationCode

1 Testuser01 User01@User.com 10-10-2013 Null Null Null Null
2 Testuser10 User10@User.com 11-10-2013 3 OfferSample2 11-11-2013 ABC
3 Testuser20 User10@User.com 11-10-2013 4 offerSample3 18-11-2013 JHG

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-28 : 01:10:51

SELECT ui.*, oa.ActivatedDate, oa.ActivationCode, o.OfferName, o.OfferID
FROM (SELECT ActivationID , UserID , OfferID , ActivationCode, ActivatedDate
, ROW_NUMBER() OVER(PARTITION by UserID ORDER BY ActivatedDate) RN
FROM tblOfferActivated ) oa
join tblUserInfo ui ON ui.UserID = oa.UserID
JOiN tblOffers o ON o.OfferID = oa.OfferID
WHERE RN=1

--
Chandu

dhinasql
Posting Yak Master

195 Posts

Posted - 2013-11-28 : 03:26:22

quote:
Originally posted by bandi

SELECT ui.*, oa.ActivatedDate, oa.ActivationCode, o.OfferName, o.OfferID
FROM (SELECT ActivationID , UserID , OfferID , ActivationCode, ActivatedDate
, ROW_NUMBER() OVER(PARTITION by UserID ORDER BY ActivatedDate) RN
FROM tblOfferActivated ) oa
join tblUserInfo ui ON ui.UserID = oa.UserID
JOiN tblOffers o ON o.OfferID = oa.OfferID
WHERE RN=1

--
Chandu

Hi Chandu,
Thank you for your Query, But my primary thing is I would like to list out all the users whether they have active offer or Not. When there is no Offer for the user i want to display 'Null' for OfferID | OfferName |ActivatedDate | ActivationCode

Example : Suppose user 'TestUser30' is there on the tblUserInfo, But dont have any activated offer on tblOfferActivated, Then my expected out put :

UserID | FirstName | Email | JoinedDate | OfferID | OfferName |ActivatedDate | ActivationCode

1 Testuser01 User01@User.com 10-10-2013 3 OfferSample1 15-11-2013 BAC
2 Testuser10 User10@User.com 11-10-2013 1 OfferSample3 11-11-2013 ABC
3 Testuser20 User20@User.com 11-10-2013 4 offerSample1 18-11-2013 JHG
4 TestUser30 User30@User.com 15-10-2013 Null Null Null Null

Let me know if you need more details in this regards.

dhinasql
Posting Yak Master

195 Posts

Posted - 2013-11-28 : 04:27:18

quote:
Originally posted by dhinasql

quote:
Originally posted by bandi

SELECT ui.*, oa.ActivatedDate, oa.ActivationCode, o.OfferName, o.OfferID
FROM (SELECT ActivationID , UserID , OfferID , ActivationCode, ActivatedDate
, ROW_NUMBER() OVER(PARTITION by UserID ORDER BY ActivatedDate) RN
FROM tblOfferActivated ) oa
join tblUserInfo ui ON ui.UserID = oa.UserID
JOiN tblOffers o ON o.OfferID = oa.OfferID
WHERE RN=1

--
Chandu

Hi Chandu,
Thank you for your Query, But my primary thing is I would like to list out all the users whether they have active offer or Not. When there is no Offer for the user i want to display 'Null' for OfferID | OfferName |ActivatedDate | ActivationCode

Example : Suppose user 'TestUser30' is there on the tblUserInfo, But dont have any activated offer on tblOfferActivated, Then my expected out put :

UserID | FirstName | Email | JoinedDate | OfferID | OfferName |ActivatedDate | ActivationCode

1 Testuser01 User01@User.com 10-10-2013 3 OfferSample1 15-11-2013 BAC
2 Testuser10 User10@User.com 11-10-2013 1 OfferSample3 11-11-2013 ABC
3 Testuser20 User20@User.com 11-10-2013 4 offerSample1 18-11-2013 JHG
4 TestUser30 User30@User.com 15-10-2013 Null Null Null Null

Let me know if you need more details in this regards.

Friends,

Let me know if you need more details in this regards.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-28 : 06:10:50

[code]
SELECT *
FROM tblUserInfo ui
OUTER APPLY (
SELECT TOP 1 o.OfferID,OfferName, ActivationCode,ActivatedDate
FROM tblOffers o
INNER JOIN tblOfferActivated oa
ON oa.OfferID = o.OfferID
WHERE oa.UserID = ui.UserID
ORDER BY ActivatedDate ASC
)off
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

dhinasql
Posting Yak Master

195 Posts

Posted - 2013-11-28 : 06:40:35

quote:

Originally posted by visakh16


SELECT *
FROM tblUserInfo ui
OUTER APPLY (
              SELECT TOP 1 o.OfferID,OfferName, ActivationCode,ActivatedDate
              FROM tblOffers o
              INNER JOIN tblOfferActivated oa
              ON oa.OfferID  = o.OfferID 
              WHERE oa.UserID = ui.UserID
              ORDER BY ActivatedDate ASC
             )off

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Thanks Visakh.

It works fine.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-28 : 07:01:34

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources