Solved

Complex SQL Server Insert Need

Posted on 2004-10-25
860 Views
Last Modified: 2011-09-20
I have an application that inserts data into tblBox. tblBox is comprised of (TranID, PO, BoxNo, Username,SiteID, EnteredTime). Somehow I need to also insert a part of this data into another table, tblBoxHeader. I need to insert the PO, Username, SiteID, and a BoxCnt. In tblBox I may have 10 rows containing different BoxNo that belong to one PO. So I would need to insert one row per PO with a calculated number for the BoxCnt = 10.  
How can I do this to avoid duplicate POs and still get an acurate box count?
0
Question by:infutech
    8 Comments
     
    LVL 6

    Expert Comment

    by:izblank
    INSERT INTO tblBoxHeader
    (PO,BoxCnt)
    SELECT PO, count(PO)
    FROM tblBox
    WHERE .....
    GROUP BY PO
    0
     
    LVL 4

    Assisted Solution

    by:BulZeyE
    So you want a distinct count of boxes per PO but how do you want the username and SiteID??

    If you want distinct box count per user and SiteID, then you need to use izblank's response but add in Username and SiteID and count the BoxNo:

    INSERT INTO tblBoxHeader
    (Username, SiteID, PO,BoxCnt)
    SELECT Username, SiteID, PO, count(BoxNo)
    FROM tblBox
    WHERE .....
    GROUP BY Username, SiteID, PO

    If you can have multiple BoxNo and you don't care about the number of TranIDs then you need a distinct

    INSERT INTO tblBoxHeader
    (Username, SiteID, PO,BoxCnt)
    SELECT Username, SiteID, PO, count( distinct BoxNo)
    FROM tblBox
    WHERE .....
    GROUP BY Username, SiteID, PO

    Other than that, I'm not sure.. your question seems to go down one path then ask a different detail.. ??

    0
     

    Author Comment

    by:infutech
    Ok maybe I confused myself...
    Sample Data in tblBox:
    TranID        PO                BoxNo                                   UserName           SiteID    EnteredTime
    1                786757          H00F786757Y001                   jsmith                 ATL       2004/10/25  13:30
    2                786757          H00F786757Y002                   jsmith  
    0
     

    Author Comment

    by:infutech
    Ok maybe I confused myself...
    Sample Data in tblBox:
    TranID        PO                BoxNo                                   UserName           SiteID    EnteredTime
    1                786757          H00F786757Y001                   jsmith                 ATL       2004/10/25  13:30
    2                888786          H00F888786Y001                   jsmith                 ATL       2004/10/25  13:30
    3                786757          H00F786757Y002                   jsmith                 ATL       2004/10/25  13:30  

    Data Needed in tblBoxHeader:
    ID           PO(no dups)                BoxCnt                   TransactionTime
    1            786757                       2                           2004/10/25  13:30
    2            888786                       1                           2004/10/25  13:30

    The ID fields are autonumber and I my concern isn't so much with the username or siteid. Those can be omitted from tblBoxHeader. I need to ensure that I have a correct count for each PO. This process would need to run as a stored procedure.

    Thanks for the help.
    0
     
    LVL 4

    Expert Comment

    by:BulZeyE
    So in your final tblBoxHeader, what is the Transaction time?  In your example, the EnteredTime happens to be the same time for the same PO....
    0
     
    LVL 12

    Expert Comment

    by:kselvia
    I think BulzEye was all over this but here is an answer.  It uses the maximum time in EnteredTime since you did not say what you really needed (as BulzEye noted)

    Points to him even if this works.

    Insert tblBoxHeader(ID, PO, BoxCnt, TransactionTime)
      select min(TranID), PO, count(1), max(EnteredTime) From tblBox Group by PO
    0
     

    Author Comment

    by:infutech
    The tblBoxHeader TransactionTime will come from the getdate() function as the record is inserted into tblBoxHeader.  The tblBoxHeader ID will be generated via an autonumber.  So really all I need to do is insert the PO and BoxCnt.

    I apologize for dragging this out. Thanks to all for the help.
    0
     
    LVL 12

    Accepted Solution

    by:
    Then this should work

    Insert tblBoxHeader(ID, PO, BoxCnt, TransactionTime)
      select min(TranID), PO, count(1), getdate() From tblBox Group by PO
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    Best code for creating a new column 3 28
    SQL help 5 25
    how to properly combine two queries 5 23
    The Trouble With Queries 18 18
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    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.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    875 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now