SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 User list with first activated offer
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dhinasql
Posting Yak Master

194 Posts

Posted - 11/27/2013 :  23:37:56  Show Profile  Reply with Quote
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

Edited by - dhinasql on 11/28/2013 04:28:25

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 11/28/2013 :  01:10:51  Show Profile  Reply with Quote
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
Go to Top of Page

dhinasql
Posting Yak Master

194 Posts

Posted - 11/28/2013 :  03:26:22  Show Profile  Reply with Quote
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.
Go to Top of Page

dhinasql
Posting Yak Master

194 Posts

Posted - 11/28/2013 :  04:27:18  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/28/2013 :  06:10:50  Show Profile  Reply with Quote

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
Go to Top of Page

dhinasql
Posting Yak Master

194 Posts

Posted - 11/28/2013 :  06:40:35  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/28/2013 :  07:01:34  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000