Solved

Stored Procedure with Columns coming from Criteria

Posted on 2007-03-29
7
253 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

738 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