Showing posts with label sample. Show all posts
Showing posts with label sample. Show all posts

Tuesday, March 20, 2012

Check if a file exists using sql

Is there is a piece for code ot sample code that can let me check if a file exists? Has any one done this before?
ThanksTry this idea.

create table #tmp(result varchar(100))
go
insert #tmp
exec master..xp_cmdshell 'dir filename.txt'
select * from #tmp where result like '%filename.txt%'|||Originally posted by snail
Try this idea.

create table #tmp(result varchar(100))
go
insert #tmp
exec master..xp_cmdshell 'dir filename.txt'
select * from #tmp where result like '%filename.txt%'

I found another method to do this. I used the xp_fileexist command and it works well. The idea above works as well

Thanks for the input.sql

Monday, March 19, 2012

Check database samples for zero or null values

I have a program that writes a sample to a database (Table A) every minute.
I like to put together a script that would check the Table to see if last 5
minutes that were sampled were not 0 or NULL. If the samples were 0 or NULL
then send alert to the RSTK.com email account.
Table A
Sample_Time datetime
Sample_Value int
Please help me complete this task.
Thanks,are you wanted to get an email based on system counters? If so you can use
SQL Server Agent Alerts to achieve this. If you are not using system
counters then you can set up a dts package to acheive this, or stored
procedure etc, and run it through a job.
Simon Worth
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:94068EE3-CBD9-4908-BFD6-5BA16C182D3E@.microsoft.com...
> I have a program that writes a sample to a database (Table A) every
minute.
> I like to put together a script that would check the Table to see if last
5
> minutes that were sampled were not 0 or NULL. If the samples were 0 or
NULL
> then send alert to the RSTK.com email account.
> Table A
> Sample_Time datetime
> Sample_Value int
> Please help me complete this task.
> Thanks,
>
>
>
>
>
>|||In principle, like this:
IF
(SELECT *
FROM TableA
WHERE sample_time
BETWEEN DATEADD(MI,-5,CURRENT_TIMESTAMP)
AND CURRENT_TIMESTAMP
AND COALESCE(sample_value,0) = 0)
EXEC usp_sendmail
@.recipients = 'foo@.bar.com',
@.message = 'Blah, blah',
..
However, I recommend you send mail from an application process rather
than from SQL Server.
David Portas
SQL Server MVP
--|||Set up SQL Server for email integration with. Create a job with a t-sql
task.
if exists ( select 1 from tablea where sampletime > dateadd(mi,-5,getdate())
and (Sample_value is null or Sample_value = 0))
xp_sendmail yada yada (
details of xp_sendmail in SQL BOL..
Schedule the job every 5 minutes.
You might wish to use Gert Drapers sqlmail ( www.sqldev.net) instead of
using MS mail integration...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:94068EE3-CBD9-4908-BFD6-5BA16C182D3E@.microsoft.com...
> I have a program that writes a sample to a database (Table A) every
> minute.
> I like to put together a script that would check the Table to see if last
> 5
> minutes that were sampled were not 0 or NULL. If the samples were 0 or
> NULL
> then send alert to the RSTK.com email account.
> Table A
> Sample_Time datetime
> Sample_Value int
> Please help me complete this task.
> Thanks,
>
>
>
>
>
>

Sunday, March 11, 2012

Check Constraints or Triggers

Hi, Im facing teh following situation:

This are just sample table names, but should do for discussing
purpouses.

Create table Invoice
(
InvoiceID Integer Not Null,
CustomerType Integer Not Null,
CustomerCode Integer Not Null,
Amount DECIMAL(10,2) Not Null,
.............
)

Create Table Type1Customer
(
CustomerCode Integer Not Null,
........................
)

Create Table Type2Customer
(
CustomerCode Integer Not Null,
........................
)

I need to add a way to restrict the CustomerType and CustomerCode,
in the Invoice table to the correct values.
This means that if customerType equals 1 the customerCode should be
checked against Type1Customer and if customerType equals 2 the
customerCode should be checked against Type2Customer.

I succesfully created a check constraint. That ensures that the valid
values exists when the rows in the Invoice table are inserted or
updated, but doesnt prevent from deleting records from tables
Type1Customer and Type2Customer that are referenced from the Invoice
table.

Are triggers the only way to go?

Thanks in advance

Sebastin streigerIn addition to Erland's suggestion,
I would recommend adding CustomerType to both Type1Customer and
Type2Customer, and adding CustomerType to their FK constraints|||(sebastian.streiger@.gmail.com) writes:
> This are just sample table names, but should do for discussing
> purpouses.
> Create table Invoice (
> InvoiceID Integer Not Null,
> CustomerType Integer Not Null,
> CustomerCode Integer Not Null,
> Amount DECIMAL(10,2) Not Null,
> ............. )
> Create Table Type1Customer (
> CustomerCode Integer Not Null,
> ....................... )
>
> Create Table Type2Customer (
> CustomerCode Integer Not Null,
> ....................... )
> I need to add a way to restrict the CustomerType and CustomerCode,
> in the Invoice table to the correct values.
> This means that if customerType equals 1 the customerCode should be
> checked against Type1Customer and if customerType equals 2 the
> customerCode should be checked against Type2Customer.
>...
> Are triggers the only way to go?

With that data model, yes. But is that really the right data model?

I would rather have a CustomerCode table which could look like this:

CREATE TABLE CustomerCode (
CustomerType integer NOT NULL,
CustomerCode integer NOT NULL,
CONSTRAINT pk_CustomerCode(CustomerType, CustomerCode))

Then Invoices could refer to this table, and so could the child
tables Type1Customer and Type2Customer.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland and AK:
Thank you for answering.
I DO agree that the model is no the best one that we can have. But due
to organizational issues Im not in position to change the tables
structures by now. So, Im trying to add constraints to ensure the
data consistency.

Thanks for your valuable feedback

Friday, February 10, 2012

changing timeframes with stock data

I have stock data in 1 min intervals and would like to convert it into other timeframes (e.g., 10 min, daily, monthly).

Here's is some sample data and my final goal:

[DateTime] [Open] [High] [Low] [Close] [Volume]
10-Feb-05 12:10:00 3.88 3.88 3.87 3.87 10
10-Feb-05 12:11:00 3.87 3.87 3.87 3.87 2
10-Feb-05 12:12:00 3.86 3.86 3.86 3.86 1
10-Feb-05 12:13:00 3.85 3.87 3.84 3.85 23
10-Feb-05 12:14:00 3.85 3.85 3.85 3.85 6
10-Feb-05 12:15:00 3.86 3.86 3.86 3.86 1
10-Feb-05 12:16:00 3.85 3.85 3.85 3.85 1
10-Feb-05 12:18:00 3.85 3.85 3.85 3.85 3
10-Feb-05 12:19:00 3.85 3.85 3.85 3.85 3

[DateTime] [Open] [High] [Low] [Close] [Volume]
10-Feb-05 12:10:00 3.88 3.88 3.84 3.85 50 *

*sum

Assuming your intervals will never span midnight,
you can do this by joining to an intervals table. This
has all minutes in one day with any intervals you want
and can be set up like this


CREATE TABLE MinuteIntervals(Mins INT NOT NULL PRIMARY KEY,
HMS CHAR(8) NOT NULL,
Interval10Mins INT NOT NULL,
Interval1Hour INT NOT NULL)

DECLARE @.i INT
SET @.i=0
WHILE @.i < 24*60
BEGIN
INSERT INTO MinuteIntervals(Mins,HMS,Interval10Mins,Interval1Hour)
SELECT @.i, CONVERT(CHAR(8),DATEADD(minute,@.i,'19000101'),108),
@.i/10, @.i/60

SET @.i=@.i+1
END

Now you need to join to this to your stock table
grouping by whatever interval you want.

SELECT MIN(t.[DateTime]) AS [DateTime],
(SELECT t2.[Open]
FROM stocktable t2
WHERE t2.[DateTime]=MIN(t.[DateTime])) AS [Open],
MAX(t.[High]) AS [High],
MIN(t.[Low]) AS [Low],
(SELECT t2.[Open]
FROM stocktable t2
WHERE t2.[DateTime]=MAX(t.[DateTime])) AS [Close],
SUM(t.[Volume]) AS [Volume]
FROM stocktable t
INNER JOIN MinuteIntervals m ON m.HMS=CONVERT(CHAR(8),t.[DateTime],108)
GROUP BY m.Interval10Mins

|||

Correction, the group by should read

GROUP BY m.Interval10Mins,CONVERT(CHAR(8),t.[DateTime],112)

|||

Of course this will only work for intervals less that one day.
Should have read the question fully!

For daily or longer intervals, don't join
to the MinuteIntervals table, simply group by the relevant amount
e.g. for daily


SELECT MIN(t.[DateTime]) AS [DateTime],
(SELECT t2.[Open]
FROM stocktable t2
WHERE t2.[DateTime]=MIN(t.[DateTime])) AS [Open],
MAX(t.[High]) AS [High],
MIN(t.[Low]) AS [Low],
(SELECT t2.[Close]
FROM stocktable t2
WHERE t2.[DateTime]=MAX(t.[DateTime])) AS [Close],
SUM(t.[Volume]) AS [Volume]
FROM stocktable t
GROUP BY CONVERT(CHAR(8),t.[DateTime],112)

For monthly

GROUP BY LEFT(CONVERT(CHAR(8),t.[DateTime],112),6)

For yearly

GROUP BY LEFT(CONVERT(CHAR(8),t.[DateTime],112),4)

|||

I very much appreciate your help. If I understand the roles of tables t and t2, it appears the code only works if two different tables are used.

Does that mean I have to first create a copy of the original table in order to run the script correctly?

|||Those are not tables they are just aliases for tables. Since you are joining with the same table you need to use table aliases to make the names unique for referencing columns in the query. See the SELECT statement topic in Books Online for the syntax details.|||

Yes, you are so right . That's what I get for trying to cut back on my caffeine.

It works like a charm . Thanks to all who have helped. I really do appreciate it.