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.
| Author |
Topic |
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
 Posted - 2011-12-22 : 00:00:19
|
| HiTable USERScolumns:User_id (PK) (generated via a sequence)EmailGenderAgeNametable SUBSCRIPTIONScolumns:SUbscription_id (PK) (generated via a sequence)user_id (UK) (FK from users)subscription_type (UK)active_indicatortable TRANSACTIONScolumns:subscription_id (PK) (FK from subscriptions)action (PK)timestamp (PK)sample data:USERSuser_id email gender age name1 a@a.com m 30 rob2 a@a.com m 31 robert3 b@b.com f 18 lucie4 b@b.com f 22 lulu5 c@c.com m 10 kim6 c@c.com f 18 kim7 c@c.com f 08 kim8 d@d.com f 18 JJ9 d@d.com m 22 Jay10 e@e.com f 88 Bill11 e@e.com f 88 Will12 e@e.com f 60 Will13 f@f.com m 70 Georgesubscriptionssubscription_id user_id subsciption_type active_indicator1 2 Magazine Yes2 3 Music CD No3 3 Magazine Yes4 3 Video Yes5 8 Magazine Yes6 9 Video Yes7 10 Magazine No8 13 Magazine yestransactionssubscription_id action timestamp1 Renewal 2002-sep-102 Renewal 2002-Jan-012 Cancellation 2002-Feb-013 Renewal 2002-Aug-204 Renewal 2002-Aug-014 Renewal 2002-Sep-015 Renewal 2002-Aug-016 Renewal 2001-Sep-017 Renewal 2002-Sep-017 Cancellation 2002-Sep-10Based on query criteria:Generate a list of unique email addresses with the latest name, genderand age for a user with that emailThe selection criteria limit the list to users which never subscribed toanything; or;users with inactive subscriptions; or;users with active subscriptions that renewed between Sep 1st and sep30th of any yearHow do i return data answer that would: thanks a lota@a.com m 31 robertb@b.com f 22 luluc@c.com f 08 kimd@d.com m 22 Jaye@e.com f 60 Will |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-22 : 01:56:09
|
| ok...your assignment questions looks goodnow post us what you tried so far and issue faced and then we will help------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
Posted - 2011-12-22 : 08:59:13
|
| hiThis is the query with the following results, but i am not able to get the result as expected. thanks a lotSELECT DISTINCT dbo.Users.Email, dbo.Users.gender, dbo.Users.age, dbo.Users.nameFROM dbo.subsciption INNER JOIN dbo.transactions ON dbo.subsciption.subscription_id = dbo.transactions.subscription_id INNER JOIN dbo.Users ON dbo.subsciption.user_id = dbo.Users.UserIDWHERE (dbo.subsciption.active_indicator = 'No') OR (MONTH(dbo.transactions.timestamp) = 9)a@a.com m 31 robertb@b.com f 18 lucied@d.com m 22 Jaye@e.com f 88 Bill |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-22 : 10:58:58
|
| [code]SELECT u.email, u.gender, u.age, u.nameFROM users uOUTER APPLY (SELECT COUNT(*) AS cnt FROM subscriptions WHERE user_id =u.user_id AND active_indicator='Yes' )scr1OUTER APPLY (SELECT COUNT(*) AS cnt FROM subscriptions s INNER JOIN transactions t ON t.subscription_id = s.subscription_id WHERE s.userid=u.user_id AND s.active_indicator='Yes' AND t.action ='RENEWAL' AND MONTH(t.timestamp)<>9 )scr2WHERE COALESCE(scr1.cnt,0) = 0OR COALESCE(scr2.cnt,0) = 0[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
Query