SQL DATE Recurrance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL DATE Recurrance

Author

Topic

giasone
Starting Member

9 Posts

Posted - 2007-10-16 : 12:07:10

Hi All.

I need to know how to write a query that will give me the results for the first day of every month from the date base.

My data base name is dbo.drive_log and the field containing the
date is time_stamp.

Can anyone help?

Thanks.

Jason.

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 12:08:51

This perhaps?


SELECT *
FROM dbo.drive_log
WHERE DATEPART(Day, time_stamp) = 1

Kristen

giasone
Starting Member

9 Posts

Posted - 2007-10-16 : 13:01:39

Kristen,

That looks like it is headed in the right direction but I am getting multiple occurences for the first of each month, that is, I only want it to happen for one occurance for that day in the month, I am getting different times for each day, 10/1/2003 at 12.00pm then 10/1/2003 at 1.00pm etc- Is it possible to make it so that it only shows me just the one time for each 1st day of the month.

Thank-you

Jason.

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 13:26:32

Well, assuming you wanted to see the Earliest time for each 1st of the month then something like this I expect:


SELECT MIN(time_stamp) AS [MinTime], 
       DATEADD(Day, DATEDIFF(Day, 0, time_stamp), 0) AS [TheMonth]
FROM dbo.drive_log
WHERE DATEPART(Day, time_stamp) = 1
GROUP BY DATEADD(Day, DATEDIFF(Day, 0, time_stamp), 0)

join that back to the table itself to get any other columns you need.

Kristen

giasone
Starting Member

9 Posts

Posted - 2007-10-16 : 13:45:37

Kristen,

You're awesome!

Thank-you!

Jason.

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 13:49:06

"You're awesome!"

I very much doubt it ... but if you want to try explaining that to my wife I would greatly appreciate it

giasone
Starting Member

9 Posts

Posted - 2007-10-16 : 14:29:41

One more question, do you know an easy way that I can bring that saved query into Excel?

Thanks again, (Oh I forgot to mention, I am a fellow Brit, living in Canada working in Detroit)

Jason.

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 14:45:19

"I am a fellow Brit, living in Canada working in Detroit"

That's not a Brit! That sounds more like Hyacinth Bouquet's errant son Sheridan!

"an easy way that I can bring that saved query into Excel?"

Excel can connect directly to a database, and an XLS can have a SQL Query associated with it. Press Refresh and the latest data will be retrieved, and the XLS updated with that data.

Or you can view the results of the query in a Grid (using the SQL Client Tools) and then Cut&Paste to Excel - which is fine for one-off jobs.

Or you can output directly to Excel - more appropriate for scheduled-jobs

Kristen

giasone
Starting Member

9 Posts

Posted - 2007-10-16 : 14:55:43

Look old chap, don't know much about flowers, I will leave that to the queen!

I am curious, this must be your day job right? Doing the sql stuff?

As for the grammar. Brit, living in Canada, working in Detroit, what can you expect but me being confused!

Jason.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-16 : 15:01:52

You can directly link to a table in SQL going thru Data---> Import External Data----> New Database Query. But Excel may not like the SQL code you need.

Jim

P.S. I grew up in Detroit and learned to drink in Canada. Toss me a brewski, eh!

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 15:07:27

"this must be your day job right? Doing the sql stuff?"

Yup. In a nutshell: my companies build web applications, the main one of which builds eCommerce sites which for clients who receive in excess of 1,000 orders a day.

" Look old chap, ..."

Well at least being "over there" hasn't mucked up your sense of Gender!!

"what can you expect but me being confused!"

I didn't like to say!

Kristen

giasone
Starting Member

9 Posts

Posted - 2007-10-16 : 15:14:55

Jimf,

Thanks, I think I got it figured out.

Jason.

giasone
Starting Member

9 Posts

Posted - 2007-10-16 : 15:36:46

Kristen,

I got the Sql doing what I want it to do. Here is what I need to do now, you might be able to help me with this as well, although I am not sure
is sql can do this.

The data that I extracted is drive space usage of a server over the last couple of years. What is want is to predict future usage based on past usage. Any ideas on how I can do this?

Thanks again.

Jason.

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 16:07:31

Well, for straight-line you can use Linear Regression. For Curve you need some quadratic equation. But its getting on for 35 years since I had to study that, so you'll be better of with "one of our younger members" for an answer to that.

How complex a formula are you needing? I expect you want the most pessimistic one so that you are guaranteed to be ready-in-time with additional drives etc.

giasone
Starting Member

9 Posts

Posted - 2007-10-16 : 16:15:11

Kristen,

Thats ok, I have the excel accountant guy here working on it for me.

Thanks for all your help.

Jason.

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 16:16:50

You are hosed! with inflation his algorithm will have you running out of disk space yesterday!

giasone
Starting Member

9 Posts

Posted - 2007-10-17 : 10:04:01

Kristen,

I am curious what is the job situation like now in England, is it easy to get a well paid job in computers.

I know though that the house prices are way too high.

Jason.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 10:14:05

Curve fitting equations are found here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77311

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 12:25:21

"is it easy to get a well paid job in computers"

Dunno the answer to that one I'm afraid. We're happy with the people we have got at the price we are paying them. We aren't having to pay seeming high prices to get the talent we need.

House prices are high because we don't have enough housing stock. Interest rates climbing would make a mess of everything though. other than that its pretty irrelevant - if house prices climb 8% a year, say, it doesn't really matter whether a house is 10 GBP / sq foot, or 100 GBP. You buy the biggest you can possibly afford and by the time you retire it has given you enough money to keep you in your old age.

At least, that's what I should have done!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources