Finding mins and max time from records with different start time other that 12:00 midnight

I have a series of start and end time records. The problem is to select the min and max time from the series of records.

The following contraint applies. The start of broadcast time is 6:00 am. That is, minimum for start time is 6:00 am and maximum for end time is 5:59 am. So the following is illegal for start and end time, for example, 4:00 am - 6:30 am because it crosses the broadcast start time of 6:00 am.

Start End

10:00 pm -- 2:00 am

6:30 am -- 8:30 am

2:00 am - 3:45 am

11:00 am - 4:00pm

12:00 am - 3:40 am

You might be tempted to used -> Select MIN(Start), Max(End), but that will return 12:00 am - 4:00 pm, which is wrong, because sql server uses 12 midnight at the start time.

Can' t seem to come up with the tsql, please help

In my opinion it's not totally clear exactly what you're looking for.

In the example that you supplied, what results would you expect to be returned by the query? Also, are you storing the data as DATETIME values?

Thanks
Chris

|||Sorry. Since the min begin time is 6:00 am and the maximum end time time is 5:59 am, the expected result should be 6:30 am - 3:45am.|||

Thanks for providing the extra info.

Please could you also clarify what datatype you are using to store the times as this will affect the solution.

Thanks
Chris

|||

job:

I think that I also am not sure exactly what you are trying to do. Maybe a starting point is to substract six hours from your "startTime" to establish a "work day"; something like:

declare @.timeStuff table
( shiftId integer,
startTime datetime,
endTime datetime
)

insert into @.timeStuff values ( 1, '3/4/7 22:00', '3/5/7 2:00' )
insert into @.timeStuff values ( 2, '3/5/7 06:30', '3/5/7 8:30' )
insert into @.timeStuff values ( 3, '3/5/7 02:00', '3/5/7 3:45' )
insert into @.timeStuff values ( 4, '3/5/7 11:00', '3/5/7 16:00')
insert into @.timeStuff values ( 5, '3/6/7 00:00', '3/6/7 3:40')

declare @.offset datetime set @.offset = '06:00:00.000'

select convert (varchar(8), startTime - @.offset, 101) as [workDate ],
left(convert (varchar(8), startTime, 108), 5) as startTime,
left(convert (varchar(8), endTime, 108), 5) as endTime
from @.timestuff
order by startTime

-- workDate startTime endTime
-- -
-- 03/05/20 00:00 03:40
-- 03/04/20 02:00 03:45
-- 03/05/20 06:30 08:30
-- 03/05/20 11:00 16:00
-- 03/04/20 22:00 02:00

|||

If 4 am is not legal how did it get into your db in the first place, going by what you want there shoudl be nothing less than 6.00 am in your Start column. Get the place of entry sorted out and you should fine.

for the bad data u posted above you could still use

Select min(start), Max(End)

from tablename

where

convert(use conversion to convert time to last digits in time in start)>5.59 and

and convert(use conversion to convert time to last digits in time)<

--you can figure out the where condtion

point is it can be done

Finding Duplicate Foreign Keys

Hi

i tried the following query and able to get the list of foreign keys with column names as well as referred tables and referenced column

select parent_column_id as 'child Column',object_name(constraint_object_id)as 'FK Name',object_name(parent_object_id) as 'parent table',name,object_name(referenced_object_id)as 'referenced table',referenced_column_id
from sys.foreign_key_columns inner join sys.columns on (parent_column_id = column_id and parent_object_id=object_id)
Order by object_name(parent_object_id) asc

but i am not able to get the fks created more than once on same column refering to same pk

Thanks in AdvanceIs this helpful?

Field1 has the following values;
1, 2, 3, 3, 4, 5, 6, 6, 6, 7, 8, 9
duplicates highlighted in bold

The following SQL will pick out duplicates only

SELECT Field1
FROM MyTable
GROUP BY Field1
HAVING Count(*) > 1

Field1
--
3
6

:cool:|||I am not sure what is the exact problem, but I think 'Information_Schema' might help. It contains number of views that can be used to retrive meta data.

E.g.
Select * from information_Schema.TABLE_CONSTRAINTS|||here's one that I use in SqlSpec, it may be more than you need though:

select
quotename(su2.name) collate Latin1_General_CI_AS + N'.' collate Latin1_General_CI_AS + quotename(so2.name) collate Latin1_General_CI_AS as parent
,quotename(su1.name) collate Latin1_General_CI_AS + N'.' collate Latin1_General_CI_AS + quotename(so1.name) collate Latin1_General_CI_AS as name
,so1.name as shortname ,sc2.name as colName
,'foreign key from ' + quotename(su2.name) + N'.' + quotename(so2.name) + ' to ' + quotename(su3.name) + N'.' + quotename(so3.name) as description
,quotename(su3.name) collate Latin1_General_CI_AS + N'.' collate Latin1_General_CI_AS + quotename(so3.name) collate Latin1_General_CI_AS as refTable
,sc3.name as refColumn
,ObjectProperty(sf.constid, 'CnstIsDisabled') as isDisabled
,ObjectProperty(sf.constid, 'CnstIsNotRepl') as isNotForReplication
,ObjectProperty(sf.constid, 'CnstIsDeleteCascade') as isDeleteCascade
,ObjectProperty(sf.constid, 'CnstIsUpdateCascade') as isUpdateCascade
,so1.object_id as id from sysforeignkeys sf
join sys.objects so1 on sf.constid=so1.object_id
join sys.objects so2 on sf.fkeyid=so2.object_id
join sys.objects so3 on sf.rkeyid=so3.object_id
join sys.schemas su1 on su1.schema_id=so1.schema_id
join sys.schemas su2 on su2.schema_id=so2.schema_id
join sys.schemas su3 on su3.schema_id=so3.schema_id
join syscolumns sc2 on sc2.colid=sf.fkey and sc2.id=so2.object_id
join syscolumns sc3 on sc3.colid=sf.rkey and sc3.id=so3.object_id|||Thanks You ALL for your reply.

i got that using
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc and INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

with CTEsql

Finding dashes/hyphens in a column.

Hi,

I'm having trouble running the following query:

select * from message where text_body like ' ----%'

ie, five spaces followed by at least ten hyphens. The query doesn't
complete, so eventually I cancel it. If I remove the hyphens from the
query ("... like ' %'") then it runs fine (though it doesn't find
the correct data).

Am I confusing SQL Server by using a wildcard or regular expression?
(I'm using SQL Server 2000 - 8.99.760).
Thanks in advance for any help

RichardOn 19 Jan 2005 07:46:49 -0800, richard_thomas@.bigfoot.com wrote:

>I'm having trouble running the following query:
>select * from message where text_body like ' ----%'
>ie, five spaces followed by at least ten hyphens. The query doesn't
>complete, so eventually I cancel it. If I remove the hyphens from the
>query ("... like ' %'") then it runs fine (though it doesn't find
>the correct data).
>Am I confusing SQL Server by using a wildcard or regular expression?
>(I'm using SQL Server 2000 - 8.99.760).
>Thanks in advance for any help

Hi Richard,

I couldn't reproduce this behaviour. The following script returns exactly
the one row I expect to be returned:

create table message (text_body varchar(50))
go
insert message values (' ---- should be returned')
insert message values (' --- one dash short')
insert message values (' ---- one space short')
insert message values (' ---- one space too much')
go
select * from message where text_body like ' ----%'
go
drop table message
go

Can you post a script that will reproduce the buggy behaviour on my
machine?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||(richard_thomas@.bigfoot.com) writes:
> I'm having trouble running the following query:
> select * from message where text_body like ' ----%'
> ie, five spaces followed by at least ten hyphens. The query doesn't
> complete, so eventually I cancel it. If I remove the hyphens from the
> query ("... like ' %'") then it runs fine (though it doesn't find
> the correct data).

How many rows are there in message? What query plans do you get in
the two cases?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hmm.

I'm beginning to have a feeling that the 'spaces' aren't in fact
spaces.

By doing this, it seems to work:

select * from message where text_body like '____---%'

but also finds strings such as

'----'
ie the first 4 characters are hyphens rather than dashes.

Is there a wildcard that matches whitespace?
Thanks again for your help!|||Sorry, that should read
"The first 4 characters are hyphens rather than spaces" (not dashes!)|||On 20 Jan 2005 02:21:21 -0800, richard_thomas@.bigfoot.com wrote:

>Is there a wildcard that matches whitespace?

Hi Richard,

Unfortunately, no.

You should locate rows that you suspect to have non-space whitespace,
using (e.g.)
WHEREtext_body like '____---%'
ANDtext_body NOT like ' ---%'

Then, inspect the real contents of text_body in such rows:
SELECT ASCII (SUBSTRING (text_body, 1, 1)),
ASCII (SUBSTRING (text_body, 2, 1)),
ASCII (SUBSTRING (text_body, 3, 1)),
ASCII (SUBSTRING (text_body, 4, 1))
FROM ...
WHERE ...

Once you know that, you can start thinking how to match all variations you
may have in your data.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

finding bad date

I have a database where a varchar field is being used to store dates. I wrot
e
the following function to check if the data is a valid date. If it is not a
valid date, Null is returned.
ALTER function f_ToDate(@.inDate varchar(50))
returns datetime
begin
declare @.returndate datetime
if isdate(@.inDate) = 0
select @.returndate = null
else
select @.returndate = convert(datetime,@.inDate,101)
return @.returndate
end
In my database, I have a table called Generation with a field called
CreateDate. When I run the following query, I get an error message stating
that “syntax error converting datetime from character string”
select dbo.f_ToDate(Generation.CreationDate) from Generation
To check, I ran the following query and it returned all null records which
is expected
select CreationDate from general where isdate(CreationDate) = 0
The following returns no records which is expected.
select CreationDate from general where isdate(CreationDate) = 0 and
CreationDate is not Null.
I can’t find the record that is causing “select
dbo.f_ToDate(Generation.CreationDate) from Generation” to fail.I have had a similar issue w/ Dates. A quick and dirty way that I used was t
o
export out the records using DTS to a table that mirrored the working one.
Only change I made was to change the varchar date field to a true datetime
field in the temp table. In my dts package I set the max number of errors to
the max (999) and started the export.
In my case I had 632 fewer records in my temp table after the export. I ran
a NOT EXISTS against the tables and was able to find the 632 records w/ bad
dates in my production table.
If the number of records w/ bad dates is GT 999, you will have to do the
export in bacthes to find all the bad recs.
Just a thought!
"Emma" wrote:

> I have a database where a varchar field is being used to store dates. I wr
ote
> the following function to check if the data is a valid date. If it is not
a
> valid date, Null is returned.
> ALTER function f_ToDate(@.inDate varchar(50))
> returns datetime
> begin
> declare @.returndate datetime
> if isdate(@.inDate) = 0
> select @.returndate = null
> else
> select @.returndate = convert(datetime,@.inDate,101)
> return @.returndate
> end
>
> In my database, I have a table called Generation with a field called
> CreateDate. When I run the following query, I get an error message stating
> that “syntax error converting datetime from character string”
> select dbo.f_ToDate(Generation.CreationDate) from Generation
> To check, I ran the following query and it returned all null records which
> is expected
> select CreationDate from general where isdate(CreationDate) = 0
> The following returns no records which is expected.
> select CreationDate from general where isdate(CreationDate) = 0 and
> CreationDate is not Null.
> I can’t find the record that is causing “select
> dbo.f_ToDate(Generation.CreationDate) from Generation” to fail.
>|||Emma,
The convert function with style 101 is supposed to be used when converting
from datetime to char or varchar. Can you execute:
select case when isdate(CreationDate) = 0 then null else convert(datetime,
CreationDate)
from Generation
AMB
"Emma" wrote:

> I have a database where a varchar field is being used to store dates. I wr
ote
> the following function to check if the data is a valid date. If it is not
a
> valid date, Null is returned.
> ALTER function f_ToDate(@.inDate varchar(50))
> returns datetime
> begin
> declare @.returndate datetime
> if isdate(@.inDate) = 0
> select @.returndate = null
> else
> select @.returndate = convert(datetime,@.inDate,101)
> return @.returndate
> end
>
> In my database, I have a table called Generation with a field called
> CreateDate. When I run the following query, I get an error message stating
> that “syntax error converting datetime from character string”
> select dbo.f_ToDate(Generation.CreationDate) from Generation
> To check, I ran the following query and it returned all null records which
> is expected
> select CreationDate from general where isdate(CreationDate) = 0
> The following returns no records which is expected.
> select CreationDate from general where isdate(CreationDate) = 0 and
> CreationDate is not Null.
> I can’t find the record that is causing “select
> dbo.f_ToDate(Generation.CreationDate) from Generation” to fail.
>|||Thanks Jay for your response. I did just what you said. The number of record
s
exported to the temp table was the same as in my original table, and the
query work on the temp table and not on the original. I can’t figure it ou
t.
"Jay Kusch" wrote:
> I have had a similar issue w/ Dates. A quick and dirty way that I used was
to
> export out the records using DTS to a table that mirrored the working one.
> Only change I made was to change the varchar date field to a true datetime
> field in the temp table. In my dts package I set the max number of errors
to
> the max (999) and started the export.
> In my case I had 632 fewer records in my temp table after the export. I ra
n
> a NOT EXISTS against the tables and was able to find the 632 records w/ ba
d
> dates in my production table.
> If the number of records w/ bad dates is GT 999, you will have to do the
> export in bacthes to find all the bad recs.
> Just a thought!
> "Emma" wrote:
>|||Thanks. That worked.
"Alejandro Mesa" wrote:
> Emma,
> The convert function with style 101 is supposed to be used when converting
> from datetime to char or varchar. Can you execute:
> select case when isdate(CreationDate) = 0 then null else convert(datetime,
> CreationDate)
> from Generation
>
> AMB
> "Emma" wrote:
>

Find top 10 tables which have worst statistics

hi guys,
I use following command to find out statistic of table, but how do I find t
op 10 tables which have worst statistics in a database?
DBCC SHOW_STATISTICS ( table , target )Kevin,
Maybe using SQL-DMO...I think you'd have a little bit more control of the ou
tput there.
However the timing of when the statistics were last updated might provide yo
u with some insight as to which tables may have the worst statistics.
This query might get you started off of the timing of the last stats update
(not fully tested):
SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.id = i.id and STATS_DATE(i.id, i.indid) IS NOT NULL and i.name NOT
LIKE '_WA%'
and i.name NOT LIKE '%sys%'
ORDER BY 2
Do you have the automatic update statistics set for the database?
HTH
Jerry
"Kevin" <pearl_77@.hotmail.com> wrote in message news:%235WH%23mGxFHA.916@.TK2
MSFTNGP10.phx.gbl...
hi guys,
I use following command to find out statistic of table, but how do I find t
op 10 tables which have worst statistics in a database?
DBCC SHOW_STATISTICS ( table , target )|||yes, I have automatic update statistics set on databases.
but I dont' think that will guarantee statistics to be perfect.
I just want to find out which tables have worst statistics. I found an arti
cle in following link, it's very helpful.
http://www.sqlservercentral.com/scr...utions/1069.asp
thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message news:ur44dEHxFHA.27
92@.tk2msftngp13.phx.gbl...
Kevin,
Maybe using SQL-DMO...I think you'd have a little bit more control of the ou
tput there.
However the timing of when the statistics were last updated might provide yo
u with some insight as to which tables may have the worst statistics.
This query might get you started off of the timing of the last stats update
(not fully tested):
SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.id = i.id and STATS_DATE(i.id, i.indid) IS NOT NULL and i.name NOT
LIKE '_WA%'
and i.name NOT LIKE '%sys%'
ORDER BY 2
Do you have the automatic update statistics set for the database?
HTH
Jerry
"Kevin" <pearl_77@.hotmail.com> wrote in message news:%235WH%23mGxFHA.916@.TK2
MSFTNGP10.phx.gbl...
hi guys,
I use following command to find out statistic of table, but how do I find t
op 10 tables which have worst statistics in a database?
DBCC SHOW_STATISTICS ( table , target )

find the most recent update row

Hi,
I have the following table.
Object_ID Rating Effective_Date
A 1 5/15/03
A 2 6/30/03
A 1 7/15/03
B 2 5/15/03
B 4 7/20/03
Another table is
Object_ID quantity query_date
A 1000 7/12/03
B 2000 7/12/03(must be the same for
all row in the column query_date)
and I wish to return the lastest Rating like
Object_ID quantity rating effective date
A 1000 2 6/30/03
B 2000 2 5/15/03
Could anyone have a one line T-sql to perform this and
advise?
I have tried to use a min function to compare the query
date and effective and choose the min non-negative number.
But it seems work fine for single row record, but if
multiple record, it seems difficult to do the grouping.
Thx..(untested)
SELECT t2.Object_ID, t2.quantity, t1.rating, t1.Effective_Date
FROM table1 t1
INNER JOIN table2 t2
ON t1.Object_ID = t2.Object_ID
WHERE t2.Effective_Date = (SELECT MAX(Effective_Date) FROM table2 t3
WHERE t3.Effective_Date < t1.query_date
AND t3.Object_ID = t2.Object_ID)
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"AW" <alexwong97@.hotmail.com> wrote in message
news:077b01c34497$dffc24e0$a001280a@.phx.gbl...
> Hi,
> I have the following table.
> Object_ID Rating Effective_Date
> A 1 5/15/03
> A 2 6/30/03
> A 1 7/15/03
> B 2 5/15/03
> B 4 7/20/03
> Another table is
> Object_ID quantity query_date
> A 1000 7/12/03
> B 2000 7/12/03(must be the same for
> all row in the column query_date)
> and I wish to return the lastest Rating like
> Object_ID quantity rating effective date
> A 1000 2 6/30/03
> B 2000 2 5/15/03
>
> Could anyone have a one line T-sql to perform this and
> advise?
> I have tried to use a min function to compare the query
> date and effective and choose the min non-negative number.
> But it seems work fine for single row record, but if
> multiple record, it seems difficult to do the grouping.
> Thx..sql

Find the most recent date of the past

Hello,

I've a table which contains the nest following data:

Date Sales Price

01-01-07 5,00

31-03-07 6,00

16-04-07 5.75

26-04-07 6.25

For example, today is 18-04-07. In my report I want to show the most recent Sales Price, but not the price of next week. I want to see the SalesPrice of 5.75.

How can I get it?

Thx!

This may work... there's probably an easier way.

select top 1 [date], [salesprice]

from table

group by [date], [salesprice]

having max(convert(varchar(10),[date],112)) < convert(varchar(10),current_timestamp,112)

order by [date] desc

cheers,

Andrew

|||

Here's a version without the grouping. Try this:

select top 1 SalesPrice

from PriceInfoTable

where Date < getdate()

order by Date desc

Jarret

|||

Jarret,

Your solutions works, but I discovered that my problem is a little bit more complex. I still have the same table, but in the table I have more items. My table looks like (for example):

No. Date Sales Price

001 01-01-07 5,00

001 31-03-07 6,00

001 16-04-07 5.75

001 26-04-07 6.25

002 21-01-07 7,00

002 27-02-07 7,00

002 06-04-07 7.75

002 26-04-07 7.25

003 11-01-07 2,00

003 17-02-07 1,00

003 01-04-07 0.75

003 26-04-07 1.25

If I use your code, I only get ItemNo. 001 with a price of 5.75. But I want for every item the most recent price:

001 5.75

002 7.75

003 0.75

I've tried some options, but I can't get the solution. Do you have any suggestions?

|||

Here's two ways you could do it...

select distinct t1.id,

( select top 1 price

from PriceInfoTable

where Date < getdate()

and t1.id = #t.id

order by Date desc

) price

from PriceInfoTable t1

select t1.id, t1.price

from PriceInfoTable t1

inner join (

select id, max(date) as MaxDate

from PriceInfoTable

where Date < getdate()

group by id

) t2 on t1.id = t2.id and t1.date = t2.MaxDate

order by t1.id

Hope this helps.

Jarret

|||

Jarret,

I don't get the first option.

Can you tell me what the function of 't1.id' is? And also from 't1.id=#t.id.'? Maybe them I can implement it to my query......

thx

|||

No the first option has a type, it is actually:

select distinct t1.id,

( select top 1 price

from PriceInfoTable t2

where Date < getdate()

and t1.id = t2.id

order by Date desc

) price

from PriceInfoTable t1

You can also use this query which is the 3rd option:

SELECT [No], SalesPrice

FROM PriceInfoTable p1

WHERE Date = (SELECT MAX(Date) FROM PriceInfoTable p2 WHERE p2.No = p1.No AND Date < GETDATE())

Shyam

|||

Sorry,

I was working with my own sample query to get the results you needed and was using a temp table (#t) to hold the data.

The t1.id corresponds to your "No." field and the #t corresponds to the PriceInfoTable in the subquery. Shyam's post has my query with it fixed. Sorry for the inconvenience.

Jarret

|||

Jarret/Shyam,

I got all the 3 solutions implemented and they are working

But the next step for me is to add another column at the "select top"-selection.

In my table I have also a 'Sales Code'. In my report the user must set a filter on this 'Sales Code' (with a parameter). The table looks like:

No. StartingDate Sales Price Sales Code

001 01-01-07 5,00 SC01

001 31-03-07 6,00 SC01

001 16-04-07 5.75 SC02

001 26-04-07 6.25 SC01

If the user select SC01, the price of 6.00 has to be displayed. By selecting SC02, 5.75.

I have tried to insert 'Sales Code' in one of your suggestions on this way,

select distinct t1.id,

( select top 1 price, Sales Code

from PriceInfoTable

where Date < getdate()

and t1.id = #t.id

order by Date desc

) price

from PriceInfoTable t1

but an error returns.

Also inserting 'Sales Code' after the 'Select distinct t1.id'-part, wasn't the right way. By doing this the same price was given to every sales code.

Can you help me out?

Thx a lot!

|||

Try this:

select t1.id, t1.price, t1.[sales code]

from PriceInfoTable t1

inner join (

select id, max(date) as MaxDate

from PriceInfoTable

where Date < getdate()

and [Sales Code] = @.SalesCodeParameter

group by id

) t2 on t1.id = t2.id and t1.date = t2.MaxDate

order by t1.id

Hope this helps.

Jarret

|||

Jarret,

thx for your help. Your suggestion is working!

But I see that I've me an error by posting an example of the table. The field StartingDate is not only the date but also the time, it's the starting date and time. So the code-part of

WHERE([Starting Date Time]) < GETDATE

has to be something else. I have tried GETDATETIME, but that was not correct. How can I solve it?

Starting Date Time is a combined field. The fields Starting Date and Starting Time are also seperated available, if necessary.

Thx

|||

Try this:

WHERE([Starting Date Time]) < GETDATE()

Jarret

|||

Jarret,

I did that suggestion and it didn't worked out.

The problem is that the report is filtering only on date, not on date and time....

Do you have a suggestion?

Thx

|||

This will remove the time from the current date time.

WHERE([Starting Date Time]) < convert(varchar, GETDATE(), 101)

If you need to remove the time part off of [Starting Date Time], just wrap it in the convert statement as well.

Hope this helps.

Jarret

|||

Jarret,

More than one price for a date can be inserted In my table. I can insert a price and give it a starting time of 22.00 tonight. Of course I want to see the price of now if it is not yet 22.00.

I think I need more something like GETTIME or someting like that.

I was thinking of a statement with

WHERE(([Starting Date Time]) < GETDATE() and Starting Date Time]) < GETTIME()

in it.

But this one is not working.

Do you have any suggestion?

Thx

Find the most recent date of the past

Hello,

I've a table which contains the nest following data:

Date Sales Price

01-01-07 5,00

31-03-07 6,00

16-04-07 5.75

26-04-07 6.25

For example, today is 18-04-07. In my report I want to show the most recent Sales Price, but not the price of next week. I want to see the SalesPrice of 5.75.

How can I get it?

Thx!

This may work... there's probably an easier way.

select top 1 [date], [salesprice]

from table

group by [date], [salesprice]

having max(convert(varchar(10),[date],112)) < convert(varchar(10),current_timestamp,112)

order by [date] desc

cheers,

Andrew

|||

Here's a version without the grouping. Try this:

select top 1 SalesPrice

from PriceInfoTable

where Date < getdate()

order by Date desc

Jarret

|||

Jarret,

Your solutions works, but I discovered that my problem is a little bit more complex. I still have the same table, but in the table I have more items. My table looks like (for example):

No. Date Sales Price

001 01-01-07 5,00

001 31-03-07 6,00

001 16-04-07 5.75

001 26-04-07 6.25

002 21-01-07 7,00

002 27-02-07 7,00

002 06-04-07 7.75

002 26-04-07 7.25

003 11-01-07 2,00

003 17-02-07 1,00

003 01-04-07 0.75

003 26-04-07 1.25

If I use your code, I only get ItemNo. 001 with a price of 5.75. But I want for every item the most recent price:

001 5.75

002 7.75

003 0.75

I've tried some options, but I can't get the solution. Do you have any suggestions?

|||

Here's two ways you could do it...

select distinct t1.id,

( select top 1 price

from PriceInfoTable

where Date < getdate()

and t1.id = #t.id

order by Date desc

) price

from PriceInfoTable t1

select t1.id, t1.price

from PriceInfoTable t1

inner join (

select id, max(date) as MaxDate

from PriceInfoTable

where Date < getdate()

group by id

) t2 on t1.id = t2.id and t1.date = t2.MaxDate

order by t1.id

Hope this helps.

Jarret

|||

Jarret,

I don't get the first option.

Can you tell me what the function of 't1.id' is? And also from 't1.id=#t.id.'? Maybe them I can implement it to my query......

thx

|||

No the first option has a type, it is actually:

select distinct t1.id,

( select top 1 price

from PriceInfoTable t2

where Date < getdate()

and t1.id = t2.id

order by Date desc

) price

from PriceInfoTable t1

You can also use this query which is the 3rd option:

SELECT [No], SalesPrice

FROM PriceInfoTable p1

WHERE Date = (SELECT MAX(Date) FROM PriceInfoTable p2 WHERE p2.No = p1.No AND Date < GETDATE())

Shyam

|||

Sorry,

I was working with my own sample query to get the results you needed and was using a temp table (#t) to hold the data.

The t1.id corresponds to your "No." field and the #t corresponds to the PriceInfoTable in the subquery. Shyam's post has my query with it fixed. Sorry for the inconvenience.

Jarret

|||

Jarret/Shyam,

I got all the 3 solutions implemented and they are working

But the next step for me is to add another column at the "select top"-selection.

In my table I have also a 'Sales Code'. In my report the user must set a filter on this 'Sales Code' (with a parameter). The table looks like:

No. StartingDate Sales Price Sales Code

001 01-01-07 5,00 SC01

001 31-03-07 6,00 SC01

001 16-04-07 5.75 SC02

001 26-04-07 6.25 SC01

If the user select SC01, the price of 6.00 has to be displayed. By selecting SC02, 5.75.

I have tried to insert 'Sales Code' in one of your suggestions on this way,

select distinct t1.id,

( select top 1 price, Sales Code

from PriceInfoTable

where Date < getdate()

and t1.id = #t.id

order by Date desc

) price

from PriceInfoTable t1

but an error returns.

Also inserting 'Sales Code' after the 'Select distinct t1.id'-part, wasn't the right way. By doing this the same price was given to every sales code.

Can you help me out?

Thx a lot!

|||

Try this:

select t1.id, t1.price, t1.[sales code]

from PriceInfoTable t1

inner join (

select id, max(date) as MaxDate

from PriceInfoTable

where Date < getdate()

and [Sales Code] = @.SalesCodeParameter

group by id

) t2 on t1.id = t2.id and t1.date = t2.MaxDate

order by t1.id

Hope this helps.

Jarret

|||

Jarret,

thx for your help. Your suggestion is working!

But I see that I've me an error by posting an example of the table. The field StartingDate is not only the date but also the time, it's the starting date and time. So the code-part of

WHERE([Starting Date Time]) < GETDATE

has to be something else. I have tried GETDATETIME, but that was not correct. How can I solve it?

Starting Date Time is a combined field. The fields Starting Date and Starting Time are also seperated available, if necessary.

Thx

|||

Try this:

WHERE([Starting Date Time]) < GETDATE()

Jarret

|||

Jarret,

I did that suggestion and it didn't worked out.

The problem is that the report is filtering only on date, not on date and time....

Do you have a suggestion?

Thx

|||

This will remove the time from the current date time.

WHERE([Starting Date Time]) < convert(varchar, GETDATE(), 101)

If you need to remove the time part off of [Starting Date Time], just wrap it in the convert statement as well.

Hope this helps.

Jarret

|||

Jarret,

More than one price for a date can be inserted In my table. I can insert a price and give it a starting time of 22.00 tonight. Of course I want to see the price of now if it is not yet 22.00.

I think I need more something like GETTIME or someting like that.

I was thinking of a statement with

WHERE(([Starting Date Time]) < GETDATE() and Starting Date Time]) < GETTIME()

in it.

But this one is not working.

Do you have any suggestion?

Thx

Find Table Size

Env: SQL Server 2000

The following sql stmt seems to find a particular table's size
programmatically:

select top 1 [rows],rowcnt
from sysindexes
where ID = object_id('aUserTable')
and status = 0
and used > 0

However,
a) I'm not 100% sure of its consistency;
b) Both [rows] col and [rowcnt] col seems to produce same data, which
one is supposed to be more accurate (or more up to date)?

TIA.NickName (dadada@.rock.com) writes:
> The following sql stmt seems to find a particular table's size
> programmatically:
> select top 1 [rows],rowcnt
> from sysindexes
> where ID = object_id('aUserTable')
> and status = 0
> and used > 0
> However,
> a) I'm not 100% sure of its consistency;
> b) Both [rows] col and [rowcnt] col seems to produce same data, which
> one is supposed to be more accurate (or more up to date)?

A TOP 1 without ORDER BY is not a good thing. However, if you with
"size" means rowcount, this may do. Better though is to add the
"AND indid IN (0, 1)" to the WHERE clause. (There is never rows for
both 0 and 1, but always for exactly one of them.)

The values in sysindexes are not the exact values, for that you need to
do SELECT COUNT(*). However, a DBCC UPDATEUSAGE before you run the
SELECT query will give you good accuracy.

Judging from the documentation, rowcnt is the better column to use.

--
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,

Yes, when I say table size I meant row count of a table.

Results of some testing of the following queries:

select rowcnt
from sysindexes
where ID = object_id('customer')
-- and status = 0
and used > 0
AND indid IN (0, 1)
produces correct resultset

select rowcnt
from sysindexes
where ID = object_id('customer')
and status = 0
and used > 0
AND indid IN (0, 1)

produces incorrect resultset (zero count)

Not to use SELECT COUNT(*) ... is because I intend to get row count for
each and all tables of a huge database, COUNT would take considerable
longer to do.

You're the man!

Don

Find subsequent days

I have the following table:
CREATE TABLE [dbo].[ShortStay](
[SS_ID] [int] IDENTITY(1,1) NOT NULL,
[SS_WLID] [int] NULL,
[SS_From] [smalldatetime] NOT NULL,
[SS_Till] [smalldatetime] NOT NULL)
There are these records for example:
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-05', '2006-05-21')
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-02', '2006-05-04')
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-22', '2006-05-25')
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (4, '2006-05-05', '2006-05-21')
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-26', '2006-05-29')
I'm trying to find a query that returns the number of consequent days
based on a certain date:
So if I input: '2006-05-09' and SS_WLID = 3
then I should get: 2006-05-02 till 2006-05-29 thus: 28 days.
You get 2006-05-02 because the SS_Till is the day before 2006-05-05
that's why this row is ok.
You get 2006-05-29 because:
2006-05-09 - 2006-05-21 - 2006-05-22 - 2006-05-25 - 2006-05-26 -
2006-05-29
I hope this makes sense. So the days have to be consequent in order to
be able to be part of the count.
I have been trying to join the table to itself but I'm getting nowhere.
Thanks in advance,
Stijn Verrept.Consider creating an auxiliary Calendar table.
http://www.aspfaq.com/show.asp?id=2519
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:0--dnTs9fOP8yD_ZRVny0A@.scarlet.biz...
>I have the following table:
> CREATE TABLE [dbo].[ShortStay](
> [SS_ID] [int] IDENTITY(1,1) NOT NULL,
> [SS_WLID] [int] NULL,
> [SS_From] [smalldatetime] NOT NULL,
> [SS_Till] [smalldatetime] NOT NULL)
>
> There are these records for example:
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-05', '2006-05-21')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-02', '2006-05-04')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-22', '2006-05-25')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (4, '2006-05-05', '2006-05-21')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-26', '2006-05-29')
> I'm trying to find a query that returns the number of consequent days
> based on a certain date:
> So if I input: '2006-05-09' and SS_WLID = 3
> then I should get: 2006-05-02 till 2006-05-29 thus: 28 days.
> You get 2006-05-02 because the SS_Till is the day before 2006-05-05
> that's why this row is ok.
> You get 2006-05-29 because:
> 2006-05-09 - 2006-05-21 - 2006-05-22 - 2006-05-25 - 2006-05-26 -
> 2006-05-29
> I hope this makes sense. So the days have to be consequent in order to
> be able to be part of the count.
> I have been trying to join the table to itself but I'm getting nowhere.
>
> --
> Thanks in advance,
> Stijn Verrept.|||Roji. P. Thomas wrote:

> Consider creating an auxiliary Calendar table.
> http://www.aspfaq.com/show.asp?id=2519
Thanks for the link, I already have a Calendar table but don't see how
this solves the subsequent problem, it's in none of the examples on the
site.
Kind regards,
Stijn Verrept.|||Stijn,
google up "How to simplify a query using a calendar table", hope that
helps|||How about something like this (note that it looks for consecutive days,
defined exactly as SS_From = prior SS_Till + 1). Hours, minutes, etc.,
aren't taken into consideration. Neither are overlapping dates.
DECLARE @.date DATETIME
SELECT @.date = '2006-05-09'
DECLARE @.SS_ID INT
SELECT @.SS_ID = 3
SELECT 'Begin' AS DateType, s1.Start
FROM (
SELECT MIN(SS_From) AS Start
FROM ShortStay
WHERE @.date BETWEEN SS_From AND SS_Till
AND SS_WLID = @.SS_ID
) s1
UNION ALL
SELECT 'End', s2.Finish
FROM
(
SELECT MAX(s1.SS_Till) AS Finish
FROM ShortStay s1, ShortStay s2
WHERE s1.SS_From = s2.SS_Till + 1
AND s1.SS_From <>
(
SELECT MIN(SS_From)
FROM ShortStay
WHERE SS_WLID = @.SS_ID
AND @.date BETWEEN SS_From AND SS_Till
)
AND s1.SS_WLID = s2.SS_WLID
AND s1.SS_WLID = @.SS_ID
) s2
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:0--dnTs9fOP8yD_ZRVny0A@.scarlet.biz...
>I have the following table:
> CREATE TABLE [dbo].[ShortStay](
> [SS_ID] [int] IDENTITY(1,1) NOT NULL,
> [SS_WLID] [int] NULL,
> [SS_From] [smalldatetime] NOT NULL,
> [SS_Till] [smalldatetime] NOT NULL)
>
> There are these records for example:
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-05', '2006-05-21')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-02', '2006-05-04')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-22', '2006-05-25')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (4, '2006-05-05', '2006-05-21')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-26', '2006-05-29')
> I'm trying to find a query that returns the number of consequent days
> based on a certain date:
> So if I input: '2006-05-09' and SS_WLID = 3
> then I should get: 2006-05-02 till 2006-05-29 thus: 28 days.
> You get 2006-05-02 because the SS_Till is the day before 2006-05-05
> that's why this row is ok.
> You get 2006-05-29 because:
> 2006-05-09 - 2006-05-21 - 2006-05-22 - 2006-05-25 - 2006-05-26 -
> 2006-05-29
> I hope this makes sense. So the days have to be consequent in order to
> be able to be part of the count.
> I have been trying to join the table to itself but I'm getting nowhere.
>
> --
> Thanks in advance,
> Stijn Verrept.|||Hi There,
You may like to try this!!
I assume that SS_From is smaller than SS_Till
Select Min(SS_From),Max(SS_Till) from ShortStay where SS_WLID=3
And convert(varchar(6),'20060509',112) =
convert(varchar(6),SS_From,112)
And convert(varchar(6),'20060509',112) =
convert(varchar(6),SS_Till,112)
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
Mike C# wrote:
> How about something like this (note that it looks for consecutive days,
> defined exactly as SS_From = prior SS_Till + 1). Hours, minutes, etc.,
> aren't taken into consideration. Neither are overlapping dates.
> DECLARE @.date DATETIME
> SELECT @.date = '2006-05-09'
> DECLARE @.SS_ID INT
> SELECT @.SS_ID = 3
> SELECT 'Begin' AS DateType, s1.Start
> FROM (
> SELECT MIN(SS_From) AS Start
> FROM ShortStay
> WHERE @.date BETWEEN SS_From AND SS_Till
> AND SS_WLID = @.SS_ID
> ) s1
> UNION ALL
> SELECT 'End', s2.Finish
> FROM
> (
> SELECT MAX(s1.SS_Till) AS Finish
> FROM ShortStay s1, ShortStay s2
> WHERE s1.SS_From = s2.SS_Till + 1
> AND s1.SS_From <>
> (
> SELECT MIN(SS_From)
> FROM ShortStay
> WHERE SS_WLID = @.SS_ID
> AND @.date BETWEEN SS_From AND SS_Till
> )
> AND s1.SS_WLID = s2.SS_WLID
> AND s1.SS_WLID = @.SS_ID
> ) s2
> "Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
> news:0--dnTs9fOP8yD_ZRVny0A@.scarlet.biz...|||Mike C# wrote:

> How about something like this (note that it looks for consecutive
> days, defined exactly as SS_From = prior SS_Till + 1). Hours,
> minutes, etc., aren't taken into consideration. Neither are
> overlapping dates.
> DECLARE @.date DATETIME
> SELECT @.date = '2006-05-09'
> DECLARE @.SS_ID INT
> SELECT @.SS_ID = 3
Hi Mike,
Thanks for the reply! This one is very close but it doesn't look for
any prior consecutive rows.
In the examply I gave:
So if I input: '2006-05-09' and SS_WLID = 3
then I should get: 2006-05-02 till 2006-05-29 thus: 28 days.
Your query returns '2006-05-05' and '2006-05-29'
It doesn't consider the row:
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-02', '2006-05-04')
Thanks again,
Stijn Verrept.|||People will tell you to use a Calendar table. But your real problem is
a bad design. You have no key (IDENTITY cannot ever be a key! Quit
mimicing a sequential file), and your "wl_id" can be NULL so the query
is impossible to answer. You also have no constraints and use
proprietary data types. Then on top of all of that, you have attribute
splitting -- the fact of a continous stay is spread over many rows.
Why did you use a singular name for a table with more than one element
in it? You have no idea what an identifier is and stuck it on
everything. Your table should look more like this:
CREATE TABLE ShortStays
(wl_id INTEGER NOT NULL,
arrival_date DATETIME NOT NULL,
depart_date DATETIME NOT NULL,
CHECK(arrival_date < depart_date),
PRIMARY KEY (wl_id, arrival_date));
When a stay is extended, you update it instead of mimicking a paper
hotel register form. Ideally, you should have constraint to prevent
overlaps, but that is hard to do in SQL Server since it still lacks
much of the SQL-92 features. You can do it with a TRIGGER or with an
updatable VIEW that has WITH CHECK OPTION clause and a Calendar table.
Please learn the differences between rows and records. Fail to know
that lead to attribute splitting.|||--CELKO-- wrote:

> People will tell you to use a Calendar table. But your real problem
> is a bad design. You have no key (IDENTITY cannot ever be a key!
> Quit mimicing a sequential file), and your "wl_id" can be NULL so the
> query is impossible to answer. You also have no constraints and use
> proprietary data types. Then on top of all of that, you have
> attribute splitting -- the fact of a continous stay is spread over
> many rows.
Well Joe, the table creation script given here is just a simplified
version of the real table so hold your horses. People can stay in
different rooms and can be transferred from one room to another so
updating instead of inserting is not an option here. I didn't include
the column of the room because for this query it is irrelevant!

> When a stay is extended, you update it instead of mimicking a paper
> hotel register form. Ideally, you should have constraint to prevent
> overlaps, but that is hard to do in SQL Server since it still lacks
> much of the SQL-92 features. You can do it with a TRIGGER or with an
> updatable VIEW that has WITH CHECK OPTION clause and a Calendar table.
I already have a trigger that prevents overlap so don't worry about
that.

> Please learn the differences between rows and records. Fail to know
> that lead to attribute splitting.
LOL
"In the context of a relational database, a rowalso called a record or
tuplerepresents a single, implicitly structured data item in a table."
Wikipedia.
Will learning the naming difference really solve my query? I think not.
Thanks for your very helpful reply!
Kind regards,
Stijn Verrept.

Find Non-Matching End Points

Hello,

I'm trying to manipulate some data to find occurances where a certain end point it missing. For example, in my table I have the following record:

Sample

SHAPE_ID

SEQ_NUM

X_COORDINATE

Y_COORDINATE

82

0

606494.606

4820354.142

82

1

606487.996

4820344.571

82

2

606512.455

4820319.828

82

3

606590.877

4820420.208

82

4

606611.069

4820404.432

82

5

606634.138

4820434.514

82

6

606614.812

4820449.179

82

7

606670.587

4820520.016

82

8

606638.161

4820546.216

82

9

606500.606

4820400.142

For shape_ id = 82 (I have thousands of these to process), Seq_num = 0 is the beginning of the item, while seq_num 9 is the end point. The end point (x_coordinate/y_coordinate)i.e. Seq_num=9 is supposed to match the begin x_coordinate/y_coordinate values, but in this example, they do not match.

How do I find each occurance of the same shape_id where the end seq_num (#9, or whatever number is has been incremented to) x_coordinate/y_cooordinate, does not match the begin x_coordinate/y_coordinate seq_num (#0) ?

As well, if any of these records are indeed found, how do I increment to the next highest sequence number and update that record with the begin point (seq_num=0) x_coordinate/y_coordinate values?

I would appreciate any help with this problem....Thx. in advance...Walt

Moving to the Transact-SQL forum. I believe that's better equipped to provide an answer.

|||

Walt:

Is this close to what you are looking for:

set nocount on

declare @.sample table
( shape_id integer,
seq_num integer,
x_coordinate numeric (12,3),
y_coordinate numeric (12,3),
primary key (shape_id, seq_num)
)
insert into @.sample values (82, 0, 606494.606, 4820354.142 )
insert into @.sample values (82, 1, 606487.996, 4820344.571 )
insert into @.sample values (82, 2, 606512.455, 4820319.828 )
insert into @.sample values (82, 3, 606590.877, 4820420.208 )
insert into @.sample values (82, 4, 606611.069, 4820404.432 )
insert into @.sample values (82, 5, 606634.138, 4820434.514 )
insert into @.sample values (82, 6, 606614.812, 4820449.179 )
insert into @.sample values (82, 7, 606670.587, 4820520.016 )
insert into @.sample values (82, 8, 606638.161, 4820546.216 )
insert into @.sample values (82, 9, 606500.606, 4820400.142 )
--insert into @.sample values (82,10, 606494.606, 4820354.142 )

--select * from @.sample

select a.shape_id,
a.seq_num,
a.x_coordinate as [Last X],
a.y_coordinate as [Last Y],
z.x_coordinate as [Zero X],
z.y_coordinate as [Zero Y]
from ( select shape_id,
seq_num,
x_coordinate,
y_coordinate
from @.sample p
where seq_num
= ( select max (seq_num) from @.sample q
where p.shape_id = q.shape_id
and q.seq_num > 0
)
) a
inner join @.sample z
on a.shape_id = z.shape_id
and z.seq_num = 0
and ( a.x_coordinate <> z.x_coordinate or
a.y_coordinate <> z.y_coordinate
)

-- shape_id seq_num Last X Last Y Zero X Zero Y
-- -- -- -- -- -- --
-- 82 9 606500.606 4820400.142 606494.606 4820354.142

update @.sample
set x_coordinate = a.x_coordinate,
y_coordinate = a.y_coordinate
from ( select shape_id,
seq_num,
x_coordinate,
y_coordinate
from @.sample p
where seq_num
= ( select max (seq_num) from @.sample q
where p.shape_id = q.shape_id
and q.seq_num > 0
)
) a
inner join @.sample z
on a.shape_id = z.shape_id
and z.seq_num = 0
and ( a.x_coordinate <> z.x_coordinate or
a.y_coordinate <> z.y_coordinate
)

select * from @.sample where seq_num = 0

-- shape_id seq_num x_coordinate y_coordinate
-- -- -- -- --
-- 82 0 606500.606 4820400.142

Dave

|||

Dave,

Thanks for your prompt reply. I'll hopefully give your code a try tomorrow.

Is this a sql statement that I can paste into the Sql View of a new sql statement?

Sorry for such a dumb question......Walt

|||

Dave,

I'm in the process of importing my table into my Oracle schema and am just in the process of creating a new table.

I have over 500,000 records to load so I'll give your reply a try.

thx again....Walt

Find instances of sql running

Is it possible to do the following. If yes then how ?
- Is there a way to find out all the instances of the SQL server running ?
- Read the alerts generated by SQL server.
- Read the logs generated by SQL server.
Thanks,
-FatemaFatema
CREATE PROCEDURE dbo.ListLocalServers
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #servers(sname VARCHAR(255))
INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'
DELETE #servers WHERE sname='Servers:'
SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'
DROP TABLE #servers
END
Read up in the BOL chapter "Viewing the SQL Server Error Log"
"Fatema" <Fatema@.discussions.microsoft.com> wrote in message
news:02D6FC75-FC5B-47A8-ACAE-90D350B4FC90@.microsoft.com...
> Is it possible to do the following. If yes then how ?
> - Is there a way to find out all the instances of the SQL server running ?
> - Read the alerts generated by SQL server.
> - Read the logs generated by SQL server.
> Thanks,
> -Fatema|||Thanks for the reply.
On executing this procedure it gives me an error:
Login Failed. Reason: not associated with a trusted SQL server connection.
What is the problem ?
TIA,
-Fatema
"Uri Dimant" wrote:

> Fatema
> CREATE PROCEDURE dbo.ListLocalServers
> AS
> BEGIN
> SET NOCOUNT ON
> CREATE TABLE #servers(sname VARCHAR(255))
> INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'
> DELETE #servers WHERE sname='Servers:'
> SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'
> DROP TABLE #servers
> END
>
> Read up in the BOL chapter "Viewing the SQL Server Error Log"
> "Fatema" <Fatema@.discussions.microsoft.com> wrote in message
> news:02D6FC75-FC5B-47A8-ACAE-90D350B4FC90@.microsoft.com...
>
>|||Fatema
Change (SQL Server Properties)
under Security Tab -Authentication to SQL Server and Windows
"Fatema" <Fatema@.discussions.microsoft.com> wrote in message
news:32B44E26-E20A-40A7-BB3C-CC526F2E0BF3@.microsoft.com...
> Thanks for the reply.
> On executing this procedure it gives me an error:
> Login Failed. Reason: not associated with a trusted SQL server connection.
> What is the problem ?
> TIA,
> -Fatema
> "Uri Dimant" wrote:
>
running ?|||I am getting the following o/p :
Name (column)
--
Password
1>
WHat does this mean ?
-Fatema
"Uri Dimant" wrote:

> Fatema
> Change (SQL Server Properties)
> under Security Tab -Authentication to SQL Server and Windows
>
>
> "Fatema" <Fatema@.discussions.microsoft.com> wrote in message
> news:32B44E26-E20A-40A7-BB3C-CC526F2E0BF3@.microsoft.com...
> running ?
>
>|||1. Not in a reliable way. Uri posted a method, but please be aware that all
methods (all are based
on API calls in Windows) are based on browser service in Windows and it is n
ot guaranteed to show
all servers, also servers can be in the browser list after it was shut down.
2. Can you define what you mean by alerts?
3. Can you define what you mean by "logs"?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Fatema" <Fatema@.discussions.microsoft.com> wrote in message
news:02D6FC75-FC5B-47A8-ACAE-90D350B4FC90@.microsoft.com...
> Is it possible to do the following. If yes then how ?
> - Is there a way to find out all the instances of the SQL server running ?
> - Read the alerts generated by SQL server.
> - Read the logs generated by SQL server.
> Thanks,
> -Fatema|||Can you please tell me how is it possible to programmatically find out all
instances of sql server that are running ?
By logs I mean the logs that sql server generates during its course of
execution.
Thanks,
-fatema
"Tibor Karaszi" wrote:

> 1. Not in a reliable way. Uri posted a method, but please be aware that al
l methods (all are based
> on API calls in Windows) are based on browser service in Windows and it is
not guaranteed to show
> all servers, also servers can be in the browser list after it was shut dow
n.
> 2. Can you define what you mean by alerts?
> 3. Can you define what you mean by "logs"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Fatema" <Fatema@.discussions.microsoft.com> wrote in message
> news:02D6FC75-FC5B-47A8-ACAE-90D350B4FC90@.microsoft.com...
>
>|||> Can you please tell me how is it possible to programmatically find out all
> instances of sql server that are running ?
As I already replied: Not reliably. Search the newsgroup archives and you wi
ll probably find posts
from Gert Drapers specifying the technicalities behind this. If you want to
list the servers anyhow
(unreliably), you have classes for that in, for instance, SQLDMO. See www.sqldev.n
et for examples.

> By logs I mean the logs that sql server generates during its course of
> execution.
Errorlog? Transaction log? Entries the in the eventlog? Do you want to get t
o this using TSQL or a
client programming language, like VB.NET?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Fatema" <Fatema@.discussions.microsoft.com> wrote in message
news:353E84EC-5811-4D21-86A4-19B8F5C3A69E@.microsoft.com...
> Can you please tell me how is it possible to programmatically find out all
> instances of sql server that are running ?
> By logs I mean the logs that sql server generates during its course of
> execution.
> Thanks,
> -fatema
>
> "Tibor Karaszi" wrote:
>|||Tibor,
Another problem with OSQL -L is that it list whatever alias you create with
"Client Network Utility" even though there is not a physical server
associated to this alias.
AMB
"Tibor Karaszi" wrote:

> 1. Not in a reliable way. Uri posted a method, but please be aware that al
l methods (all are based
> on API calls in Windows) are based on browser service in Windows and it is
not guaranteed to show
> all servers, also servers can be in the browser list after it was shut dow
n.
> 2. Can you define what you mean by alerts?
> 3. Can you define what you mean by "logs"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Fatema" <Fatema@.discussions.microsoft.com> wrote in message
> news:02D6FC75-FC5B-47A8-ACAE-90D350B4FC90@.microsoft.com...
>
>

Find duplicates

I have a query that I used that was based on the following query that Hugo
gave me yesterday (I added CheckNumber in for the problem).
I had a problem with finding duplicate check numbers in the table
(voids,re-issued checks etc). It does show in my results, but out of
4300+checks it is hard to find all the duplicates for auditing purposes
while setting up my script.
How do I go about changing my query to show ONLY duplicates (or vice versa)?
Here is the data:
drop table EmployeePay
drop table PayDetail
CREATE TABLE [dbo].[EmployeePay] (
[EmployeePayID] [int] IDENTITY (1, 1),
[ClientID] [int] Not NULL ,
[CheckNumber] [int] Not Null,
[PayDate] [smalldatetime] Not NULL,
PRIMARY KEY (EmployeePayID)
) ON [PRIMARY]
CREATE TABLE [dbo].[PayDetail] (
[PayDetailID] [int] IDENTITY (1, 1),
[ClientID] [int] Not NULL ,
[PayDate] [smalldatetime] Not NULL ,
[Code] [varchar] (5) Not NULL ,
[Hours] [int] Not NULL ,
[Amount] [money] Not NULL,
PRIMARY KEY (PayDetailID)
) ON [PRIMARY]
insert EmployeePay values (1,1234,'01-01-05')
insert EmployeePay values (1,2255,'02-03-05')
insert EmployeePay values (2,1331,'01-01-05')
insert EmployeePay values (2,3011,'03-06-05')
insert EmployeePay values (2,1331,'05-06-05')
insert PayDetail values (1,'01-01-05','5',20,200.50)
insert PayDetail values (1,'01-01-05','6',25,10.50)
insert PayDetail values (1,'01-01-05','13',50,120.25)
insert PayDetail values (1,'02-03-05','5',5,110)
insert PayDetail values (1,'02-03-05','9',18,250.50)
insert PayDetail values (2,'01-01-05','5',50,120.25)
insert PayDetail values (2,'01-01-05','44',10,320.32)
insert PayDetail values (2,'01-01-05','32',50,120.25)
insert PayDetail values (2,'03-06-05','46',18,235.75)
insert PayDetail values (2,'05-06-05','5',-50,-120.25)
insert PayDetail values (2,'05-06-05','44',-10,-320.32)
insert PayDetail values (2,'05-06-05','32',-50,-120.25)
and the script I am using:
CREATE VIEW PayDetailWithRank
AS
SELECT pd1.ClientID, pd1.PayDate, pd1.Code, pd1.Hours, pd1.Amount,
(SELECT COUNT(*)
FROM PayDetail AS pd2
WHERE pd2.ClientID = pd1.ClientID
AND pd2.PayDate = pd1.PayDate
AND pd2.Code < pd1.Code) AS Rank
FROM PayDetail AS pd1
go
SELECT e.ClientID, e.PayDate, e.CheckNumber,
d1.Code, d1.Hours, d1.Amount,
d2.Code, d2.Hours, d2.Amount,
d3.Code, d3.Hours, d3.Amount
FROM EmployeePay AS e
INNER JOIN PayDetailWithRank AS d1
ON d1.ClientID = e.ClientID
AND d1.PayDate = e.PayDate
AND d1.Rank % 3 = 0
LEFT JOIN PayDetailWithRank AS d2
ON d2.ClientID = e.ClientID
AND d2.PayDate = e.PayDate
AND d2.Rank / 3 = d1.Rank / 3
AND d2.Rank % 3 = 1
LEFT JOIN PayDetailWithRank AS d3
ON d3.ClientID = e.ClientID
AND d3.PayDate = e.PayDate
AND d3.Rank / 3 = d1.Rank / 3
AND d3.Rank % 3 = 2
ORDER BY e.ClientID, e.PayDate, d1.Rank
go
DROP VIEW PayDetailWithRank
go
This will give you 1 duplicate check. Can I change this to show only
duplicate, somehow?
Thanks,
TomNever mind.
I figure out how. I have to modify the statement slightly
I need to change the Order by to Group By and add a "Having Count(*) > 1"
clause.
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23tVBdqhuFHA.1472@.TK2MSFTNGP15.phx.gbl...
>I have a query that I used that was based on the following query that Hugo
>gave me yesterday (I added CheckNumber in for the problem).
> I had a problem with finding duplicate check numbers in the table
> (voids,re-issued checks etc). It does show in my results, but out of
> 4300+checks it is hard to find all the duplicates for auditing purposes
> while setting up my script.
> How do I go about changing my query to show ONLY duplicates (or vice
> versa)?
> Here is the data:
> drop table EmployeePay
> drop table PayDetail
> CREATE TABLE [dbo].[EmployeePay] (
> [EmployeePayID] [int] IDENTITY (1, 1),
> [ClientID] [int] Not NULL ,
> [CheckNumber] [int] Not Null,
> [PayDate] [smalldatetime] Not NULL,
> PRIMARY KEY (EmployeePayID)
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[PayDetail] (
> [PayDetailID] [int] IDENTITY (1, 1),
> [ClientID] [int] Not NULL ,
> [PayDate] [smalldatetime] Not NULL ,
> [Code] [varchar] (5) Not NULL ,
> [Hours] [int] Not NULL ,
> [Amount] [money] Not NULL,
> PRIMARY KEY (PayDetailID)
> ) ON [PRIMARY]
> insert EmployeePay values (1,1234,'01-01-05')
> insert EmployeePay values (1,2255,'02-03-05')
> insert EmployeePay values (2,1331,'01-01-05')
> insert EmployeePay values (2,3011,'03-06-05')
> insert EmployeePay values (2,1331,'05-06-05')
> insert PayDetail values (1,'01-01-05','5',20,200.50)
> insert PayDetail values (1,'01-01-05','6',25,10.50)
> insert PayDetail values (1,'01-01-05','13',50,120.25)
> insert PayDetail values (1,'02-03-05','5',5,110)
> insert PayDetail values (1,'02-03-05','9',18,250.50)
> insert PayDetail values (2,'01-01-05','5',50,120.25)
> insert PayDetail values (2,'01-01-05','44',10,320.32)
> insert PayDetail values (2,'01-01-05','32',50,120.25)
> insert PayDetail values (2,'03-06-05','46',18,235.75)
> insert PayDetail values (2,'05-06-05','5',-50,-120.25)
> insert PayDetail values (2,'05-06-05','44',-10,-320.32)
> insert PayDetail values (2,'05-06-05','32',-50,-120.25)
>
> and the script I am using:
> CREATE VIEW PayDetailWithRank
> AS
> SELECT pd1.ClientID, pd1.PayDate, pd1.Code, pd1.Hours, pd1.Amount,
> (SELECT COUNT(*)
> FROM PayDetail AS pd2
> WHERE pd2.ClientID = pd1.ClientID
> AND pd2.PayDate = pd1.PayDate
> AND pd2.Code < pd1.Code) AS Rank
> FROM PayDetail AS pd1
> go
> SELECT e.ClientID, e.PayDate, e.CheckNumber,
> d1.Code, d1.Hours, d1.Amount,
> d2.Code, d2.Hours, d2.Amount,
> d3.Code, d3.Hours, d3.Amount
> FROM EmployeePay AS e
> INNER JOIN PayDetailWithRank AS d1
> ON d1.ClientID = e.ClientID
> AND d1.PayDate = e.PayDate
> AND d1.Rank % 3 = 0
> LEFT JOIN PayDetailWithRank AS d2
> ON d2.ClientID = e.ClientID
> AND d2.PayDate = e.PayDate
> AND d2.Rank / 3 = d1.Rank / 3
> AND d2.Rank % 3 = 1
> LEFT JOIN PayDetailWithRank AS d3
> ON d3.ClientID = e.ClientID
> AND d3.PayDate = e.PayDate
> AND d3.Rank / 3 = d1.Rank / 3
> AND d3.Rank % 3 = 2
> ORDER BY e.ClientID, e.PayDate, d1.Rank
> go
> DROP VIEW PayDetailWithRank
> go
>
> This will give you 1 duplicate check. Can I change this to show only
> duplicate, somehow?
> Thanks,
> Tom
>|||Hi
Changing the first left outer join to be an inner join will ensure at least
one duplicate.
SELECT e.ClientID, e.PayDate, e.CheckNumber,
d1.Code, d1.Hours, d1.Amount, d1.rank,
d2.Code, d2.Hours, d2.Amount, d2.rank,
d3.Code, d3.Hours, d3.Amount, d3.rank
FROM EmployeePay AS e
JOIN PayDetailWithRank AS d1
ON d1.ClientID = e.ClientID
AND d1.PayDate = e.PayDate
AND d1.Rank % 3 = 0
JOIN PayDetailWithRank AS d2
ON d2.ClientID = e.ClientID
AND d2.PayDate = e.PayDate
AND d2.Rank / 3 = d1.Rank / 3
AND d2.Rank % 3 = 1
LEFT JOIN PayDetailWithRank AS d3
ON d3.ClientID = e.ClientID
AND d3.PayDate = e.PayDate
AND d3.Rank / 3 = d1.Rank / 3
AND d3.Rank % 3 = 2
ORDER BY e.ClientID, e.PayDate, d1.Rank
What are you expecting if there are 4 duplicates?
John
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23tVBdqhuFHA.1472@.TK2MSFTNGP15.phx.gbl...
>I have a query that I used that was based on the following query that Hugo
>gave me yesterday (I added CheckNumber in for the problem).
> I had a problem with finding duplicate check numbers in the table
> (voids,re-issued checks etc). It does show in my results, but out of
> 4300+checks it is hard to find all the duplicates for auditing purposes
> while setting up my script.
> How do I go about changing my query to show ONLY duplicates (or vice
> versa)?
> Here is the data:
> drop table EmployeePay
> drop table PayDetail
> CREATE TABLE [dbo].[EmployeePay] (
> [EmployeePayID] [int] IDENTITY (1, 1),
> [ClientID] [int] Not NULL ,
> [CheckNumber] [int] Not Null,
> [PayDate] [smalldatetime] Not NULL,
> PRIMARY KEY (EmployeePayID)
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[PayDetail] (
> [PayDetailID] [int] IDENTITY (1, 1),
> [ClientID] [int] Not NULL ,
> [PayDate] [smalldatetime] Not NULL ,
> [Code] [varchar] (5) Not NULL ,
> [Hours] [int] Not NULL ,
> [Amount] [money] Not NULL,
> PRIMARY KEY (PayDetailID)
> ) ON [PRIMARY]
> insert EmployeePay values (1,1234,'01-01-05')
> insert EmployeePay values (1,2255,'02-03-05')
> insert EmployeePay values (2,1331,'01-01-05')
> insert EmployeePay values (2,3011,'03-06-05')
> insert EmployeePay values (2,1331,'05-06-05')
> insert PayDetail values (1,'01-01-05','5',20,200.50)
> insert PayDetail values (1,'01-01-05','6',25,10.50)
> insert PayDetail values (1,'01-01-05','13',50,120.25)
> insert PayDetail values (1,'02-03-05','5',5,110)
> insert PayDetail values (1,'02-03-05','9',18,250.50)
> insert PayDetail values (2,'01-01-05','5',50,120.25)
> insert PayDetail values (2,'01-01-05','44',10,320.32)
> insert PayDetail values (2,'01-01-05','32',50,120.25)
> insert PayDetail values (2,'03-06-05','46',18,235.75)
> insert PayDetail values (2,'05-06-05','5',-50,-120.25)
> insert PayDetail values (2,'05-06-05','44',-10,-320.32)
> insert PayDetail values (2,'05-06-05','32',-50,-120.25)
>
> and the script I am using:
> CREATE VIEW PayDetailWithRank
> AS
> SELECT pd1.ClientID, pd1.PayDate, pd1.Code, pd1.Hours, pd1.Amount,
> (SELECT COUNT(*)
> FROM PayDetail AS pd2
> WHERE pd2.ClientID = pd1.ClientID
> AND pd2.PayDate = pd1.PayDate
> AND pd2.Code < pd1.Code) AS Rank
> FROM PayDetail AS pd1
> go
> SELECT e.ClientID, e.PayDate, e.CheckNumber,
> d1.Code, d1.Hours, d1.Amount,
> d2.Code, d2.Hours, d2.Amount,
> d3.Code, d3.Hours, d3.Amount
> FROM EmployeePay AS e
> INNER JOIN PayDetailWithRank AS d1
> ON d1.ClientID = e.ClientID
> AND d1.PayDate = e.PayDate
> AND d1.Rank % 3 = 0
> LEFT JOIN PayDetailWithRank AS d2
> ON d2.ClientID = e.ClientID
> AND d2.PayDate = e.PayDate
> AND d2.Rank / 3 = d1.Rank / 3
> AND d2.Rank % 3 = 1
> LEFT JOIN PayDetailWithRank AS d3
> ON d3.ClientID = e.ClientID
> AND d3.PayDate = e.PayDate
> AND d3.Rank / 3 = d1.Rank / 3
> AND d3.Rank % 3 = 2
> ORDER BY e.ClientID, e.PayDate, d1.Rank
> go
> DROP VIEW PayDetailWithRank
> go
>
> This will give you 1 duplicate check. Can I change this to show only
> duplicate, somehow?
> Thanks,
> Tom
>|||>> had a problem with finding duplicate check numbers in the table (voids,r
e-issued checks etc). <<
I have a better question for you: why are you creating duplicate check
numbers? Do you think that perhaps not having a proper relational key
could be part of the problem?
If you will learn to write proper DDL, you will save a lot of complex
DML.
Also, look up the ISO-8601 format for dates and times.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1126814005.385526.122020@.z14g2000cwz.googlegroups.com...
> I have a better question for you: why are you creating duplicate check
> numbers? Do you think that perhaps not having a proper relational key
> could be part of the problem?
As I had mentioned, this is not my database. I built a table to mimic that
tables I am extracting information from to create a CSV file that I can use
to import into our system. I am not creating Duplicate Check numbers - that
is what is in their system and I have to deal with it. I have over 5000
checks and as it turned out 5 duplicate check numbers. I used my group
by/having solutions to find them. As I mentioned, they were voids (hence
the duplicate check number) as well as a check that was re-issued (we don't
know why yet).
Are you suggesting I should spend my time creating a PROPER database schema,
even if it doesn't mirror what I am trying to accomplish?
And BTW, you have pounded incessantly about the evils of the IDENTITY.
I for one am grateful they used it, as you would have seen in my other post.
Hugos solution with the Views worked great, but hadn't taken into account
multiple codes on one check (which there would have been no way for him to
know this). I found that their tables used identities and that was the only
field I could replace the Code field with to make it work. It had to be
sequential and unique - Gaps would not have been an issue.
Tom
> If you will learn to write proper DDL, you will save a lot of complex
> DML.
> Also, look up the ISO-8601 format for dates and times.
>|||On Thu, 15 Sep 2005 13:40:38 -0700, tshad wrote:
(snip)
>Hugos solution with the Views worked great, but hadn't taken into account
>multiple codes on one check (which there would have been no way for him to
>know this). I found that their tables used identities and that was the onl
y
>field I could replace the Code field with to make it work. It had to be
>sequential and unique - Gaps would not have been an issue.
Hi Tom,
It would also have been solveable without the identity column. If there
was no single column to make each row unique within Client/Paydate, I'd
have used a combination of two columns. Or three. Or ... well, you get
the point. The code would have been more messy, but it would have
worked.
The only thing I would not have been able to handle were complete
duplicates (i.e. two or more rows with the same value in each of their
columns). But if you had those, you'd have much bigger problems to solve
than generating a kind-of-crosstab report. <g>
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:5oqji11o59rufd33auh0psimi867s65rj7@.
4ax.com...
> On Thu, 15 Sep 2005 13:40:38 -0700, tshad wrote:
> (snip)
> Hi Tom,
> It would also have been solveable without the identity column. If there
> was no single column to make each row unique within Client/Paydate, I'd
> have used a combination of two columns. Or three. Or ... well, you get
> the point. The code would have been more messy, but it would have
> worked.
I agree.
But in this case, there was no other set of columns (that I could make out)
that would have solved the problem. I am not sure you could have made
multiple columns work with a surrogate key, in this case. All the tables
(earnings, taxes and deductions) all could have multiple records with the
same code, date, employee number (SSN) etc on the same check. That was what
I was looking for, I just happen to notice that they have an identity field
there and it solved the problem.
Thanks,
Tom
> The only thing I would not have been able to handle were complete
> duplicates (i.e. two or more rows with the same value in each of their
> columns). But if you had those, you'd have much bigger problems to solve
> than generating a kind-of-crosstab report. <g>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Filters ! Filters ! Filters - Cascading parameters

Hi,
I have a table with 100 rows in the following format
---
country name state name city name
---
USA NY NY
USA NJ Newark
USA FL Orlando
USA FL Miami
USA CA LA
USA CA SF
China XX XX
India XX XX
Australia XX XX
China XX XX
India XX XX
----
I have 3 dropdown cascading parameters (all of them have default values
when the report loads) - say USA - CA - LA
Country
State
City
So here is my problem. The report loads first time once i click on the
report name. (because it has the default values). But when the user
clicks on the country drop down to select another country say China,
the report down goes blank (white page) UNITL THE USER CLICKS THE "VIEW
REPORT" BUTTON (WHICH IS VERY VERY ANNOYIG). Is there anyway i can
leave the table as it is without making it blank.
Please help
Thanks
Prashhi
I don't think there is a work around to this (but I would be interested in
finding out one). This is just the default behavior which appears to be a
postback on change.
Also, coming from the other direction AND just as annoying (my defaults, by
request of the customer, are 'ALL','ALL','ALL' - which is a waste of computer
resources). Fortunately, we have an ASP.NET interface which 90% of the end
users use.
Rob
"Prash" wrote:
> Hi,
> I have a table with 100 rows in the following format
> ---
> country name state name city name
> ---
> USA NY NY
> USA NJ Newark
> USA FL Orlando
> USA FL Miami
> USA CA LA
> USA CA SF
> China XX XX
> India XX XX
> Australia XX XX
> China XX XX
> India XX XX
> ----
>
> I have 3 dropdown cascading parameters (all of them have default values
> when the report loads) - say USA - CA - LA
> Country
> State
> City
>
> So here is my problem. The report loads first time once i click on the
> report name. (because it has the default values). But when the user
> clicks on the country drop down to select another country say China,
> the report down goes blank (white page) UNITL THE USER CLICKS THE "VIEW
> REPORT" BUTTON (WHICH IS VERY VERY ANNOYIG). Is there anyway i can
> leave the table as it is without making it blank.
>
> Please help
> Thanks
> Prash
>

Filtering with StartTime and Endtime problem

Hello,

I have a problem with the following task:

The user is prompted to specify a starttime like 02 and a endtime 04.

The user for example wants to search all events between 2 am and 10 am regardless of the date.

I use the following SQL ti filter:

Where (DATEPART(hour, Event.EventDateTime) BETWEEN @.StartHour AND @.EndHour)

This works fine as long starhour is smaller than endhour.

When i want to filter Events beteween 23 (11 pm.) and 02 (2 am) i get no results. I have to make a search possible for a time span that is over midnight.

Anyone who has some ideas?

Thank you i advance!

Hello Luskan,

Try this:

select *

from Table1

where 1 =

case

when @.StartHour > @.EndHour and (datepart(hour, Event.EventDateTime) >= @.StartHour or datepart(hour, Event.EventDateTime) <= @.EndHour) then 1

when @.StartHour <= @.EndHour and datepart(hour, Event.EventDateTime) between @.StartHour and @.EndHour then 1

end

Hope this helps.

Jarret

Filtering structure across 2 dimensions

I would appreciate any help with the following problem:

Other than cube redesign, which I know we can accomplish, if theres an actual way through MDX to filter structure between 2 dimensions. As of right now I can only accomplish filtering measures by 2 dimensions. Example:

SELECT { [Measures].Members } ON COLUMNS,
{ [Dimension1].[AnyHeirarchy].[AnyHeirarchyLevel].Members } ON ROWS
FROM [Planogram]
WHERE ( [Dimension2].[AnyHeirarchy].[AnyHeirarchyLevel].[AnyMember] )

What occurs is all members from Dimension1 is returned and the Measures are filtered accordingly. What I want is it to only bring back the rows which have a relationship with Dimension2. Is this possible?

But if I try to filter structure across 2 different heirarchies in the same dimension it works fine.

SELECT { [Measures].Members } ON COLUMNS,
{ [Dimension1].[Heirarchy2].[AnyHeirarchyLevel].Members } ON ROWS
FROM [Planogram]
WHERE ( [Dimension1].[Heirarchy1].[AnyHeirarchyLevel].[AnyMember] )

This only returns rows in which the two heirarchy members are related.

So obvisouly If I moved my 2nd Dimension under the 1st Dimesion and make it a heirachy it would work, but any other way around this?

Thanks a lot.

When you say: "only bring back the rows which have a relationship with Dimension2", are they only related via the fact table, or in some other way? In the former case, try Non Empty, like:

SELECT { [Measures].Members } ON COLUMNS,
Non Empty [Dimension1].[AnyHeirarchy].[AnyHeirarchyLevel].Members ON ROWS
FROM [Planogram]
WHERE ( [Dimension2].[AnyHeirarchy].[AnyHeirarchyLevel].[AnyMember] )

|||

Thanks for the reply.

Yes, they are only related via the fact table. If I try non empty, and please correct me if im wrong, that will filter by measures (sales, units, etc), and only show members that have sales/units. In essence exluding the real relationships (members that have no sales won't be shown)

I would like to bring back every member that is related to Dimension 2's member, regardless of no sales or not.

Thanks.

|||

The fact of sales is a relation between dimension1 and dimension2. There isn't another relation between dimension1 and dimension2.

If you have another one the it should exists as another fact table or anything else.

Filtering SQL statement by server variable

Hi

Hope somebody can help me with a problem that i am having. I have the following piece of code

<%

Dim sqlConnAs SqlConnectionDim sqlCmdAs SqlCommandDim sqlReaderAs SqlDataReader

sqlConn =

New SqlConnection("server=myserver;database=aspnet;uid=username;pwd=password")

sqlCmd =

New SqlCommand("SELECT SamAccount, FirstName, Surname, FirstName + N' ' + Surname+' Logged In ' AS UserLoggedIn FROM tblUsers WHERE (SamAccount = " + Request.ServerVariables("logon_user") +")", sqlConn)

sqlConn.Open()

sqlReader = sqlCmd.ExecuteReader()

%>

<%

Dim strPCNAmeAsString

sqlReader.Read()

strPCNAme = sqlReader(

"UserLoggedIn")

Response.Write(strPCNAme)

%>

The bit i have i have underlined and made bold throws up the error. Basically what i am trying to do is to filter the the SQL statement by the server variable logon user. Everthing works fine until the point that i put the where clase in.

Can anybody help please?

Thanks

Marcus

put your loginname in single quotes for query maybe it will help :

New SqlCommand("SELECT SamAccount, FirstName, Surname, FirstName + N' ' + Surname+' Logged In ' AS UserLoggedIn FROM tblUsers WHERE (SamAccount ='" + Request.ServerVariables("logon_user") +"')", sqlConn)

|||Thanks for that managed to get it working now