Results 1 to 2 of 2

Thread: Sql Plz He[p!!

  1. #1
    Join Date
    Jun 2003
    Posts
    34

    Unanswered: Sql Plz He[p!!

    We have a table which looks like the following...

    Rep_det
    =========

    Job_num Rep_num Dim1 Dim2 Dim3 Met1 Met2 Met3
    ------- -------- ---- ---- ---- ---- ---- ----
    100 1 AK 200 300
    100 1 CA 500 200
    100 1 NY 1000 350
    100 1 MA 750 250
    200 1 CA 250 350
    200 1 NY 1000 700
    200 1 MA 500 100
    200 1 NJ 750 1100

    WE WANT A REPORT AS SHOWN BELOW...

    STATE CID SID PRIOR_CID PRIOR_SID
    ----- --- --- --------- ---------
    AK 200 300
    cA 250 350 500 200
    NY 1000 700 1000 350
    MA 500 100 750 250
    NJ 750 1000

    If you would notice that AK is not present in current "Job" so current "CID" / "SID" is NULL but it was present in the previous "Job" so there are entries in PRIOR_CID / PRIOR_SID.

    Its opposite for NJ.

    Could anyone help me out with a query for the same. Would really appreciate a help. I have attched the above so that you have a look.

    Thanks.
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Sql Plz He[p!!

    The output in your post was hard to understand so I'm a little unsure the below suggestion is what you're looking for:

    SELECT dim1 STATE, MAX(dim2) CID, MAX(dim3) SID,
    MAX(met1) PRIOR_CID, MAX(met2) PRIOR_SID
    FROM table
    GROUP BY dim1

    The above assumes using MA as an example that the data in the table is this:

    Job_num Rep_num Dim1 Dim2 Dim3 Met1 Met2
    100 1 MA 750 250
    200 1 MA 500 100

    The purpose of the MAX in the SELECT is to collapse null/value.

    If this is not the route you meant, my I suggest using a spreadsheet to accurately show the data to columns in the table rather than a notepad document.

    Originally posted by arin_am
    We have a table which looks like the following...

    Rep_det
    =========

    Job_num Rep_num Dim1 Dim2 Dim3 Met1 Met2 Met3
    ------- -------- ---- ---- ---- ---- ---- ----
    100 1 AK 200 300
    100 1 CA 500 200
    100 1 NY 1000 350
    100 1 MA 750 250
    200 1 CA 250 350
    200 1 NY 1000 700
    200 1 MA 500 100
    200 1 NJ 750 1100

    WE WANT A REPORT AS SHOWN BELOW...

    STATE CID SID PRIOR_CID PRIOR_SID
    ----- --- --- --------- ---------
    AK 200 300
    cA 250 350 500 200
    NY 1000 700 1000 350
    MA 500 100 750 250
    NJ 750 1000

    If you would notice that AK is not present in current "Job" so current "CID" / "SID" is NULL but it was present in the previous "Job" so there are entries in PRIOR_CID / PRIOR_SID.

    Its opposite for NJ.

    Could anyone help me out with a query for the same. Would really appreciate a help. I have attched the above so that you have a look.

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •