help needed on tricky strored procedure

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

help needed on tricky strored procedure

Author

Topic

limericklad1974
Starting Member

39 Posts

Posted - 2012-05-08 : 07:45:12

Hi there
I have a shopping plan tool and it allows people to be assigned to go and find certain types of food e.g beef, apples, milk. The foods are broken down into types :
Meat: Beef, Ham, Turkey etc
Veg: Carrots, Sprouts, Cabbage etc
Fruit: Oranges, Apples, Pears etc
Drinks: Coffee, Milk, Water etc

I want to assign the foods to categories such as Primary Target, Secondary Target and Other Target.

There will be a person table where each person has an ID, and a Shopping Plan table which will store the date the shopping took place and which person did the shopping.

Each person can have a Primary target for Meat, Veg, Fruit and Drinks. They can also have a Secondary target for them and they can have multiple options for them in the Other Target category....

I need a stored procedure which shows me the latest shopping plan for each person. I want to pass in a variable for Category, and see a table of data which has the following columns:
Person
Date
Meat
Veg
Fruit
Drinks

e.g.

Person Date Meat Veg Fruit Drinks
Mary Hughes 5/5/12 Ham Carrots Oranges Coffee

Here is the data to create the tables if anyone wants to help!!!


GO
/****** Object:  Table [dbo].[Person]    Script Date: 05/08/2012 12:54:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Person](
	[ID] [int] NOT NULL,
	[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF







GO
/****** Object:  Table [dbo].[OptionsMappingLookup]    Script Date: 05/08/2012 12:54:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OptionsMappingLookup](
	[ID] [int] NOT NULL,
	[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_OptionsMappingLookup] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF





GO
/****** Object:  Table [dbo].[OptionsMapping]    Script Date: 05/08/2012 12:54:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OptionsMapping](
	[ID] [int] NOT NULL,
	[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[OMLID] [int] NULL,
 CONSTRAINT [PK_OptionsMapping] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF






GO
/****** Object:  Table [dbo].[ShoppingPlans]    Script Date: 05/08/2012 12:54:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ShoppingPlans](
	[ID] [int] NOT NULL,
	[PersonID] [int] NULL,
	[StartDate] [smalldatetime] NULL,
 CONSTRAINT [PK_ShoppingPlans] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]








GO
/****** Object:  Table [dbo].[FoodName]    Script Date: 05/08/2012 12:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FoodName](
	[ID] [int] NOT NULL,
	[FoodName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[FoodType] [int] NULL,
 CONSTRAINT [PK_FoodName] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF










GO
/****** Object:  Table [dbo].[ShoppingPlanFoodMapping]    Script Date: 05/08/2012 12:55:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ShoppingPlanFoodMapping](
	[ID] [int] NOT NULL,
	[ShoppingPlanID] [int] NULL,
	[FoodCategoryID] [int] NULL,
	[FoodTypeID] [int] NULL,
	[FoodNameID] [int] NULL,
 CONSTRAINT [PK_ShoppingPlanFoodMapping] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]



Here is the contents of the Person table
ID	FirstName	LastName
1	Mary	Hughes
2	John	Smith
3	Adam	Wells


Here is the contents of a table called OptionsMappingLookup
ID	Name
100	Food-Categories
101	Food-Types



Here is the contents of a table called OptionsMapping
ID	Name	               OMLID
1	Primary Target	        100
2	Secondary Target	100
3	Other Target	        100
4	Meat	                101
5	Veg	                101
6	Fruit	                101
7	Drinks	                101

Here is the contents of a table called ShoppingPlans
ID	PersonID	StartDate
1	1	01/05/2012
2	3	03/05/2012
3	1	05/05/2012



Here is the contents of a table called FoodName
ID	FoodName	FoodType
1	Beef	4
2	Ham	4
3	Turkey	4
4	Carrots	5
5	Sprouts	5
6	Cabbage	5
7	Turnips	5
8	Oranges	6
9	Apples	6
10	Pears	6
11	Tea	7
12	Coffee	7
13	Milk	7
14	Water	7


Here is the contents of a table called ShoppingPlanFoodMapping
ID	ShoppingPlanID	FoodCategoryID	FoodNameID	FoodTypeID
1	1	1	1	4
2	1	2	2	4
3	1	3	3	4
4	1	1	6	5
5	1	2	5	5
6	1	1	8	6
7	1	2	9	6
8	1	3	10	6
9	1	1	11	7
10	1	2	14	7
11	2	1	2	4
12	2	2	3	4
13	2	1	4	5
14	2	2	7	5
15	2	3	9	6
16	2	3	8	6
17	2	3	10	6
18	2	4	12	7
19	2	4	11	7
20	3	1	2	4
21	3	2	3	4
22	3	3	1	4
23	3	1	4	5
24	3	2	6	5
25	3	1	9	6
26	3	2	10	6
27	3	1	12	7
28	3	2	13	7
29	3	3	11	7

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-08 : 08:09:21

I would suggest not uploading an Excel file. For one thing, SQLTeam does not have a feature that allows uploading of files. Even if you were to upload to another site and post the link people are reluctant to open such files for security reasons.

You should simplify the description of your problem to a few rows of representative input data and figure out what the output data you would like to see for that input data is. Then, post the DDL for your tables along with that sample data. Brett's blog might be helpful: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

When you post insert your code and tabular data between a [code] and a [/code]. That will preserve spaces to make code and tabular data more readable.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 12:11:26

[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY COALESCE(p.FirstName + ' ','') + COALESCE(p.LastName,'') ORDER BY sp.StartDate DESC) AS Rn,
COALESCE(p.FirstName + ' ','') + COALESCE(p.LastName,'') AS Person,
sp.StartDate AS [Date],
MAX(CASE WHEN om.Name = 'Meat' THEN fn.FoodName END) AS Meat,
MAX(CASE WHEN om.Name = 'Veg' THEN fn.FoodName END) AS Veg,
MAX(CASE WHEN om.Name = 'Fruit' THEN fn.FoodName END) AS Fruit,
MAX(CASE WHEN om.Name = 'Drinks' THEN fn.FoodName END) AS Drinks
FROM ShoppingPlanFoodMapping spfm
INNER JOIN ShoppingPlans sp
ON sp.ID = spfm.ShoppingPlanID
INNER JOIN Person p
ON p.ID = sp.PersonID
INNER JOIN OptionsMapping om
ON om.ID = spfm.FoodTypeID
INNER JOIN OptionsMappingLookup oml
ON oml.ID = om.OMLID
INNER JOIN FoodName fn
ON fn.ID = spfm.FoodNameID
WHERE oml.Name = 'Food-Types'
GROUP BY COALESCE(p.FirstName + ' ','') + COALESCE(p.LastName,''),
sp.StartDate
)t
WHERE Rn=1
[/code]

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

limericklad1974
Starting Member

39 Posts

Posted - 2012-05-08 : 12:30:26

quote:

Originally posted by visakh16


SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY COALESCE(p.FirstName + ' ','') + COALESCE(p.LastName,'') ORDER BY sp.StartDate DESC) AS Rn,
COALESCE(p.FirstName + ' ','') + COALESCE(p.LastName,'') AS Person,
sp.StartDate AS [Date],
MAX(CASE WHEN om.Name = 'Meat' THEN fn.FoodName END) AS Meat,
MAX(CASE WHEN om.Name = 'Veg' THEN fn.FoodName END) AS Veg,
MAX(CASE WHEN om.Name = 'Fruit' THEN fn.FoodName END) AS Fruit,
MAX(CASE WHEN om.Name = 'Drinks' THEN fn.FoodName END) AS Drinks
FROM ShoppingPlanFoodMapping spfm
INNER JOIN ShoppingPlans sp
ON sp.ID = spfm.ShoppingPlanID
INNER JOIN Person p
ON p.ID = sp.PersonID
INNER JOIN OptionsMapping om
ON om.ID = spfm.FoodTypeID
INNER JOIN OptionsMappingLookup oml
ON oml.ID = om.OMLID
INNER JOIN FoodName fn
ON fn.ID = spfm.FoodNameID
WHERE oml.Name = 'Food-Types'
GROUP BY COALESCE(p.FirstName + ' ','') + COALESCE(p.LastName,''),
sp.StartDate 
)t
WHERE Rn=1

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

Hi there

Many thanks for trying to solve this query for me. However, when I run your query, I get the following results

1 Adam Wells 2012-05-03 Sprouts NULL NULL Sprouts
1 Mary Hughes 2012-05-05 Sprouts NULL Sprouts NULL

It should give me:

Mary Hughes 2012-05-05 Ham Carrots Apples Coffee
Adam Wells 2012-05-03 Ham Carrots NULL NULL

I need the code to find the latest Shopping plan for each person.
Then I need it to find the Primary Targets (which is ID 1 in the Options Mapping table, and using the Food CategoryID column in the ShoppingPlanFoodMapping table

Many thanks

D.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 12:40:04

[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY COALESCE(p.FirstName + ' ','') + COALESCE(p.LastName,'') ORDER BY sp.StartDate DESC) AS Rn,
COALESCE(p.FirstName + ' ','') + COALESCE(p.LastName,'') AS Person,
sp.StartDate AS [Date],
MAX(CASE WHEN om.Name = 'Meat' THEN fn.FoodName END) AS Meat,
MAX(CASE WHEN om.Name = 'Veg' THEN fn.FoodName END) AS Veg,
MAX(CASE WHEN om.Name = 'Fruit' THEN fn.FoodName END) AS Fruit,
MAX(CASE WHEN om.Name = 'Drinks' THEN fn.FoodName END) AS Drinks
FROM ShoppingPlanFoodMapping spfm
INNER JOIN ShoppingPlans sp
ON sp.ID = spfm.ShoppingPlanID
INNER JOIN Person p
ON p.ID = sp.PersonID
INNER JOIN OptionsMapping om
ON om.ID = spfm.FoodTypeID
INNER JOIN OptionsMappingLookup oml
ON oml.ID = om.OMLID
INNER JOIN FoodName fn
ON fn.ID = spfm.FoodNameID
INNER JOIN OptionsMapping om2
ON om2.ID = spfm.FoodCategoryID
WHERE oml.Name = 'Food-Types'
AND om2.Name = 'Primary Target'
GROUP BY COALESCE(p.FirstName + ' ','') + COALESCE(p.LastName,''),
sp.StartDate
)t
WHERE Rn=1

[/code]

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

limericklad1974
Starting Member

39 Posts

Posted - 2012-05-08 : 12:46:50

quote:

Originally posted by visakh16


SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY COALESCE(p.FirstName + ' ','') + COALESCE(p.LastName,'') ORDER BY sp.StartDate DESC) AS Rn,
COALESCE(p.FirstName + ' ','') + COALESCE(p.LastName,'') AS Person,
sp.StartDate AS [Date],
MAX(CASE WHEN om.Name = 'Meat' THEN fn.FoodName END) AS Meat,
MAX(CASE WHEN om.Name = 'Veg' THEN fn.FoodName END) AS Veg,
MAX(CASE WHEN om.Name = 'Fruit' THEN fn.FoodName END) AS Fruit,
MAX(CASE WHEN om.Name = 'Drinks' THEN fn.FoodName END) AS Drinks
FROM ShoppingPlanFoodMapping spfm
INNER JOIN ShoppingPlans sp
ON sp.ID = spfm.ShoppingPlanID
INNER JOIN Person p
ON p.ID = sp.PersonID
INNER JOIN OptionsMapping om
ON om.ID = spfm.FoodTypeID
INNER JOIN OptionsMappingLookup oml
ON oml.ID = om.OMLID
INNER JOIN FoodName fn
ON fn.ID = spfm.FoodNameID
INNER JOIN OptionsMapping om2
ON om2.ID = spfm.FoodCategoryID
WHERE oml.Name = 'Food-Types'
AND om2.Name = 'Primary Target'
GROUP BY COALESCE(p.FirstName + ' ','') + COALESCE(p.LastName,''),
sp.StartDate 
)t
WHERE Rn=1

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

Again, many thanks for trying but your code reveals the following results

1 Adam Wells 2012-05-03 Sprouts NULL NULL NULL
1 Mary Hughes 2012-05-05 Sprouts NULL NULL NULL

Unfortunately this is not correct

If you open the ShoppingPlanFoodMapping table, and restrict it to where foodcategoryid = 1 and shoppingplanid = 3, you will get 4 rows.
These are the primary targets for Mary Hughes shoping plan on 5/5/2012 and you will see the Food Type ID's are 2, 4, 9 and 12
2 = Ham
4 = Carrots
9 = Apples
12 = Coffee

limericklad1974
Starting Member

39 Posts

Posted - 2012-05-09 : 05:04:05

quote:
Originally posted by limericklad1974

quote:
Originally posted by visakh16

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY COALESCE(p.FirstName + ' ','') + COALESCE(p.LastName,'') ORDER BY sp.StartDate DESC) AS Rn,
COALESCE(p.FirstName + ' ','') + COALESCE(p.LastName,'') AS Person,
sp.StartDate AS [Date],
MAX(CASE WHEN om.Name = 'Meat' THEN fn.FoodName END) AS Meat,
MAX(CASE WHEN om.Name = 'Veg' THEN fn.FoodName END) AS Veg,
MAX(CASE WHEN om.Name = 'Fruit' THEN fn.FoodName END) AS Fruit,
MAX(CASE WHEN om.Name = 'Drinks' THEN fn.FoodName END) AS Drinks
FROM ShoppingPlanFoodMapping spfm
INNER JOIN ShoppingPlans sp
ON sp.ID = spfm.ShoppingPlanID
INNER JOIN Person p
ON p.ID = sp.PersonID
INNER JOIN OptionsMapping om
ON om.ID = spfm.FoodTypeID
INNER JOIN OptionsMappingLookup oml
ON oml.ID = om.OMLID
INNER JOIN FoodName fn
ON fn.ID = spfm.FoodNameID
INNER JOIN OptionsMapping om2
ON om2.ID = spfm.FoodCategoryID
WHERE oml.Name = 'Food-Types'
AND om2.Name = 'Primary Target'
GROUP BY COALESCE(p.FirstName + ' ','') + COALESCE(p.LastName,''),
sp.StartDate
)t
WHERE Rn=1

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

Again, many thanks for trying but your code reveals the following results

1 Adam Wells 2012-05-03 Sprouts NULL NULL NULL
1 Mary Hughes 2012-05-05 Sprouts NULL NULL NULL

Unfortunately this is not correct

If you open the ShoppingPlanFoodMapping table, and restrict it to where foodcategoryid = 1 and shoppingplanid = 3, you will get 4 rows.
These are the primary targets for Mary Hughes shoping plan on 5/5/2012 and you will see the Food Type ID's are 2, 4, 9 and 12
2 = Ham
4 = Carrots
9 = Apples
12 = Coffee

hi Visakh
Have you had a chanc eot look again at this, any further help would be greatly appreciated!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-09 : 22:01:20

i'm getting correct results. So either your data representation of scenario is not correct or you're not using query in correct way.

see full illustration beloe


GO
/****** Object:  Table [dbo].[Person]    Script Date: 05/08/2012 12:54:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Person](
	[ID] [int] NOT NULL,
	[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF







GO
/****** Object:  Table [dbo].[OptionsMappingLookup]    Script Date: 05/08/2012 12:54:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OptionsMappingLookup](
	[ID] [int] NOT NULL,
	[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_OptionsMappingLookup] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF





GO
/****** Object:  Table [dbo].[OptionsMapping]    Script Date: 05/08/2012 12:54:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OptionsMapping](
	[ID] [int] NOT NULL,
	[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[OMLID] [int] NULL,
 CONSTRAINT [PK_OptionsMapping] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF






GO
/****** Object:  Table [dbo].[ShoppingPlans]    Script Date: 05/08/2012 12:54:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ShoppingPlans](
	[ID] [int] NOT NULL,
	[PersonID] [int] NULL,
	[StartDate] [smalldatetime] NULL,
 CONSTRAINT [PK_ShoppingPlans] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]








GO
/****** Object:  Table [dbo].[FoodName]    Script Date: 05/08/2012 12:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FoodName](
	[ID] [int] NOT NULL,
	[FoodName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[FoodType] [int] NULL,
 CONSTRAINT [PK_FoodName] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF










GO
/****** Object:  Table [dbo].[ShoppingPlanFoodMapping]    Script Date: 05/08/2012 12:55:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ShoppingPlanFoodMapping](
	[ID] [int] NOT NULL,
	[ShoppingPlanID] [int] NULL,
	[FoodCategoryID] [int] NULL,
	[FoodTypeID] [int] NULL,
	[FoodNameID] [int] NULL,
 CONSTRAINT [PK_ShoppingPlanFoodMapping] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]



--Here is the contents of the Person table
INSERT INTO PErson(ID,	FirstName,	LastName)
VALUES(1,'Mary','Hughes'),
(2,	'John',	'Smith'),
(3,	'Adam',	'Wells')


--Here is the contents of a table called OptionsMappingLookup
INSERT INTO OptionsMappingLookup(ID,	Name)
VALUES(100,	'Food-Categories'),
(101	,'Food-Types')



--Here is the contents of a table called OptionsMapping
INSERT INTO OptionsMapping
(ID,	Name,          OMLID)
VALUES (1,	'Primary Target',        100),
(2,	'Secondary Target',	100),
(3,	'Other Target',	        100),
(4,	'Meat',	                101),
(5,	'Veg',	                101),
(6,	'Fruit',	                101),
(7,	'Drinks',	                101)

--Here is the contents of a table called ShoppingPlans
INSERT INTO ShoppingPlans
(ID,	PersonID,	StartDate)
VALUES(1,	1,	'01/05/2012'),
(2,	3,	'03/05/2012'),
(3,	1,	'05/05/2012')



--Here is the contents of a table called FoodName
INSERT INTO FoodName
(ID,	FoodName,	FoodType)
VALUES(1,	'Beef',	4),
(2,	'Ham',	4),
(3,	'Turkey',	4),
(4,	'Carrots',	5),
(5,	'Sprouts',	5),
(6,	'Cabbage',	5),
(7,	'Turnips',	5),
(8,	'Oranges',	6),
(9,	'Apples',	6),
(10,	'Pears',	6),
(11,	'Tea',	7),
(12,	'Coffee',	7),
(13,	'Milk',	7),
(14,	'Water',	7)


--Here is the contents of a table called ShoppingPlanFoodMapping
INSERT INTO ShoppingPlanFoodMapping
(ID,	ShoppingPlanID,	FoodCategoryID,	FoodNameID,	FoodTypeID)
VALUES(1,	1,	1,	1,	4),
(2,	1,	2	,2,	4),
(3,	1,	3,	3,	4),
(4,	1,	1,	6,	5),
(5,	1,	2,	5,	5),
(6,	1,	1,	8,	6),
(7,	1,	2,	9,	6),
(8,	1,	3,	10,	6),
(9,	1,	1,	11,	7),
(10,	1,	2,	14,	7),
(11,	2,	1,	2,	4),
(12,	2,	2,	3,	4),
(13,	2,	1,	4,	5),
(14,	2,	2,	7,	5),
(15,	2,	3,	9,	6),
(16,	2,	3,	8,	6),
(17,	2,	3,	10,	6),
(18,	2,	4,	12,	7),
(19,	2,	4,	11,	7),
(20,	3,	1,	2,	4),
(21,	3,	2,	3,	4),
(22,	3,	3,	1,	4),
(23,	3,	1,	4,	5),
(24,	3,	2,	6,	5),
(25,	3,	1,	9,	6),
(26,	3,	2,	10,	6),
(27,	3,	1,	12,	7),
(28,	3,	2,	13,	7),
(29,	3,	3,	11,	7)


SELECT Person,[Date],Meat,Veg,Fruit,Drinks
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY COALESCE(p.FirstName + ' ','') + COALESCE(p.LastName,'') ORDER BY sp.StartDate DESC) AS Rn,
COALESCE(p.FirstName + ' ','') + COALESCE(p.LastName,'') AS Person,
sp.StartDate AS [Date],
MAX(CASE WHEN om.Name = 'Meat' THEN fn.FoodName END) AS Meat,
MAX(CASE WHEN om.Name = 'Veg' THEN fn.FoodName END) AS Veg,
MAX(CASE WHEN om.Name = 'Fruit' THEN fn.FoodName END) AS Fruit,
MAX(CASE WHEN om.Name = 'Drinks' THEN fn.FoodName END) AS Drinks
FROM ShoppingPlanFoodMapping spfm
INNER JOIN ShoppingPlans sp
ON sp.ID = spfm.ShoppingPlanID
INNER JOIN Person p
ON p.ID = sp.PersonID
INNER JOIN OptionsMapping om
ON om.ID = spfm.FoodTypeID
INNER JOIN OptionsMappingLookup oml
ON oml.ID = om.OMLID
INNER JOIN FoodName fn
ON fn.ID = spfm.FoodNameID
INNER JOIN OptionsMapping om2
ON om2.ID = spfm.FoodCategoryID
WHERE oml.Name = 'Food-Types'
AND om2.Name = 'Primary Target'
GROUP BY COALESCE(p.FirstName + ' ','') + COALESCE(p.LastName,''),
sp.StartDate 
)t
WHERE Rn=1


ouput
-------------------------------------------------------------------------
Person	        Date	                Meat	Veg	Fruit	Drinks
-------------------------------------------------------------------------
Adam Wells	2012-03-05 00:00:00	Ham	Carrots	NULL	NULL
Mary Hughes	2012-05-05 00:00:00	Ham	Carrots	Apples	Coffee

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources