March 3, 2014 at 10:14 am
Hi
I have two tables I need to get info from
Client
Services
I need to find clients with whose last service > 80 days old
so I know I need something like (DATEDIFF(dd, SERVICE.STARTTIME, GETDATE()) > 80)
But I only want client where the last service was greater than 80 days
not the services greater than 80 days
Any ideas in the right direction would be great
Thanks
Joe
March 3, 2014 at 10:20 am
How about this?
SELECT [columns]
FROM Client c
INNER JOIN Services s ON
c.SomeColumn = s.SomeColumn
WHERE s.START > GETDATE()-80
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 3, 2014 at 10:22 am
jbalbo (3/3/2014)
HiI have two tables I need to get info from
Client
Services
I need to find clients with whose last service > 80 days old
so I know I need something like (DATEDIFF(dd, SERVICE.STARTTIME, GETDATE()) > 80)
But I only want client where the last service was greater than 80 days
not the services greater than 80 days
Any ideas in the right direction would be great
Thanks
Joe
Presumably, there are multiple rows in Services for each row in Client and they're joined by a foreign key. If this is true, you're going to want to SELECT from Client INNER JOIN Services where the MAX service date is more then 80 days old.
This should get you there, but please post DDL and data if you'd like to go further.
March 3, 2014 at 10:25 am
Thanks for the quick response
but that would the get me records older than 80 days
Sorry I may have not explained it correctly, I need clients that the most recent date is older than 80 day old
March 3, 2014 at 10:38 am
Not sure if this helps using another example,
I receive an error on this but it may explain what I need a bit better
SELECT lname
FROM Service INNER JOIN
Provider ON SERVICE.PROVIDER_MONIKER = Provider.OID
WHERE max(DATEDIFF(dd, dbo.RECORDED_SERVICE.STARTTIME, GETDATE())) > 80
March 3, 2014 at 12:37 pm
How about...
SELECT ClientID
--,MAX(ServiceDate) AS LastSvcDate
FROM #Services
GROUP BY ClientID
HAVING MAX(ServiceDate)<DATEADD(d,-80,GETDATE());
Then just join that back to the Client table to get the client info...
March 4, 2014 at 2:01 am
;WITH cte_Client AS (SELECT 1 AS ClientID ,'bob' AS NAME UNION SELECT 2,'john')
,cte_Services AS (SELECT 1 AS ServicesID, 1 AS ClientID, GETDATE() AS ServiceDate UNION select 2,1,DATEADD(DAY,-82,GETDATE()) UNION SELECT 1,2,DATEADD(DAY,-81,GETDATE()))
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (PARTITION BY [c].[ClientID] ORDER BY [c].[ClientID]) AS Numbers,
s.* FROM [cte_Client] [c]
INNER JOIN [cte_Services] ON .[ClientID] = c.[ClientID]
)y
WHERE
y.[Numbers] = 1
AND [y].[ServiceDate] < DATEADD(DAY,-80,GETDATE())
March 7, 2014 at 8:40 am
Sorry for not getting back I've been under the weather ..
I apprecate the help with this..
This is what I have come up with but I dont think it will work with what I need
SELECT client_id, LName, starttime
FROM dbo.RECORDED_SERVICE INNER JOIN
dbo.Provider ON dbo.RECORDED_SERVICE.PRIMARY_PROVIDER_MONIKER = dbo.Provider.OID INNER JOIN
dbo.Recorded_Service_Helper ON dbo.RECORDED_SERVICE.OID = dbo.Recorded_Service_Helper.Recorded_Service_OID
GROUP BY lname, CLIENT_ID, starttime
HAVING MAX(Starttime)<DATEADD(d,-80,GETDATE());
Here is a better explanation of what I really need...
I want to script something that will list client ids that have a MAx starttime of 80 days old
I want to list all the CLient ids by the provider then using DBMail send an email to that provider (which is lname + "@company.org")
then repeat that for each provider
example
send email to joetest@company.org
contains
client id 1
2
3
then
send email to janetest@company.org
contains
client id 11
21
31
etc
ANy ideas in the right direction would be great
Thanks
Joe
March 7, 2014 at 8:48 am
Here is what would greatly help getting you what you are requesting, DDL (CREATE TABLE statement(s)) for the table(s) involved, sample data (a series of INSERT INTO statements (some people are still using SQL Server 2005)) for the table(s) involved, the expected results based on the sample data.
Trying to provide you code without these things just makes anything we provide nothing more than shots in the dark.
March 7, 2014 at 1:37 pm
select * from
(
SELECT [columns], row_number() over (partition by c.id order by s.start desc) rn
FROM Client c
INNER JOIN Services s ON
c.SomeColumn = s.SomeColumn
WHERE s.START > GETDATE()-80
) a where rn=1
March 7, 2014 at 2:08 pm
Perhaps not the most elegant solution, but I think it answers the question you're trying to get at...
SELECT D.*
FROM (
SELECT C.Client
, MAX(S.ServiceDate) AS LastServiceDate
FROM CLIENT C
LEFT JOIN SERVICE S
ON C.somecol = S.somecol
) D
HAVING D.LastServiceDate < GETDATE() - 80
March 7, 2014 at 2:25 pm
Thanks for the info
here is what I Have
DO I need a group by ?
SELECT D.*
FROM (
SELECT C.Client_ID
, MAX(r.starttime) AS LastServiceDate
from RECORDED_SERVICE r INNER JOIN
Provider p ON r.PRIMARY_PROVIDER_MONIKER = p.OID INNER JOIN
Recorded_Service_Helper c ON r.OID = c.Recorded_Service_OID
) D
HAVING D.LastServiceDate < GETDATE() - 80
March 7, 2014 at 2:30 pm
Yup, add a group by... as below - should work I think.
jbalbo (3/7/2014)
Thanks for the infohere is what I Have
DO I need a group by ?
SELECT D.*FROM (
SELECT C.Client_ID
, MAX(r.starttime) AS LastServiceDate
from RECORDED_SERVICE r INNER JOIN
Provider p ON r.PRIMARY_PROVIDER_MONIKER = p.OID INNER JOIN
Recorded_Service_Helper c ON r.OID = c.Recorded_Service_OID
GROUP BY C.Client_ID
) D
HAVING D.LastServiceDate < GETDATE() - 80
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply