June 2, 2017 at 1:37 pm
Hello Everyone,
I am trying to work with this query to return the latest date (in this case it is userDate2):
SELECT COUNTY.CountyName,MAX(ORDERS.UserDate2) AS LastCovered, FSLog.CompletedDate, property.State
FROM ORDERS
INNER JOIN property property ON (property.GFNo = ORDERS.GFNo)
INNER JOIN COUNTY COUNTY ON (COUNTY.CountyCode = property.County)
INNER JOIN FSLog FSLog ON (FSLog.GFNo = ORDERS.GFNo)
INNER JOIN FSSetup FSSetup ON (FSSetup.IDNum = FSLog.IDNum)
WHERE (( FSLog.IDNum IN (186,183,189) )
AND ( FSLog.CompletedDate IS NOT NULL ))
AND ( ORDERS.Status NOT IN ('Cancelled','Template') )
AND ( ORDERS.OpenDate > '2011/01/01' )
GROUP BY Property.State,County.CountyName,ORDERS.UserDate2,FsLog.CompletedDate
ORDER BY property.State,COUNTY.CountyName, ORDERS.UserDate2 DESC,FSLog.CompletedDate DESC
here is a sample of my returned data:
Column1 Column2 Column3
Allegheny 2016-06-17 00:00:00.000 2016-06-23 11:01:05.000
Allegheny 2016-05-01 00:00:00.000 2016-05-25 16:54:09.000
Allegheny 2014-12-26 00:00:00.000 2015-01-19 11:49:08.000
Allegheny 2013-09-30 00:00:00.000 2013-10-22 11:23:38.000
Allegheny 2013-09-30 00:00:00.000 2013-10-22 11:21:01.000
Atlantic 2017-03-08 00:00:00.000 2017-03-14 13:37:19.000
Atlantic 2016-09-14 00:00:00.000 2016-09-26 11:51:01.000
Berks 2015-10-20 00:00:00.000 2015-11-03 15:51:48.000
Berks 2014-12-05 00:00:00.000 2014-12-29 07:16:48.000
Blair 2016-06-25 00:00:00.000 2016-07-22 11:28:05.000
Blair 2016-01-07 00:00:00.000 2016-01-20 15:34:30.000
So we need to concentrate on column 2. what I want is the record with the most recent date in column 2:
Allegheny 2016-06-17 00:00:00.000 2016-06-23 11:01:05.000
Atlantic 2017-03-08 00:00:00.000 2017-03-14 13:37:19.000
Berks 2015-10-20 00:00:00.000 2015-11-03 15:51:48.000
Blair 2016-06-25 00:00:00.000 2016-07-22 11:28:05.000
im not worried about optimizing the code right now, but if anyone could help me tweak it to just get the most recent record according to column2 that would be great!
Thanks!
Matt
June 2, 2017 at 1:43 pm
I know how but don't know exactly how in your situation. We could use the DDL (CREATE TABLE statement) for the table(s) involved, sample data for each of the table(s) as INSERT INTO statements.
Barring that, look at writing a CTE and use rn = ROW_NUMBER() OVER (PARTITION BY ... ORDER BY UserDate2 DESC) and then select data in the outer query where rn = 1.
June 2, 2017 at 1:54 pm
This should do the trick...
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;
CREATE TABLE #TestData (
Column1 VARCHAR(20) NOT NULL,
Column2 DATETIME NOT NULL,
Column3 DATETIME NOT NULL
);
INSERT #TestData (Column1, Column2, Column3) VALUES
('Allegheny', '2016-06-17 00:00:00.000', '2016-06-23 11:01:05.000'),
('Allegheny', '2016-05-01 00:00:00.000', '2016-05-25 16:54:09.000'),
('Allegheny', '2014-12-26 00:00:00.000', '2015-01-19 11:49:08.000'),
('Allegheny', '2013-09-30 00:00:00.000', '2013-10-22 11:23:38.000'),
('Allegheny', '2013-09-30 00:00:00.000', '2013-10-22 11:21:01.000'),
('Atlantic', '2017-03-08 00:00:00.000', '2017-03-14 13:37:19.000'),
('Atlantic', '2016-09-14 00:00:00.000', '2016-09-26 11:51:01.000'),
('Berks', '2015-10-20 00:00:00.000', '2015-11-03 15:51:48.000'),
('Berks', '2014-12-05 00:00:00.000', '2014-12-29 07:16:48.000'),
('Blair', '2016-06-25 00:00:00.000', '2016-07-22 11:28:05.000'),
('Blair', '2016-01-07 00:00:00.000', '2016-01-20 15:34:30.000');
WITH
cte_DistinctNames AS (
SELECT DISTINCT
td.Column1
FROM
#TestData td
WHERE
td.Column2 > '2011-01-01'
)
SELECT
dn.Column1,
tdx.Column2,
tdx.Column3
FROM
cte_DistinctNames dn
CROSS APPLY (
SELECT TOP 1
td.Column2,
td.Column3
FROM
#TestData td
WHERE
dn.Column1 = td.Column1
-- AND other filters...
ORDER BY
td.Column2 DESC
) tdx;
June 2, 2017 at 1:56 pm
would this work:
SELECT DISTINCT COUNTY.CountyName,
MAX(ORDERS.UserDate2) OVER (PARTITION BY CountyName) AS LastCovered,
MAX(FSLog.CompletedDate) OVER (PARTITION BY CountyName) as CompletedDate,
property.State
FROM ORDERS
INNER JOIN property property ON (property.GFNo = ORDERS.GFNo)
INNER JOIN COUNTY COUNTY ON (COUNTY.CountyCode = property.County)
INNER JOIN FSLog FSLog ON (FSLog.GFNo = ORDERS.GFNo)
INNER JOIN FSSetup FSSetup ON (FSSetup.IDNum = FSLog.IDNum)
WHERE (( FSLog.IDNum IN (186,183,189) )
AND ( FSLog.CompletedDate IS NOT NULL ))
AND ( ORDERS.Status NOT IN ('Cancelled','Template') )
AND ( ORDERS.OpenDate > '2011/01/01' )
ORDER BY property.State,COUNTY.CountyName, ORDERS.UserDate2 DESC,FSLog.CompletedDate DESC
Looking at it, I think that should work? Using a CTE as Lynn posted should work as well.
But, aslo as Lynn indicated, without seeing the acutal data, it is tricky to know what will and won't work. The query you posted returns 4 columns, your result only shows 3. The above is making the assumption that the row with the max for column 2 is also going to have the max for column 3. With the sample data you provided, this is the case, but I don't know if that is ALWAYS the case. If this is not the case, then a CTE will be your only option.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 5, 2017 at 4:24 am
bmg002 - Friday, June 2, 2017 1:56 PMwould this work:SELECT DISTINCT COUNTY.CountyName,
MAX(ORDERS.UserDate2) OVER (PARTITION BY CountyName) AS LastCovered,
MAX(FSLog.CompletedDate) OVER (PARTITION BY CountyName) as CompletedDate,
property.StateFROM ORDERS
INNER JOIN property property ON (property.GFNo = ORDERS.GFNo)INNER JOIN COUNTY COUNTY ON (COUNTY.CountyCode = property.County)
INNER JOIN FSLog FSLog ON (FSLog.GFNo = ORDERS.GFNo)
INNER JOIN FSSetup FSSetup ON (FSSetup.IDNum = FSLog.IDNum)
WHERE (( FSLog.IDNum IN (186,183,189) )
AND ( FSLog.CompletedDate IS NOT NULL ))
AND ( ORDERS.Status NOT IN ('Cancelled','Template') )
AND ( ORDERS.OpenDate > '2011/01/01' )
ORDER BY property.State,COUNTY.CountyName, ORDERS.UserDate2 DESC,FSLog.CompletedDate DESC
Looking at it, I think that should work? Using a CTE as Lynn posted should work as well.
But, aslo as Lynn indicated, without seeing the acutal data, it is tricky to know what will and won't work. The query you posted returns 4 columns, your result only shows 3. The above is making the assumption that the row with the max for column 2 is also going to have the max for column 3. With the sample data you provided, this is the case, but I don't know if that is ALWAYS the case. If this is not the case, then a CTE will be your only option.
Well, I tried this and even though it returned only 1 county at a time, it came up with really erroneous dates:Allegheny 3015-03-09 00:00:00.000 2017-06-02 16:13:48.000
Atlantic 2017-10-10 00:00:00.000 2017-06-02 20:00:41.000
Berks 2017-05-19 00:00:00.000 2017-06-02 13:22:04.000
Blair 2017-05-04 00:00:00.000 2017-05-18 17:23:42.000
June 5, 2017 at 5:34 am
Jason A. Long - Friday, June 2, 2017 1:54 PMThis should do the trick...
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;CREATE TABLE #TestData (
Column1 VARCHAR(20) NOT NULL,
Column2 DATETIME NOT NULL,
Column3 DATETIME NOT NULL
);INSERT #TestData (Column1, Column2, Column3) VALUES
('Allegheny', '2016-06-17 00:00:00.000', '2016-06-23 11:01:05.000'),
('Allegheny', '2016-05-01 00:00:00.000', '2016-05-25 16:54:09.000'),
('Allegheny', '2014-12-26 00:00:00.000', '2015-01-19 11:49:08.000'),
('Allegheny', '2013-09-30 00:00:00.000', '2013-10-22 11:23:38.000'),
('Allegheny', '2013-09-30 00:00:00.000', '2013-10-22 11:21:01.000'),
('Atlantic', '2017-03-08 00:00:00.000', '2017-03-14 13:37:19.000'),
('Atlantic', '2016-09-14 00:00:00.000', '2016-09-26 11:51:01.000'),
('Berks', '2015-10-20 00:00:00.000', '2015-11-03 15:51:48.000'),
('Berks', '2014-12-05 00:00:00.000', '2014-12-29 07:16:48.000'),
('Blair', '2016-06-25 00:00:00.000', '2016-07-22 11:28:05.000'),
('Blair', '2016-01-07 00:00:00.000', '2016-01-20 15:34:30.000');WITH
cte_DistinctNames AS (
SELECT DISTINCT
td.Column1
FROM
#TestData td
WHERE
td.Column2 > '2011-01-01'
)
SELECT
dn.Column1,
tdx.Column2,
tdx.Column3
FROM
cte_DistinctNames dn
CROSS APPLY (
SELECT TOP 1
td.Column2,
td.Column3
FROM
#TestData td
WHERE
dn.Column1 = td.Column1
-- AND other filters...
ORDER BY
td.Column2 DESC
) tdx;
This query works as intended using the hardcoded values, so how can I replace the hardcoded values with my select statement?
June 5, 2017 at 6:03 am
Replace the
FROM
#TestData td
with the real tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 5, 2017 at 7:18 am
SELECT
COUNTY.CountyName,
property.State.
x.UserDate2 AS LastCovered,
x.CompletedDate
FROM COUNTY COUNTY
INNER JOIN property property
ON COUNTY.CountyCode = property.County
CROSS APPLY (
SELECT TOP(1)
ORDERS.UserDate2, FSLog.CompletedDate
FROM ORDERS ORDERS
INNER JOIN FSLog FSLog
ON FSLog.GFNo = ORDERS.GFNo
WHERE ORDERS.GFNo = property.GFNo
AND FSLog.IDNum IN (186,183,189)
AND FSLog.CompletedDate IS NOT NULL
AND ORDERS.Status NOT IN ('Cancelled','Template')
AND ORDERS.OpenDate > '2011/01/01'
ORDER BY ORDERS.UserDate2 DESC
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 5, 2017 at 9:26 am
meichmann - Monday, June 5, 2017 4:24 AMbmg002 - Friday, June 2, 2017 1:56 PMwould this work:SELECT DISTINCT COUNTY.CountyName,
MAX(ORDERS.UserDate2) OVER (PARTITION BY CountyName) AS LastCovered,
MAX(FSLog.CompletedDate) OVER (PARTITION BY CountyName) as CompletedDate,
property.StateFROM ORDERS
INNER JOIN property property ON (property.GFNo = ORDERS.GFNo)INNER JOIN COUNTY COUNTY ON (COUNTY.CountyCode = property.County)
INNER JOIN FSLog FSLog ON (FSLog.GFNo = ORDERS.GFNo)
INNER JOIN FSSetup FSSetup ON (FSSetup.IDNum = FSLog.IDNum)
WHERE (( FSLog.IDNum IN (186,183,189) )
AND ( FSLog.CompletedDate IS NOT NULL ))
AND ( ORDERS.Status NOT IN ('Cancelled','Template') )
AND ( ORDERS.OpenDate > '2011/01/01' )
ORDER BY property.State,COUNTY.CountyName, ORDERS.UserDate2 DESC,FSLog.CompletedDate DESC
Looking at it, I think that should work? Using a CTE as Lynn posted should work as well.
But, aslo as Lynn indicated, without seeing the acutal data, it is tricky to know what will and won't work. The query you posted returns 4 columns, your result only shows 3. The above is making the assumption that the row with the max for column 2 is also going to have the max for column 3. With the sample data you provided, this is the case, but I don't know if that is ALWAYS the case. If this is not the case, then a CTE will be your only option.Well, I tried this and even though it returned only 1 county at a time, it came up with really erroneous dates:
Allegheny 3015-03-09 00:00:00.000 2017-06-02 16:13:48.000
Atlantic 2017-10-10 00:00:00.000 2017-06-02 20:00:41.000
Berks 2017-05-19 00:00:00.000 2017-06-02 13:22:04.000
Blair 2017-05-04 00:00:00.000 2017-05-18 17:23:42.000
The results that the query I provided should be returning would be the max userdate2 and max completedate. But these may not be the same row of data. What I mean is if the data was:
Berks 2017-05-19 00:00:00.000 2017-05-20 00:00:00.000
Berks 2017-05-18 00:00:00.000 2017-06-02 13:22:04.000
the result would be:
Berks 2017-05-19 00:00:00.000 2017-06-02 13:22:04.000
Looking at the sample data you provided, it looked like the max for the userdate2 corresponded with the max of completedate which was why I did it that way.
When you say that it has erroneous data, which of those was wrong? It looks to me like the data with the year 3015 is the erroneous one, but are you sure that that data doesn't exist?
What do you get if you run:SELECT MAX(Orders.UserDate2)
FROM ORDERS
INNER JOIN property property ON (property.GFNo = ORDERS.GFNo)
INNER JOIN COUNTY COUNTY ON (COUNTY.CountyCode = property.County)
INNER JOIN FSLog FSLog ON (FSLog.GFNo = ORDERS.GFNo)
INNER JOIN FSSetup FSSetup ON (FSSetup.IDNum = FSLog.IDNum)
WHERE (( FSLog.IDNum IN (186,183,189) )
AND ( FSLog.CompletedDate IS NOT NULL ))
AND ( ORDERS.Status NOT IN ('Cancelled','Template') )
AND ( ORDERS.OpenDate > '2011/01/01' )
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 5, 2017 at 9:55 am
select * from
(
SELECT COUNTY.CountyName,ORDERS.UserDate2 AS LastCovered, FSLog.CompletedDate, property.State, ROW_NUMBER() over (partition by COUNTY.CountyName order by ORDERS.UserDate2 desc) as ordering
FROM ORDERS
INNER JOIN property property ON (property.GFNo = ORDERS.GFNo)
INNER JOIN COUNTY COUNTY ON (COUNTY.CountyCode = property.County)
INNER JOIN FSLog FSLog ON (FSLog.GFNo = ORDERS.GFNo)
INNER JOIN FSSetup FSSetup ON (FSSetup.IDNum = FSLog.IDNum)
WHERE (( FSLog.IDNum IN (186,183,189) )
AND ( FSLog.CompletedDate IS NOT NULL ))
AND ( ORDERS.Status NOT IN ('Cancelled','Template') )
AND ( ORDERS.OpenDate > '2011/01/01' )
) sub1
where ordering=1
order by
CountyName
June 5, 2017 at 11:24 am
bmg002 - Monday, June 5, 2017 9:26 AMmeichmann - Monday, June 5, 2017 4:24 AMbmg002 - Friday, June 2, 2017 1:56 PMwould this work:SELECT DISTINCT COUNTY.CountyName,
MAX(ORDERS.UserDate2) OVER (PARTITION BY CountyName) AS LastCovered,
MAX(FSLog.CompletedDate) OVER (PARTITION BY CountyName) as CompletedDate,
property.StateFROM ORDERS
INNER JOIN property property ON (property.GFNo = ORDERS.GFNo)INNER JOIN COUNTY COUNTY ON (COUNTY.CountyCode = property.County)
INNER JOIN FSLog FSLog ON (FSLog.GFNo = ORDERS.GFNo)
INNER JOIN FSSetup FSSetup ON (FSSetup.IDNum = FSLog.IDNum)
WHERE (( FSLog.IDNum IN (186,183,189) )
AND ( FSLog.CompletedDate IS NOT NULL ))
AND ( ORDERS.Status NOT IN ('Cancelled','Template') )
AND ( ORDERS.OpenDate > '2011/01/01' )
ORDER BY property.State,COUNTY.CountyName, ORDERS.UserDate2 DESC,FSLog.CompletedDate DESC
Looking at it, I think that should work? Using a CTE as Lynn posted should work as well.
But, aslo as Lynn indicated, without seeing the acutal data, it is tricky to know what will and won't work. The query you posted returns 4 columns, your result only shows 3. The above is making the assumption that the row with the max for column 2 is also going to have the max for column 3. With the sample data you provided, this is the case, but I don't know if that is ALWAYS the case. If this is not the case, then a CTE will be your only option.Well, I tried this and even though it returned only 1 county at a time, it came up with really erroneous dates:
Allegheny 3015-03-09 00:00:00.000 2017-06-02 16:13:48.000
Atlantic 2017-10-10 00:00:00.000 2017-06-02 20:00:41.000
Berks 2017-05-19 00:00:00.000 2017-06-02 13:22:04.000
Blair 2017-05-04 00:00:00.000 2017-05-18 17:23:42.000The results that the query I provided should be returning would be the max userdate2 and max completedate. But these may not be the same row of data. What I mean is if the data was:
Berks 2017-05-19 00:00:00.000 2017-05-20 00:00:00.000
Berks 2017-05-18 00:00:00.000 2017-06-02 13:22:04.000the result would be:
Berks 2017-05-19 00:00:00.000 2017-06-02 13:22:04.000Looking at the sample data you provided, it looked like the max for the userdate2 corresponded with the max of completedate which was why I did it that way.
When you say that it has erroneous data, which of those was wrong? It looks to me like the data with the year 3015 is the erroneous one, but are you sure that that data doesn't exist?
What do you get if you run:SELECT MAX(Orders.UserDate2)
FROM ORDERS
INNER JOIN property property ON (property.GFNo = ORDERS.GFNo)
INNER JOIN COUNTY COUNTY ON (COUNTY.CountyCode = property.County)
INNER JOIN FSLog FSLog ON (FSLog.GFNo = ORDERS.GFNo)
INNER JOIN FSSetup FSSetup ON (FSSetup.IDNum = FSLog.IDNum)
WHERE (( FSLog.IDNum IN (186,183,189) )
AND ( FSLog.CompletedDate IS NOT NULL ))
AND ( ORDERS.Status NOT IN ('Cancelled','Template') )
AND ( ORDERS.OpenDate > '2011/01/01' )
When I ran the query you provided, I got this return:
9016-09-09 00:00:00.000
June 5, 2017 at 1:24 pm
meichmann - Monday, June 5, 2017 11:24 AMbmg002 - Monday, June 5, 2017 9:26 AMmeichmann - Monday, June 5, 2017 4:24 AMbmg002 - Friday, June 2, 2017 1:56 PMwould this work:SELECT DISTINCT COUNTY.CountyName,
MAX(ORDERS.UserDate2) OVER (PARTITION BY CountyName) AS LastCovered,
MAX(FSLog.CompletedDate) OVER (PARTITION BY CountyName) as CompletedDate,
property.StateFROM ORDERS
INNER JOIN property property ON (property.GFNo = ORDERS.GFNo)INNER JOIN COUNTY COUNTY ON (COUNTY.CountyCode = property.County)
INNER JOIN FSLog FSLog ON (FSLog.GFNo = ORDERS.GFNo)
INNER JOIN FSSetup FSSetup ON (FSSetup.IDNum = FSLog.IDNum)
WHERE (( FSLog.IDNum IN (186,183,189) )
AND ( FSLog.CompletedDate IS NOT NULL ))
AND ( ORDERS.Status NOT IN ('Cancelled','Template') )
AND ( ORDERS.OpenDate > '2011/01/01' )
ORDER BY property.State,COUNTY.CountyName, ORDERS.UserDate2 DESC,FSLog.CompletedDate DESC
Looking at it, I think that should work? Using a CTE as Lynn posted should work as well.
But, aslo as Lynn indicated, without seeing the acutal data, it is tricky to know what will and won't work. The query you posted returns 4 columns, your result only shows 3. The above is making the assumption that the row with the max for column 2 is also going to have the max for column 3. With the sample data you provided, this is the case, but I don't know if that is ALWAYS the case. If this is not the case, then a CTE will be your only option.Well, I tried this and even though it returned only 1 county at a time, it came up with really erroneous dates:
Allegheny 3015-03-09 00:00:00.000 2017-06-02 16:13:48.000
Atlantic 2017-10-10 00:00:00.000 2017-06-02 20:00:41.000
Berks 2017-05-19 00:00:00.000 2017-06-02 13:22:04.000
Blair 2017-05-04 00:00:00.000 2017-05-18 17:23:42.000The results that the query I provided should be returning would be the max userdate2 and max completedate. But these may not be the same row of data. What I mean is if the data was:
Berks 2017-05-19 00:00:00.000 2017-05-20 00:00:00.000
Berks 2017-05-18 00:00:00.000 2017-06-02 13:22:04.000the result would be:
Berks 2017-05-19 00:00:00.000 2017-06-02 13:22:04.000Looking at the sample data you provided, it looked like the max for the userdate2 corresponded with the max of completedate which was why I did it that way.
When you say that it has erroneous data, which of those was wrong? It looks to me like the data with the year 3015 is the erroneous one, but are you sure that that data doesn't exist?
What do you get if you run:SELECT MAX(Orders.UserDate2)
FROM ORDERS
INNER JOIN property property ON (property.GFNo = ORDERS.GFNo)
INNER JOIN COUNTY COUNTY ON (COUNTY.CountyCode = property.County)
INNER JOIN FSLog FSLog ON (FSLog.GFNo = ORDERS.GFNo)
INNER JOIN FSSetup FSSetup ON (FSSetup.IDNum = FSLog.IDNum)
WHERE (( FSLog.IDNum IN (186,183,189) )
AND ( FSLog.CompletedDate IS NOT NULL ))
AND ( ORDERS.Status NOT IN ('Cancelled','Template') )
AND ( ORDERS.OpenDate > '2011/01/01' )When I ran the query you provided, I got this return:
9016-09-09 00:00:00.000
To me it looks like your UserDate2 column either has strange data in it or bad data in it. I have an odd feeling that you aren't expecting to have a UserDate2 value for September 9th, 9016, correct?
You may need to do some data cleanup if values that far in the future are incorrect.
It looks to me like the erroneous data that you saw with the query I provided was actually just bad-data that was entered by an end user or tool. Might want to talk to the owner of the data to ensure that the data is accurate before you try building the query. Otherwise how can you ensure that the results you are getting are accurate?
What you could try running to get a list of all of the potentially incorrectly entered data would be something like:
SELECT DISTINCT TOP 25 COUNTY.CountyName,
ORDERS.UserDate2
FSLOG.CompletedDate
property.State
FROM ORDERS
INNER JOIN property property ON (property.GFNo = ORDERS.GFNo)
INNER JOIN COUNTY COUNTY ON (COUNTY.CountyCode = property.County)
INNER JOIN FSLog FSLog ON (FSLog.GFNo = ORDERS.GFNo)
INNER JOIN FSSetup FSSetup ON (FSSetup.IDNum = FSLog.IDNum)
WHERE (( FSLog.IDNum IN (186,183,189) )
AND ( FSLog.CompletedDate IS NOT NULL ))
AND ( ORDERS.Status NOT IN ('Cancelled','Template') )
AND ( ORDERS.OpenDate > '2011/01/01' )
ORDER BY ORDERS.UserDate2 DESC
That will show you the 25 County Name, UserDate2, CompletedDate and State ordered by the UserDate2 descending (ie newest first). So this way you can see which county and state and completed date correspond with that September 9th, 9016 (plus the other 24 that could be erroneous) UserDate2. If you see the 25 results are all erroneous, increase the 25 in the "TOP 25" to a larger number until you start getting "good" data again.
The other thing you could do is restrict the year of UserDate2. So add this to the WHERE clause:
AND YEAR(UserDate2) <= YEAR(GETDATE())
This will ignore any results where UserDate2 is later than the current year.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 7, 2017 at 12:04 pm
Thanks everyone for your help and input. Unfortunately I couldn't get the results exactly as I needed. The query was for a SSRS report, so I left the query alone and let the report sort it out. I know it's kind of a fopah to let SSRS do the work but in this case it was my only option.
Thanks again everyone!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply