ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-02-05 : 13:43:19
|
Dear All,Need help with query.I am using three tables1.Users2.Subscriptions3.TransactionsRequirement:Generate a list of unique email addresses with the latest name, genderand age for a user with that emailThe selection criteria limits 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 yearanswer should be:Email Gender Age Namea@a.com m 31 robertb@b.com f 22 luluc@c.com f 08 kimd@d.com m 22 Jaye@e.com f 60 WillBelow is the DDL along with insert data scriptUSE [TEST_DB]GO/****** Object: Table [dbo].[SUBSCRIPTIONS] Script Date: 2/6/2013 12:07:33 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[SUBSCRIPTIONS]( [SUbscription_id] [int] IDENTITY(1,1) NOT NULL, [UserId] [int] NULL, [subscription_type] [varchar](100) NULL, [active_indicator] [varchar](3) NULL,PRIMARY KEY CLUSTERED ( [SUbscription_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [unique_UserId_subscription_type] UNIQUE NONCLUSTERED ( [UserId] ASC, [subscription_type] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGO/****** Object: Table [dbo].[TRANSACTIONS] Script Date: 2/6/2013 12:07:33 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[TRANSACTIONS]( [subscription_id] [int] NOT NULL, [action] [varchar](100) NOT NULL, [timestamp] [date] NOT NULL, CONSTRAINT [Transactions_PK] PRIMARY KEY CLUSTERED ( [subscription_id] ASC, [action] ASC, [timestamp] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGO/****** Object: Table [dbo].[USERS] Script Date: 2/6/2013 12:07:33 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[USERS]( [UserId] [int] IDENTITY(1,1) NOT NULL, [Email] [varchar](100) NULL, [Gender] [char](1) NULL, [Age] [int] NULL, [Name] [varchar](200) NULL,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]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[SUBSCRIPTIONS] WITH CHECK ADD FOREIGN KEY([UserId])REFERENCES [dbo].[USERS] ([UserId])GOALTER TABLE [dbo].[TRANSACTIONS] WITH CHECK ADD FOREIGN KEY([subscription_id])REFERENCES [dbo].[SUBSCRIPTIONS] ([SUbscription_id])GOUSE [TEST_DB]GOSET IDENTITY_INSERT [dbo].[USERS] ON GOINSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (1, N'a@a.com', N'm', 30, N'rob')GOINSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (2, N'a@a.com', N'm', 31, N'robert')GOINSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (3, N'b@b.com', N'f', 18, N'lucie')GOINSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (4, N'b@b.com', N'f', 22, N'lulu')GOINSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (5, N'c@c.com', N'm', 10, N'kim')GOINSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (6, N'c@c.com', N'f', 18, N'kim')GOINSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (7, N'c@c.com', N'f', 8, N'kim')GOINSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (8, N'd@d.com', N'f', 18, N'jj')GOINSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (9, N'd@d.com', N'm', 22, N'jay')GOINSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (10, N'e@e.com', N'f', 88, N'Gill')GOINSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (11, N'e@e.com', N'f', 88, N'Will')GOINSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (12, N'e@e.com', N'f', 60, N'Will')GOINSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (13, N'f@f.com', N'm', 70, N'George')GOSET IDENTITY_INSERT [dbo].[USERS] OFFGOSET IDENTITY_INSERT [dbo].[SUBSCRIPTIONS] ON GOINSERT [dbo].[SUBSCRIPTIONS] ([SUbscription_id], [UserId], [subscription_type], [active_indicator]) VALUES (1, 2, N'Magazine', N'Yes')GOINSERT [dbo].[SUBSCRIPTIONS] ([SUbscription_id], [UserId], [subscription_type], [active_indicator]) VALUES (2, 3, N'Music CD', N'No')GOINSERT [dbo].[SUBSCRIPTIONS] ([SUbscription_id], [UserId], [subscription_type], [active_indicator]) VALUES (3, 3, N'Magazine', N'Yes')GOINSERT [dbo].[SUBSCRIPTIONS] ([SUbscription_id], [UserId], [subscription_type], [active_indicator]) VALUES (4, 3, N'Video', N'Yes')GOINSERT [dbo].[SUBSCRIPTIONS] ([SUbscription_id], [UserId], [subscription_type], [active_indicator]) VALUES (5, 8, N'Magazine', N'Yes')GOINSERT [dbo].[SUBSCRIPTIONS] ([SUbscription_id], [UserId], [subscription_type], [active_indicator]) VALUES (6, 9, N'Video', N'Yes')GOINSERT [dbo].[SUBSCRIPTIONS] ([SUbscription_id], [UserId], [subscription_type], [active_indicator]) VALUES (7, 10, N'Magazine', N'No')GOINSERT [dbo].[SUBSCRIPTIONS] ([SUbscription_id], [UserId], [subscription_type], [active_indicator]) VALUES (8, 13, N'Magazine', N'Yes')GOSET IDENTITY_INSERT [dbo].[SUBSCRIPTIONS] OFFGOINSERT [dbo].[TRANSACTIONS] ([subscription_id], [action], [timestamp]) VALUES (1, N'Renewal', CAST(0xDE270B00 AS Date))GOINSERT [dbo].[TRANSACTIONS] ([subscription_id], [action], [timestamp]) VALUES (2, N'Cancellation', CAST(0x01270B00 AS Date))GOINSERT [dbo].[TRANSACTIONS] ([subscription_id], [action], [timestamp]) VALUES (2, N'Renewal', CAST(0xE2260B00 AS Date))GOINSERT [dbo].[TRANSACTIONS] ([subscription_id], [action], [timestamp]) VALUES (3, N'Renewal', CAST(0xC9270B00 AS Date))GOINSERT [dbo].[TRANSACTIONS] ([subscription_id], [action], [timestamp]) VALUES (4, N'Renewal', CAST(0xB6270B00 AS Date))GOINSERT [dbo].[TRANSACTIONS] ([subscription_id], [action], [timestamp]) VALUES (4, N'Renewal', CAST(0xD5270B00 AS Date))GOINSERT [dbo].[TRANSACTIONS] ([subscription_id], [action], [timestamp]) VALUES (5, N'Renewal', CAST(0xB6270B00 AS Date))GOINSERT [dbo].[TRANSACTIONS] ([subscription_id], [action], [timestamp]) VALUES (6, N'Renewal', CAST(0x68260B00 AS Date))GOINSERT [dbo].[TRANSACTIONS] ([subscription_id], [action], [timestamp]) VALUES (7, N'Cancellation', CAST(0xDE270B00 AS Date))GOINSERT [dbo].[TRANSACTIONS] ([subscription_id], [action], [timestamp]) VALUES (7, N'Renewal', CAST(0xD5270B00 AS Date))GOThanks,Javeed |
|