| Author |
 Topic  |
|
|
limericklad1974
Starting Member
39 Posts |
 Posted - 05/08/2012 : 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
|
Edited by - limericklad1974 on 05/08/2012 08:34:46
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 05/08/2012 : 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
India
47173 Posts |
Posted - 05/08/2012 : 12:11:26
|
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/
|
|
|
|
limericklad1974
Starting Member
39 Posts |
Posted - 05/08/2012 : 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
India
47173 Posts |
Posted - 05/08/2012 : 12:40:04
|
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/
|
|
|
|
limericklad1974
Starting Member
39 Posts |
Posted - 05/08/2012 : 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 - 05/09/2012 : 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!! |
Edited by - limericklad1974 on 05/09/2012 05:14:25 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 05/09/2012 : 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/
|
Edited by - visakh16 on 05/09/2012 22:01:42 |
|
|
| |
Topic |
|
|
|
help needed on tricky strored procedure
