Multiple Records / Max Date with a Twist
-
I have an interesting one here. I have a table of Purchase Order Item information where if there is a changed to a line item, rather than updating the current POI line a brand new line is created with the same line item number.
So for example PO A has lines 1,2,3,4 added when it was initially created on 01/01/11. it looks like this:
The user then changes two lines, lines 2 and 4 are modified and now the Date Last Changed is 01/05/11. The table now looks like this:
I need to grab the line in the table with the latest 'PO Item Date Last Changed'. I am able to successfully do this, however what I have found is that in this particular data set (which comes in via an AS400 DB) when there is no 'Date Last Changed' because this was a new line the system defaults to '9999999'. So whenever I run this query I always get '9999999' as my max.
I cannot simply remove the '9999999' or filter them out because I still want to capture lines where there were NO changes and therefore no additional line entry with a 'Date Last Changed' column populated with an actual date. Any ideas on how to handle this?
Thanks in Advance.
use TestDB
SELECT A.OrdN03
, A.Line03
, A.DtLc03
FROM dbo.JBA_POLineItems A
INNER JOIN
(
SELECT dbo.JBA_POLineItems.OrdN03
, dbo.JBA_POLineItems.Line03
, MAX(dbo.JBA_POLineItems.DtLc03) AS 'LastDate'
FROM dbo.JBA_POLineItems
--WHERE /*dbo.JBA_POLineItems.DtLc03 <> 9999999 AND*/ dbo.JBA_POLineItems.OrdN03 = 'PO12692'
GROUP BY dbo.JBA_POLineItems.OrdN03, dbo.JBA_POLineItems.Line03
) B
ON A.OrdN03 = B.OrdN03 AND A.Line03 = B.Line03 AND A.DtLc03 = B.LastDate
-
try using CAST(ISNULL(NullIF(dtDate, '999999'), '1/1/1900') AS DATETIME) for your selection, then use CASE WHEN ISNULL(dtdate,'1/1/1900') = '1/1/1900' THEN '99999' ELSE dtdate END
Since I don't know your DDL and such, this is the best I can do.
SELECT A.OrdN03
, A.Line03
, A.DtLc03
FROM dbo.JBA_POLineItems A
INNER JOIN
(
SELECT dbo.JBA_POLineItems.OrdN03
, dbo.JBA_POLineItems.Line03
, MAX(CAST(ISNULL(NULLIF(dbo.JBA_POLineItems.DtLc03, '9999999'), '1/1/1900') AS DATETIME)) AS 'LastDate'
FROM dbo.JBA_POLineItems
--WHERE /*dbo.JBA_POLineItems.DtLc03 <> 9999999 AND*/ dbo.JBA_POLineItems.OrdN03 = 'PO12692'
GROUP BY dbo.JBA_POLineItems.OrdN03, dbo.JBA_POLineItems.Line03
) B
ON A.OrdN03 = B.OrdN03 AND A.Line03 = B.Line03 AND A.DtLc03 = (CASE WHEN ISNULL(B.LastDate, '1/1/1900') = '1/1/1900' THEN '99999' ELSE B.LastDate END)
edit: to correct NULLIF
edit2: added code
-
You don't need to supply a real datetime for the 99999999. Min() and Max() will only return NULL if all of the values are NULL, but that's what you want anyhow.
Also, since you're using the same table in your main query and subquery, you might be better off using a CTE with the Row_Number() function so that you don't have to read the table twice.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
This is good example of two very common problems with the way people use sql. First of all, you are storing two types of data in a single column. You are storing a string and a datetime value in the same column. The second issue is that you are storing datetime data as a string. This is fraught with errors and is one of the more common issues encountered by people. This column should be a datetime with a default of null. It sounds like you at least have been given a way to work around the design but imho you should see if you can fix the problem instead or treading softly around it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Hello,
Thank you for the replies. I will attempt the methods provided.
I have to apologize, I should have included that in my initial post that the data sets are being put into SQL Server on an as needed basis. This is not a transactional database. There is no one here any longer that is proficient enough on this AS/400 server to do this via it's originating database.
With that said, since these tables are temp tables we typically take the data type that the import provides us. For instance in the case of the date it is stored as a float value and comes over as such when imported. The dates are actually stored in the originating database as a Julian type date (CYYMMDD) where C=century, YY=Year,MM=Month and DD=Day. So all dates come over as a float into the SSDB as float data type.
I am actually creating an additional column which is an nvarchar data type and storing that date as a string, so that I can convert it into the following format in the resultant data set (MM/DD/YY).
I apologize, my screen shots do not indicate exactly what is going on in the table as accurately as I thought, there is that additional set I left out. We are not storing multiple data types in one column.
Thanks for the help and any more suggestions would be greatly appreciated.
Rick
-
Okay, so what I did was a Max() CASE WHEN the Date Last received=9999999 I change it to '999' that way it will be lower than any other valid date and I can still evaluate on it. This did work only pulling 1 line entry for each line and that entry being the one with the latest Date Last Received.
However, when I add the remaining qty columns to the query and run it, I end up getting all lines again. This does not make any sense to me. Below is the query and the commented portions are the portions that I added AFTER I got the correct results which caused me to get erroneous results again. Any suggestions on what is going on here.
SELECT POI2.OrdN03
, POI2.Line03
, POI2.CoNo03
, POI2.Item03
/*
, POI2.QtOs03
, POI2.DtLc03
, POI2.DtLc03_Fmt
, POI2.DueD03
, POI2.OQty03
, POI2.QRec03*/
, MAX(CASE WHEN POI2.DtLc03=9999999 THEN 999 ELSE POI2.DtLc03 END) AS 'LastDate'
FROM dbo.JBA_POLineItems POI2
WHERE POI2.OrdN03 = 'P012692'
GROUP BY POI2.OrdN03, POI2.Line03, POI2.Item03, POI2.CoNo03
/*
, POI2.QtOs03, POI2.DtLc03
, POI2.DtLc03_Fmt
, POI2.DueD03
, POI2.OQty03
, POI2.QRec03
*/
As soon as I uncomment the commented SQL, I then get ALL lines again.
-
At least one of those fields has data that is not the same across all the records for that group.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Well, Dt Last Received across each line will be different because that is where the deltas are in these rows, but I need that column as I need to display that to the user so I am using it in my primary query. How can I get around this?
-
It makes sense that it can't be in a group if you think about it. Which value should be in the group?
To have your query return only one row for each group you would have to make this an aggregate of some sort. Do you want the most recent one [Max()], the first one [Min()]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
As I suggested earlier, you probably want to use a CTE with Row_Number()
WITH POLineItems AS (
SELECT A.OrdN03
, A.Line03
, A.DtLc03
, Row_Number() OVER( PARTITION BY a.OrdN03, a.Line03 ORDER BY NULLIF(a.DtLc03, '99999999') DESC) AS rn
FROM dbo.JBA_POLineItems A
)
SELECT li.OrdN03
, li.Line03
, DtLc03 AS LastDate
FROM POLineItems AS li
WHERE li.rn = 1
AND dbo.JBA_POLineItems.OrdN03 = 'PO12692'
This code is completely untested, because you haven't supplied easily digestible sample data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
You can't get all the values in the same select as you get the max, otherwise the max is the max for a set of all the values so you get all the rows. You have to do th emax in a separate select that looks only at the rows that you want to be fixed to determine the set you want the max from.
So use code something like this:-
With getmax as (
select POI2.OrdN03
, POI2.Line03
, POI2.CoNo03
, POI2.Item03
, Max(case when POI2.DtLc03=9999999 then 999 else POI2.DtLc03 end) as lastdate
FROM dbo.JBA_POLineItems POI2
GROUP BY OrdN03, Line03,CoNo03,Item03
)
select POI2.OrdN03
, POI2.Line03
, POI2.CoNo03
, POI2.Item03
, POI2.QtOs03
, POI2.DtLc03
, POI2.DtLc03_Fmt
, POI2.DueD03
, POI2.OQty03
, POI2.QRec03
from dbo.JBA_POLineItems POI2
inner join getmax
on getmax.OrdN03 = POI2.OrdN03
and getmax.Line03 = POI2.Line03
and getmax.CoNo03 = POI2.CoNo03
and getmax.Item03 = POI2.Item03
and POI2.DtLc03 = case when getmax.DtLc03 = 999 then 9999999 else getmax.DtLc03 end
I haven't tested this as you have'n provided a script to create the schema; so try it and see what it gets you.
Tom
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply