SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculating and Comparing Values in a Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dwdwone
Yak Posting Veteran

USA
68 Posts

Posted - 01/25/2013 :  15:49:05  Show Profile  Reply with Quote
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

Edited by - dwdwone on 01/25/2013 15:55:21

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 01/25/2013 :  17:15:06  Show Profile  Reply with Quote
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

USA
68 Posts

Posted - 01/25/2013 :  17:26:41  Show Profile  Reply with Quote
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

USA
17 Posts

Posted - 01/25/2013 :  19:51:38  Show Profile  Reply with Quote
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

USA
68 Posts

Posted - 01/26/2013 :  00:04:33  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 01/26/2013 :  05:41:12  Show Profile  Reply with Quote
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

USA
68 Posts

Posted - 01/26/2013 :  09:10:38  Show Profile  Reply with Quote
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)?


Edited by - dwdwone on 01/26/2013 09:41:52
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 01/26/2013 :  09:49:12  Show Profile  Reply with Quote
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

USA
68 Posts

Posted - 01/26/2013 :  10:56:55  Show Profile  Reply with Quote
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

USA
68 Posts

Posted - 02/13/2013 :  17:42:41  Show Profile  Reply with Quote
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

Edited by - dwdwone on 02/13/2013 17:50:40
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000