Sql query help + - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Sql query help +

Author

Topic

drdre
Starting Member

3 Posts

Posted - 2011-03-15 : 15:55:12

hi all,

I have been out of the programming field for years and only recently i have started using sql and so forth. after a few days of reading and stuff i was able to get a sql query that i needed to work:

WITH Ranked ( Itemcode, rnk, ItemDesc )
AS ( SELECT Itemcode,
ROW_NUMBER() OVER( PARTITION BY Itemcode ORDER BY Itemcode ),
CAST( rssqldb.dbo.RSITEMDESCRIPTION.ItemDesc AS VARCHAR(8000) )
FROM rssqldb.dbo.RSITEM INNER JOIN
rssqldb.dbo.RSITEMDESCRIPTION ON rssqldb.dbo.RSITEM.ItemID = rssqldb.dbo.RSITEMDESCRIPTION.ItemID INNER JOIN
rssqldb.dbo.RSACCOUNT ON rssqldb.dbo.RSITEM.AccountID = rssqldb.dbo.RSACCOUNT.AccountID
Where rssqldb.dbo.RSACCOUNT.AccountID=63
And rssqldb.dbo.RSITEM.ObjectID=3),
AnchorRanked ( Itemcode, rnk, ItemDesc )
AS ( SELECT Itemcode, rnk, ItemDesc
FROM Ranked
WHERE rnk = 1 ),
RecurRanked ( Itemcode, rnk, ItemDesc )
AS ( SELECT Itemcode, rnk, ItemDesc
FROM AnchorRanked
UNION ALL
SELECT Ranked.Itemcode, Ranked.rnk,
RecurRanked.ItemDesc + ', ' + Ranked.ItemDesc
FROM Ranked
INNER JOIN RecurRanked
ON Ranked.Itemcode = RecurRanked.Itemcode
AND Ranked.rnk = RecurRanked.rnk + 1 )
SELECT Itemcode, SUBSTRING(MAX( ItemDesc ), CHARINDEX('DOCUMENT',MAX( ItemDesc COLLATE Latin1_General_CS_AS)), CHARINDEX(' PROJECT',MAX( ItemDesc COLLATE Latin1_General_CS_AS)))
FROM RecurRanked
GROUP BY Itemcode;

but now i realize that i want to pull(just to display the info) 2 more fields from the RSITEM table.. but i am really at a lost on where to them in.

For example one of this fields is called AlternateCode..

i am open to ideas.

With Jah Jah
anything is possible

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-15 : 17:51:03

open to ideas?

How about telling us what the base population you want is suppose to be

Seems to me that you've gone a built a rocket ship

Start simple and build

Out of the b'dness for years and you jump into CTE's?

I don't think so

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

drdre
Starting Member

3 Posts

Posted - 2011-03-15 : 18:17:18

Yeah i jumped into CTEs..

I started off with a small query... did some reading online and asked one or 2 of my friends some questions and got this far..

Didnt even know that CTE was a phrase till i was half way through that complex monster.

Basically we use an application at my office that users multiple lines to hold data for items that we scanned.. I have no idea why they did that.. but thats how it was setup.. took me 3 or more days of research and testing to get that query to function with many cups of coffee and alot of fowl words being through at my pc..

I am always open to ideas.. at present my base population of my db is over 150 items.. but i am working on it

With Jah Jah
anything is possible

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources