SQLTeam.com | Weblogs | Forums

Help with logic


#1

I'd like to see if someone can give me some ideas as to how to tackle an issue I'm having. The attachment has three results, I only want the last one, the one with the red text, to return.

The query is returning items that have shipped complete, in this case this order had two separate shipments for one each to fulfill the order for two, but once it was shipped complete, triggered by the shipment on 4/27, I want it to return, but just the 4/27 item, not the other two (that first one had a shipment for 0, that's an anomaly, data entry error probably)

Hopefully I've explained it well enough.

Thank you.


#2

In the following, assuming you want to get one row per [SO No] and you want to get the latest record (when ordered by [Ship Dt]), do the following:

;WITH cte AS
(
	SELECT
		*, -- replace the * with the columns you want to see in your output
		ROW_NUMBER() OVER (PARTITION BY [SO No] ORDER BY [Ship Dt] DESC) AS RN
	FROM
		YourTable
)
SELECT
	* -- replace the * with the columns you want to see in your output
FROM
	cte
WHERE RN = 1;

If one row per [SO No] is not what you want, replace the PARTITION BY Clause with the column (s) for which a unique combination should result in one row. If the latest [Ship Dt] is not the criterion (or if there can be more than one row for a given [SO No] with the latest ship date, replace the Order by columns in the ROW_NUMBER function.


#3

dumb question but would I add this as part of the WHERE statement or the SELECT
statement?

Also, I'd like 1 return per SO AND line item, so would the partition look something like PARTITION BY [SO No] + [Line Item] ORDER BY.....


#4

It would be PARTITION BY [SO No], [Line Item]... notice the comma. I believe this should be your select statement. Your current statement should be instead of the "*".


#5

I've augmented JamesK's code with logic to return only orders that have completely shipped:;with ShippedComplete as ( select [SO No], from YourTable group by [SO No] having max([SO Qty]) = sum([Ship Qty]) ), cte AS ( SELECT *, -- replace the * with the columns you want to see in your output ROW_NUMBER() OVER (PARTITION BY [SO No] ORDER BY [Ship Dt] DESC) AS RN FROM YourTable WHERE YourTable.[SO No] in select [SO No] from ShippedComplete ) SELECT * -- replace the * with the columns you want to see in your output FROM cte WHERE RN = 1;Also, please stop putting spaces in your column names.


#6

Most people use underscores to replace the spaces. It is an informal convention since the beginning of time (or SQL).


#7

Thanks for the help everyone.

Using a CTE is beyond my SQL knowledge so I'll need to study up, you don't think this can be accomplished with a clever WHERE statement or some sort of DISTINCT statement?


#8

There are only two constructs in that query that you may not be familiar with. One is the CTE, and the other is the ROW_NUMBER function. While each of those can have its nuances and complexities, they are very simple the way they have been used here.

You can think of CTE as a sub-query written on top (for purposes of this discussion). So you have subquery, and it can have any name you want - I just chose to name it CTE. Just like in a subquery, that gives you a virtual table and you are selecting from that virtual table. In fact, you can write this query using a subquery if you are more comfortable with that.

The other construct, ROW_NUMBER, simply gives you a row number starting at 1 for each of the rows that are returned in the query. If you have no partition by clause, the numbers will start at 1 and increment for each row returned. If you do have a partition clause, as in this case, the row number will reset for each unique value of the columns in the partition by clause. The order by clause determines which row gets to be number one, and which on number two and so on.

It would be well worth the time to read up on these two constructs - they come in handy in a lot of places in T-SQL. You could use clever programming and avoid these, but they would be harder to read and less efficient.


#9

Thanks James.

I'm just confused as to where to put my existing query.

There appears to be two queries in one, not sure which is the CTE or subquery and what is the main query.

attached is what i think it should be?

;WITH cte AS
(
SELECT
*, -- replace the * with the columns you want to see in your output
ROW_NUMBER() OVER (PARTITION BY [SO No] ORDER BY [Ship Dt] DESC) AS RN
FROM
YourTable
)
SELECT
EXISTING QUERY
FROM
EXISTING TABLE
WHERE RN = 1;


#10

It should be the other way around. Take the code below and plug in the followng. If you have an order by clause in the query, remove that and add it back at the very bottom as I have indicated.

------------ FIRST CTE --------------------
;WITH cte1 AS
(
	--------- BEGIN: YOUR EXISTING QUERY --------------
	SELECT
	EXISTING QUERY
	FROM
	EXISTING TABLE 
	-- OMIT THE ORDER BY CLAUSE IF YOU HAVE ANY
	--------- END: YOUR EXISTING QUERY --------------

),

------------ SECOND CTE --------------------

cte2 AS
(
	SELECT *,
		ROW_NUMBER() OVER (PARTITION BY [SO No] ORDER BY [Ship Dt] DESC) AS RN
	FROM
		cte1
)

------------ FINAL SELECT --------------------

SELECT
	*
FROM
	cte2 
WHERE
	RN = 1
ORDER BY
	-- YOUR ORDER BY CLAUSE IF ANYHERE

#11

That worked James! Thank you so much for your time and patience.


#12

Ah, yes (and of course I agree) but with the advent of [MyColumnName] allowing any characters, similar to when Filenames started allowing Spaces etc., then teh fact that it is possible to have spaces encourages their use - if we stick to a more narrow naming convention / rule we don't need "[" and "]" at all :slight_smile:

... my recollection is that "[" & "]" came about at the same time that Upsizing Access DBs to SQL Server could all be done with a Wizard, and with that came the spaces in column names - which Access never had a problem with in the first place.

For me, having to put "[" and "]" around a column name, because it contained a space/punctuation character, would be incentive enough not to use spaces!

When we mechanically generate code we only wrap columns with "[" & "]" if the column name contains non alpha-numeric (plus "_") characters, because I find their inclusion peppered in the code at every opportunity!! makes reading the code so much harder (SSMS code-generation is inclined to chuck "[" & "]" around everything "just in case")

e.g.

SELECT @SQLCommand = 
    CASE WHEN MyColumnName LIKE '%[^A-Z0-9_]%' 
        THEN QuoteName(MyColumnName) 
        ELSE MyColumnName  
        END
etc.

#13

We do something similar when we generate code but augment it to look through a table of SQL keywords and wrap those as well. BOL supplies the original set of keywords in the table.


#14

James:

I just realized there are 3 lines that are returning erroneously because the calculated ship date is incorrect. Is there another PARTITION field I should add to refine the results more? I have SO and SO Line Item.

Thanks.


#15

If ordering by ship date is giving you the wrong row, look at what else you should be ordering by to get the correct row. Why is it wrong? Is it that there is more than one row with the same ship date for a given SO No? If that is the case, look at those three rows and find another column that would make the ordering such that it will give you the row you want. For example, something like this:

ROW_NUMBER() OVER (PARTITION BY [SO No] ORDER BY [Ship Dt] DESC, [SO Line Item] ) AS RN


#16

That's what I think I'm doing, I have tried using different fields but this one return in particular keeps returning the wrong ship date, see attached, it's pulling in the first line item 4/18, when it should be returning 4/29 (last line item)


#17

That's posh!


#18

Looking at your screenshot, assuming you are using the column "fshipdate desc" as the order by column and assuming that the partition column is fsokey, it would return the lat row with the date 4/29/2016.

If it is not, it could be because of any number of reasons - for example, if you fshipdate is the wrong data type (it should be DATE, DATETIME, DATETIME2 or one of the other datetime data types), or perhpas there are hidden characters in the fsokey, or something else.

Without seeing your actual data and the exact query, it is hard to say what the cause is.


#19

I think I need to create a select statement to create a calculated ship date column. Something like:

select...
Max(shmast.fshipdate)
FROM SHMAST
WHERE soitem.fsono = SUBSTRING(SHITEM.FSOKEY, 1, 6)
AND (sorels.fenumber = shitem.fenumber)
AND (SOITEM.FSONO + SOITEM.FQUANTITY = SUBSTRING(SHITEM.FSOKEY, 1, 6) + SHITEM.FORDERQTY)) AS MaxOfShipdate,


#20

You can do that, but I don't think there is a need for that. When you post screenshots, it does not help someone to write a query against it. Also, some hidden characters or other features that you may be aware of would not be picked up by someone who looks at a screenshot.

You can run the following query which is a simulation of your data and see what it does. When you post, if you post this type of DDL (table creation script) and sample data, that would be most useful.

CREATE TABLE #shmast(fshipdate DATETIME,fsokey VARCHAR(32));
INSERT INTO #shmast
        ( fshipdate, fsokey )
VALUES
('20160418','024576 8000'),
('20160425','024576 8000'),
('20160425','024576 8000'),
('20160428','024576 8000'),
('20160428','024576 8000'),
('20160429','024576 8000');


;WITH cte1 AS
(
	--------- BEGIN: YOUR EXISTING QUERY --------------
	SELECT * FROM #shmast
	-- OMIT THE ORDER BY CLAUSE IF YOU HAVE ANY
	--------- END: YOUR EXISTING QUERY --------------

),

------------ SECOND CTE --------------------

cte2 AS
(
	SELECT *,
		ROW_NUMBER() OVER (PARTITION BY fsokey ORDER BY fshipdate DESC) AS RN
	FROM
		cte1
)
SELECT
	*
FROM
	cte2 
WHERE
	RN = 1