sql reports: following

Showing posts with label following. Show all posts

Thursday, March 22, 2012

can u guide me writing a simple query

I am trying to do following thing:

i got a table which contains some words...all i need to do is whenever i see a word starting with 'R', i need to remove the starting 'R' and update it in the same table...

could anyone help me out here....thanks a lot in advance.......

I'm assuming by the title that you want to do it in the sql query. Here is one way to do it:

Select
Case WhenWordlike 'r%' Then right(Word, len(Word)-1) end
From Table1

|||

Can you provide some sample data and what you are trying to update it to so we can get a better idea?

sql

Tuesday, March 20, 2012

Can this be simplified?

I have the following table,
CREATE TABLE [dbo].[XXX_TBL] (
[PERSON_ID] [nchar] (10) NOT NULL,
[BEGIN_DT] [datetime] NOT NULL,
[END_DT] [datetime] ,
[TYPE1_ID] [nchar] (10) ,
[TYPE2_ID] [nchar] (10) )
that charts two attributes (TYPE1_ID, TYPE2_ID) assigned to a person,
over time.
The end date field can be left blank and in that case the end date of
the row would be either the day before the next row starts in the table
for that person, or if this does not exist, then the record is valid
until the end of time!
Suppose we have the following rows in the table,
1001, 2005-01-01, NULL, Value 1, Value A
1001, 2005-01-08, NULL, Value 2, Value B
1001, 2005-01-15, NULL, Value 2, Value C
1001, 2005-01-22, NULL, Value 1, Value D
1001, 2005-01-29, NULL, Value 2, Value E
1001, 2005-02-05, NULL, Value 2, Value F
All I am interested in is the values in column, TYPE1_ID. So really
the information in this table looks like,
1001, 2005-01-01, NULL, Value 1
1001, 2005-01-08, NULL, Value 2
1001, 2005-01-22, NULL, Value 1
1001, 2005-01-29, NULL, Value 2
What is the easiest what to manipulate the data in the XXX_TBL table,
so that it looks like above?
I have been able to do this, but only by creating serveral views. The
net effect of is, it is fine if I am looking at a small number of
people only, but if I look at the whole table (about 13500 people, and
around 14500 rows) I get space issues with tempdb.
My method was,
1 - Create a view, VW1, to list both a start and end date for every row
and only show the TYPE1_ID attribute.
So we get,
1001, 2005-01-01, 2005-01-07, Value 1
1001, 2005-01-08, 2005-01-14, Value 2
1001, 2005-01-15, 2005-01-21, Value 2
1001, 2005-01-22, 2005-01-28, Value 1
1001, 2005-01-29, 2005-02-04, Value 2
1001, 2005-02-05, 9999-12-31, Value 2
2 - Join VW1 to a calendar table (a table with a row for each day) so
that we get a row for each day that an individual row is valid for.
ie,
1001, 2005-01-01, 2005-01-07, Value 1
would be transformed to (7 rows),
1001, 2005-01-01, 2005-01-07, Value 1, 2005-01-01
1001, 2005-01-01, 2005-01-07, Value 1, 2005-01-02
...
...
1001, 2005-01-01, 2005-01-07, Value 1, 2005-01-06
1001, 2005-01-01, 2005-01-07, Value 1, 2005-01-07
then save this SQL as VW_2
3 - Join VW_2 to itself where the new date field = row valid for the
next day. This way be can compare the values for TYPE1_ID and identify
on which days it changes. Create a view VW_3 from the above.
So we finally get,
1001, 2005-01-01, 2005-01-07, Value 1
1001, 2005-01-08, 2005-01-21, Value 2
1001, 2005-01-22, 2005-01-28, Value 1
1001, 2005-01-29, 9999-12-31, Value 2
which is the abbreviated data that we originally above. However this
is rather long winded!
Can this be simplified?...
Many thanks in advance.
JonathanI simplified the table:
drop TABLE [dbo].[XXX_TBL]
go
CREATE TABLE [dbo].[XXX_TBL] (
[PERSON_ID] [nchar] (10) NOT NULL,
[BEGIN_DT] [datetime] NOT NULL,
[END_DT] [datetime] ,
[TYPE1_ID] [int])
here is my test data:
insert into [dbo].[XXX_TBL] values('1001', '20050101', NULL, 1)
insert into [dbo].[XXX_TBL] values('1001', '20050103', '20050201', 1)
insert into [dbo].[XXX_TBL] values('1001', '20050202', NULL, 1)
insert into [dbo].[XXX_TBL] values('1001', '20050209', NULL, 2)
insert into [dbo].[XXX_TBL] values('1001', '20050215', '20050217', 2)
insert into [dbo].[XXX_TBL] values('1001', '20050219', '20050222', 2)
insert into [dbo].[XXX_TBL] values('1001', '20050223', null, 2)
insert into [dbo].[XXX_TBL] values('1001', '20050225', null, 1)
select * from dbo.xxx_tbl x1
where not exists(select 1 from dbo.xxx_tbl x2
where x1.person_id=x2.person_id
and x1.type1_id = x2.type1_id
and x2.begin_dt < x1.begin_dt
and ( dateadd(day, 1, x2.end_dt) = x1.begin_dt
or
((x2.end_dt is null)
and not exists(select 1 from dbo.xxx_tbl x3 where
x3.person_id=x2.person_id
and( x3.type1_id <> x2.type1_id or (x3.end_dt is not null))
and (x2.begin_dt < x3.begin_dt) and (x3.begin_dt <
x1.begin_dt))
)
)
)
order by begin_dt
take over from this point, test more - I'm gonna have my lunch now
;)|||That's very much for your reply! It's been a big help. Much
appreciated.

Can this be simplified?

I have the following table,
CREATE TABLE [dbo].[XXX_TBL] (
[PERSON_ID] [nchar] (10) NOT NULL,
[BEGIN_DT] [datetime] NOT NULL,
[END_DT] [datetime] ,
[TYPE1_ID] [nchar] (10) ,
[TYPE2_ID] [nchar] (10) )
that charts two attributes (TYPE1_ID, TYPE2_ID) assigned to a person,
over time.
The end date field can be left blank and in that case the end date of
the row would be either the day before the next row starts in the table
for that person, or if this does not exist, then the record is valid
until the end of time!
Suppose we have the following rows in the table,
1001, 2005-01-01, NULL, Value 1, Value A
1001, 2005-01-08, NULL, Value 2, Value B
1001, 2005-01-15, NULL, Value 2, Value C
1001, 2005-01-22, NULL, Value 1, Value D
1001, 2005-01-29, NULL, Value 2, Value E
1001, 2005-02-05, NULL, Value 2, Value F
All I am interested in is the values in column, TYPE1_ID. So really
the information in this table looks like,
1001, 2005-01-01, NULL, Value 1
1001, 2005-01-08, NULL, Value 2
1001, 2005-01-22, NULL, Value 1
1001, 2005-01-29, NULL, Value 2
What is the easiest what to manipulate the data in the XXX_TBL table,
so that it looks like above?
I have been able to do this, but only by creating serveral views. The
net effect of is, it is fine if I am looking at a small number of
people only, but if I look at the whole table (about 13500 people, and
around 14500 rows) I get space issues with tempdb.
My method was,
1 - Create a view, VW1, to list both a start and end date for every row
and only show the TYPE1_ID attribute.
So we get,
1001, 2005-01-01, 2005-01-07, Value 1
1001, 2005-01-08, 2005-01-14, Value 2
1001, 2005-01-15, 2005-01-21, Value 2
1001, 2005-01-22, 2005-01-28, Value 1
1001, 2005-01-29, 2005-02-04, Value 2
1001, 2005-02-05, 9999-12-31, Value 2
2 - Join VW1 to a calendar table (a table with a row for each day) so
that we get a row for each day that an individual row is valid for.
ie,
1001, 2005-01-01, 2005-01-07, Value 1
would be transformed to (7 rows),
1001, 2005-01-01, 2005-01-07, Value 1, 2005-01-01
1001, 2005-01-01, 2005-01-07, Value 1, 2005-01-02
...
...
1001, 2005-01-01, 2005-01-07, Value 1, 2005-01-06
1001, 2005-01-01, 2005-01-07, Value 1, 2005-01-07
then save this SQL as VW_2
3 - Join VW_2 to itself where the new date field = row valid for the
next day. This way be can compare the values for TYPE1_ID and identify
on which days it changes. Create a view VW_3 from the above.
So we finally get,
1001, 2005-01-01, 2005-01-07, Value 1
1001, 2005-01-08, 2005-01-21, Value 2
1001, 2005-01-22, 2005-01-28, Value 1
1001, 2005-01-29, 9999-12-31, Value 2
which is the abbreviated data that we originally above. However this
is rather long winded!
Can this be simplified?...
Many thanks in advance.
JonathanI simplified the table:
drop TABLE [dbo].[XXX_TBL]
go
CREATE TABLE [dbo].[XXX_TBL] (
[PERSON_ID] [nchar] (10) NOT NULL,
[BEGIN_DT] [datetime] NOT NULL,
[END_DT] [datetime] ,
[TYPE1_ID] [int])
here is my test data:
insert into [dbo].[XXX_TBL] values('1001', '20050101', NULL, 1)
insert into [dbo].[XXX_TBL] values('1001', '20050103', '20050201', 1
)
insert into [dbo].[XXX_TBL] values('1001', '20050202', NULL, 1)
insert into [dbo].[XXX_TBL] values('1001', '20050209', NULL, 2)
insert into [dbo].[XXX_TBL] values('1001', '20050215', '20050217', 2
)
insert into [dbo].[XXX_TBL] values('1001', '20050219', '20050222', 2
)
insert into [dbo].[XXX_TBL] values('1001', '20050223', null, 2)
insert into [dbo].[XXX_TBL] values('1001', '20050225', null, 1)
select * from dbo.xxx_tbl x1
where not exists(select 1 from dbo.xxx_tbl x2
where x1.person_id=x2.person_id
and x1.type1_id = x2.type1_id
and x2.begin_dt < x1.begin_dt
and ( dateadd(day, 1, x2.end_dt) = x1.begin_dt
or
((x2.end_dt is null)
and not exists(select 1 from dbo.xxx_tbl x3 where
x3.person_id=x2.person_id
and( x3.type1_id <> x2.type1_id or (x3.end_dt is not null))
and (x2.begin_dt < x3.begin_dt) and (x3.begin_dt <
x1.begin_dt))
)
)
)
order by begin_dt
take over from this point, test more - I'm gonna have my lunch now
;)|||That's very much for your reply! It's been a big help. Much
appreciated.

Can this be simplified?

I have the following table,
CREATE TABLE [dbo].[XXX_TBL] (
[PERSON_ID] [nchar] (10) NOT NULL,
[BEGIN_DT] [datetime] NOT NULL,
[END_DT] [datetime] ,
[TYPE1_ID] [nchar] (10) ,
[TYPE2_ID] [nchar] (10) )
that charts two attributes (TYPE1_ID, TYPE2_ID) assigned to a person,
over time.
The end date field can be left blank and in that case the end date of
the row would be either the day before the next row starts in the table
for that person, or if this does not exist, then the record is valid
until the end of time!
Suppose we have the following rows in the table,
1001, 2005-01-01, NULL, Value 1, Value A
1001, 2005-01-08, NULL, Value 2, Value B
1001, 2005-01-15, NULL, Value 2, Value C
1001, 2005-01-22, NULL, Value 1, Value D
1001, 2005-01-29, NULL, Value 2, Value E
1001, 2005-02-05, NULL, Value 2, Value F
All I am interested in is the values in column, TYPE1_ID. So really
the information in this table looks like,
1001, 2005-01-01, NULL, Value 1
1001, 2005-01-08, NULL, Value 2
1001, 2005-01-22, NULL, Value 1
1001, 2005-01-29, NULL, Value 2
What is the easiest what to manipulate the data in the XXX_TBL table,
so that it looks like above?
I have been able to do this, but only by creating serveral views. The
net effect of is, it is fine if I am looking at a small number of
people only, but if I look at the whole table (about 13500 people, and
around 14500 rows) I get space issues with tempdb.
My method was,
1 - Create a view, VW1, to list both a start and end date for every row
and only show the TYPE1_ID attribute.
So we get,
1001, 2005-01-01, 2005-01-07, Value 1
1001, 2005-01-08, 2005-01-14, Value 2
1001, 2005-01-15, 2005-01-21, Value 2
1001, 2005-01-22, 2005-01-28, Value 1
1001, 2005-01-29, 2005-02-04, Value 2
1001, 2005-02-05, 9999-12-31, Value 2
2 - Join VW1 to a calendar table (a table with a row for each day) so
that we get a row for each day that an individual row is valid for.
ie,
1001, 2005-01-01, 2005-01-07, Value 1
would be transformed to (7 rows),
1001, 2005-01-01, 2005-01-07, Value 1, 2005-01-01
1001, 2005-01-01, 2005-01-07, Value 1, 2005-01-02
....
....
1001, 2005-01-01, 2005-01-07, Value 1, 2005-01-06
1001, 2005-01-01, 2005-01-07, Value 1, 2005-01-07
then save this SQL as VW_2
3 - Join VW_2 to itself where the new date field = row valid for the
next day. This way be can compare the values for TYPE1_ID and identify
on which days it changes. Create a view VW_3 from the above.
So we finally get,
1001, 2005-01-01, 2005-01-07, Value 1
1001, 2005-01-08, 2005-01-21, Value 2
1001, 2005-01-22, 2005-01-28, Value 1
1001, 2005-01-29, 9999-12-31, Value 2
which is the abbreviated data that we originally above. However this
is rather long winded!
Can this be simplified?...
Many thanks in advance.
Jonathan
I simplified the table:
drop TABLE [dbo].[XXX_TBL]
go
CREATE TABLE [dbo].[XXX_TBL] (
[PERSON_ID] [nchar] (10) NOT NULL,
[BEGIN_DT] [datetime] NOT NULL,
[END_DT] [datetime] ,
[TYPE1_ID] [int])
here is my test data:
insert into [dbo].[XXX_TBL] values('1001', '20050101', NULL, 1)
insert into [dbo].[XXX_TBL] values('1001', '20050103', '20050201', 1)
insert into [dbo].[XXX_TBL] values('1001', '20050202', NULL, 1)
insert into [dbo].[XXX_TBL] values('1001', '20050209', NULL, 2)
insert into [dbo].[XXX_TBL] values('1001', '20050215', '20050217', 2)
insert into [dbo].[XXX_TBL] values('1001', '20050219', '20050222', 2)
insert into [dbo].[XXX_TBL] values('1001', '20050223', null, 2)
insert into [dbo].[XXX_TBL] values('1001', '20050225', null, 1)
select * from dbo.xxx_tbl x1
where not exists(select 1 from dbo.xxx_tbl x2
where x1.person_id=x2.person_id
and x1.type1_id = x2.type1_id
and x2.begin_dt < x1.begin_dt
and ( dateadd(day, 1, x2.end_dt) = x1.begin_dt
or
((x2.end_dt is null)
and not exists(select 1 from dbo.xxx_tbl x3 where
x3.person_id=x2.person_id
and( x3.type1_id <> x2.type1_id or (x3.end_dt is not null))
and (x2.begin_dt < x3.begin_dt) and (x3.begin_dt <
x1.begin_dt))
)
)
)
order by begin_dt
take over from this point, test more - I'm gonna have my lunch now
;)
|||That's very much for your reply! It's been a big help. Much
appreciated.
sql

Can this be simplified?

Can this be optimized? Newbie question

Hi,
I'm running the following SQL to get values for 4 fields. It is
unacceptably slow. I have no control over the structure of the
database, field names, indexes etc. - what I'm given as far as DB
design is all I'm going to get. If anyone could make any suggestions
I'd really appreciate it!

Thanks,
Bill

SELECT DISTINCT
T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,
T_RECEIVING_DETAIL.amount
FROM T_MULTILIST, T_RECEIVING_DETAIL,
T_MULTILIST_GRADE,T_REQUISITION,T_REQUISITION_DETA IL,T_ORDER,T_DEPOSITORY,
T_RECEIVING
WHERE
(
T_RECEIVING_DETAIL.invoice_number =T_RECEIVING.invoice_number
AND T_RECEIVING_DETAIL.order_id =T_ORDER.id
AND T_ORDER.depository_id =T_DEPOSITORY.id
AND T_REQUISITION.id =T_ORDER.requisition_id
AND T_REQUISITION_DETAIL.requisition_id =T_REQUISITION.id
AND T_REQUISITION_DETAIL.multilist_code
=T_MULTILIST_GRADE.multilist_code
AND T_MULTILIST_GRADE.multilist_code =T_MULTILIST.code

AND T_ORDER.requisition_time_stamp BETWEEN '05/31/2005' AND
'06/01/2006'
AND T_MULTILIST.expiration_year > '2005'
AND T_MULTILIST.code IN ('0043','1043')
AND T_DEPOSITORY.depository_type = 'PRIVATE'
AND T_RECEIVING.status <> 'PAID'
)
wgblackmon@.yahoo.com wrote:

> Hi,
> I'm running the following SQL to get values for 4 fields. It is
> unacceptably slow. I have no control over the structure of the
> database, field names, indexes etc. - what I'm given as far as DB
> design is all I'm going to get. If anyone could make any suggestions
> I'd really appreciate it!
> Thanks,
> Bill

Well, it sounds like you're pretty much screwed. How many rows
does the query return? Is it appreciably faster if you remove
the 'DISTINCT' and do you have the opportunity to detect
and ignore duplicates at the client? Can you even find out
what indexes are on the tables or get the query plan for this?
There may be other query criteria that you could drop, and
instead post-qualify rows in the client.
Hope this (or someone else smarter) helps,
Joe Weinstein at BEA Systems

> SELECT DISTINCT
> T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,
> T_RECEIVING_DETAIL.amount
> FROM T_MULTILIST, T_RECEIVING_DETAIL,
> T_MULTILIST_GRADE,T_REQUISITION,T_REQUISITION_DETA IL,T_ORDER,T_DEPOSITORY,
> T_RECEIVING
> WHERE
> (
> T_RECEIVING_DETAIL.invoice_number =T_RECEIVING.invoice_number
> AND T_RECEIVING_DETAIL.order_id =T_ORDER.id
> AND T_ORDER.depository_id =T_DEPOSITORY.id
> AND T_REQUISITION.id =T_ORDER.requisition_id
> AND T_REQUISITION_DETAIL.requisition_id =T_REQUISITION.id
> AND T_REQUISITION_DETAIL.multilist_code
> =T_MULTILIST_GRADE.multilist_code
> AND T_MULTILIST_GRADE.multilist_code =T_MULTILIST.code
> AND T_ORDER.requisition_time_stamp BETWEEN '05/31/2005' AND
> '06/01/2006'
> AND T_MULTILIST.expiration_year > '2005'
> AND T_MULTILIST.code IN ('0043','1043')
> AND T_DEPOSITORY.depository_type = 'PRIVATE'
> AND T_RECEIVING.status <> 'PAID'
> )|||I'm using this query (and up to 20 similar ones combined with 'UNION')
in a Crystal Report. The report may or may not be able to remove dupes,
but I doubt it (I'm new at Crystal Reports). I'm using DBArtisan to
design the query. The database is an undocumented nightmare with few
indexes. I know it's hideous, but I was hoping I was missing something
really obvious...:)|||Bill,

There is nothing wrong with the query, except that maybe the DISTINCT is
not necessary and could save some time if you dropped it.

The key of this query's performance is in the available indexes (and
maybe the hardware configuration). If no usuable indexes are available
and the tables are large then this query will run like a dog. You should
really turn to the DBA who can put the proper indexes in place...

Gert-Jan

"wgblackmon@.yahoo.com" wrote:
> Hi,
> I'm running the following SQL to get values for 4 fields. It is
> unacceptably slow. I have no control over the structure of the
> database, field names, indexes etc. - what I'm given as far as DB
> design is all I'm going to get. If anyone could make any suggestions
> I'd really appreciate it!
> Thanks,
> Bill
> SELECT DISTINCT
> T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,
> T_RECEIVING_DETAIL.amount
> FROM T_MULTILIST, T_RECEIVING_DETAIL,
> T_MULTILIST_GRADE,T_REQUISITION,T_REQUISITION_DETA IL,T_ORDER,T_DEPOSITORY,
> T_RECEIVING
> WHERE
> (
> T_RECEIVING_DETAIL.invoice_number =T_RECEIVING.invoice_number
> AND T_RECEIVING_DETAIL.order_id =T_ORDER.id
> AND T_ORDER.depository_id =T_DEPOSITORY.id
> AND T_REQUISITION.id =T_ORDER.requisition_id
> AND T_REQUISITION_DETAIL.requisition_id =T_REQUISITION.id
> AND T_REQUISITION_DETAIL.multilist_code
> =T_MULTILIST_GRADE.multilist_code
> AND T_MULTILIST_GRADE.multilist_code =T_MULTILIST.code
> AND T_ORDER.requisition_time_stamp BETWEEN '05/31/2005' AND
> '06/01/2006'
> AND T_MULTILIST.expiration_year > '2005'
> AND T_MULTILIST.code IN ('0043','1043')
> AND T_DEPOSITORY.depository_type = 'PRIVATE'
> AND T_RECEIVING.status <> 'PAID'
> )|||T_MULTILIST.description may be wide. Sorting wide result sets may be
slow. try removing duplicates before joining with T_MULTILIST. Look up
article "The Less SQL Server Sorts, the Faster It Responds"|||wgblackmon@.yahoo.com (wgblackmon@.yahoo.com) writes:
> I'm running the following SQL to get values for 4 fields. It is
> unacceptably slow. I have no control over the structure of the
> database, field names, indexes etc. - what I'm given as far as DB
> design is all I'm going to get. If anyone could make any suggestions
> I'd really appreciate it!

My newsserver had an outage, so the reply I posted originally got lost.
What I said in that post was not that fantastic:

Without know the tables and indexes it's about impossible to give
suggestions. If you post the CREATE TABLE and CREATE INDEX statements
(don't forget constraints!), as well some indication of table sizes,
we might be able to give some tips.

Even better if you can run:

SET STATISTICS PROFILE ON
go
-- query goes here
go
SET STATISTICS PROFILE OFF
go

and post the output. (Preferably in an attachment, as the output is far too
wide for news article).

However, a few minutes later one more thing occurred to me, and that was
when I discovered that the newsserver was sick.

Anyway, what you could try is to run DBCC DBREINDEX on all involved tables.
While it is not going to cause the query to run with the speed of light
all of a sudden, you could see an improvement with 20-30% if there is
serious fragmentation of the tables.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Can this be done??

Hi,
Can anyone give me some advice on how I can accomplish the following.

I have a table that has a value like the following "2010302NOV01222004"

The above value is made of of 3 distinct values
They are:
Employee Code - 2010302
Course Code - Nov012
Quarter and Year: 22004

In another table I have a set of values that relate to the middle part of the above value (Course Code), i would like to return the course name that relates to the course code from the other table.

I have been able to extract the coursecode using the following code but can't see how to pass vthe value to the courseType table to return my CourseName value.

<code>
SUBSTRING(dbo.Leave.Training, 8, LEN(dbo.Leave.Training) - 12)
</Code
I would need to do this from a stored procedure.

Regards..
Peter.

You can join your Leave table to the CourseType table in order to pull the CourseName for every Training field value using something like the following:

SELECT Leave.Training, CourseType.CourseCode, CourseType.CourseName
FROM Leave JOIN CourseType
ON SUBSTRING(dbo.Leave.Training, 8, LEN(dbo.Leave.Training) - 12) = CourseType.CourseCode

I'm not sure what you're trying to accomplish with the stored proc, so I won't go into details on using parameters, etc...

|||Jason,
Thanks for the reply, what i am trying to accopmlish is:

1. I have a table that lists all the leave employees take, this includes any training. I need all records returned from the leave table and those that have entries in the training filed of the leave table require the courseName to be returned from the Course type Table.

The training is listed in the leave table as described previously and I need to extract the training CourseID from that field, as you saw "2010302NOV01222004" is in the training field in the leave table.

I need to extract "NOV012" from that filed and get the coursename (Novell iChain 2.2) returned from the Course Type table, if the field is null then ignore it.

Hope this expalins better what i am trying to accomplish.

Regards..
Peter.|||

Try executing the following to see if it doesn't give you exactly what you asked for:

SELECT Leave.*, CourseType.CourseCode, CourseType.CourseName
FROM Leave LEFT JOIN CourseType
ON SUBSTRING(dbo.Leave.Training, 8, LEN(dbo.Leave.Training) - 12) = CourseType.CourseCode

|||Jason,
Thanks that has hit the nail on the head.. Exactly what i needed...

Regards..
Peter

Monday, March 19, 2012

can this be done in SQL2000

Hi I need to create a stored procedure that can do the following but now sure
if it can be done with sql2000.
1. read in existing data from a table.
2. create the current Julian date.
3. make a comparison and add a sequence number onto this number if it is a
specific Julian date.
I think all of this could probably be done with 2005 since it allows using
C#,vb for TSQL.
Paul G
Software engineer.On Wed, 30 Aug 2006 08:18:02 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>Hi I need to create a stored procedure that can do the following but now sure
>if it can be done with sql2000.
>1. read in existing data from a table.
Stored procedures are very good at retrieving data from database
tables.
>2. create the current Julian date.
SQL Server has sufficient tools for date manipulation that calculating
Julian date from the current date (getdate()) should be no problem. It
does, however, require knowing which definition of Julian date is
intended.
>3. make a comparison and add a sequence number onto this number if it is a
>specific Julian date.
Comparison of what to what? By "this number" do you mean the Julian
data calculated in item 2? What specific Julian date? One passed as
a parameter to the stored procedure? One retrieved from the table in
item 1?
>I think all of this could probably be done with 2005 since it allows using
>C#,vb for TSQL.
I am sure it can be done in 2005. It is unclear from the information
give that it will require C# or VB, but if it turns out to be
complicated they are available. Perhaps if you provided a bit more
detail someone will be able to suggest an appropriate approach.
>Paul G
>Software engineer.
Roy Harvey
Beacon Falls, CT|||Hi thanks for the response. Here are more details on what I am trying to do.
There is a table (table1) that has hundreds of records that look like
JTB-ABC-MDTC-06200-0001
JCV-BCD-ABAM-06201-0001
JTB-ABC-MDAC-06200-0002
I need the stored procedure to take the value
JCV-BCD-RBSV
and build the rest of it based on the following conditions and then save it
to a table.
Get the current julian date, today would be 06242, two hundred forty two day
of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
and only the last section still to be built. This is done by doing the
following.
1. Compare the jul date for the day the stored procedure will run (06242)
and find all records in table 1 with the same date (call this subset a).
2. Next out of subset a find all that match the first 6 letters (create
subset b).
3. Next out of subset b find the greatest value of the last 4 numbers (say
it was 0002).
4. Finally increment this value by 1 and use it to finish the newly created
value, so we would have JVC-BCD-RBSV-06242-0003.
This is easy to do in vb or C#.
--
Paul G
Software engineer.
"Roy Harvey" wrote:
> On Wed, 30 Aug 2006 08:18:02 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >Hi I need to create a stored procedure that can do the following but now sure
> >if it can be done with sql2000.
> >
> >1. read in existing data from a table.
> Stored procedures are very good at retrieving data from database
> tables.
> >2. create the current Julian date.
> SQL Server has sufficient tools for date manipulation that calculating
> Julian date from the current date (getdate()) should be no problem. It
> does, however, require knowing which definition of Julian date is
> intended.
> >3. make a comparison and add a sequence number onto this number if it is a
> >specific Julian date.
> Comparison of what to what? By "this number" do you mean the Julian
> data calculated in item 2? What specific Julian date? One passed as
> a parameter to the stored procedure? One retrieved from the table in
> item 1?
> >I think all of this could probably be done with 2005 since it allows using
> >C#,vb for TSQL.
> I am sure it can be done in 2005. It is unclear from the information
> give that it will require C# or VB, but if it turns out to be
> complicated they are available. Perhaps if you provided a bit more
> detail someone will be able to suggest an appropriate approach.
> >Paul G
> >Software engineer.
> Roy Harvey
> Beacon Falls, CT
>|||Of course all the parts of that complex column should be individual
table columns, but I will not belabor that point.
There is nothing about that which requires going to C# or VB. This
should give you some ideas. Note that I used a view, but each of the
pieces could have been substringed out when references. However since
all the real work is one in one SQL command, all those references
would make it a good bit more opaque. Another advantage to the view
is that it is possible to make it an indexed view, which could make a
major difference in performance.
CREATE TABLE Table1
(StrungOut char(23) NOT NULL)
INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
GO
CREATE VIEW Table1_V
AS
SELECT StrungOut,
First6 = SUBSTRING(StrungOut,1,6),
JDate = SUBSTRING(StrungOut,14,5),
JYear = SUBSTRING(StrungOut,14,2),
JDay = SUBSTRING(StrungOut,16,3),
Last4 = SUBSTRING(Strungout,20,4)
FROM Table1
GO
CREATE TABLE Table2
(StrungOut char(23) NOT NULL)
GO
CREATE PROC Demonstration
@.front char(12)
AS
INSERT Table2
SELECT @.front + '-' +
right(convert(char(4),datepart(year,getdate())),2) +
right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
'-' +
RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
FROM Table1_V
WHERE JDate = right(convert(char(4),datepart(year,getdate())),2) +
right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
AND First6 = SUBSTRING(@.front,1,6)
GO
EXEC Demonstration 'JCV-BCD-RBSV'
SELECT *
FROM Table2
StrungOut
--
JCV-BCD-RBSV-06242-0006
Also, in the spec you mentioned matching on the first six characters,
but I wondered if it might have actually been the first seven.
One final point worth making. It would be quite practical to modify
this so that rather than taking in a string as a parameter, it
produced a new row such as this for each Front6 in Table1 that matches
the current date.
Roy Harvey
Beacon Falls, CT
On Wed, 30 Aug 2006 09:09:02 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>Hi thanks for the response. Here are more details on what I am trying to do.
>There is a table (table1) that has hundreds of records that look like
>JTB-ABC-MDTC-06200-0001
>JCV-BCD-ABAM-06201-0001
>JTB-ABC-MDAC-06200-0002
>I need the stored procedure to take the value
>JCV-BCD-RBSV
>and build the rest of it based on the following conditions and then save it
>to a table.
>Get the current julian date, today would be 06242, two hundred forty two day
>of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
>and only the last section still to be built. This is done by doing the
>following.
>1. Compare the jul date for the day the stored procedure will run (06242)
>and find all records in table 1 with the same date (call this subset a).
>2. Next out of subset a find all that match the first 6 letters (create
>subset b).
>3. Next out of subset b find the greatest value of the last 4 numbers (say
>it was 0002).
>4. Finally increment this value by 1 and use it to finish the newly created
>value, so we would have JVC-BCD-RBSV-06242-0003.
>This is easy to do in vb or C#.|||An alternate coding of the WHERE clause:
WHERE JYear = right(convert(char(4),datepart(year,getdate())),2)
AND JDay = right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
AND First6 = SUBSTRING(@.front,1,6)
Roy
On Wed, 30 Aug 2006 13:00:08 -0400, Roy Harvey <roy_harvey@.snet.net>
wrote:
>Of course all the parts of that complex column should be individual
>table columns, but I will not belabor that point.
>There is nothing about that which requires going to C# or VB. This
>should give you some ideas. Note that I used a view, but each of the
>pieces could have been substringed out when references. However since
>all the real work is one in one SQL command, all those references
>would make it a good bit more opaque. Another advantage to the view
>is that it is possible to make it an indexed view, which could make a
>major difference in performance.
>CREATE TABLE Table1
>(StrungOut char(23) NOT NULL)
>INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
>INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
>INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
>INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
>GO
>CREATE VIEW Table1_V
>AS
>SELECT StrungOut,
> First6 = SUBSTRING(StrungOut,1,6),
> JDate = SUBSTRING(StrungOut,14,5),
> JYear = SUBSTRING(StrungOut,14,2),
> JDay = SUBSTRING(StrungOut,16,3),
> Last4 = SUBSTRING(Strungout,20,4)
> FROM Table1
>GO
>CREATE TABLE Table2
>(StrungOut char(23) NOT NULL)
>GO
>CREATE PROC Demonstration
>@.front char(12)
>AS
>INSERT Table2
>SELECT @.front + '-' +
> right(convert(char(4),datepart(year,getdate())),2) +
> right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> FROM Table1_V
> WHERE JDate => right(convert(char(4),datepart(year,getdate())),2) +
> right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> AND First6 = SUBSTRING(@.front,1,6)
>GO
>EXEC Demonstration 'JCV-BCD-RBSV'
>SELECT *
> FROM Table2
>StrungOut
>--
>JCV-BCD-RBSV-06242-0006
>Also, in the spec you mentioned matching on the first six characters,
>but I wondered if it might have actually been the first seven.
>One final point worth making. It would be quite practical to modify
>this so that rather than taking in a string as a parameter, it
>produced a new row such as this for each Front6 in Table1 that matches
>the current date.
>Roy Harvey
>Beacon Falls, CT
>On Wed, 30 Aug 2006 09:09:02 -0700, Paul
><Paul@.discussions.microsoft.com> wrote:
>>Hi thanks for the response. Here are more details on what I am trying to do.
>>There is a table (table1) that has hundreds of records that look like
>>JTB-ABC-MDTC-06200-0001
>>JCV-BCD-ABAM-06201-0001
>>JTB-ABC-MDAC-06200-0002
>>I need the stored procedure to take the value
>>JCV-BCD-RBSV
>>and build the rest of it based on the following conditions and then save it
>>to a table.
>>Get the current julian date, today would be 06242, two hundred forty two day
>>of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
>>and only the last section still to be built. This is done by doing the
>>following.
>>1. Compare the jul date for the day the stored procedure will run (06242)
>>and find all records in table 1 with the same date (call this subset a).
>>2. Next out of subset a find all that match the first 6 letters (create
>>subset b).
>>3. Next out of subset b find the greatest value of the last 4 numbers (say
>>it was 0002).
>>4. Finally increment this value by 1 and use it to finish the newly created
>>value, so we would have JVC-BCD-RBSV-06242-0003.
>>This is easy to do in vb or C#.|||Hi thanks for the response. Yes the table was already setup and there was
not time to restructure. Will take a look at what you have. Was not aware
that you can do a lot with SQL.
--
Paul G
Software engineer.
"Roy Harvey" wrote:
> Of course all the parts of that complex column should be individual
> table columns, but I will not belabor that point.
> There is nothing about that which requires going to C# or VB. This
> should give you some ideas. Note that I used a view, but each of the
> pieces could have been substringed out when references. However since
> all the real work is one in one SQL command, all those references
> would make it a good bit more opaque. Another advantage to the view
> is that it is possible to make it an indexed view, which could make a
> major difference in performance.
> CREATE TABLE Table1
> (StrungOut char(23) NOT NULL)
> INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
> INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
> INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
> INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
> GO
> CREATE VIEW Table1_V
> AS
> SELECT StrungOut,
> First6 = SUBSTRING(StrungOut,1,6),
> JDate = SUBSTRING(StrungOut,14,5),
> JYear = SUBSTRING(StrungOut,14,2),
> JDay = SUBSTRING(StrungOut,16,3),
> Last4 = SUBSTRING(Strungout,20,4)
> FROM Table1
> GO
> CREATE TABLE Table2
> (StrungOut char(23) NOT NULL)
> GO
> CREATE PROC Demonstration
> @.front char(12)
> AS
> INSERT Table2
> SELECT @.front + '-' +
> right(convert(char(4),datepart(year,getdate())),2) +
> right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> FROM Table1_V
> WHERE JDate => right(convert(char(4),datepart(year,getdate())),2) +
> right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> AND First6 = SUBSTRING(@.front,1,6)
> GO
> EXEC Demonstration 'JCV-BCD-RBSV'
> SELECT *
> FROM Table2
> StrungOut
> --
> JCV-BCD-RBSV-06242-0006
> Also, in the spec you mentioned matching on the first six characters,
> but I wondered if it might have actually been the first seven.
> One final point worth making. It would be quite practical to modify
> this so that rather than taking in a string as a parameter, it
> produced a new row such as this for each Front6 in Table1 that matches
> the current date.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 30 Aug 2006 09:09:02 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >Hi thanks for the response. Here are more details on what I am trying to do.
> >There is a table (table1) that has hundreds of records that look like
> >JTB-ABC-MDTC-06200-0001
> >JCV-BCD-ABAM-06201-0001
> >JTB-ABC-MDAC-06200-0002
> >
> >I need the stored procedure to take the value
> >JCV-BCD-RBSV
> >and build the rest of it based on the following conditions and then save it
> >to a table.
> >Get the current julian date, today would be 06242, two hundred forty two day
> >of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
> >and only the last section still to be built. This is done by doing the
> >following.
> >1. Compare the jul date for the day the stored procedure will run (06242)
> >and find all records in table 1 with the same date (call this subset a).
> >2. Next out of subset a find all that match the first 6 letters (create
> >subset b).
> >3. Next out of subset b find the greatest value of the last 4 numbers (say
> >it was 0002).
> >4. Finally increment this value by 1 and use it to finish the newly created
> >value, so we would have JVC-BCD-RBSV-06242-0003.
> >This is easy to do in vb or C#.
>|||I see were you get the current julian date, JYear and JDay but I did not see
if combine these to get the 06242 for example for today. thanks.
Paul G
Software engineer.
"Roy Harvey" wrote:
> An alternate coding of the WHERE clause:
> WHERE JYear => right(convert(char(4),datepart(year,getdate())),2)
> AND JDay => right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> AND First6 = SUBSTRING(@.front,1,6)
> Roy
> On Wed, 30 Aug 2006 13:00:08 -0400, Roy Harvey <roy_harvey@.snet.net>
> wrote:
> >Of course all the parts of that complex column should be individual
> >table columns, but I will not belabor that point.
> >
> >There is nothing about that which requires going to C# or VB. This
> >should give you some ideas. Note that I used a view, but each of the
> >pieces could have been substringed out when references. However since
> >all the real work is one in one SQL command, all those references
> >would make it a good bit more opaque. Another advantage to the view
> >is that it is possible to make it an indexed view, which could make a
> >major difference in performance.
> >
> >CREATE TABLE Table1
> >(StrungOut char(23) NOT NULL)
> >
> >INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
> >INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
> >INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
> >INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
> >
> >GO
> >CREATE VIEW Table1_V
> >AS
> >SELECT StrungOut,
> > First6 = SUBSTRING(StrungOut,1,6),
> > JDate = SUBSTRING(StrungOut,14,5),
> > JYear = SUBSTRING(StrungOut,14,2),
> > JDay = SUBSTRING(StrungOut,16,3),
> > Last4 = SUBSTRING(Strungout,20,4)
> > FROM Table1
> >GO
> >
> >CREATE TABLE Table2
> >(StrungOut char(23) NOT NULL)
> >GO
> >
> >CREATE PROC Demonstration
> >@.front char(12)
> >AS
> >
> >INSERT Table2
> >SELECT @.front + '-' +
> > right(convert(char(4),datepart(year,getdate())),2) +
> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> > '-' +
> > RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> > FROM Table1_V
> > WHERE JDate => > right(convert(char(4),datepart(year,getdate())),2) +
> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> > AND First6 = SUBSTRING(@.front,1,6)
> >GO
> >
> >EXEC Demonstration 'JCV-BCD-RBSV'
> >
> >SELECT *
> > FROM Table2
> >
> >StrungOut
> >--
> >JCV-BCD-RBSV-06242-0006
> >
> >Also, in the spec you mentioned matching on the first six characters,
> >but I wondered if it might have actually been the first seven.
> >
> >One final point worth making. It would be quite practical to modify
> >this so that rather than taking in a string as a parameter, it
> >produced a new row such as this for each Front6 in Table1 that matches
> >the current date.
> >
> >Roy Harvey
> >Beacon Falls, CT
> >
> >On Wed, 30 Aug 2006 09:09:02 -0700, Paul
> ><Paul@.discussions.microsoft.com> wrote:
> >
> >>Hi thanks for the response. Here are more details on what I am trying to do.
> >>There is a table (table1) that has hundreds of records that look like
> >>JTB-ABC-MDTC-06200-0001
> >>JCV-BCD-ABAM-06201-0001
> >>JTB-ABC-MDAC-06200-0002
> >>
> >>I need the stored procedure to take the value
> >>JCV-BCD-RBSV
> >>and build the rest of it based on the following conditions and then save it
> >>to a table.
> >>Get the current julian date, today would be 06242, two hundred forty two day
> >>of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
> >>and only the last section still to be built. This is done by doing the
> >>following.
> >>1. Compare the jul date for the day the stored procedure will run (06242)
> >>and find all records in table 1 with the same date (call this subset a).
> >>2. Next out of subset a find all that match the first 6 letters (create
> >>subset b).
> >>3. Next out of subset b find the greatest value of the last 4 numbers (say
> >>it was 0002).
> >>4. Finally increment this value by 1 and use it to finish the newly created
> >>value, so we would have JVC-BCD-RBSV-06242-0003.
> >>This is easy to do in vb or C#.
>|||JYear and JDay are the pieces of the Julian date in Table1, so they
are only used for the comparison.
In the assignment:
SELECT @.front + '-' +
right(convert(char(4),datepart(year,getdate())),2) +
right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
'-' +
RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
the Julian date part of the output string is the second and third
lines. BUT, I just thought of an alternate way to code the
assignment. We went to all that trouble to make sure Table1_V.JDate
matched the current day, so instead of using the current day we could
just use Table1_V.JDate.
SELECT @.front + '-' +
JDate +
'-' +
RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
However, I'm guessing that IF there is no matching row with the
current date you might actually want to create the new row with 0000
or 0001 as the final part of the string. In that case I would keep to
using the derivation from getdate() as it will be easier to code in
the row-not-found branch.
Roy Harvey
Beacon Falls, CT
On Wed, 30 Aug 2006 10:32:01 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>I see were you get the current julian date, JYear and JDay but I did not see
>if combine these to get the 06242 for example for today. thanks.
>Paul G
>Software engineer.
>
>"Roy Harvey" wrote:
>> An alternate coding of the WHERE clause:
>> WHERE JYear =>> right(convert(char(4),datepart(year,getdate())),2)
>> AND JDay =>> right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
>> AND First6 = SUBSTRING(@.front,1,6)
>> Roy
>> On Wed, 30 Aug 2006 13:00:08 -0400, Roy Harvey <roy_harvey@.snet.net>
>> wrote:
>> >Of course all the parts of that complex column should be individual
>> >table columns, but I will not belabor that point.
>> >
>> >There is nothing about that which requires going to C# or VB. This
>> >should give you some ideas. Note that I used a view, but each of the
>> >pieces could have been substringed out when references. However since
>> >all the real work is one in one SQL command, all those references
>> >would make it a good bit more opaque. Another advantage to the view
>> >is that it is possible to make it an indexed view, which could make a
>> >major difference in performance.
>> >
>> >CREATE TABLE Table1
>> >(StrungOut char(23) NOT NULL)
>> >
>> >INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
>> >INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
>> >INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
>> >INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
>> >
>> >GO
>> >CREATE VIEW Table1_V
>> >AS
>> >SELECT StrungOut,
>> > First6 = SUBSTRING(StrungOut,1,6),
>> > JDate = SUBSTRING(StrungOut,14,5),
>> > JYear = SUBSTRING(StrungOut,14,2),
>> > JDay = SUBSTRING(StrungOut,16,3),
>> > Last4 = SUBSTRING(Strungout,20,4)
>> > FROM Table1
>> >GO
>> >
>> >CREATE TABLE Table2
>> >(StrungOut char(23) NOT NULL)
>> >GO
>> >
>> >CREATE PROC Demonstration
>> >@.front char(12)
>> >AS
>> >
>> >INSERT Table2
>> >SELECT @.front + '-' +
>> > right(convert(char(4),datepart(year,getdate())),2) +
>> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
>> > '-' +
>> > RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
>> > FROM Table1_V
>> > WHERE JDate =>> > right(convert(char(4),datepart(year,getdate())),2) +
>> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
>> > AND First6 = SUBSTRING(@.front,1,6)
>> >GO
>> >
>> >EXEC Demonstration 'JCV-BCD-RBSV'
>> >
>> >SELECT *
>> > FROM Table2
>> >
>> >StrungOut
>> >--
>> >JCV-BCD-RBSV-06242-0006
>> >
>> >Also, in the spec you mentioned matching on the first six characters,
>> >but I wondered if it might have actually been the first seven.
>> >
>> >One final point worth making. It would be quite practical to modify
>> >this so that rather than taking in a string as a parameter, it
>> >produced a new row such as this for each Front6 in Table1 that matches
>> >the current date.
>> >
>> >Roy Harvey
>> >Beacon Falls, CT
>> >
>> >On Wed, 30 Aug 2006 09:09:02 -0700, Paul
>> ><Paul@.discussions.microsoft.com> wrote:
>> >
>> >>Hi thanks for the response. Here are more details on what I am trying to do.
>> >>There is a table (table1) that has hundreds of records that look like
>> >>JTB-ABC-MDTC-06200-0001
>> >>JCV-BCD-ABAM-06201-0001
>> >>JTB-ABC-MDAC-06200-0002
>> >>
>> >>I need the stored procedure to take the value
>> >>JCV-BCD-RBSV
>> >>and build the rest of it based on the following conditions and then save it
>> >>to a table.
>> >>Get the current julian date, today would be 06242, two hundred forty two day
>> >>of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
>> >>and only the last section still to be built. This is done by doing the
>> >>following.
>> >>1. Compare the jul date for the day the stored procedure will run (06242)
>> >>and find all records in table 1 with the same date (call this subset a).
>> >>2. Next out of subset a find all that match the first 6 letters (create
>> >>subset b).
>> >>3. Next out of subset b find the greatest value of the last 4 numbers (say
>> >>it was 0002).
>> >>4. Finally increment this value by 1 and use it to finish the newly created
>> >>value, so we would have JVC-BCD-RBSV-06242-0003.
>> >>This is easy to do in vb or C#.|||ok thanks for the information.
--
Paul G
Software engineer.
"Roy Harvey" wrote:
> JYear and JDay are the pieces of the Julian date in Table1, so they
> are only used for the comparison.
> In the assignment:
> SELECT @.front + '-' +
> right(convert(char(4),datepart(year,getdate())),2) +
> right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> the Julian date part of the output string is the second and third
> lines. BUT, I just thought of an alternate way to code the
> assignment. We went to all that trouble to make sure Table1_V.JDate
> matched the current day, so instead of using the current day we could
> just use Table1_V.JDate.
> SELECT @.front + '-' +
> JDate +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> However, I'm guessing that IF there is no matching row with the
> current date you might actually want to create the new row with 0000
> or 0001 as the final part of the string. In that case I would keep to
> using the derivation from getdate() as it will be easier to code in
> the row-not-found branch.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 30 Aug 2006 10:32:01 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >I see were you get the current julian date, JYear and JDay but I did not see
> >if combine these to get the 06242 for example for today. thanks.
> >Paul G
> >Software engineer.
> >
> >
> >"Roy Harvey" wrote:
> >
> >> An alternate coding of the WHERE clause:
> >>
> >> WHERE JYear => >> right(convert(char(4),datepart(year,getdate())),2)
> >> AND JDay => >> right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> >> AND First6 = SUBSTRING(@.front,1,6)
> >>
> >> Roy
> >>
> >> On Wed, 30 Aug 2006 13:00:08 -0400, Roy Harvey <roy_harvey@.snet.net>
> >> wrote:
> >>
> >> >Of course all the parts of that complex column should be individual
> >> >table columns, but I will not belabor that point.
> >> >
> >> >There is nothing about that which requires going to C# or VB. This
> >> >should give you some ideas. Note that I used a view, but each of the
> >> >pieces could have been substringed out when references. However since
> >> >all the real work is one in one SQL command, all those references
> >> >would make it a good bit more opaque. Another advantage to the view
> >> >is that it is possible to make it an indexed view, which could make a
> >> >major difference in performance.
> >> >
> >> >CREATE TABLE Table1
> >> >(StrungOut char(23) NOT NULL)
> >> >
> >> >INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
> >> >INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
> >> >INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
> >> >INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
> >> >
> >> >GO
> >> >CREATE VIEW Table1_V
> >> >AS
> >> >SELECT StrungOut,
> >> > First6 = SUBSTRING(StrungOut,1,6),
> >> > JDate = SUBSTRING(StrungOut,14,5),
> >> > JYear = SUBSTRING(StrungOut,14,2),
> >> > JDay = SUBSTRING(StrungOut,16,3),
> >> > Last4 = SUBSTRING(Strungout,20,4)
> >> > FROM Table1
> >> >GO
> >> >
> >> >CREATE TABLE Table2
> >> >(StrungOut char(23) NOT NULL)
> >> >GO
> >> >
> >> >CREATE PROC Demonstration
> >> >@.front char(12)
> >> >AS
> >> >
> >> >INSERT Table2
> >> >SELECT @.front + '-' +
> >> > right(convert(char(4),datepart(year,getdate())),2) +
> >> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> >> > '-' +
> >> > RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> >> > FROM Table1_V
> >> > WHERE JDate => >> > right(convert(char(4),datepart(year,getdate())),2) +
> >> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> >> > AND First6 = SUBSTRING(@.front,1,6)
> >> >GO
> >> >
> >> >EXEC Demonstration 'JCV-BCD-RBSV'
> >> >
> >> >SELECT *
> >> > FROM Table2
> >> >
> >> >StrungOut
> >> >--
> >> >JCV-BCD-RBSV-06242-0006
> >> >
> >> >Also, in the spec you mentioned matching on the first six characters,
> >> >but I wondered if it might have actually been the first seven.
> >> >
> >> >One final point worth making. It would be quite practical to modify
> >> >this so that rather than taking in a string as a parameter, it
> >> >produced a new row such as this for each Front6 in Table1 that matches
> >> >the current date.
> >> >
> >> >Roy Harvey
> >> >Beacon Falls, CT
> >> >
> >> >On Wed, 30 Aug 2006 09:09:02 -0700, Paul
> >> ><Paul@.discussions.microsoft.com> wrote:
> >> >
> >> >>Hi thanks for the response. Here are more details on what I am trying to do.
> >> >>There is a table (table1) that has hundreds of records that look like
> >> >>JTB-ABC-MDTC-06200-0001
> >> >>JCV-BCD-ABAM-06201-0001
> >> >>JTB-ABC-MDAC-06200-0002
> >> >>
> >> >>I need the stored procedure to take the value
> >> >>JCV-BCD-RBSV
> >> >>and build the rest of it based on the following conditions and then save it
> >> >>to a table.
> >> >>Get the current julian date, today would be 06242, two hundred forty two day
> >> >>of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
> >> >>and only the last section still to be built. This is done by doing the
> >> >>following.
> >> >>1. Compare the jul date for the day the stored procedure will run (06242)
> >> >>and find all records in table 1 with the same date (call this subset a).
> >> >>2. Next out of subset a find all that match the first 6 letters (create
> >> >>subset b).
> >> >>3. Next out of subset b find the greatest value of the last 4 numbers (say
> >> >>it was 0002).
> >> >>4. Finally increment this value by 1 and use it to finish the newly created
> >> >>value, so we would have JVC-BCD-RBSV-06242-0003.
> >> >>This is easy to do in vb or C#.
> >>
>|||I tried out the sample and it worked. Anyhow just had a quick last question.
Do you know if there is a way to conditionally run a job? I am thinking of
scheduling the stored procedure but in some cases if a manual data entry has
taken place (through an asp.net web application) I will not want to add a new
record with the scheduled job. I guess this could be properly handled in the
stored procedure(scheduled job), possibly perform some type of table check to
see when the last entry took place to know weather or not to add a record.
Thanks.
--
Paul G
Software engineer.
"Roy Harvey" wrote:
> JYear and JDay are the pieces of the Julian date in Table1, so they
> are only used for the comparison.
> In the assignment:
> SELECT @.front + '-' +
> right(convert(char(4),datepart(year,getdate())),2) +
> right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> the Julian date part of the output string is the second and third
> lines. BUT, I just thought of an alternate way to code the
> assignment. We went to all that trouble to make sure Table1_V.JDate
> matched the current day, so instead of using the current day we could
> just use Table1_V.JDate.
> SELECT @.front + '-' +
> JDate +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> However, I'm guessing that IF there is no matching row with the
> current date you might actually want to create the new row with 0000
> or 0001 as the final part of the string. In that case I would keep to
> using the derivation from getdate() as it will be easier to code in
> the row-not-found branch.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 30 Aug 2006 10:32:01 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >I see were you get the current julian date, JYear and JDay but I did not see
> >if combine these to get the 06242 for example for today. thanks.
> >Paul G
> >Software engineer.
> >
> >
> >"Roy Harvey" wrote:
> >
> >> An alternate coding of the WHERE clause:
> >>
> >> WHERE JYear => >> right(convert(char(4),datepart(year,getdate())),2)
> >> AND JDay => >> right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> >> AND First6 = SUBSTRING(@.front,1,6)
> >>
> >> Roy
> >>
> >> On Wed, 30 Aug 2006 13:00:08 -0400, Roy Harvey <roy_harvey@.snet.net>
> >> wrote:
> >>
> >> >Of course all the parts of that complex column should be individual
> >> >table columns, but I will not belabor that point.
> >> >
> >> >There is nothing about that which requires going to C# or VB. This
> >> >should give you some ideas. Note that I used a view, but each of the
> >> >pieces could have been substringed out when references. However since
> >> >all the real work is one in one SQL command, all those references
> >> >would make it a good bit more opaque. Another advantage to the view
> >> >is that it is possible to make it an indexed view, which could make a
> >> >major difference in performance.
> >> >
> >> >CREATE TABLE Table1
> >> >(StrungOut char(23) NOT NULL)
> >> >
> >> >INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
> >> >INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
> >> >INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
> >> >INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
> >> >
> >> >GO
> >> >CREATE VIEW Table1_V
> >> >AS
> >> >SELECT StrungOut,
> >> > First6 = SUBSTRING(StrungOut,1,6),
> >> > JDate = SUBSTRING(StrungOut,14,5),
> >> > JYear = SUBSTRING(StrungOut,14,2),
> >> > JDay = SUBSTRING(StrungOut,16,3),
> >> > Last4 = SUBSTRING(Strungout,20,4)
> >> > FROM Table1
> >> >GO
> >> >
> >> >CREATE TABLE Table2
> >> >(StrungOut char(23) NOT NULL)
> >> >GO
> >> >
> >> >CREATE PROC Demonstration
> >> >@.front char(12)
> >> >AS
> >> >
> >> >INSERT Table2
> >> >SELECT @.front + '-' +
> >> > right(convert(char(4),datepart(year,getdate())),2) +
> >> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> >> > '-' +
> >> > RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> >> > FROM Table1_V
> >> > WHERE JDate => >> > right(convert(char(4),datepart(year,getdate())),2) +
> >> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> >> > AND First6 = SUBSTRING(@.front,1,6)
> >> >GO
> >> >
> >> >EXEC Demonstration 'JCV-BCD-RBSV'
> >> >
> >> >SELECT *
> >> > FROM Table2
> >> >
> >> >StrungOut
> >> >--
> >> >JCV-BCD-RBSV-06242-0006
> >> >
> >> >Also, in the spec you mentioned matching on the first six characters,
> >> >but I wondered if it might have actually been the first seven.
> >> >
> >> >One final point worth making. It would be quite practical to modify
> >> >this so that rather than taking in a string as a parameter, it
> >> >produced a new row such as this for each Front6 in Table1 that matches
> >> >the current date.
> >> >
> >> >Roy Harvey
> >> >Beacon Falls, CT
> >> >
> >> >On Wed, 30 Aug 2006 09:09:02 -0700, Paul
> >> ><Paul@.discussions.microsoft.com> wrote:
> >> >
> >> >>Hi thanks for the response. Here are more details on what I am trying to do.
> >> >>There is a table (table1) that has hundreds of records that look like
> >> >>JTB-ABC-MDTC-06200-0001
> >> >>JCV-BCD-ABAM-06201-0001
> >> >>JTB-ABC-MDAC-06200-0002
> >> >>
> >> >>I need the stored procedure to take the value
> >> >>JCV-BCD-RBSV
> >> >>and build the rest of it based on the following conditions and then save it
> >> >>to a table.
> >> >>Get the current julian date, today would be 06242, two hundred forty two day
> >> >>of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
> >> >>and only the last section still to be built. This is done by doing the
> >> >>following.
> >> >>1. Compare the jul date for the day the stored procedure will run (06242)
> >> >>and find all records in table 1 with the same date (call this subset a).
> >> >>2. Next out of subset a find all that match the first 6 letters (create
> >> >>subset b).
> >> >>3. Next out of subset b find the greatest value of the last 4 numbers (say
> >> >>it was 0002).
> >> >>4. Finally increment this value by 1 and use it to finish the newly created
> >> >>value, so we would have JVC-BCD-RBSV-06242-0003.
> >> >>This is easy to do in vb or C#.
> >>
>|||If the proc should NOT add an entry based on conditions that it can
test in the database, then by all means put those tests in the proc,
and just schedule it to run unconditionally. Coding to prevent bad
data in your database is what we all strive for. It also means you
can schedule it to simply execute and not worry about it.
Roy Harvey
Beacon Falls, CT
On Wed, 30 Aug 2006 14:33:01 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>I tried out the sample and it worked. Anyhow just had a quick last question.
> Do you know if there is a way to conditionally run a job? I am thinking of
>scheduling the stored procedure but in some cases if a manual data entry has
>taken place (through an asp.net web application) I will not want to add a new
>record with the scheduled job. I guess this could be properly handled in the
>stored procedure(scheduled job), possibly perform some type of table check to
>see when the last entry took place to know weather or not to add a record.
>Thanks.|||ok sounds like a good idea!
--
Paul G
Software engineer.
"Roy Harvey" wrote:
> If the proc should NOT add an entry based on conditions that it can
> test in the database, then by all means put those tests in the proc,
> and just schedule it to run unconditionally. Coding to prevent bad
> data in your database is what we all strive for. It also means you
> can schedule it to simply execute and not worry about it.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 30 Aug 2006 14:33:01 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >I tried out the sample and it worked. Anyhow just had a quick last question.
> > Do you know if there is a way to conditionally run a job? I am thinking of
> >scheduling the stored procedure but in some cases if a manual data entry has
> >taken place (through an asp.net web application) I will not want to add a new
> >record with the scheduled job. I guess this could be properly handled in the
> >stored procedure(scheduled job), possibly perform some type of table check to
> >see when the last entry took place to know weather or not to add a record.
> >Thanks.
>

can this be done in SQL2000

Can This Be Accomplished in My Time Dimension in my AS2005 Cube?

In my AS2005 cube, I have a time dimension with the following attributes: year, quarter and month. Also, there is currently no hierarchy by design.

Is there a way to make a custom attribute/time period that reflects the last 12 months (R12M) and work in the same manner such as year, quarter and month?

The time dimension would then have: years, quarter, month and R12M.

As a result, a user can drop a measure such as Sales Amt and then add the R12M attribute from the time dimension and would then receive the total sales amount for the last 12 months.

I hope that made sense. I can and have written a calculated member for R12M Sales, but this R12M view is very common and repeated in our world. As an example, we have R12M Sales, R12M Volume, R12M # of Customers, etc.

Right now I have a calculated member for each one of those metrics, but it would be nice to move the repeatedness of R12M into the time dimension.

Is this possible? If so, how?

Thanks!

If I am understanding your question correctly all you would need to do is to create a calculated member and then place it on one of your attribute hierarchies. Here is an example calculated member that resides on the [Time].[Month] hierarchy and sums up any measure for the last 12 months that have sales data. You can remove the filter if you do not load month members unless there is data.

HTH,

- Steve

//BEGIN Last 12 Sales Months

CREATE MEMBER CURRENTCUBE.[Time].[Month].[All].[Last 12 Sales Months]

Aggregate(

Tail(

Filter([Time].[Month].Members,

([Measures].[Sales Amt]) <> 0),

12)

FORMAT_STRING = "Standard",

VISIBLE = 1 ;

//END Last 12 Sales Months

|||Steve,

Thanks for your help. Unfortunately, I'm still fairly new to Analysis Services and the whole cube thing that my understanding is very limited. With that said, assuming your approach will work, how do I add a Calculated Member to a time dimension?

I'm essentially doing all of this in BI Development Studio.

Regards,

Johnny|||Okay, so I think I know how to add the calculated member to the time dimension. I'll let you know how it goes.|||I don't think your suggestion is what I'm looking for, or I don't understand it.

I'd like to add an attirbute to the time dimension that represents the last 12 months, which would then be applied to any measure, whether it's sales or volume.

So a user could drag this "Last 12 Months" attribute from the time dimensoin and then whatever measure is dropped would result in the value for the last 12 months.

I believe your suggestion isn't as flexible and is only possible for sales?

Does that make sense?|||

Have you tried creating the member and adding to your "Month" attribute hierarchy? The reference to [Measures].[Sales Amt] is not needed and was used to demonstrate how you could filter out empty months if they existed in your cube. Here is a revised version that would give you a member on the "Time.Month" hierarchy which could be place on rows or columns for any measure to give you the twelve month total.

CREATE MEMBER CURRENTCUBE.[Time].[Month].[All].[Last 12 Sales Months]

Aggregate(

Tail(

[Time].[Month].Members,

12)

FORMAT_STRING = "Standard",

VISIBLE = 1 ;

|||

I have not implemented a time hierarchy, so Month, Quarter, Year and now [R12 Months] are all attributes from my time dimension.

Hopefully, I'm using the correct terminology. The current members for the Month attribute are 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 and "R12 Months".

If I add both the Year and Month attributes as row fields with Sales as a Data Item, I'm not getting what I want.

For example, for:

Year = 2006, Month = 1, Sales = $10M. "R12 Months" also equals $10M.

In this scenario, I'd want "R12 Months" during 01/2006 to be equal to the total sales for the 12 month period of 01/2005 to 12/2005.

If it was June 2006, I'd want the total sales for the period of June 2005 to May 2006.

Is this possible?

|||

I think I understand what the issue is now.What you need to do is to create a new attribute called “Period Calculations” and then create your rolling 12 month member using the “Period Calculations” dimension. Here are the steps you will need to follow:

1.) Edit your data source view and add a “Named Calculation” to your time dimension called “Period Calculations”. Enter the following string value for the calculation:

‘Current Period’

2.) Edit your Time dimension and drag the “Period Calculations” column you added to the time entity in the data source view to your “Attributes”.

3.) Click on the new “Period Calculations” attribute and then hit “F4” to bring up the properties window. Set the “IsAggregatable” property to “False”.

4.) Process your time dimension and cube.

5.) Add the following to your cube calculations script:

CREATE MEMBER CURRENTCUBE.[Time].[Period Calculations].[Rolling 12]

Aggregate(

[Time].[Month].CurrentMember.Lag(12):[Time].[Month].CurrentMember *

{([Time].[Period Calculations].DefaultMember,

[Time].[Year].[All],

[Time].[Quarter].[All])}),

VISIBLE = 1 ;

This will allow you to display:

2005 10 Rolling 12 (Sum of any measure for 10-2004 through 10-2005)

2005 11 Rolling 12 (Sum of any measure for 11-2004 through 11-2005)

…

2006 1 Rolling 12 (Sum of any measure for 1-2005 through 1-2006)

HTH,

- Steve

|||

Steve,

Your help has been invaluable! However, it's not returning the exact figures I'm expecting. Unfortunately, I can't figure out what it's really doing. The "Current Period" works just fine, but the "Rolling 12" doesn't.

Unfortunately, since I don't know what it's really doing, I'm not sure what kind of information I can provide that would help you understand what's going on.

Adding just the Year and Month attributes, I get "$115,488,690" for the Year 2005 total. Adding in "Period Calculations", "Rolling 12" never has the value of $115,488,960. In my ideal world, I would expect to see that figure of $115,488,960 in January 2006.

Our Rolling 12 is for the prevoius 12 months -- not including the current month. So for January 2006, the Rolling 12 period for us is January 2005 thru December 2005. If it was September 2006, the Rolling 12 period would be September 2005 thru August 2006.

I assumed with your misunderstanding of our Rolling 12 period, I would see the figure of $115,488,960 under Rolling 12 for December 2005. For what it's worth, the figure I see is higher: $125,075,196.

Any idea what I can do or provide that would help you understand what's going on?

Again, I REALLY appreciate your time and efforts!!!

|||

Well at least I think we are getting closer. First lets look at adjusting the MDX to calculate the 12 months prior to the current month. To accomplish this all we have to do is adjust the "range" specifid in the Aggregate dimension. I am using the "Lag" function which will use the current month to move back through the previous periods, so all that is needed is to set the range to Lag(13):Lag(1) as shown here:

CREATE MEMBER CURRENTCUBE.[Time].[Period Calculations].[Rolling 12]

Aggregate(

[Time].[Month].CurrentMember.Lag(13):[Time].[Month].CurrentMember.Lag(1) *

{([Time].[Period Calculations].DefaultMember,

[Time].[Year].[All],

[Time].[Quarter].[All])}),

VISIBLE = 1 ;

Now there is another topic that we have not discussed that may also be influencing your results. In an earlier post you stated that your Month attribute had the following members: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 and 12. I made what may be a bad assumption that your "Key" values for the Month attribute were unique. What I mean by this is that the source data used to populate your month attributes looks like the following:

Key Name

200401 1

200402 2

...

200501 1

200502 2

If this is not the case then the data for each month will sum for all your years and you will not get the results you are looking for. Just another item to check.

HTH,

- Steve

|||

Hi Steve,

I tried the new definition and notice that for January of any year, it will not having the "Rolling 12" available, only "Current Period".

Here's how the underlying Date table looks like that the Time dimension points to:

2 | 1996-01-01 00:00:00.000 | 1 | 1 | 1 | 1996

3 | 1996-01-02 00:00:00.000 | 2 | 1 | 1| 1996

...

3783 | 2006-01-29 00:00:00.000 | 29 | 1 | 1 | 2006

....

and so on until 12/31/2020.

I'm in my dsv, I used a Named Query to pull data from this Data table where year <= year(getdate()).

So with our structure in our date table, anyway to get the results that I am looking for?

Thanks,

Johnny

|||

Johnny,

There is an important concept that you need to be aware of with regard to "attribute" hierarchies in 2005. It is important that your attribute keys are unique when you are trying to sum up the months for a specific year. The description of your time dimension leads me to believe that you do not have unique keys and the calculation you want will not work without them. There is a great section in one of the ProjectReal write ups that talks about this issue. Check out the following URL:

http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx#EGAA

and do a search on:

"Best Practice (required): You must always ensure attribute key uniqueness"

HTH,

- Steve

|||

Steve,

Please see my previous post regarding the new behavior of "Rolling 12". I edited the post without realizing you had already replied.

Unfortunately, I don't understand MDX or Analysis Services that well at all.

I'm somewhat confused with my attribute keys not being unique. You're not referring to the date_key right?

This issue that you bring up, would it affect all calculations dependent on the Time dimesnion, or just in the case of Rolling 12?

I used to have a calculated member (still do, but it's not visible) for each Rolling 12 metric: Roling 12 Sales, Rolling 12 Volume, Rolling 12 # of Customers, etc. These all worked and calculated correctly.

I can provide you with the expression if that would help.

Regards,

Johnny

|||

Johnny,

There are many reasons why the unique key issue could cause difficulties. Given the limitations of this method of communication it is hard for me to know exactly what the issue is in your particular case. Feel free to contact me via my direct email and we can work further on this off line if you still need assistance.

- Steve

|||To anyone that may be reading this, I seriously cannot express how much appreciation I have for Steve helping me in this thread and offline. Again, thank you Steve!!!!

Thursday, March 22, 2012

can u guide me writing a simple query

Tuesday, March 20, 2012

Can this be simplified?

Can this be simplified?

Can this be simplified?

Can this be simplified?

Can this be optimized? Newbie question

Can this be done??

Monday, March 19, 2012

can this be done in SQL2000

can this be done in SQL2000

Can This Be Accomplished in My Time Dimension in my AS2005 Cube?

sql reports

Blog Archive

About Me