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
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: 16675 Visits: 17035
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 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)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24284 Visits: 37991
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
jbalbo
jbalbo
SSC-Addicted
SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)

Group: General Forum Members
Points: 407 Visits: 668
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
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: 16675 Visits: 17035
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/

_______________________________________________________________

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 (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)

Group: General Forum Members
Points: 407 Visits: 668
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
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: 16675 Visits: 17035
jbalbo (4/15/2014)
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


You can read all about how to build it by following the first link in my signature, or the first link in Lynn's signature. The big advantage to you is that by posting ddl and sample data in a consumable format you will get tested, accurate and fast code.

Did the last guess at code I post help?

_______________________________________________________________

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 (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)

Group: General Forum Members
Points: 407 Visits: 668
it did.. thank you

But it gave me all the OIDS for the client and not just the most recent

Thanks Again
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24284 Visits: 37991
As Sean said, if you post the DDL (CREATE TABLE statement) for the table(s) involved, some sample data (a series of INSERT INTO statements (some people here still use SQL Server 2005)) for the tables involved, and the expected results based on the sample data provided you will get better answers to your questions plus the bonus of tested code.

If you don't know how to do this then please read the article that Sean also told you about. You can get to it using the first link in my signature block. It will show you what you need to post and how to do it.

Anything less, then you are just going to get shots in the dark that may or may not help you.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
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: 16675 Visits: 17035
Here is my last shot in the dark. This may or may not produce the results you are looking for.


with MySortedRows as
(
SELECT
rsh.Client_ID,
rs.STARTTIME,
rs.OID,
ROW_NUMBER() over (Partition by rsh.ClientID order by rs.StartTime desc) as RowNum
FROM RECORDED_SERVICE rs
INNER JOIN Recorded_Service_Helper rsh ON rs.OID = rsh.Recorded_Service_OID
where rs.STARTTIME < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), -80)
)

select *
from MySortedRows
where RowNum = 1
ORDER BY 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 (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)

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

Thanks for the info, it does what I need, but I HATE the idea that I don't know how to do it

I guess I have a bit of reading....

Thanks Again
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