Pass results from one select statement to another 1

[DebbieC]

I'm not sure how to do this but I have a database that has Incidents that have an IncidentID. There are usually multiple records for an IncidentID - they are seperate events for each Incident. I was searching for the records by the timestamp date but I wouldn't get all of the events for a particular Incident if there were some events prior to that. I am calling a view from a stored procedure.

For instance:

CREATE PROCEDURE [dbo].[stoSelectIncidentLog] 
@StartDate		datetime,
@EndDate		datetime
AS

SELECT * FROM vHDIncidentLog WHERE TimeStamp BETWEEN '10/2/2006' AND '10/2/2006' 
ORDER BY IncidentID, TimeStamp
GO

This is what I get:

IncidentID Date & Time Incident Status
1776 10/2/3006 11:56:50 PM Resolved
1781 10/2/2006 1:27:58 PM Resolved
1783 10/2/2006 4:42:35 AM Open
1783 10/2/2006 1:22:58 PM Resolved

The problem is IncidentID # 1776 was Opened on 9/27/2006 and there are 4 events for this Incident so the list is only partial for that Incident. The same for # 1781. I want to run a Select statement to get the IncidentID's in the date range and then pass the IncidentID's to the view to get all events for those Incidents.

Something like this:

SELECT Distinct IncidentID As Incidents FROM HelpDeskIncidents WHERE TimeStamp BETWEEN '10/2/2006' AND '10/2/2006'

And then call this:

SELECT * FROM vHDIncidentLog 
WHERE IncidentID = Incidents
ORDER BY IncidentID, TimeStamp

Can I run both of these in one stored procedure?


Thanks.

 

[gmmastros]

Try this...

[COLOR=blue]SELECT[/color] * 
[COLOR=blue]FROM[/color]   vHDIncidentLog 
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
         [COLOR=blue]SELECT[/color] [COLOR=#FF00FF]Distinct[/color] IncidentID [COLOR=blue]As[/color] Incidents 
         [COLOR=blue]FROM[/color]   HelpDeskIncidents 
         [COLOR=blue]WHERE[/color]  [COLOR=blue]TimeStamp[/color] BETWEEN [COLOR=red]'10/2/2006'[/color] AND [COLOR=red]'10/2/2006'[/color]
         ) [COLOR=blue]As[/color] A
         [COLOR=blue]On[/color] vHDIncidentLog.IncidentId = A.IncidentId
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] vHDIncidentLog.IncidentID, [COLOR=blue]TimeStamp[/color]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

You want to do a self join for this. SOmething like this query:

[COLOR=blue]select[/color] a.* [COLOR=blue]from[/color] vHDIncidentLog a
[COLOR=blue]inner[/color] [COLOR=blue]join[/color]
(
[COLOR=blue]select[/color] [COLOR=#FF00FF]distinct[/color] IncidentID
[COLOR=blue]from[/color] HelpDeskIncidents
[COLOR=blue]where[/color] [COLOR=blue]TimeStamp[/color] Between [COLOR=red]'10/2/2006'[/color] AND [COLOR=red]'10/2/2006'[/color]
) b
[COLOR=blue]on[/color] a.IncidentID = b.IncidentID

I am curious how you are getting anything between 10/2/06 and 10/2/06, but if that part is working , then so should this.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[DebbieC]

Thank you. It amazes me that someone can just look at the code and come up with an answer so quickly. Unfortunately it returns this error message:

Invalid column name 'IncidentId'.

I'm trying to figure out which one is the problem. In the meantime you might be able to find it quicker than me. It might also help to have the view code. Here it is:

SELECT     dbo.HelpDeskIncidents.IncidentID, dbo.HelpDeskIncidents.Title, dbo.HelpDeskIncidents.Attachment, dbo.HelpDeskIncidents.Archive, 
                      dbo.HelpDeskIncidentDetails.IncidentDetailID, dbo.HelpDeskIncidentDetails.Description, SUBSTRING(dbo.HelpDeskIncidentDetails.Description, 1, 255) 
                      AS Desc1, SUBSTRING(dbo.HelpDeskIncidentDetails.Description, 256, 510) AS Desc2, SUBSTRING(dbo.HelpDeskIncidentDetails.Description, 511, 765) 
                      AS Desc3, SUBSTRING(dbo.HelpDeskIncidentDetails.Description, 766, 1020) AS Desc4, SUBSTRING(dbo.HelpDeskIncidentDetails.Description, 1021, 
                      1275) AS Desc5, SUBSTRING(dbo.HelpDeskIncidentDetails.Description, 1276, 1530) AS Desc6, SUBSTRING(dbo.HelpDeskIncidentDetails.Description, 
                      1531, 1785) AS Desc7, SUBSTRING(dbo.HelpDeskIncidentDetails.Description, 1786, 2040) AS Desc8, 
                      SUBSTRING(dbo.HelpDeskIncidentDetails.Description, 2041, 2295) AS Desc9, SUBSTRING(dbo.HelpDeskIncidentDetails.Description, 2296, 2500) 
                      AS Desc10, dbo.HelpDeskIncidentDetails.PostedBy, dbo.HelpDeskIncidentDetails.DistType, dbo.HelpDeskIncidentDetails.TicketStatus, 
                      dbo.HelpDeskIncidentDetails.IncidentStatus, dbo.HelpDeskIncidentDetails.ETR, dbo.HelpDeskIncidentDetails.Importance, 
                      dbo.HelpDeskIncidentDetails.[TimeStamp], dbo.HelpDeskIncidentDetails.Summary
FROM         dbo.HelpDeskIncidents INNER JOIN
                      dbo.HelpDeskIncidentDetails ON dbo.HelpDeskIncidents.IncidentID = dbo.HelpDeskIncidentDetails.IncidentID

 

[DebbieC]

I got it working. I had to make a minor change:

SELECT * 
FROM   vHDIncidentLog 
       Inner Join (
         SELECT Distinct IncidentID As Incidents 
         FROM   HelpDeskIncidentDetails 
         WHERE  TimeStamp BETWEEN '1/2/2003' AND '10/2/2004'
         ) As A
         On vHDIncidentLog.IncidentId = Incidents
         --On vHDIncidentLog.IncidentId = A.IncidentId
--ORDER BY vHDIncidentLog.IncidentID, TimeStamp
ORDER BY vHDIncidentLog.IncidentID, TimeStamp

Thank you sooooooo much for your help!!!!!!

 

[gmmastros]

You're welcome. I'm glad that I could help.

I can't leave this thread without saying something about your description field. It looks like you have 10 (logical) fields stored within a single (actual) field. There are a variety of reasons why this is bad. You should consider changing your database structure so that each field contains 1 piece of data. To get a better understanding of what I am suggesting, I urge you to do a google search on database normalization. Also, please don't take this a criticism. Instead, view this as a helpful suggestion.

I would also like to add that Alex's query would have worked and also had the proper column alias (the problem that my query had). So... he probably deserves some special recognition also.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom