[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 880
  • Last Modified:

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?
0
infutech
Asked:
infutech
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now