SQL adding multiple values within query

[bluehangook629]

I have a sql query with that adds numerical values from different table to find total for a shipment.
When I execute this query, it works fine for the first record but rest of the records show null value when I know there are records present.
SQL pros please take a look and let me know if there is a problem with my syntax.

SELECT        InID, InDate, OrderID,
                             (SELECT        CompanyName
                               FROM            tblCompany
                               WHERE        (CompanyID = tblIn.ShipperID)) AS SHIPPER,
                             (SELECT        CompanyName
                               FROM            tblCompany AS tblCompany_1
                               WHERE        (CompanyID = tblIn.CarrierID)) AS CARRIER, Tracking, BolQty,
                             
(SELECT        COUNT(ControlID) AS Expr1
                               FROM            tblInventoryDetail
                               WHERE        (InID = tblIn.InID)) +
                             
(SELECT        COUNT(*) AS expr1
                               FROM            tblWrongRA
                               WHERE        (InID = tblIn.InID)) +
                            
 (SELECT        SUM(Qty) AS Expr1
                               FROM            tblInBulk
                               WHERE        (InID = tblIn.InID)) AS QTY,

                             (SELECT        Fname + ' ' + Lname AS Expr1
                               FROM            tblUser
                               WHERE        (EmpID = tblIn.EmpID)) AS 'USER', TimeRecord
FROM            tblIn

[lolafuertes]

Talking about SQL Tunning or SQL Optimization, your query is almost inadecuate because the comparasion values in the select subqueries are calculated once (with the first record) then used for the other records, resulting in null values.

Your calculation involves counting or summing data from tables depending on the current value of the tblIn record.

To do so, I will recommend to create an stored procedure that will create a temp table, then fill it in with the necessary records and return the content from this table.

In order to fill the temp table, you can use a cursor to navigate the tblIn inner joined to tblCompany twice with alias C1 and C2 to obtain the shipper and the carier, and to tblUser to obtain the user full name, and, for each recor retrieved,
* Create/clear the temp variables CountOfInventoryDetail, CountOfWrongRa and SumOfInBulk
* do the select from the tblInventory detail and store the count in a variable CountOfInventoryDetail,
* do the select from the tblWrongRA and store the result in a variable CountOfWrongRa
* do the select from the tblInBulk and store the result in a variable SumOfInBulk

Then insert into the temp table the required values, including the sum of the 3 temp variables. Be aware that some of them can result in null, so try to use of the ISNULL function to return a 0 in case of null.

Once retrieved all records, close the cursor and do a SELECT from your temp table.

Once the stored procedure is created, you can call it from your application using the expresion "EXECUTE MyStoredProcedureName" and issuing an ExecuteQuery or equivalent.

Hope this helps.

[bluehangook629]

Thank you lola for your input.
lol, information overflow for a SQL newbie like myself.
I don't quite understand but I'll google it and try my best.
Thanks again~!

[bluehangook629]

With help from Lola and google.com, this is the code that I came up with.
It seems to work when I execute it in SQL management studio but when I bind this to a datagrid, nothing shows.
Someone please tell me what I'm missing.

Begin
create table #Total(InID nvarchar(10), qty int)

insert Into #total (inid, qty)
select distinct inid, count(controlID) from tblinventorydetail
group by inid

insert into #total (inid, qty)
select distinct inid, count(inid) from tblWrongRa
group by inid

INSERT INTO #total (inid,qty)
select distinct inid, sum(qty) from tblinbulk
group by inid


SELECT        InID, InDate, OrderID,
    (SELECT CompanyName FROM tblCompany WHERE(CompanyID = tblIn.ShipperID)) AS SHIPPER,
    (SELECT CompanyName FROM tblCompany AS tblCompany_1 WHERE(CompanyID = tblIn.CarrierID)) AS CARRIER, Tracking, BolQty,
    (SELECT Sum(qty) FROM #total WHERE InID = tblIn.InID) AS QTY,
	(SELECT Fname + ' ' + Lname AS Expr1 FROM tblUser WHERE (EmpID = tblIn.EmpID)) AS 'USER', TimeRecord
FROM tblIn

DROP TABLE #TOTAL
End

Thanks in advance.

[lolafuertes]

Well done so far. But... There is always ...

Obviously, you still need to create the temporary table before, but it will need to have all the destination fileds like:

CREATE TABLE #Total(
InID nvarchar(10)
, InDate datetime
, OrderId nvarchar(15)
, Shipper nvarchar(20)
, Carrier nvarchar(20)
, Qty int
, User nvarchar(20)
)

I suggested to create a cursor to cicle over every record in the tblIn, but you did'nt.

To create a cursor please read here

I would suggest the following cursor:

DECLARE tblIn_Cursor CURSOR FORWARD_ONLY FOR
SELECT tblIn.InId as InId, tblIn.InDate as InDate, tblIn.OrderID as OrderId, C1.Companyame as Shipper, C2.ComapnyName as carrier, tblUser.Fname + ' ' + tblUser.Lname As User
FROM tblIn
INNER JOIN tblCompany C1 on C1.CompanyID = tblIn.ShipperID
INNER JOIN tblCompany C2 on C2.ComapnyID = tblIn.CarrierID
INNER JOIN tblUser on tblUser.EmpId = tblIn.EmpId;

Also you will need to declare a few variables and adjust the types according (here only a suggestion): DECLARE @InId nvarchar(10);
DECLARE @InDate datetime;
DECLARE @OrderId nvarchar(15);
DECLARE @Shipper nvarchar(20);
DECLARE @Carrier nvarchar(20);
DECLARE @User nvarchar(20);

Also you will need: DECALRE @CountOfTblInventoryDetail int;
DECLARE @CountOfTblWrongRa int;
DECLARE @SumOfTblinBulk int;

Then you can open the cursor and fetch the next record OPEN tblIn_Cursor;
FETCH NEXT FROM tblInCursor INTO @InId, @InDate, @OrderId, @shipper, @Carrier, @User

Then you will need a WHILE loop like: WHILE @@FETCH_STATUS = 0 BEGIN
.
.
.
END

Then, to close the cursor and free the resources you will need CLOSE tblIn_Cursor;
DEALLOCATE tblIn_Cursor;

OK. Here we have a basic cursor structure.

Inside the BEGIN - END block you need to calculate the totals from the other tables.

To do so, you will need to do the following selects based on the current InId of the cursor: SELECT @CountOfTblInventoryDetail = ISNULL(Count(ControlId),0) FROM tblinventorydetal WHERE inid = @InId

SELECT @CountOfTblWrongRA = ISNULL(Count(InId),0) FROM tblinventorydetal WHERE InId = @InId

SELECT @SumOfTblinBulk = ISNULL(SUM(qty),0) FROM tblinbulk where inid = @InId

At this moment you have all you need for the insert: INSERT INTO #Total Values (@InId, @InDate, @OrderId, @shipper, @Carrier, @CountOfTblInventoryDetail + @CountOfTblWrongRA + @SumOfTblinBulk, @User)

Hopefully with all that, we got teh #Total table with a record for each InID.

After closing and deallocating the cursor, you need to return the contents of the tamporary table issuing a SELECT * FROM #Total

And that's should be all. You do not need to delete a local temporary table because the system does it for you.

Ah, Do not forget to SET the NOCOUNT flag in order to only return the content of the SELECT.

Hope this helps

[bluehangook629]

Thanks for your input.
I didn't know how to use a cursor so I thought I'll just work around it.
I will try following your instruction, something new for me with all these fancy syntax lol. I'll mark this post solved once I try it out.
Thanks Lola and you the best!

[bluehangook629]

Do I write the insert statement inside of BEGIN-END block? Also, wouldnt this only read first record? add another FETCH NEXT inside of BEGIN-END block?

OPEN tblIn_Cursor;
FETCH NEXT FROM tblInCursor INTO @InId, @InDate, @OrderId, @shipper, @Carrier, @User

WHILE @@FETCH_STATUS = 0 BEGIN
SELECT  @CountOfTblInventoryDetail = ISNULL(Count(ControlId),0) FROM tblinventorydetal WHERE inid = @InId
SELECT  @CountOfTblWrongRA = ISNULL(Count(InId),0) FROM tblinventorydetal WHERE InId = @InId
SELECT  @SumOfTblinBulk = ISNULL(SUM(qty),0) FROM tblinbulk where inid = @InId

INSERT INTO #Total Values (@InId, @InDate, @OrderId, @shipper, @Carrier, @CountOfTblInventoryDetail + @CountOfTblWrongRA + @SumOfTblinBulk, @User)

FETCH NEXT FROM tblInCursor INTO @InId, @InDate, @OrderId, @shipper, @Carrier, @User
END

CLOSE tblIn_Cursor;
DEALLOCATE tblIn_Cursor;

SELECT * FROM #Total

[bluehangook629]

Here is what I came up with.
Some how it is taking way too long to execute query.
Someone please tell me if I left anything out or made any mistake.

CREATE TABLE #TOTAL(
InID nvarchar(10),
InDate datetime,
OrderID VARCHAR(30),
Shipper VARCHAR(50),
Carrier VARCHAR(50),
Tracking VARCHAR(50),
BolQty INT,
Qty INT,
UserName VARCHAR(50),
TimeRecord SmallDateTime
)

DECLARE @InID nvarchar(10);
DECLARE @InDate datetime; 
DECLARE @OrderID varchar(50);
DECLARE @Shipper VARCHAR(50);
DECLARE @Carrier VARCHAR(50);
DECLARE @Tracking VARCHAR(50);
DECLARE @BOL INT;
DECLARE @UserName VARCHAR(50);
DECLARE @TimeRecord SmallDateTime;

DECLARE @CountOfTblInventoryDetail INT;
DECLARE @CountOfTblWrongRa INT;
DECLARE @SumOfTblInBulk INT ;

DECLARE tblIn_Cursor CURSOR FORWARD_ONLY FOR
(
SELECT tblIn.InID as INID, tblIn.Indate as InDate, tblIn.OrderID as OrderID,C1.CompanyName as Shipper, C2.CompanyName as Carrier, tblIn.Tracking as Tracking, tblIn.BolQty as BOL, tblUser.Fname + ' ' + tblUser.lname as 'User Name', TimeRecord AS 'Time'
FROM tblIn
INNER JOIN tblCompany C1 on C1.CompanyID = tblIn.ShipperID
INNER JOIN tblCompany C2 on C2.CompanyID = tblIn.CarrierID
INNER JOIN tblUser on tblUser.EmpID = tblIn.EmpID
)

OPEN tblIn_Cursor;
FETCH NEXT FROM tblIn_Cursor INTO @InID, @InDate, @OrderID, @Shipper, @Carrier, @Tracking, @BOL, @UserName, @TimeRecord

WHILE @@FETCH_STATUS = 0 
BEGIN
	SELECT @CountofTblInventoryDetail = ISNULL(Count(ControlID),0) FROM tblInventoryDetail WHERE InID = @InID
	SELECT @CountOfTblWrongRA = ISNULL(Count(InID),0) FROM tblWrongRA WHERE InID = @InID
	SELECT @SumOfTblInBulk = ISNULL(SUM(Qty),0) FROM tblInBulk WHERE InID = @InID
END

CLOSE tblIn_Cursor;
DEALLOCATE tblIn_Cursor;

SELECT * FROM #TOTAL

DROP TABLE #TOTAL

[bluehangook629]

This is it

CREATE TABLE #TOTAL(
InID nvarchar(10),
InDate datetime,
OrderID VARCHAR(30),
Shipper VARCHAR(50),
Carrier VARCHAR(50),
Tracking VARCHAR(50),
BolQty INT,
Qty INT,
UserName VARCHAR(50),
TimeRecord SmallDateTime
)

DECLARE @InID nvarchar(10);
DECLARE @InDate datetime; 
DECLARE @OrderID varchar(50);
DECLARE @Shipper VARCHAR(50);
DECLARE @Carrier VARCHAR(50);
DECLARE @Tracking VARCHAR(50);
DECLARE @BOL INT;
DECLARE @UserName VARCHAR(50);
DECLARE @TimeRecord SmallDateTime;

DECLARE @CountOfTblInventoryDetail INT;
DECLARE @CountOfTblWrongRa INT;
DECLARE @SumOfTblInBulk INT ;

DECLARE tblIn_Cursor CURSOR FORWARD_ONLY FOR
(
SELECT tblIn.InID as INID, tblIn.Indate as InDate, tblIn.OrderID as OrderID,C1.CompanyName as Shipper, C2.CompanyName as Carrier, tblIn.Tracking as Tracking, tblIn.BolQty as BOL, tblUser.Fname + ' ' + tblUser.lname as 'User Name', TimeRecord AS 'Time'
FROM tblIn
INNER JOIN tblCompany C1 on C1.CompanyID = tblIn.ShipperID
INNER JOIN tblCompany C2 on C2.CompanyID = tblIn.CarrierID
INNER JOIN tblUser on tblUser.EmpID = tblIn.EmpID
)

OPEN tblIn_Cursor;
FETCH NEXT FROM tblIn_Cursor INTO @InID, @InDate, @OrderID, @Shipper, @Carrier, @Tracking, @BOL, @UserName, @TimeRecord

WHILE @@FETCH_STATUS = 0 
BEGIN
	SELECT @CountofTblInventoryDetail = ISNULL(Count(ControlID),0) FROM tblInventoryDetail WHERE InID = @InID
	SELECT @CountOfTblWrongRA = ISNULL(Count(InID),0) FROM tblWrongRA WHERE InID = @InID
	SELECT @SumOfTblInBulk = ISNULL(SUM(Qty),0) FROM tblInBulk WHERE InID = @InID
	
	INSERT INTO #TOTAL 
	VALUES (@InID, @InDate, @OrderID, @Shipper, @Carrier, @Tracking, @BOL,@CountofTblInventoryDetail+ @CountOfTblWrongRA + @SumOfTblInBulk, @UserName, @TimeRecord)
	
	FETCH NEXT FROM tblIn_Cursor INTO @InID, @InDate, @OrderID, @Shipper, @Carrier, @Tracking, @BOL, @UserName, @TimeRecord
END

CLOSE tblIn_Cursor;
DEALLOCATE tblIn_Cursor;

SELECT * FROM #TOTAL

DROP TABLE #TOTAL

Special thanks to Lola