Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Stored Procedure with Columns coming from Criteria

Posted on 2007-03-29
7
Medium Priority
?
262 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 2000 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

636 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