Post reply

SQL Query question

  • January 13, 2007 at 10:07 am

    #122722

    Hi everyone,

     

    I am in the process of creating a report that will report the number of permits that have met date event milestones. Each permit has a number of milestones to meet and the data would look something like this for each permit:

     

    PermitNum   EventDate   EventDesc                   EventDateID

    _________________________________________________

    PSD-1206     5/11/06      App Received               1

    PSD-1206     5/25/06       Determination              2

    PSD-1206     7/25/06       Complete App Rec       3

    PSD-1206     8/25/06       Process                         5

    PSD-1206     9/25/06       Disclosure                    6

    PSD-1206     11/22/06     Final Decision              7

     

    NOC-1055     3/11/06      App Received               1

    NOC-1055      3/25/06       Determination              2

    NOC-1055      7/25/06       Complete App Rec       3

    NOC-1055      9/25/06       Process                         5

    NOC-1055      10/25/06       Disclosure                   6

    NOC-1055      12/25/06       Final Decision             7

     

     

     

    The question I have is this:

    How can I query all the data above using a date range (quarter) and where the “Final Decision” EventDate is the date that will fall within the date range parameter.  (SQL Server 2000)

     

  • January 13, 2007 at 8:01 pm

    #683108

    --===== Build some test data so we can demo the solution

     CREATE TABLE #YourTable (PermitNum VARCHAR(10),EventDate DATETIME ,EventDesc VARCHAR(20),EventDateID INT)

     INSERT INTO #YourTable

            (PermitNum,EventDate,EventDesc,EventDateID)

     SELECT 'PSD-1206','5/11/06','App Received',1 UNION ALL

     SELECT 'PSD-1206','5/25/06','Determination',2 UNION ALL

     SELECT 'PSD-1206','7/25/06','Complete App Rec',3 UNION ALL

     SELECT 'PSD-1206','8/25/06','Process',5 UNION ALL

     SELECT 'PSD-1206','9/25/06','Disclosure',6 UNION ALL

     SELECT 'PSD-1206','11/22/06','Final Decision',7 UNION ALL

     SELECT 'NOC-1055','3/11/06','App Received',1 UNION ALL

     SELECT 'NOC-1055','3/25/06','Determination',2 UNION ALL

     SELECT 'NOC-1055','7/25/06','Complete App Rec',3 UNION ALL

     SELECT 'NOC-1055','9/25/06','Process',5 UNION ALL

     SELECT 'NOC-1055','10/25/06','Disclosure',6 UNION ALL

     SELECT 'NOC-1055','12/25/06','Final Decision',7

    --===== These variables represent parameters for a sproc

    DECLARE @StartDate DATETIME

    DECLARE @EndDate   DATETIME

        SET @StartDate = '12/01/2006'

        SET @EndDate   = '12/31/2007'

    --===== This would be the body of the sproc

     SELECT y.*

       FROM #YourTable y,

            (--Derived table "d" finds PermitNums with Final Decision in the correct date range

             SELECT PermitNum

               FROM #YourTable

              WHERE EventDateID = 7

                AND EventDate  >= @StartDate

                AND EventDate  <  @EndDate+1

            ) d

      WHERE y.PermitNum = d.PermitNum

      ORDER BY y.PermitNum,EventDateID

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 14, 2007 at 12:50 pm

    #683141

    Thanks Jeff,

    This looks like what I need. The real data comes from multiple related tables which I will apply this query to.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply