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
 Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ahmeds08
Aged Yak Warrior

India
576 Posts

Posted - 02/05/2013 :  13:43:19  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 02/06/2013 :  00:09:47  Show Profile  Reply with Quote
sounds a lot like homework to me. Sorry, we don't do homework (for free )


KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/06/2013 :  00:16:57  Show Profile  Reply with Quote
show us what you did so far and explain your problem . then we will help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

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