April 14, 2014 at 9:00 am
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
April 14, 2014 at 9:24 am
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
April 14, 2014 at 9:44 am
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
April 14, 2014 at 9:51 am
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
April 14, 2014 at 9:51 am
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)
April 14, 2014 at 10:13 am
jbalbo (4/14/2014)
I think I need something like thisBut 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 14, 2014 at 10:35 am
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
April 14, 2014 at 11:10 am
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
April 14, 2014 at 11:51 am
jbalbo (4/14/2014)
so this gives me the Client ID and the max Starttime for the client id using now()-80what 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 14, 2014 at 12:03 pm
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_IDMaxDate
22011-10-14 16:25:00.000
42012-02-14 11:00:00.000
52012-04-02 09:00:00.000
62012-02-15 11:00:00.000
72011-08-08 11:00:00.000
82011-04-29 14:15:00.000
92012-06-28 14:15:00.000
102011-05-26 08:45:00.000
112013-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.....
April 14, 2014 at 12:11 pm
That does NOT help me understand what you want. I can't see your screen, I have no idea what datatypes are in your table, I have no idea what values are in the columns. In short, without ddl and sample data I am shooting in the dark at unknown targets for an unknown reason. The fact that I was able to come close in a previous post was mostly dumb luck.
Add to that the strange syntax you keep throwing.
< { fn NOW() } - 80)
That is NOT t-sql and I am not sure what it really is. I am also pretty sure it is not accurate because if you store the timestamp you are missing a portion of the day.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 14, 2014 at 12:48 pm
Sean Lange (4/14/2014)
That does NOT help me understand what you want. I can't see your screen, I have no idea what datatypes are in your table, I have no idea what values are in the columns. In short, without ddl and sample data I am shooting in the dark at unknown targets for an unknown reason. The fact that I was able to come close in a previous post was mostly dumb luck.Add to that the strange syntax you keep throwing.
< { fn NOW() } - 80)
That is NOT t-sql and I am not sure what it really is. I am also pretty sure it is not accurate because if you store the timestamp you are missing a portion of the day.
It is the ODBC Conical function equivalent of GETDATE() in SQL Server. It works, but unless you have had the opportunity to actually work with it, it looks wrong. I do agree, however, that if you are working with T-SQL that you should use T-SQL functions.
April 15, 2014 at 7:40 am
Thanks for the info...
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) < (GETDATE() - 80))
ORDER BY rsh.Client_ID, MaxDate DESC
so the above gets what I need, which is the by client id the rs.starttime having a max date of today - 80
The output looks like
Client ID Max Date
002 2011-10-14 16:25:00.000
etc....
What I'd like to add is the field rs.OID the goes along with the rs.starttime(Max Date)
Does that make any more sense?
Thanks
April 15, 2014 at 8:09 am
Still nothing solid to work with...why can't you just create ddl and sample data? It isn't that tough.
Is it possibly this simple? You might end up with more than 1 row where the date is within the last 80 days. If accuracy of 80 days is important your code is not accurate because of time values. Also, you really should use DATEADD instead of assuming that is the default for dates.
SELECT
rsh.Client_ID, MAX(rs.STARTTIME) AS MaxDate, rs.OID
FROM RECORDED_SERVICE rs
INNER JOIN Recorded_Service_Helper rsh ON rs.OID = rsh.Recorded_Service_OID
GROUP BY rsh.Client_ID, rs.OID
HAVING(MAX(rs.STARTTIME) < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), -80))
ORDER BY rsh.Client_ID, MaxDate DESC
Lynn from this thread has an awesome post on his blog about date time routines. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 15, 2014 at 8:21 am
Thanks for the info..
Sorry Im not in here all the time and although I'm sure its not tough just not sure how to create the ddl
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply