Need To CalcuThe Number Of Days Between The Current Date And A Stored Date

I need help with creating a query that compares the current date with a stored date field. If the difference between the two dates is greater or equal to 5 days for example, I need to be able to return these records. I am not sure if this can be done through a query alone but any help and suggestions would greatly be appreciated. Thanks in advance.yes it can be done through a query, but the standard sql for it will almost certainly not work in whatever database system you're using

date functions are notoriously non-standard, and each database system pretty much has its own proprietary functions

if you wouldn't mind mentioning which database system you're using, we could probably help you|||Nevermind I figured it out. I am using a SQL server 2000 database to query my data. I used the Datediff() function in conjunction with getdate(). See examples below for anyone else needing help with this topic.

DATEDIFF([day], dbo.table.field, GETDATE()) AS DAYS,
DATEDIFF([HOUR], dbo.table.field, GETDATE()) AS HOURS|||moved to SQL Server forum|||The solution you arrived at will work, but will not benefit from any indexing on your dbo.table.field column. If, instead, you used the DateAdd() function to find the date five days prior to the current date, then the optimizer will be able to use an index on dbo.table.field for comparisons:Where dbo.table.field >= DateAdd(day, -5, GetDate())

Need to Calculate Grade Age

Need to calculate the Grade age based on the birthdate and Nov month and 30th Day of the current year.
I have a working datediff statement but I need to always but in the current year. I would like to have the statement get the current year. Then if the age is greater than xx and less than xx your age level is "xyz"

This works DateDiff("d" [Birthdate], 11/30/2004) /365.25 will return the age.

I want to replace the 2004 with a getdate yyyy so I do not need to maintain this statement.

Thanks in advance of a reply
GaryTry this:

Declare @.birthdate as smalldatetime,@.mydate as varchar(10)
SET @.birthdate = '07/11/1978'
SET @.mydate = '11/30/' + cast(year(getdate())as varchar)
DateDiff("d", @.birthdate, @.mydate) /365.25

|||Thanks that helped me with the project.sql

Need script to kill all processes on a database

I restore a backup every week. It serves a couple of purposes. One, to verify the backup and secondly, to give us a current testing/training environment. As things evolve, it would be nice to script this and then schedule it as a job.

I can write the restore statement, but it fails if any sessions are open on the db. I thought about using sp_who and the the kill statement. But I was having trouble using the recordset returned by sp_who.

Now I'm thinking the sysprocess table is a better place to look. Does anybody have some experience in this area and can point me in the right direction?

While I'm going down this path, does anybody have a script to look at the master database and dynamically manage their backup strategy? That would be super cool?!?!

Thanks,

Alex8675I restore a backup every week. It serves a couple of purposes. One, to verify the backup and secondly, to give us a current testing/training environment. As things evolve, it would be nice to script this and then schedule it as a job.

I can write the restore statement, but it fails if any sessions are open on the db. I thought about using sp_who and the the kill statement. But I was having trouble using the recordset returned by sp_who.

Now I'm thinking the sysprocess table is a better place to look. Does anybody have some experience in this area and can point me in the right direction?

Go to google type "kill all processes IN A DATABASE SQL SERVER" i got atleast 10 different SP's which will do your job.....choose the one which suits you|||this will work if you are not logged connected to the database and no other sysadmin is doing anything...

ALTER DATABASE MyDatabase SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE|||Very elegant solution and exactly what I was looking for...but I couldn't find and was going a long way around to find what was readily available.|||this will work if you are not logged connected to the database and no other sysadmin is doing anything...

ALTER DATABASE MyDatabase SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
This one's not as elegant, but worked when I had to disconnect / move the log files tonight.
DECLARE @.sql nVARCHAR(4000)
SET @.sql = ''

-- To disconnect, first kill all processes.
SELECT @.sql = @.sql + ' KILL ' + CAST(spid AS VARCHAR(10)) + ' '
FROM master.dbo.sysprocesses
WHERE DB_NAME(dbid) = @.MYDB
AND spid > 50 AND spid <> @.@.SPID
EXEC(@.sql)

Thrasymachus; Do I have to do something to undo the SET?

Need report for current heaviest queries

What tools can I use to get an immediate report of which queries are the
heavist on my sql server? Sometimes it can max for 30-40 seconds and I need
to troubleshoot where the bottleneck it.
All tips and links would be appreciated!
Thanks,
Moshe
You can use a server side trace or Profiler to capture
information on queries such as duration, cpu, reads, writes,
etc. You can use the tool or tracing in different ways but
I'm not sure what you mean by an immediate report - if you
need to see what's hardest on your server you'd need to
monitor this over some period of time. You can save the
trace results to a file and from there you can import them
to a table using fn_trace_gettable and do some analysis,
reporting, etc.
-Sue
On Mon, 6 Mar 2006 14:07:26 -0800, Moshe Rosenberg
<MosheRosenberg@.discussions.microsoft.com> wrote:

>What tools can I use to get an immediate report of which queries are the
>heavist on my sql server? Sometimes it can max for 30-40 seconds and I need
>to troubleshoot where the bottleneck it.
>All tips and links would be appreciated!
>Thanks,
>Moshe

Need report for current heaviest queries

What tools can I use to get an immediate report of which queries are the
heavist on my sql server? Sometimes it can max for 30-40 seconds and I need
to troubleshoot where the bottleneck it.
All tips and links would be appreciated!
Thanks,
MosheYou can use a server side trace or Profiler to capture
information on queries such as duration, cpu, reads, writes,
etc. You can use the tool or tracing in different ways but
I'm not sure what you mean by an immediate report - if you
need to see what's hardest on your server you'd need to
monitor this over some period of time. You can save the
trace results to a file and from there you can import them
to a table using fn_trace_gettable and do some analysis,
reporting, etc.
-Sue
On Mon, 6 Mar 2006 14:07:26 -0800, Moshe Rosenberg
<MosheRosenberg@.discussions.microsoft.com> wrote:

>What tools can I use to get an immediate report of which queries are the
>heavist on my sql server? Sometimes it can max for 30-40 seconds and I need
>to troubleshoot where the bottleneck it.
>All tips and links would be appreciated!
>Thanks,
>Moshe

Need report for current heaviest queries

What tools can I use to get an immediate report of which queries are the
heavist on my sql server? Sometimes it can max for 30-40 seconds and I need
to troubleshoot where the bottleneck it.
All tips and links would be appreciated!
Thanks,
MosheYou can use a server side trace or Profiler to capture
information on queries such as duration, cpu, reads, writes,
etc. You can use the tool or tracing in different ways but
I'm not sure what you mean by an immediate report - if you
need to see what's hardest on your server you'd need to
monitor this over some period of time. You can save the
trace results to a file and from there you can import them
to a table using fn_trace_gettable and do some analysis,
reporting, etc.
-Sue
On Mon, 6 Mar 2006 14:07:26 -0800, Moshe Rosenberg
<MosheRosenberg@.discussions.microsoft.com> wrote:
>What tools can I use to get an immediate report of which queries are the
>heavist on my sql server? Sometimes it can max for 30-40 seconds and I need
>to troubleshoot where the bottleneck it.
>All tips and links would be appreciated!
>Thanks,
>Moshe

need matrix report advice

I have to create a report that displays monthly actual and forecasted values by project with a variance column for the current month. Has anyone ever attempted something like this? I am having a difficult time determining how the data should return from the procedure so that it can be display in the following format:

JUL

AUG

SEP

OCT

NOV

DEC

JAN

FEB

Actuals

Var

Forecast

PROJECT A

10

20

15

-15

30

15

41

26

47

64

PROJECT B

15

10

25

5

20

20

10

5

10

10

I need to use a matrix since the number of columns can vary. I have thought about returning the following fields: project, date, label, hours.

I am hoping someone has attempted this before and can offer advice on whether I am approaching the problem correctly.

Thanks for any help.

hey there

not sure if this is what you want but this is how I approached a similar report using a Matrix - with varying columns

Report was for a Weekly user worktime

put this in your layout view

(1)=(Parameters!PersonNameValue)

(2-across)=Fields!WorkDate.Value)

(3)=Fields!CallSubject3.Value

(4-across)=Format(Sum(Fields!WorkTime.Value)

(5)=Format(Sum(Field...hrs total)

(6) blank

this shows like below

person date1 date 2 etc

subject time time etc

total time totaltime totaltime etc

sorry I wasn't sure how to post a graphical view on here so I hope this helps you

just add extra fields as required

any questions please ask

Jewel

Need infos regarding install of SQL Express 2005 SP1

Hi,

A little technical question. If I want to install SQL Express 2005 SP1, do I have to uninstall current version first?

Thanks in advance,

Stphane

No, you apply a service pack to an existing installation.|||

Hi again,

Thanks for the info. Maybe a last question. Considering the fact that a SP1 has also been issued for Visual Studio 2005, must I first install the SP1 for VS 2005 before installing SP1 for SQL Express 2005 or it doesn't matter?

Thanks for your help,

Stphane

|||

It doesn't matter.

Mike

|||

SQL Server and Visual Studio are separate products. You do not have to apply SP1 to them in any particular order, nor do you have to apply it to both (though it is a good idea.)

Please indicate if your question has been answered properly. Thanks,

|||

Hi again,

I just finished install VS 2005 Sp1. Quite long, especially the section where it was evaluating the pre-requisites, but everything completed without error. It detected the appropriate version of VS2005 I was using. Now I'm ready to apply SP1 to SQL Server Express 2005. Before going on, I really need to clarify those things.

Considering the fact that the version of SQL Server Express 2005 actually present on my system is the one I originally installed from VS2005 Standard disks, what is the appropriate install sequence I should take?:

1- If I want to install "Microsoft SQL Server 2005 Express Edition with Advanced Services", do I have to uninstall the SQL Server Express 2005 version actually installed on my machine first or do I simply install the new one over it?

ref: http://www.microsoft.com/downloads/details.aspx?familyid=4C6BA9FD-319A-4887-BC75-3B02B5E48A40&displaylang=en

2- I presume that I can then install "Microsoft SQL Server 2005 Express Edition Toolkit" without problem. Anything I should take care of before install?

ref: http://www.microsoft.com/downloads/details.aspx?FamilyID=3c856b93-369f-4c6f-9357-c35384179543&DisplayLang=en

3- Finally, I presume that I can apply SP1 "Microsoft SQL Server 2005 Express Edition (SP1)" without any problem. Is the following link the appropriate version to apply or should I apply another one?

ref: http://www.microsoft.com/downloads/details.aspx?familyid=11350B1F-8F44-4DB6-B542-4A4B869C2FF1&displaylang=en

Thanks for your help,

Stphane

|||

Stphane,

Your sequence of steps should be work ok.

|||

Hi Arnie,

Just to be sure. Must I uninstall current version of SQL Express before going on with others listed in previous message?

Thanks for your help, really appreciated!

Stphane

|||Unless you are using a beta version, you do not need to unInstall before applying the service pack.|||

Hi again,

For applying SP1, I was aware that there was no need for uninstalling as I already applied SP1 over Visual Studio 2005. In fact, my question was more about the installation of "Microsoft SQL Server 2005 Express Edition with Advanced Services" over actual SQL Express installed on my machine. Can I install it over actual version or must I first uninstall current SQL Express version installed?

Thanks again,

Stphane

|||

The 'Advanced Services" edition is the same version of SQL 2005 Express, and it includes additional functionality, including Management Studio Express.

Again, you do NOT have to uninstall before adding a service pack.

(Now is that complete enough for you to indicate you have been responded and answered?

|||

Hi again,

This last question was not about SP1. Actually the SQL Server 2005 Express version I have is the one installed directly from Visual Studio 2005 Standard Edition disks. I would like before applying SP1 to install instead version/package "Microsoft SQL Server 2005 Express Edition with Advanced Services". During your last posts, you seemed to talk only about SP1. Again, "Microsoft SQL Server 2005 Express Edition with Advanced Services" is NOT installed but I would like to install it before applying SP1.

That is why I asked again, and still ask, in order to install "Microsoft SQL Server 2005 Express Edition with Advanced Services" over current version I have of SQL Express, do I have to uninstall SQL Express version currently installed first?

After this new install will be done, THEN I know I will be able to install SP1 without touching anything, the same way I already did so far for VS 2005.

Sorry for misunderstanding, but I want to be sure before screwing up anything.

Stphane

|||

And as I have replied many, many times: NO, you do not have to uninstall your current version of SQL 2005 Express. When you start up the "Advanced Services" edition, you will be asked if you want to install a new instance, or just install the new advanced services and Management Studio Express.

Please, give it a rest. Go make your installation. Go with Peace and Florish.

|||

Hi,

Really sorry to bug you but I never went through this install so far. Having known from start that install process would have asked me this question would have made things clear in my mind. Now, with that last detail, YES, we can close the subject.

Thanks for your patience,

Stphane

|||Cool, you gave yourself credit for answering your own question!