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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Adding a new column to a view's result set

Author  Topic 

capella07
Starting Member

46 Posts
Posted - 2007-04-18 : 15:51:21
Hello, all

I'm working with a view where its result set has multiple dates/times for any given item.

The view's result set looks something like (the actual view has 8000+ rows):

Item Date DataPointID
It1 4/15/07 1
It1 4/03/07 2
It2 3/29/07 3
It3 4/10/07 4
It3 4/17/07 5
It3 4/18/07 6

I created a SELECT statement that found the latest date/time for every item in the view. Applying that SELECT to the above example, I'd get:

Item Date DataPointID
It1 4/15/07 1
It2 3/29/07 3
It3 4/18/07 6

The example result above is the latest date for each Item in the first table.

The code to get that latest date is:


SELECT item, MAX(detailtime) AS detailtime, datapointid
FROM ac_mappingexportdetails_test
WHERE datapointid IS NOT NULL
GROUP BY item, datapointid
ORDER BY datapointid

With me so far?

Now what I need to do is add a new column to the view's result set and in that column I want to somehow mark only those records that are found in the above-mentioned SELECT statement that finds the latest date.

How can I do this?

Thanks for anyone's help!

Capella07



=====================================
f u cn rd ths, u cn gt a gd jb n prgrmng

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-04-18 : 16:01:45
To get other columns, you need to use a derived table.


SELECT a.item, a.detailtime, a.datapointid, a.newcolumn
FROM ac_mappingexportdetails_test a
INNER JOIN
(
	SELECT item, MAX(detailtime) AS detailtime, datapointid
	FROM ac_mappingexportdetails_test
	WHERE datapointid IS NOT NULL
	GROUP BY item, datapointid
) t
ON a.item = t.item AND a.detailtime = t.detailtime AND a.datapointid = t.datapointid
ORDER BY a.datapointid


Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

capella07
Starting Member

46 Posts
Posted - 2007-04-18 : 16:21:39
Thanks for the quick reply, tkizer.

I just copy/pasted that into the Query Analyzer and realized that maybe I wasn't clear on a point.

You included in the first line: "a.newcolumn". When I "ran" that query in Query Analyzer, it correctly complained, "Invalid column name 'newcolumn'." I guess you assumed a.newcolumn was just a column currently in the view. This column that I'm adding, doesn't currently exist. I need to add it to the view's result set as a new column, "marking", as I said in my first post each column found in the query I posted.

I hope that clarifies things!

=====================================
f u cn rd ths, u cn gt a gd jb n prgrmng
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-04-18 : 16:25:48
quote:
Originally posted by capella07

....I want to somehow mark only those records that are found in the above-mentioned SELECT statement that finds the latest date.


Can you explain more what you mean by above statement?


************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-04-18 : 16:30:40
I guess I don't understand what you want then.

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2007-04-18 : 16:37:59
Building on Tara's example:
DECLARE @Table TABLE(Item VARCHAR(3), Date DATETIME, DataPointID INT)

INSERT @Table
SELECT 'It1', '4/15/07', 1 UNION ALL
SELECT 'It1', '4/03/07', 2 UNION ALL
SELECT 'It2', '3/29/07', 3 UNION ALL
SELECT 'It3', '4/10/07', 4 UNION ALL
SELECT 'It3', '4/17/07', 5 UNION ALL
SELECT 'It3', '4/18/07', 6

SELECT 
	a.Item, 
	a.Date, 
	a.DatapointID, 
	CASE 
		WHEN t.Item IS NULL THEN 0
		ELSE 1
	END AS NewColumn 
FROM 
	@Table a
LEFT OUTER JOIN
	(
		SELECT Item, MAX(Date) AS Date
		FROM @Table
		WHERE Date IS NOT NULL
		GROUP BY Item
	) t
	ON a.Item = t.Item 
	AND a.Date = t.Date
ORDER BY 
	a.DatapointID

-Ryan
Go to Top of Page

capella07
Starting Member

46 Posts
Posted - 2007-04-18 : 16:46:04
Yeah, sorry. Your confusion is definitely understandable - took me a while to get a grip on what I needed to do myself!

Lemme try this again...

Heck, I'll just post the stinkin view's code!

CREATE VIEW dbo.ac_MappingExportDetails_test
AS
SELECT     s.SeasonID AS SeasonID, fld.ShortName AS Block, CASE WHEN l2s.L2SeasonID IS NOT NULL THEN substring(l2s.ExternalID1, 5, 20) END AS Planting, 
                      att.ShortName AS Type, ai.ShortName AS Item, ass.ShortName AS Scale, 
                      CASE ass.ScaleValueType WHEN 1 THEN 'Number' WHEN 2 THEN 'Text' WHEN 3 THEN 'Date' ELSE '' END AS ValueType, 
                      trd.AttributeScaleNumberValue AS NumberValue, trd.AttributeScaleTextValue AS TextValue, trd.AttributeScaleDateValue AS DateValue, 
                      trd.DetailComment, trd.DetailTime, 0.0 AS Latitude, 0.0 AS Longitude, 0 AS DataPointId
FROM         dbo.work_TaskRecDetail trd INNER JOIN
                      dbo.work_TaskRec tr ON tr.TaskRecID = trd.TaskRecID INNER JOIN
                      dbo.work_Task t ON t .TaskID = tr.TaskID INNER JOIN
                      dbo.locn_Season s ON s.SeasonID = t .SeasonID INNER JOIN
                      dbo.locn_L1PDUnit fld ON fld.L1PDUnitID = s.L1PDUnitID INNER JOIN
                      admn_AttributeType att ON att.AttributeTypeID = trd.AttributeTypeID INNER JOIN
                      admn_AttributeItem ai ON ai.AttributeItemID = trd.AttributeItemID INNER JOIN
                      admn_AttributeScale ass ON ass.AttributeScaleID = trd.AttributeScaleID LEFT JOIN
                      locn_L2PDUnit l2u ON l2u.L1PDUnitID = fld.L1PDUnitID LEFT JOIN
                      locn_L2Season l2s ON l2s.L2PDUnitID = l2u.L2PDUnitID
UNION
SELECT     s.SeasonID, substring(fld.ExternalID1, 6, 20) AS Contract, CASE WHEN l2s.L2SeasonID IS NOT NULL THEN substring(l2s.ExternalID1, 5, 20) 
                      END AS Planting, att.ShortName AS Type, ai.ShortName AS Item, ass.ShortName AS Scale, 
                      CASE ass.ScaleValueType WHEN 1 THEN 'Number' WHEN 2 THEN 'Text' WHEN 3 THEN 'Date' ELSE '' END AS ValueType, 
                      trod.AttributeScaleNumberValue AS NumberValue, trod.AttributeScaleTextValue AS TextValue, trod.AttributeScaleDateValue AS DateValue, 
                      trod.DetailComment, trod.DetailTime, tro.Latitude, tro.Longitude, DataPointID
FROM         work_TaskRecObservationDetail trod INNER JOIN
                      work_TaskRecObservation tro ON tro.TaskRecObservationID = trod.TaskRecObservationID INNER JOIN
                      work_TaskRec tr ON tr.TaskRecID = tro.TaskRecID INNER JOIN
                      work_Task t ON t .TaskID = tr.TaskID INNER JOIN
                      locn_Season s ON s.SeasonID = t .SeasonID INNER JOIN
                      locn_L1PDUnit fld ON fld.L1PDUnitID = s.L1PDUnitID INNER JOIN
                      admn_AttributeType att ON att.AttributeTypeID = trod.AttributeTypeID INNER JOIN
                      admn_AttributeItem ai ON ai.AttributeItemID = trod.AttributeItemID INNER JOIN
                      admn_AttributeScale ass ON ass.AttributeScaleID = trod.AttributeScaleID LEFT JOIN
                      locn_L2PDUnit l2u ON l2u.L1PDUnitID = fld.L1PDUnitID LEFT JOIN
                      locn_L2Season l2s ON l2s.L2PDUnitID = l2u.L2PDUnitID


What this does is generate a table that has, among others, the following columns: Item, DetailTime, DataPointID. The view's result set is almost 9000 rows big.

What I had to do first was figure out how to find the most recent date for each Item. I did that (separately for now in query analyzer) with this query: 

SELECT item, MAX(detailtime) AS detailtime, datapointid
FROM ac_mappingexportdetails_test
WHERE datapointid IS NOT NULL
GROUP BY item, datapointid
ORDER BY datapointid

Took a while to get, but works like a charm. I get the latest date for each Item in the view's result set.

Now, what I need to do (and what the main task is) I need to somehow integrate the second query (that found the latest date) into the view's query, add a new column called IsMostRecent and for each row that the second query finds in the view, set the value in the IsMostRecent column to true.

I hope that clarifies things.

Thanks for your help!

Capella07

=====================================
f u cn rd ths, u cn gt a gd jb n prgrmng
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-04-18 : 16:51:37
Something like this you mean:


SELECT item, MAX(detailtime) AS detailtime, datapointid, IsMostRecent = 'True'
FROM ac_mappingexportdetails_test
WHERE datapointid IS NOT NULL
GROUP BY item, datapointid
ORDER BY datapointid


the view returns around 9k rows. but you are filtering the view with your datapoingid IS NOT NULL (along with the group by) which returns only the rows that match..not sure if I still understand what you want..


************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2007-04-18 : 16:52:44
quote:
Originally posted by capella07

SELECT item, MAX(detailtime) AS detailtime, datapointid
FROM ac_mappingexportdetails_test
WHERE datapointid IS NOT NULL
GROUP BY item, datapointid
ORDER BY datapointid

Do you have some other sample data? That query does not return the sub set that you posted in the original post.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-04-18 : 16:54:37
If dinakar's solution doesn't work, please provide a data example that illustrates what you want. Showing us your view just complicates things right now as we can't seem to get a grasp on what the problem is. A data example that shows what your data looks like and a data example of what the result set should look like should help us help you.

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

capella07
Starting Member

46 Posts
Posted - 2007-04-18 : 16:56:01
Looks like I was writing while you posted.

Please see my last post that includes the view's query code.

Hopefully that explains sufficiently.

Thanks again, guys

=====================================
f u cn rd ths, u cn gt a gd jb n prgrmng
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-04-18 : 16:59:35
No that doesn't help as that's code not data. We need to see the expected result set that will be returned from your final query. We need to see data!

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2007-04-18 : 17:02:09
Sorry I could not resist.... :)
CREATE VIEW dbo.ac_MappingExportDetails_test
AS
SELECT 
	a.*,
	CASE 
		WHEN t.Item IS NULL THEN 0
		ELSE 1
	END AS NewColumn 
FROM
(
	SELECT     s.SeasonID AS SeasonID, fld.ShortName AS Block, CASE WHEN l2s.L2SeasonID IS NOT NULL THEN substring(l2s.ExternalID1, 5, 20) END AS Planting, 
						  att.ShortName AS Type, ai.ShortName AS Item, ass.ShortName AS Scale, 
						  CASE ass.ScaleValueType WHEN 1 THEN 'Number' WHEN 2 THEN 'Text' WHEN 3 THEN 'Date' ELSE '' END AS ValueType, 
						  trd.AttributeScaleNumberValue AS NumberValue, trd.AttributeScaleTextValue AS TextValue, trd.AttributeScaleDateValue AS DateValue, 
						  trd.DetailComment, trd.DetailTime, 0.0 AS Latitude, 0.0 AS Longitude, 0 AS DataPointId
	FROM         dbo.work_TaskRecDetail trd INNER JOIN
						  dbo.work_TaskRec tr ON tr.TaskRecID = trd.TaskRecID INNER JOIN
						  dbo.work_Task t ON t .TaskID = tr.TaskID INNER JOIN
						  dbo.locn_Season s ON s.SeasonID = t .SeasonID INNER JOIN
						  dbo.locn_L1PDUnit fld ON fld.L1PDUnitID = s.L1PDUnitID INNER JOIN
						  admn_AttributeType att ON att.AttributeTypeID = trd.AttributeTypeID INNER JOIN
						  admn_AttributeItem ai ON ai.AttributeItemID = trd.AttributeItemID INNER JOIN
						  admn_AttributeScale ass ON ass.AttributeScaleID = trd.AttributeScaleID LEFT JOIN
						  locn_L2PDUnit l2u ON l2u.L1PDUnitID = fld.L1PDUnitID LEFT JOIN
						  locn_L2Season l2s ON l2s.L2PDUnitID = l2u.L2PDUnitID
	UNION
	SELECT     s.SeasonID, substring(fld.ExternalID1, 6, 20) AS Contract, CASE WHEN l2s.L2SeasonID IS NOT NULL THEN substring(l2s.ExternalID1, 5, 20) 
						  END AS Planting, att.ShortName AS Type, ai.ShortName AS Item, ass.ShortName AS Scale, 
						  CASE ass.ScaleValueType WHEN 1 THEN 'Number' WHEN 2 THEN 'Text' WHEN 3 THEN 'Date' ELSE '' END AS ValueType, 
						  trod.AttributeScaleNumberValue AS NumberValue, trod.AttributeScaleTextValue AS TextValue, trod.AttributeScaleDateValue AS DateValue, 
						  trod.DetailComment, trod.DetailTime, tro.Latitude, tro.Longitude, DataPointID
	FROM         work_TaskRecObservationDetail trod INNER JOIN
						  work_TaskRecObservation tro ON tro.TaskRecObservationID = trod.TaskRecObservationID INNER JOIN
						  work_TaskRec tr ON tr.TaskRecID = tro.TaskRecID INNER JOIN
						  work_Task t ON t .TaskID = tr.TaskID INNER JOIN
						  locn_Season s ON s.SeasonID = t .SeasonID INNER JOIN
						  locn_L1PDUnit fld ON fld.L1PDUnitID = s.L1PDUnitID INNER JOIN
						  admn_AttributeType att ON att.AttributeTypeID = trod.AttributeTypeID INNER JOIN
						  admn_AttributeItem ai ON ai.AttributeItemID = trod.AttributeItemID INNER JOIN
						  admn_AttributeScale ass ON ass.AttributeScaleID = trod.AttributeScaleID LEFT JOIN
						  locn_L2PDUnit l2u ON l2u.L1PDUnitID = fld.L1PDUnitID LEFT JOIN
						  locn_L2Season l2s ON l2s.L2PDUnitID = l2u.L2PDUnitID
) a
LEFT OUTER JOIN
	(
		SELECT Item, MAX(DetailTime) AS DetailTime
		FROM 
			(
				SELECT     s.SeasonID AS SeasonID, fld.ShortName AS Block, CASE WHEN l2s.L2SeasonID IS NOT NULL THEN substring(l2s.ExternalID1, 5, 20) END AS Planting, 
									  att.ShortName AS Type, ai.ShortName AS Item, ass.ShortName AS Scale, 
									  CASE ass.ScaleValueType WHEN 1 THEN 'Number' WHEN 2 THEN 'Text' WHEN 3 THEN 'Date' ELSE '' END AS ValueType, 
									  trd.AttributeScaleNumberValue AS NumberValue, trd.AttributeScaleTextValue AS TextValue, trd.AttributeScaleDateValue AS DateValue, 
									  trd.DetailComment, trd.DetailTime, 0.0 AS Latitude, 0.0 AS Longitude, 0 AS DataPointId
				FROM         dbo.work_TaskRecDetail trd INNER JOIN
									  dbo.work_TaskRec tr ON tr.TaskRecID = trd.TaskRecID INNER JOIN
									  dbo.work_Task t ON t .TaskID = tr.TaskID INNER JOIN
									  dbo.locn_Season s ON s.SeasonID = t .SeasonID INNER JOIN
									  dbo.locn_L1PDUnit fld ON fld.L1PDUnitID = s.L1PDUnitID INNER JOIN
									  admn_AttributeType att ON att.AttributeTypeID = trd.AttributeTypeID INNER JOIN
									  admn_AttributeItem ai ON ai.AttributeItemID = trd.AttributeItemID INNER JOIN
									  admn_AttributeScale ass ON ass.AttributeScaleID = trd.AttributeScaleID LEFT JOIN
									  locn_L2PDUnit l2u ON l2u.L1PDUnitID = fld.L1PDUnitID LEFT JOIN
									  locn_L2Season l2s ON l2s.L2PDUnitID = l2u.L2PDUnitID
				UNION
				SELECT     s.SeasonID, substring(fld.ExternalID1, 6, 20) AS Contract, CASE WHEN l2s.L2SeasonID IS NOT NULL THEN substring(l2s.ExternalID1, 5, 20) 
									  END AS Planting, att.ShortName AS Type, ai.ShortName AS Item, ass.ShortName AS Scale, 
									  CASE ass.ScaleValueType WHEN 1 THEN 'Number' WHEN 2 THEN 'Text' WHEN 3 THEN 'Date' ELSE '' END AS ValueType, 
									  trod.AttributeScaleNumberValue AS NumberValue, trod.AttributeScaleTextValue AS TextValue, trod.AttributeScaleDateValue AS DateValue, 
									  trod.DetailComment, trod.DetailTime, tro.Latitude, tro.Longitude, DataPointID
				FROM         work_TaskRecObservationDetail trod INNER JOIN
									  work_TaskRecObservation tro ON tro.TaskRecObservationID = trod.TaskRecObservationID INNER JOIN
									  work_TaskRec tr ON tr.TaskRecID = tro.TaskRecID INNER JOIN
									  work_Task t ON t .TaskID = tr.TaskID INNER JOIN
									  locn_Season s ON s.SeasonID = t .SeasonID INNER JOIN
									  locn_L1PDUnit fld ON fld.L1PDUnitID = s.L1PDUnitID INNER JOIN
									  admn_AttributeType att ON att.AttributeTypeID = trod.AttributeTypeID INNER JOIN
									  admn_AttributeItem ai ON ai.AttributeItemID = trod.AttributeItemID INNER JOIN
									  admn_AttributeScale ass ON ass.AttributeScaleID = trod.AttributeScaleID LEFT JOIN
									  locn_L2PDUnit l2u ON l2u.L1PDUnitID = fld.L1PDUnitID LEFT JOIN
									  locn_L2Season l2s ON l2s.L2PDUnitID = l2u.L2PDUnitID
			) b
		WHERE DetailTime IS NOT NULL
		GROUP BY Item
	) t			
	ON a.Item = t.Item 
	AND a.DealTime= t.DealTime
Go to Top of Page

capella07
Starting Member

46 Posts
Posted - 2007-04-18 : 17:12:38
Okay, thanks for your patience and help...

I'll used the simplified example table in my first post:

The view has a result set that includes these columns
Item _Date__ DataPointID
Itm1 4/15/07 1
Itm1 4/03/07 2
Itm2 3/29/07 3
Itm3 4/10/07 4
Itm3 4/17/07 5
Itm3 4/18/07 6

The query I wrote finds each of the rows in that view's result set that has the most recent date for each distinct Item. So the query would "pick out" from the above example the rows with DataPointIDs of 1, 3, and 6.

So what I want the view's result set to look like is something like:
Item _Date__ DataPointID IsMostRecent
Itm1 4/15/07 1 1
Itm1 4/03/07 2 0
Itm2 3/29/07 3 1
Itm3 4/10/07 4 0
Itm3 4/17/07 5 0
Itm3 4/18/07 6 1

I italicized the right hand column to highlight the fact that that is the one I need added to the view. A 1, of course indicates a true state (that row has the most recent date for that Item).

Remember, the IsMostRecent isn't a column in any table in the database. I want to add it to the view's result set.

Boy, I hope that helps!

Thanks again, all.

Capella07

=====================================
f u cn rd ths, u cn gt a gd jb n prgrmng
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-04-18 : 17:46:05
Well it looks like Lamprey has understood exactly what you wanted since the beginning. Check out the code he posted.

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

capella07
Starting Member

46 Posts
Posted - 2007-04-18 : 17:56:40
Well, a copy/paste & run gives me this error:
Server: Msg 4506, Level 16, State 1, Procedure ac_MappingExportDetails_test, Line 4
Column names in each view or function must be unique. Column name 'Item' in view or function 'dbo.ac_MappingExportDetails_test' is specified more than once.

I'm guessing it's the "item" after the Left Outer Join line that is the offender, but I barely understand this anyway! (That's why I'm asking for help from you guys.)

Also, Lamprey's code doesn't include adding the new column that indicates the latest datetime row. (But looking at the post times, I posted after him, so he wouldn't have seen it yet anyway).

Capella07


=====================================
f u cn rd ths, u cn gt a gd jb n prgrmng
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2007-04-18 : 18:21:26
quote:
Originally posted by capella07

Well, a copy/paste & run gives me this error:
Server: Msg 4506, Level 16, State 1, Procedure ac_MappingExportDetails_test, Line 4
Column names in each view or function must be unique. Column name 'Item' in view or function 'dbo.ac_MappingExportDetails_test' is specified more than once.

I made a change to my big post, marked in red. That should solve your issue. I would suggest that you change the "a.*" to list out each column that you want your view to return. i.e.: a.Item, a.DatapointID, etc.
Go to Top of Page

capella07
Starting Member

46 Posts
Posted - 2007-04-19 : 10:32:01
Lamprey, you're my hero!

Thanks, man. I just had to make a few minor tweaks and viola! Done!

Thanks a million!

Capella07

=====================================
f u cn rd ths, u cn gt a gd jb n prgrmng
Go to Top of Page

jacintha
Starting Member

3 Posts
Posted - 2008-07-03 : 00:21:24
Capella
i just need ur help regarding adding a new column to the resultset where dat new coloumn is not a column in any of the tables

jessy
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site