Export query with count and getdate()

Question

Post reply

Export query with count and getdate()

Jason Hairston

SSC Enthusiast

Points: 116
More actions
September 13, 2006 at 9:47 am

#67638

I have a table that lists current logins in an application.
I have queried to get the current count with:

SELECT     COUNT(*) AS #CurrentLogins
FROM         Workstation
WHERE     (LogoutTime IS NULL)

What I would like to do is set up a recurring job to run this query and append to a new table with a getdate() timestamp included.
Is there an easy way to accomplish this?
Any help would definitely be appreciated.

Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Create a table like UsersHistory

DateHistory smalldatetime not null default (getDate())

Total int not null

The query to insert the data :

Insert into dbo.UsersHistory (Total)

SELECT COUNT(*) AS Total

FROM dbo.Workstation

WHERE LogoutTime IS NULL

Then set up a job that runs this statement. You can scedule it to run like any task in windows (monthly, daily, weekly...) just look around and I'm sure you'll figure it out.

Jason Hairston SSC Enthusiast Points: 116 More actions · Answer 2

Thanks for the reply.

I have the query which does exactly what I want.

It gives a current total.

Really, all I want to do is populate a new table or new CSV with this number (appended list) every 10 minutes.

But I would like to include a 2nd column in the new table or CSV file with the date/time.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

That what this is for :

Create a table like UsersHistory

DateHistory smalldatetime not null default (getDate())

Then the job fills a table with the data you want. Now if you want to export it to excel or whatever it's another issue all together. Look into dts and you'll find what you need.