Solved

Stored Procedure with Columns coming from Criteria

Posted on 2007-03-29
7
209 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 42

Expert Comment

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

Author Comment

by:craskin
Comment Utility
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
Comment Utility
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
Comment Utility
ok, i think i got it working. thanks lowfat!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

772 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

10 Experts available now in Live!

Get 1:1 Help Now