Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Complex SQL Server Insert Need

Posted on 2004-10-25
8
Medium Priority
?
870 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
Comment
Question by:infutech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 6

Expert Comment

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

Assisted Solution

by:BulZeyE
BulZeyE earned 1000 total points
ID: 12406018
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
ID: 12406843
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:infutech
ID: 12406948
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
ID: 12407157
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
ID: 12407270
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
ID: 12410747
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:
kselvia earned 1000 total points
ID: 12410764
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

604 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