Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculating and Comparing Values in a Table

Author  Topic 

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2013-01-25 : 15:49:05
Hi. I am working on a way to calculate aggregate values in a table and display the results that are not 0.

Specifically, I have a table called tblParcelAssigned AS PA. When a Parcel is scanned in, it creates a record in PA with the scan in time. It also creates a record for the parcel to be scanned out, with the time (PA.ScannedWhen) as a null value until the parcel is scanned out, at which time PA. ScannedWhen is updated to the scan in time.

I am trying to create a query which when run will alert staff that one of two conditions has taken place:

1. A parcel is scanned in but has not been scanned out.
2. A parcel was scanned out but was never scanned in.

In essence, an over and short alert. If the difference between the total in and total out is not 0, then there is a problem.

I am trying to use a GROUP BY as my initial test but am getting errors. Here is the code:


SELECT O.OrderID, COUNT (PA.parcelid), PA.ParcelID, PA.PKID, PA.CreatedWhen, PA.ScannedWhen, PA.Type

FROM tblOrder AS O
INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderID

WHERE

PA.Createdwhen BETWEEN @Calendar1 AND @Calendar2 + 1
AND PA.Type <> 4

GROUP BY PA.ParcelID

I've always been a bit confused by GROUP BYs, and have been doing reading on them this afternoon until my head hurts. Can anyone guide be in the proper direction?

Thanks,
Dan

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-25 : 17:15:06
I didn't follwo the reason for joining on the tblOrder table. But from your description, it seems like you want the third query below
-- 1 how many are there in total      
SELECT
PA.ParcelID,
COUNT(*) AS TotalPackages
FROM
tblParcelAssigned AS PA
GROUP BY
PA.ParcelID

-- 2 how many have a problem?
SELECT
PA.ParcelID,
COUNT(*) AS TotalProblemPackages
FROM
tblParcelAssigned AS PA
WHERE
CreatedWhen IS NULL OR ScannedWhen IS NULL
OR CreatedWhen > ScannedWhen
GROUP BY
PA.ParcelID

-- 3 break down the problem count
SELECT
PA.ParcelID,
COUNT(*) AS TotalPackages,
SUM(CASE WHEN CreatedWhen IS NOT NULL AND ScannedWhen IS NULL THEN 1 ELSE 0 END) AS NotScannedOut,
SUM(CASE WHEN CreatedWhen IS NULL AND ScannedWhen IS NOT NULL THEN 1 ELSE 0 END) AS NotScannedIn
FROM
tblParcelAssigned AS PA
GROUP BY
PA.ParcelID
Go to Top of Page

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2013-01-25 : 17:26:41
The order table was in there because it allows the result to be "clickable", so our CSR can review the order. For example, running query # 1 and clicking on a row would result in this error message: "Cannot edit from this query. First column must contain the primary key field."

The interface I'm stuck with using won't allow me to run consecutive queries. So you sort of have to jump through hoops and embed alot of stuff. Which makes it even crazier!
Go to Top of Page

shan007
Starting Member

17 Posts

Posted - 2013-01-25 : 19:51:38
I guess you get error because of Group BY clause. When you use group by, except aggregate column, all other columns used in select statement should be listed in group by clause. I don't know how z data in your Orders and parcelassigned tables, however I created below script based on my understanding for you. Hope this would help. If you need more help, please list down the data in tables and expected output.


declare @order table(
orderID varchar(3)
)

declare @tblparcelassigned table (
orderID varchar(3),
parcelID varchar (3),
pkid varchar(3),
createdWhen datetime default null,
scannedWhen datetime default null,
Type varchar(2)
)

insert into @order values ('001')
insert into @order values ('002')
insert into @order values ('003')

insert into @tblparcelassigned(orderID,parcelID,pkid,createdWhen,scannedWhen,Type )
select '001','100','100','2013-01-01','2013-01-03','1'
union all
select '001','101','101','2013-01-05','2013-01-05','1'
union all
select '002','200','200','2013-01-10','2013-01-15','1'
union all
select '002','201','201','2013-01-11','2013-01-16','4'

insert into @tblparcelassigned(orderID,parcelID,pkid,createdWhen,Type)
select '003','300','300','2013-01-07','1'

SELECT O.OrderID, COUNT (PA.parcelid) Parcelcount, PA.ParcelID, PA.PKID, PA.CreatedWhen, PA.ScannedWhen, PA.Type

FROM @order AS O
INNER JOIN @tblparcelassigned AS PA ON O.OrderID = PA.OrderID

WHERE

PA.Createdwhen BETWEEN '2012-12-31' AND '2013-01-16' --+ 1
AND PA.Type <> 4

GROUP BY PA.ParcelID,O.orderID,PA.pkid,pa.createdWhen,pa.scannedWhen,pa.Type

==============================
I'm here to learn new things everyday..
Go to Top of Page

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2013-01-26 : 00:04:33
Unfortunately I cannot do an insert. Here is the reply I got from their tech support:

The Advanced Find cannot run multiple queries in one window like your example. It also can't insert into/drop temp tables, which might be required for what you are trying to do.

Might I be able to use subselects to achieve the result? Or maybe global variables - assuming, of course, that us litte people are permitted to use those features.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-26 : 05:41:12
What I was showing you and what shan was showing you are examples. I posted 3 different queries because I was not sure what you will need. You can write one query, joining the tables as necessary. If you need the OrderId as well, modify the 3rd query I had posted to join on the orders table (exactly like you had in your original query) and add the OrderId column in the select list and in the group by clause.
SELECT
O.OrderId,
PA.ParcelID,
COUNT(*) AS TotalPackages,
SUM(CASE WHEN CreatedWhen IS NOT NULL AND ScannedWhen IS NULL THEN 1 ELSE 0 END) AS NotScannedOut,
SUM(CASE WHEN CreatedWhen IS NULL AND ScannedWhen IS NOT NULL THEN 1 ELSE 0 END) AS NotScannedIn
FROM
tblOrder AS O
INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderID
GROUP BY
O.OrderId
PA.ParcelID
Go to Top of Page

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2013-01-26 : 09:10:38
The way the CASEs were embedded into the aggregate was really cool and is going to help me a lot. Thank you.

Am still not getting the expected results but your illustrations have really given me a good jump start. Am thinking I need to play with the CASE statements, because items that have been both scanned in and scanned out are showing in the results. I think I need to take the total of each parcel ID that were scanned in, then the total that were scanned out, and the differences that are not zero would be the suspected offenders.

Also, COUNT (&) AS 'Total Parcels' always show as 1, maybe because there will always be only one record for each Pa entry?

If I wanted to filter the results by Notscannedin or Notscannedout, can i use the aliases (e. WHERE Notscannedin > 0)?

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-26 : 09:49:12
I don't fully understand the relationships between parcels and orders - whether it is one to one or one to many etc. If for example, one order can have many parcels, and you are trying to get the status of all packages within each orderid, then you would not need to group by parcelid. If you want to get only parcels that have ScannedWhen or CreatedWhen is null, then you would need to add that to the where clause. So one of these (you don't need to run both these queries, I am just showing two possibilities. You probably need one of these or some variation of one of these)

You cannot use aggregate in the WHERE clause, but you can use it in the having clause - see the third query below
SELECT
O.OrderId,
COUNT(*) AS TotalPackages,
SUM(CASE WHEN CreatedWhen IS NOT NULL AND ScannedWhen IS NULL THEN 1 ELSE 0 END) AS NotScannedOut,
SUM(CASE WHEN CreatedWhen IS NULL AND ScannedWhen IS NOT NULL THEN 1 ELSE 0 END) AS NotScannedIn
FROM
tblOrder AS O
INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderID
GROUP BY
O.OrderId


SELECT
O.OrderId,
COUNT(*) AS TotalProblemPackages,
SUM(CASE WHEN CreatedWhen IS NOT NULL AND ScannedWhen IS NULL THEN 1 ELSE 0 END) AS NotScannedOut,
SUM(CASE WHEN CreatedWhen IS NULL AND ScannedWhen IS NOT NULL THEN 1 ELSE 0 END) AS NotScannedIn
FROM
tblOrder AS O
INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderID
WHERE
CreatedWhen IS NULL OR ScannedWhen IS NULL
GROUP BY
O.OrderId


SELECT
O.OrderId,
COUNT(*) AS TotalProblemPackages,
SUM(CASE WHEN CreatedWhen IS NOT NULL AND ScannedWhen IS NULL THEN 1 ELSE 0 END) AS NotScannedOut,
SUM(CASE WHEN CreatedWhen IS NULL AND ScannedWhen IS NOT NULL THEN 1 ELSE 0 END) AS NotScannedIn
FROM
tblOrder AS O
INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderID
GROUP BY
O.OrderId
HAVING
SUM(CASE WHEN CreatedWhen IS NOT NULL AND ScannedWhen IS NULL THEN 1 ELSE 0 END) > 0
OR
SUM(CASE WHEN CreatedWhen IS NULL AND ScannedWhen IS NOT NULL THEN 1 ELSE 0 END) > 0
Go to Top of Page

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2013-01-26 : 10:56:55
Thank you James. I'll be pouring over these for the rest of the day!

The relationship is one or more parcels for each order. The relatonship is something like this: Order ->(Many)Parcels -> ParcelsAssigned (Many). I skipped the Parcels table because the "meat" is in ParcelsAssigned, and both Parcels and ParcelsAssigned can be linked to the Order table.

Sometimes people do not scan packages in but they do scan them out. Sometimes they scan them in but do not scan them out. I am thinking that the second query should work well, but will be experimenting with variations of all three. I have a long afternoon ahead of me, but this has been an awesome learning experience. Thanks again to everyone here.

Dan
Go to Top of Page

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2013-02-13 : 17:42:41
I have two questions in one.

I've been asked to add a column which looks for all delivered orders without scanned signatures. The conditions are slightly different from the original ones. The existance of a signature is in a table attached to my orders.

I have the query pulling up the expected records using a list of conditions using OR, but I'm wondering if this is the most efficient way to do it as there seems to be a lot of repetion. Others have recommended using a a UNION SELECT instead. Which is better?

ALSO, I'm wondering if I can use the SUM(Case part of the SELECTed columns to display a count of delivered orders without scanned signatures. What is the syntax used to do this? I've experimented with CASE WHEN EXISTS (SELECT followed by the conditions but haven't had any luck.

This is the functioning code so far. The third CASE statement only counts some occurrances since it is not selecting values based on the OR conditions at the end of the query.

DECLARE @Calendar1 AS DateTime
DECLARE @Calendar2 AS DateTime
SET @Calendar1 = '{{{ Please choose a start date. }}}'
SET @Calendar2 = '{{{ Please choose an end date. <(non inclusive)> }}}'

SELECT
O.OrderId,
O.CustID,
O.OriginName,
OS.POD,
OD.DriverId,
COUNT(*) AS TotalProblemPackages,
SUM(CASE WHEN pa.CreatedWhen IS NOT NULL AND pa.ScannedWhen IS NULL and pa.type = 3 THEN 1 ELSE 0 END) AS NotScannedIn,
SUM(CASE WHEN pa.CreatedWhen IS NULL AND pa.ScannedWhen IS NOT NULL and pa.type = 2 THEN 1 ELSE 0 END) AS NotScannedOut,
SUM(CASE WHEN pa.CreatedWhen IS not NULL AND pa.ScannedWhen IS not NULL and pa.type = 3 THEN 1 ELSE 0 END) AS MissingSignature

FROM tblOrder AS O
INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderID
INNER JOIN tblOrderStatus AS OS ON OS.OrderID = PA.OrderID
INNER JOIN tblOrderDrivers AS OD ON O.OrderID = OD.OrderID

WHERE PA.Createdwhen BETWEEN @Calendar1 AND @Calendar2 + 1

AND (PA.CreatedWhen IS NULL OR PA.ScannedWhen IS NULL)
AND (PA.Type = 3 or PA.Type = 2)
AND O.Status <> 4
AND OS.POD > ' '
AND PA.ParcelID IN (Select P.PKID from tblParcel AS P)

OR

PA.Createdwhen BETWEEN @Calendar1 AND @Calendar2 + 1
AND OS.Pod > ' '
AND PA.Type = 3
AND O.Status <> 4
AND O.Orderid NOT IN (SELECT ST.PKID FROM tblSignature_table AS ST WHERE ST.PKID = O.Orderid)

GROUP BY O.OrderId, O.CustID, O.OriginName, OS.POD, OD.DriverID
Go to Top of Page
   

- Advertisement -