March 20, 2017 at 12:04 pm
We have a requirement to pull all the records from the last month with max(Date).
Dates:
2017-03-13 09:23:30.000
2017-02-13 09:31:23.000
2017-02-28 11:02:32.000
2017-03-20 11:34:03.000
So, i need records for last month February and should pull only 2017-02-28 11:02:32.000 date records.
I am using the below query to pull the records for single table. It seems working fine with only one but. But, when i join this table with other tables, i am gettting the both dates from February.
select max(submmitedon)
* from osusr_xl1_scorecard
where submittedon between
DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0) and DATEADD(DAY, -(DAY(GETDATE())), GETDATE())
But, when i join this table with other tables, i am gettting the both dates from February.
SELECT l.code AS location_code,
max(scard.submittedon),
--MaxScard.MaxSubmittedon,
--max(scard.submittedon) Maxsubmittedon,
c.label AS Category,
lh.source,
lh.item,
lh.elements,
scitem.result,
scitem.score,
comments,
lh.assessorguidelines,
ou.username AS Created_By,
scard.submittedon AS Create_Date,
NULL AS source_File
FROM osusr_xl1_category c
INNER JOIN osusr_xl1_lhsctemplateitem lh
ON c.id = lh.categoryid
INNER JOIN osusr_xl1_scorecarditem scitem
ON scitem.lhsctemplateitemid = lh.id
INNER JOIN osusr_xl1_scorecard scard
ON scard.id = scitem.scorecardid
INNER JOIN osusr_0ty_scorecardstatus ss
ON scard.scorecardstatusid = ss.id
INNER JOIN osusr_0ty_location l
ON l.id = scard.locationid
INNER JOIN ossys_user ou
ON ou.id = scard.submittedby
--INNER JOIN (SELECT
-- Max(submittedon) AS MaxSubmittedon
-- FROM [osusr_xl1_scorecard]
-- ) MaxScard
-- on maxscard.maxsubmittedon = scard.submittedon
where l.CODE in (10)
and scard.submittedon between
DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0) and DATEADD(DAY, -(DAY(GETDATE())), GETDATE())
Group by l.code ,
--scard.submittedon,
c.label,
lh.source,
lh.item,
lh.elements,
scitem.result,
scitem.score,
comments,
lh.assessorguidelines,
ou.username ,
scard.submittedon
ORDER BY scard.submittedon DESC
Please let me know if i am missing anything!!
March 20, 2017 at 12:26 pm
Break your query down to find issues like this. Start with the table that you filter on. Make sure it gives expected results. Join in one table to it, make sure it gives expected results, etc. Eventually you will get to the join that gets you the unwanted data and you can figure out why it is doing that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 20, 2017 at 2:13 pm
it looks like the problem isn't with your joins, but that you don't seem to compare the value of submmitedon in each row to the maximum value in your date range. I don't think you can do that in one step though, because you can't compare an aggregated value and non-aggregated value directly. It looks like you have a derived table subquery to find that MAX(submmitedon) value but it's missing the date range WHERE clause and is commented out.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply