Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


?? on get info when last date was today - 80 ??


?? on get info when last date was today - 80 ??

Author
Message
jbalbo
jbalbo
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 668
Hi

I have the following below to get the info i need.
Except I only want the records when the most recent recorded service starttime is now()-80

Thanks
Joe

SELECT dbo.Client.ID, dbo.RECORDED_SERVICE.STARTTIME
FROM dbo.Recorded_Service_Helper INNER JOIN
dbo.RECORDED_SERVICE INNER JOIN
dbo.Provider ON dbo.RECORDED_SERVICE.PRIMARY_PROVIDER_MONIKER = dbo.Provider.OID ON
dbo.Recorded_Service_Helper.Recorded_Service_OID = dbo.RECORDED_SERVICE.OID INNER JOIN
dbo.CostCtrGrp ON dbo.Recorded_Service_Helper.CostCtrGrp_OID = dbo.CostCtrGrp.OID INNER JOIN
dbo.CostCenter INNER JOIN
dbo.AGENCY_COST_CENTER ON dbo.CostCenter.OID = dbo.AGENCY_COST_CENTER.COST_CENTER_MONIKER ON
dbo.CostCtrGrp.CostCenter = dbo.AGENCY_COST_CENTER.OID INNER JOIN
dbo.Client ON dbo.Recorded_Service_Helper.Client_OID = dbo.Client.OID
WHERE (dbo.RECORDED_SERVICE.STARTTIME < { fn NOW() } - 80)
ORDER BY dbo.RECORDED_SERVICE.STARTTIME DESC, dbo.Client.ID
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
change this


WHERE (dbo.RECORDED_SERVICE.STARTTIME < { fn NOW() } - 80)




to


WHERE (dbo.RECORDED_SERVICE.STARTTIME < DATEADD(day,-80, GetDATE())



Be aware this will not affect the time.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
jbalbo
jbalbo
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 668
Thanks for the info

But I think I have explained this wrong...

I need clients that their last record on the recorded_Service table is today - 80

if anyone has any examples that would be great

Thanks
Joe
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
You can still use part of what Ive posted, as we don't have access to your data etc its difficult to be specific.



WHERE dbo.RECORDED_SERVICE.STARTTIME Between CONVERT(DATE, DATEADD(day,-80, GetDATE()))
AND CONVERT(DATE, DATEADD(day,-79, GetDATE()))




Alternatively you could do



WHERE CONVERT(DATE,dbo.RECORDED_SERVICE.STARTTIME) = CONVERT(DATE, DATEADD(day,-80, GetDATE()))





The performance may be an issue depending on how big the data set is and you may need to play around with the dates a little.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
jbalbo
jbalbo
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 668
I think I need something like this

But I really have my where clause screwed up !!!


sELECT distinct dbo.RECORDED_SERVICE.OID, MAX(dbo.RECORDED_SERVICE.STARTTIME) AS Expr1, dbo.SERVICE_ITEM.SERVICE_ITEM_DESCRIPTION, dbo.CostCenter.AbbrName,
dbo.Recorded_Service_Helper.Client_ID
FROM dbo.CostCenter INNER JOIN
dbo.AGENCY_COST_CENTER ON dbo.CostCenter.OID = dbo.AGENCY_COST_CENTER.COST_CENTER_MONIKER INNER JOIN
dbo.RECORDED_SERVICE INNER JOIN
dbo.Recorded_Service_Helper ON dbo.RECORDED_SERVICE.OID = dbo.Recorded_Service_Helper.Recorded_Service_OID INNER JOIN
dbo.CostCtrGrp ON dbo.Recorded_Service_Helper.CostCtrGrp_OID = dbo.CostCtrGrp.OID INNER JOIN
dbo.SERVICE_ITEM ON dbo.RECORDED_SERVICE.SERVICE_ITEM_MONIKER = dbo.SERVICE_ITEM.OID ON
dbo.AGENCY_COST_CENTER.OID = dbo.CostCtrGrp.CostCenter


where STARTTIME <
(select MAX(starttime) from RECORDED_SERVICE where OID = RECORDED_SERVICE.OID)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
jbalbo (4/14/2014)
I think I need something like this

But I really have my where clause screwed up !!!


sELECT distinct dbo.RECORDED_SERVICE.OID, MAX(dbo.RECORDED_SERVICE.STARTTIME) AS Expr1, dbo.SERVICE_ITEM.SERVICE_ITEM_DESCRIPTION, dbo.CostCenter.AbbrName,
dbo.Recorded_Service_Helper.Client_ID
FROM dbo.CostCenter INNER JOIN
dbo.AGENCY_COST_CENTER ON dbo.CostCenter.OID = dbo.AGENCY_COST_CENTER.COST_CENTER_MONIKER INNER JOIN
dbo.RECORDED_SERVICE INNER JOIN
dbo.Recorded_Service_Helper ON dbo.RECORDED_SERVICE.OID = dbo.Recorded_Service_Helper.Recorded_Service_OID INNER JOIN
dbo.CostCtrGrp ON dbo.Recorded_Service_Helper.CostCtrGrp_OID = dbo.CostCtrGrp.OID INNER JOIN
dbo.SERVICE_ITEM ON dbo.RECORDED_SERVICE.SERVICE_ITEM_MONIKER = dbo.SERVICE_ITEM.OID ON
dbo.AGENCY_COST_CENTER.OID = dbo.CostCtrGrp.CostCenter


where STARTTIME <
(select MAX(starttime) from RECORDED_SERVICE where OID = RECORDED_SERVICE.OID)


You should get in the habit of aliasing your tables in your query. It makes life about a gazillion time easier. Also, it is rumored that referencing by full name in the select list is going to be deprecated.

Here is what your query looks like as you posted it using aliases.


SELECT distinct rs.OID,
MAX(rs.STARTTIME) AS Expr1,
si.SERVICE_ITEM_DESCRIPTION,
cc.AbbrName,
rsh.Client_ID
FROM dbo.CostCenter cc
INNER JOIN dbo.AGENCY_COST_CENTER acc ON cc.OID = acc.COST_CENTER_MONIKER
INNER JOIN dbo.RECORDED_SERVICE rs --Missing one here
INNER JOIN dbo.Recorded_Service_Helper rsh ON rs.OID = rsh.Recorded_Service_OID
INNER JOIN dbo.CostCtrGrp ccg ON rsh.CostCtrGrp_OID = ccg.OID
INNER JOIN dbo.SERVICE_ITEM si ON rs.SERVICE_ITEM_MONIKER = si.OID
ON dbo.AGENCY_COST_CENTER.OID = dbo.CostCtrGrp.CostCenter --??? What is this???

where STARTTIME < (select MAX(starttime) from RECORDED_SERVICE where OID = RECORDED_SERVICE.OID)



There are some definite issues with the sql you posted. You have a join with no ON clause. You have an extra ON clause at the end. You have an aggregate but no group by. You are using distinct and aggregate in the same query. You have a subquery that I am assuming you want to reference the OID of the inner query but you don't reference it in your subquery.

A complete shot in the dark since we have no idea what these tables are like...


SELECT rs.OID,
MAX(rs.STARTTIME) AS Expr1,
si.SERVICE_ITEM_DESCRIPTION,
cc.AbbrName,
rsh.Client_ID
FROM dbo.CostCenter cc
INNER JOIN dbo.AGENCY_COST_CENTER acc ON cc.OID = acc.COST_CENTER_MONIKER
INNER JOIN dbo.RECORDED_SERVICE rs on rs.OID = cc.OID --Don't know if this is right or not????
INNER JOIN dbo.Recorded_Service_Helper rsh ON rs.OID = rsh.Recorded_Service_OID
INNER JOIN dbo.CostCtrGrp ccg ON rsh.CostCtrGrp_OID = ccg.OID
INNER JOIN dbo.SERVICE_ITEM si ON rs.SERVICE_ITEM_MONIKER = si.OID

where STARTTIME < (select MAX(starttime) from RECORDED_SERVICE rs2 where OID = rs.OID)
--using an alias here we can now reference the outer table to get the value.
group by rs.OID,
si.SERVICE_ITEM_DESCRIPTION,
cc.AbbrName,
rsh.Client_ID



_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
jbalbo
jbalbo
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 668
Hi Sean

Thanks for the info

Sorry having a hard time with grouping...

but after looking at yours I did come up with this which giving me the records I need (I Think so far!!)

BUT.. I need to add rs.OID but keek the same grouping and order ...
Thanks Again

SELECT
rsh.Client_ID, MAX(rs.STARTTIME) AS MaxDate
FROM RECORDED_SERVICE rs
INNER JOIN Recorded_Service_Helper rsh ON rs.OID = rsh.Recorded_Service_OID
GROUP BY rsh.Client_ID
HAVING(MAX(rs.STARTTIME) < { fn NOW() } - 80)
ORDER BY rsh.Client_ID, MaxDate DESC
jbalbo
jbalbo
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 668
so this gives me the Client ID and the max Starttime for the client id using now()-80
what I'd like to add is the RS.OID for that MAX Starttime
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
jbalbo (4/14/2014)
so this gives me the Client ID and the max Starttime for the client id using now()-80
what I'd like to add is the RS.OID for that MAX Starttime


I can help if you need it but I can't really offer much because I have no idea what your table structure is like. Post up some ddl and sample data along with the desired output and we can have a crack at 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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
jbalbo
jbalbo
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 668
sorry for the confusion...

SO this code

SELECT
rsh.Client_ID, MAX(rs.STARTTIME) AS MaxDate
FROM RECORDED_SERVICE rs
INNER JOIN Recorded_Service_Helper rsh ON rs.OID = rsh.Recorded_Service_OID
GROUP BY rsh.Client_ID
HAVING(MAX(rs.STARTTIME) < { fn NOW() } - 80)
ORDER BY rsh.Client_ID, MaxDate DESC

is getting this output ...
Client_ID MaxDate
2 2011-10-14 16:25:00.000
4 2012-02-14 11:00:00.000
5 2012-04-02 09:00:00.000
6 2012-02-15 11:00:00.000
7 2011-08-08 11:00:00.000
8 2011-04-29 14:15:00.000
9 2012-06-28 14:15:00.000
10 2011-05-26 08:45:00.000
11 2013-04-17 14:15:00.000


and the max date on the RS table is 10/14/11 for client ID #2 etc....

What I want to add is the RS.OID that matches the MAXDATE record

SO the output would look like

Client ID Max Date OID
2 2011-10-14 16:25:00.000 283902303
4 2012-02-14 11:00:00.000 904404040 etc.....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search