SQL Clone
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
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

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

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 Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96337 Visits: 38981
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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1499 Visits: 688
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
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63540 Visits: 17966
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 Modens 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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1499 Visits: 688
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
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63540 Visits: 17966
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 Modens 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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1499 Visits: 688
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 Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96337 Visits: 38981
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
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63540 Visits: 17966
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 Modens 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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1499 Visits: 688
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