Solved

Stored Procedure with Columns coming from Criteria

Posted on 2007-03-29
7
222 Views
Last Modified: 2010-03-19
here's what i'm trying to do. i have a table that keeps track of dates for tasks on a object (a lot in this case) in the following way:

id    projectid   lotid    taskid      date
1       1              1           1       1/1/2006

what i need to do is create a stored procedure with only 4 columns where 2 of the columns come from criteria on the taskid. so say for instance i wanted to know all the dates entered on all lots for tasks 1 and 2 where those tasks are 2 of the columns

projectid   lotid       task1         task2
  1               1       1/1/2006  1/1/2007

how would i go about doing this? i actually need to do this for only those lots where task1 and task2 both have dates (neither can be null), so if that makes it easier (not having to catch records that don't exist), all the better.

0
Comment
Question by:craskin
7 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 18820968
I think you're looking for a crosstab.

DO you want to be able to add more columns... i.e. will it ever look like this:

projectid   lotid       task1         task2       task3         task5
  1               1       1/1/2006  1/1/2007   5/1/2007   6/1/2007

Does the column 'task1' indicate a record which taskid=1, and the column 'task2' indicate a record which taskid=2


If so you are after a crosstab.
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 18821815
don't think he's specifcally after a cross tab or pivot ...

isn't it more like

create  procedure usp_trackTasks (@Taskid1 int, @taskid2 int )
as
set nocount on

select *,identity(int,1,1) as rowid into #temp1 from yourtable where taskid=@taskid1
order by projectid,lotid,date
select *,identity(int,1,1) as rowid into #temp2 from yourtable where taskid=@taskid2
order by projectid,lotid,date

select coalesce(a.projectid,b.projectid) as projectid
         ,coalesce(a.lotid,b.lotid) as lotid
         ,a.date as task1
         ,b.date as task2
from #temp1 as a
full outer join #temp2 as b
  on a.projectid=b.projectid
 and a.lotid=b.lotid
 and a.rowid=b.rowid
order by 1,2,coalesce(a.rowid,b.rowid)
return
go
0
 
LVL 42

Expert Comment

by:dqmq
ID: 18821991
Or maybe it's as simple as this :>)

create  procedure usp_TaskPairs (@Taskid1 int, @taskid2 int )
as
set nocount on

SELECT T1.projectID, T1.lotID, T1.[Date] AS Task1, T2.[Date] AS Task2
FROM yourtable T1 INNER JOIN yourtable T2
ON T1.ProjectID = T2.ProjectID
AND T1.LotID = T2.LotID
WHERE T1.TaskID=@Taskid1
AND T2.TaskID=@Taskid2  
ORDER BY T1.projectid, T1.lotid, T1.
go
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 42

Expert Comment

by:dqmq
ID: 18821994
Got a little carried way. It should be:
ORDER BY T1.projectid, T1.lotid
0
 
LVL 12

Author Comment

by:craskin
ID: 18823885
task1 is the task where taskid = 1

i do not need all tasks as columns, only two of them. i'm trying lowfat's suggestion, but i'm getting the error

Cannot add identity column, using the SELECT INTO statement, to table '#temp1', which already has column 'ID' that inherits the identity property.
0
 
LVL 12

Author Comment

by:craskin
ID: 18823911
when i change the select *,identity to
select ProjectNumber,Lot,Date,identity(int,1,1) as rowid

i get the error

Invalid column name 'rowid'.
0
 
LVL 12

Author Comment

by:craskin
ID: 18823934
ok, i think i got it working. thanks lowfat!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now