|
ahmeds08
Constraint Violating Yak Guru
India
423 Posts |
 Posted - 02/05/2013 : 13:43:19
|
Dear All,
Need help with query.
I am using three tables
1.Users
2.Subscriptions
3.Transactions
Requirement:
Generate a list of unique email addresses with the latest name, gender
and age for a user with that email
The selection criteria limits the list to users which never subscribed to
anything; or;
users with inactive subscriptions; or;
users with active subscriptions that renewed between Sep 1st and Sep
30th of any year
answer should be:
Email Gender Age Name
a@a.com m 31 robert
b@b.com f 22 lulu
c@c.com f 08 kim
d@d.com m 22 Jay
e@e.com f 60 Will
Below is the DDL along with insert data script
USE [TEST_DB]
GO
/****** Object: Table [dbo].[SUBSCRIPTIONS] Script Date: 2/6/2013 12:07:33 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE 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]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[TRANSACTIONS] Script Date: 2/6/2013 12:07:33 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE 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]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[USERS] Script Date: 2/6/2013 12:07:33 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE 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]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SUBSCRIPTIONS] WITH CHECK ADD FOREIGN KEY([UserId])
REFERENCES [dbo].[USERS] ([UserId])
GO
ALTER TABLE [dbo].[TRANSACTIONS] WITH CHECK ADD FOREIGN KEY([subscription_id])
REFERENCES [dbo].[SUBSCRIPTIONS] ([SUbscription_id])
GO
USE [TEST_DB]
GO
SET IDENTITY_INSERT [dbo].[USERS] ON
GO
INSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (1, N'a@a.com', N'm', 30, N'rob')
GO
INSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (2, N'a@a.com', N'm', 31, N'robert')
GO
INSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (3, N'b@b.com', N'f', 18, N'lucie')
GO
INSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (4, N'b@b.com', N'f', 22, N'lulu')
GO
INSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (5, N'c@c.com', N'm', 10, N'kim')
GO
INSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (6, N'c@c.com', N'f', 18, N'kim')
GO
INSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (7, N'c@c.com', N'f', 8, N'kim')
GO
INSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (8, N'd@d.com', N'f', 18, N'jj')
GO
INSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (9, N'd@d.com', N'm', 22, N'jay')
GO
INSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (10, N'e@e.com', N'f', 88, N'Gill')
GO
INSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (11, N'e@e.com', N'f', 88, N'Will')
GO
INSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (12, N'e@e.com', N'f', 60, N'Will')
GO
INSERT [dbo].[USERS] ([UserId], [Email], [Gender], [Age], [Name]) VALUES (13, N'f@f.com', N'm', 70, N'George')
GO
SET IDENTITY_INSERT [dbo].[USERS] OFF
GO
SET IDENTITY_INSERT [dbo].[SUBSCRIPTIONS] ON
GO
INSERT [dbo].[SUBSCRIPTIONS] ([SUbscription_id], [UserId], [subscription_type], [active_indicator]) VALUES (1, 2, N'Magazine', N'Yes')
GO
INSERT [dbo].[SUBSCRIPTIONS] ([SUbscription_id], [UserId], [subscription_type], [active_indicator]) VALUES (2, 3, N'Music CD', N'No')
GO
INSERT [dbo].[SUBSCRIPTIONS] ([SUbscription_id], [UserId], [subscription_type], [active_indicator]) VALUES (3, 3, N'Magazine', N'Yes')
GO
INSERT [dbo].[SUBSCRIPTIONS] ([SUbscription_id], [UserId], [subscription_type], [active_indicator]) VALUES (4, 3, N'Video', N'Yes')
GO
INSERT [dbo].[SUBSCRIPTIONS] ([SUbscription_id], [UserId], [subscription_type], [active_indicator]) VALUES (5, 8, N'Magazine', N'Yes')
GO
INSERT [dbo].[SUBSCRIPTIONS] ([SUbscription_id], [UserId], [subscription_type], [active_indicator]) VALUES (6, 9, N'Video', N'Yes')
GO
INSERT [dbo].[SUBSCRIPTIONS] ([SUbscription_id], [UserId], [subscription_type], [active_indicator]) VALUES (7, 10, N'Magazine', N'No')
GO
INSERT [dbo].[SUBSCRIPTIONS] ([SUbscription_id], [UserId], [subscription_type], [active_indicator]) VALUES (8, 13, N'Magazine', N'Yes')
GO
SET IDENTITY_INSERT [dbo].[SUBSCRIPTIONS] OFF
GO
INSERT [dbo].[TRANSACTIONS] ([subscription_id], [action], [timestamp]) VALUES (1, N'Renewal', CAST(0xDE270B00 AS Date))
GO
INSERT [dbo].[TRANSACTIONS] ([subscription_id], [action], [timestamp]) VALUES (2, N'Cancellation', CAST(0x01270B00 AS Date))
GO
INSERT [dbo].[TRANSACTIONS] ([subscription_id], [action], [timestamp]) VALUES (2, N'Renewal', CAST(0xE2260B00 AS Date))
GO
INSERT [dbo].[TRANSACTIONS] ([subscription_id], [action], [timestamp]) VALUES (3, N'Renewal', CAST(0xC9270B00 AS Date))
GO
INSERT [dbo].[TRANSACTIONS] ([subscription_id], [action], [timestamp]) VALUES (4, N'Renewal', CAST(0xB6270B00 AS Date))
GO
INSERT [dbo].[TRANSACTIONS] ([subscription_id], [action], [timestamp]) VALUES (4, N'Renewal', CAST(0xD5270B00 AS Date))
GO
INSERT [dbo].[TRANSACTIONS] ([subscription_id], [action], [timestamp]) VALUES (5, N'Renewal', CAST(0xB6270B00 AS Date))
GO
INSERT [dbo].[TRANSACTIONS] ([subscription_id], [action], [timestamp]) VALUES (6, N'Renewal', CAST(0x68260B00 AS Date))
GO
INSERT [dbo].[TRANSACTIONS] ([subscription_id], [action], [timestamp]) VALUES (7, N'Cancellation', CAST(0xDE270B00 AS Date))
GO
INSERT [dbo].[TRANSACTIONS] ([subscription_id], [action], [timestamp]) VALUES (7, N'Renewal', CAST(0xD5270B00 AS Date))
GO
Thanks,
Javeed
|
|
Query Help
)