Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

?? on get info when last date was today - 80 ?? Expand / Collapse
Author
Message
Posted Monday, April 14, 2014 12:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 13,085, Visits: 12,549
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)
Post #1561623
Posted Monday, April 14, 2014 12:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 3:27 PM
Points: 20,734, Visits: 32,505
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.



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)
Post #1561640
Posted Tuesday, April 15, 2014 7:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498
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
Post #1561874
Posted Tuesday, April 15, 2014 8:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 13,085, Visits: 12,549
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)
Post #1561888
Posted Tuesday, April 15, 2014 8:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498
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
Post #1561896
Posted Tuesday, April 15, 2014 8:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 13,085, Visits: 12,549
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)
Post #1561899
Posted Tuesday, April 15, 2014 10:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498
it did.. thank you

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

Thanks Again
Post #1561975
Posted Tuesday, April 15, 2014 11:14 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 3:27 PM
Points: 20,734, Visits: 32,505
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.



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)
Post #1561995
Posted Tuesday, April 15, 2014 12:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 13,085, Visits: 12,549
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)
Post #1562010
Posted Tuesday, April 15, 2014 3:08 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498
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
Post #1562058
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse