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.
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.TypeFROM tblOrder AS O INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderIDWHERE PA.Createdwhen BETWEEN @Calendar1 AND @Calendar2 + 1 AND PA.Type <> 4GROUP BY PA.ParcelIDI'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 TotalPackagesFROM tblParcelAssigned AS PAGROUP BY PA.ParcelID-- 2 how many have a problem? SELECT PA.ParcelID, COUNT(*) AS TotalProblemPackagesFROM tblParcelAssigned AS PAWHERE CreatedWhen IS NULL OR ScannedWhen IS NULL OR CreatedWhen > ScannedWhenGROUP BY PA.ParcelID -- 3 break down the problem countSELECT 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 NotScannedInFROM tblParcelAssigned AS PAGROUP BY PA.ParcelID |
|
|
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! |
|
|
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 allselect '001','101','101','2013-01-05','2013-01-05','1'union allselect '002','200','200','2013-01-10','2013-01-15','1'union allselect '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.TypeFROM @order AS OINNER JOIN @tblparcelassigned AS PA ON O.OrderID = PA.OrderIDWHEREPA.Createdwhen BETWEEN '2012-12-31' AND '2013-01-16' --+ 1AND PA.Type <> 4GROUP BY PA.ParcelID,O.orderID,PA.pkid,pa.createdWhen,pa.scannedWhen,pa.Type==============================I'm here to learn new things everyday.. |
|
|
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. |
|
|
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 NotScannedInFROM tblOrder AS O INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderIDGROUP BY O.OrderId PA.ParcelID |
|
|
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)? |
|
|
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 belowSELECT 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 NotScannedInFROM tblOrder AS O INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderIDGROUP 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 NotScannedInFROM tblOrder AS O INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderIDWHERE 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 NotScannedInFROM tblOrder AS O INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderIDGROUP BY O.OrderIdHAVING 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 |
|
|
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 |
|
|
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 MissingSignatureFROM tblOrder AS O INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderIDINNER JOIN tblOrderStatus AS OS ON OS.OrderID = PA.OrderIDINNER JOIN tblOrderDrivers AS OD ON O.OrderID = OD.OrderIDWHERE 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 <> 4AND OS.POD > ' 'AND PA.ParcelID IN (Select P.PKID from tblParcel AS P)ORPA.Createdwhen BETWEEN @Calendar1 AND @Calendar2 + 1 AND OS.Pod > ' 'AND PA.Type = 3AND O.Status <> 4AND 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 |
|
|
|
|
|
|
|