Solved

Stored Procedure with Columns coming from Criteria

Posted on 2007-03-29
7
241 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

828 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