T-SQL: Aggregate and/or conditional subquery on where clause

Question

T-SQL: Aggregate and/or conditional subquery on where clause

Viewing 8 posts - 31 through 37 (of 37 total)

You must be logged in to reply to this topic. Login to reply

marcossuriel SSChasing Mays Points: 636 More actions · Answer 1

Hello Drew,

I did look at the results I'm getting over and over and I'm trying to understand what the ROW_NUMBER() is doing, but I'm a little lost. I see it's ordering by the TransType then TransDate, but I don't understand how this violates the > 0 condition in the WHERE clause and also only selecting TransType = 4

drew.allen SSC Guru Points: 76985 More actions · Answer 2

marcossuriel (6/8/2016)
Hello Drew,
I did look at the results I'm getting over and over and I'm trying to understand what the ROW_NUMBER() is doing, but I'm a little lost. I see it's ordering by the TransType then TransDate, but I don't understand how this violates the > 0 condition in the WHERE clause and also only selecting TransType = 4

Remember the WHERE clause also includes "RN=1", so it's only evaluating rows where this is true. The whole purpose of the ROW_NUMBER is to make sure that the rows of interest are sorting first. If that's not the case, and it's not, then the rest of your criteria is moot.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

marcossuriel SSChasing Mays Points: 636 More actions · Answer 3

Ohhh I see what you mean. I mean the rows of interest are being ordered by TransType Desc and by TransDate Desc, but that leaves out the TransType =1 according to the results.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 4

So there are two separate problems.

The first is that there are TransTypes other than 4 and 1, so ordering by TransType DESC means that the RN=1 could well be rows other than a 4 or a 1.

The second is still that the WHERE clause as originally written allowed for results from lots with a current quantity of 0 (or a TransDate that doesn't meet the date criterion, for that matter).

Both are real problems with the original query, and they each need to be addressed.

In this case, based on the desired results, it seems we can resolve them both fairly easily (with some other changes to the query to account for the date and TransType being stored differently, as well as the absence of a TransNo):

WITH LotHistOrdered AS

(

SELECT *,

rn=ROW_NUMBER()OVER(PARTITION BY lh.LotNo ORDER BY lh.TransType DESC, lh.TransDate DESC)

FROM LotHist lh

WHERE TransType = '4'

OR

TransType = '1'

)

SELECT LOT.LotNo,

ITEM.ItemNo,

ITEM.[DESC],

LOT.Warehouse,

LOT.CurrentQty,

LOT.Cost,

LOTHIST.TransDate,

lothist.TransType,

LOTHIST.TransQty

FROM LOT

INNER JOIN

ITEMON LOT.ItemNo = ITEM.ItemNo

INNER JOIN

LotHistOrdered lothist ON LOTHIST.LotNo = LOT.LotNo

WHERE lothist.rn = 1

AND

LOT.CurrentQty > 0

AND

LOTHIST.TransDate < CAST(CONVERT(VARCHAR(8),GETDATE()- 90,112) AS INT)

;

Cheers!

drew.allen SSC Guru Points: 76985 More actions · Answer 5

Jacob Wilkins (6/8/2016)
So there are two separate problems.

There's actually a third problem, but I don't want to say what it is, because the OP needs to understand how this query is working. The reason that I told him to look at the ROW_NUMBER, is because that should lead him to the correct solution for two of the three problems, and he should have been able to figure out the third once he got the other two corrected.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

marcossuriel SSChasing Mays Points: 636 More actions · Answer 6

Hello Jacob,

Thanks a lot for your reply. I ran this query on the live database and got about 360 records returned which sounds about right. I have checked 35 so far against the ERP front end and they look good so far. I'm going to check a lot more just to make sure. I will keep you posted, thanks a million 🙂

marcossuriel SSChasing Mays Points: 636 More actions · Answer 7

Hey Drew,

Thanks again for your feedback. I understand where you're coming from and believe me I really want to learn, I'm not looking for someone to just hand me all the answers. I have been struggling with this for weeks now. One of the reasons is because I'm not any kind of developer or programmer, I'm an IT admin and a Jack of all trades at my job.

I'm the only IT person for the company so I don't have as much time right now as I'd really like to dedicate to learning SQL, but I'm working on it. I will really study this query and try to see what the 3rd issue is and give you some feedback. I really really appreciate your effort in helping me out through this.

marcossuriel SSChasing Mays Points: 636 More actions · Answer 8

Hello Drew,

I've been at it for a few hours not, but I can't find the 3rd problem you're talking about. I've also been testing more sample data and everything is checking out so far.