Complex SQL Server Insert Need

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?
infutechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

izblankCommented:
INSERT INTO tblBoxHeader
(PO,BoxCnt)
SELECT PO, count(PO)
FROM tblBox
WHERE .....
GROUP BY PO
0
BulZeyECommented:
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
infutechAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

infutechAuthor Commented:
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
BulZeyECommented:
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
Ken SelviaRetiredCommented:
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
infutechAuthor Commented:
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
Ken SelviaRetiredCommented:
Then this should work

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.