Output max date from a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Output max date from a query

Author

Topic

blund
Starting Member

23 Posts

Posted - 2012-02-08 : 15:58:36

I have the following query which I am using to get a list of dates (sysmodified) for each company (cmp_name) and for only the specific "Type"'s of correspondence listed in the query.

SELECT cicmpy.cmp_name, Absences.sysmodified

FROM cicmpy INNER JOIN
Absences ON cicmpy.cmp_wwn = Absences.CustomerID INNER JOIN
AbsenceTypes ON Absences.Type = AbsenceTypes.ID

WHERE (Absences.Type = 70) OR
(Absences.Type = 201) OR
(Absences.Type = 203)
ORDER BY cicmpy.cmp_name

Now, I would like to output the "cicmpy.cmp_name" if there is none of these specific "Type"'s associated with it, and if there is, then show company and date if the maximum "sysmodified" in that query/filtered group of "Type"'s is more than 1 year ago.

Essentially, I would like to show all companies that have not have had any of the specific correspondence, what so ever, and also the companies that have not had any of the specific correspondence in the last year.

How should I create or add code for this? Would this need to be 2 queries, or can it be combined into 1 one?

Also, this will be going into a SSRS report, if there happens to be a better option to do this in the report.
Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-08 : 16:15:50

[code]
SELECT cicmpy.cmp_name,
CASE WHEN MAX(Absences.sysmodified) IS NULL THEN 'No Correspondence' ELSE 'No specific correspondence' END
FROM cicmpy
LEFT JOIN Absences ON cicmpy.cmp_wwn = Absences.CustomerID
LEFT JOIN AbsenceTypes ON Absences.Type = AbsenceTypes.ID
AND Absences.Type IN (70,201,203)
GROUP BY cicmpy.cmp_name
ORDER BY cicmpy.cmp_name
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

blund
Starting Member

23 Posts

Posted - 2012-02-08 : 16:26:20

Thank you,
One more thing, how can I include the option of outputing "No specific correspondence" when the sysmodified date is more than 1 year ago.
I assume I can use something like "getdate()-365", but not sure how to work it into the code.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-08 : 16:30:56

[code]
SELECT cicmpy.cmp_name,
CASE WHEN MAX(Absences.sysmodified) IS NULL THEN 'No Correspondence' WHEN MAX(Absences.sysmodified) > getdate()-365 THEN 'No specific correspondence' END
FROM cicmpy
LEFT JOIN Absences ON cicmpy.cmp_wwn = Absences.CustomerID
LEFT JOIN AbsenceTypes ON Absences.Type = AbsenceTypes.ID
AND Absences.Type IN (70,201,203)
GROUP BY cicmpy.cmp_name
ORDER BY cicmpy.cmp_name
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

blund
Starting Member

23 Posts

Posted - 2012-02-08 : 16:40:23

Thank you once again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-08 : 21:32:32

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources