query - which came last?

Hi,
Query puzzler here.
I have a table of PEOPLE.
Besides the Person ID, each record has five Date fields- Married ,
Graduated, Started Job, Got Famous, Got Rich
I want to write a query that shows what the current status of each person
is.
In other words, show which event has happened most recently for each person
Any help would be , and might keep me from getting fired.No datefields in the table? Can you show one or two rows as sample in your
table?
Thanks,
Sree
"Tales Mein" wrote:

> Hi,
> Query puzzler here.
> I have a table of PEOPLE.
> Besides the Person ID, each record has five Date fields- Married ,
> Graduated, Started Job, Got Famous, Got Rich
> I want to write a query that shows what the current status of each person
> is.
> In other words, show which event has happened most recently for each perso
n
> Any help would be , and might keep me from getting fired.
>
>
>
>|||Hi, no-one wants to see you get fired...
SELECT PersonID, max(Married) FROM (
select PersonID, Married from PEOPLE
union
select PersonID, Graduated from PEOPLE
union
select PersonID, [Started Job] from PEOPLE
union
select PersonID, [Got Famous] from PEOPLE
union
select PersonID, [Got Rich] from PEOPLE) as Derived
GROUP BY PersonID
HTH. Ryan
"Tales Mein" <toddough@.doughpdu.com> wrote in message
news:IuednZ9bsJhDO0neRVn-oQ@.comcast.com...
> Hi,
> Query puzzler here.
> I have a table of PEOPLE.
> Besides the Person ID, each record has five Date fields- Married ,
> Graduated, Started Job, Got Famous, Got Rich
> I want to write a query that shows what the current status of each person
> is.
> In other words, show which event has happened most recently for each
> person
> Any help would be , and might keep me from getting fired.
>
>
>
>|||you can use this code:
SELECT ID,
Case When Married IS NOT NULL
And Married >= Coalesce (Graduated,'19000101')
And Married >= Coalesce ([Started Job],'19000101')
And Married >= Coalesce ([Got Famous],'19000101')
And Married >= Coalesce ([Got Rich],'19000101')
Then 'Married'
When Graduated IS NOT NULL
And Graduated >= Coalesce (Married,'19000101')
And Graduated >= Coalesce ([Started Job],'19000101')
And Graduated >= Coalesce ([Got Famous],'19000101')
And Graduated >= Coalesce ([Got Rich],'19000101')
Then 'Graduated'
When [Started Job] IS NOT NULL
And [Started Job] >= Coalesce (Graduated,'19000101')
And [Started Job] >= Coalesce (Married,'19000101')
And [Started Job] >= Coalesce ([Got Famous],'19000101')
And [Started Job] >= Coalesce ([Got Rich],'19000101')
Then '[Started Job]'
When [Got Famous] IS NOT NULL
And [Got Famous] >= Coalesce (Graduated,'19000101')
And [Got Famous] >= Coalesce ([Started Job],'19000101')
And [Got Famous] >= Coalesce (Married,'19000101')
And [Got Famous] >= Coalesce ([Got Rich],'19000101')
Then '[Got Famous]'
When [Got Rich] IS NOT NULL
And [Got Rich] >= Coalesce (Graduated,'19000101')
And [Got Rich] >= Coalesce ([Started Job],'19000101')
And [Got Rich] >= Coalesce ([Got Famous],'19000101')
And [Got Rich] >= Coalesce (Married,'19000101')
Then '[Got Rich]'
Else 'nothing happend'
End
As LastEvent,
Case When Married IS NOT NULL
And Married >= Coalesce (Graduated,'19000101')
And Married >= Coalesce ([Started Job],'19000101')
And Married >= Coalesce ([Got Famous],'19000101')
And Married >= Coalesce ([Got Rich],'19000101')
Then Married
When Graduated IS NOT NULL
And Graduated >= Coalesce (Married,'19000101')
And Graduated >= Coalesce ([Started Job],'19000101')
And Graduated >= Coalesce ([Got Famous],'19000101')
And Graduated >= Coalesce ([Got Rich],'19000101')
Then Graduated
When [Started Job] IS NOT NULL
And [Started Job] >= Coalesce (Graduated,'19000101')
And [Started Job] >= Coalesce (Married,'19000101')
And [Started Job] >= Coalesce ([Got Famous],'19000101')
And [Started Job] >= Coalesce ([Got Rich],'19000101')
Then [Started Job]
When [Got Famous] IS NOT NULL
And [Got Famous] >= Coalesce (Graduated,'19000101')
And [Got Famous] >= Coalesce ([Started Job],'19000101')
And [Got Famous] >= Coalesce (Married,'19000101')
And [Got Famous] >= Coalesce ([Got Rich],'19000101')
Then [Got Famous]
When [Got Rich] IS NOT NULL
And [Got Rich] >= Coalesce (Graduated,'19000101')
And [Got Rich] >= Coalesce ([Started Job],'19000101')
And [Got Rich] >= Coalesce ([Got Famous],'19000101')
And [Got Rich] >= Coalesce (Married,'19000101')
Then [Got Rich]
End
As LastEventDate
FROM PEOPLE
"Tales Mein" wrote:

> Hi,
> Query puzzler here.
> I have a table of PEOPLE.
> Besides the Person ID, each record has five Date fields- Married ,
> Graduated, Started Job, Got Famous, Got Rich
> I want to write a query that shows what the current status of each person
> is.
> In other words, show which event has happened most recently for each perso
n
> Any help would be , and might keep me from getting fired.
>
>
>
>|||Thanks - this seems to be the shorter of the two, saving typing, allowing
more time to beg for my job.
Both are instructive.
thnanks a lot
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23iR7usAIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hi, no-one wants to see you get fired...
> SELECT PersonID, max(Married) FROM (
> select PersonID, Married from PEOPLE
> union
> select PersonID, Graduated from PEOPLE
> union
> select PersonID, [Started Job] from PEOPLE
> union
> select PersonID, [Got Famous] from PEOPLE
> union
> select PersonID, [Got Rich] from PEOPLE) as Derived
> GROUP BY PersonID
> --
> HTH. Ryan
> "Tales Mein" <toddough@.doughpdu.com> wrote in message
> news:IuednZ9bsJhDO0neRVn-oQ@.comcast.com...
>|||Hi this is almost there, but it returns dates. I need the column name
returened.
Like: Married
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23iR7usAIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hi, no-one wants to see you get fired...
> SELECT PersonID, max(Married) FROM (
> select PersonID, Married from PEOPLE
> union
> select PersonID, Graduated from PEOPLE
> union
> select PersonID, [Started Job] from PEOPLE
> union
> select PersonID, [Got Famous] from PEOPLE
> union
> select PersonID, [Got Rich] from PEOPLE) as Derived
> GROUP BY PersonID
> --
> HTH. Ryan
> "Tales Mein" <toddough@.doughpdu.com> wrote in message
> news:IuednZ9bsJhDO0neRVn-oQ@.comcast.com...
>|||I need the status, not the date
OK I could use both.
I may get fired after all
"Tales Mein" <toddough@.doughpdu.com> wrote in message
news:IuednZ9bsJhDO0neRVn-oQ@.comcast.com...
> Hi,
> Query puzzler here.
> I have a table of PEOPLE.
> Besides the Person ID, each record has five Date fields- Married ,
> Graduated, Started Job, Got Famous, Got Rich
> I want to write a query that shows what the current status of each person
> is.
> In other words, show which event has happened most recently for each
> person
> Any help would be , and might keep me from getting fired.
>
>
>
>|||On Mon, 23 Jan 2006 02:50:28 -0700, Tales Mein wrote:

>Hi,
>Query puzzler here.
>I have a table of PEOPLE.
>Besides the Person ID, each record has five Date fields- Married ,
>Graduated, Started Job, Got Famous, Got Rich
>I want to write a query that shows what the current status of each person
>is.
>In other words, show which event has happened most recently for each person
>Any help would be , and might keep me from getting fired.
Hi Tales,
The person who designed that table should get fired, not you.
First, let's create a view to get a better normalised version of the
data:
CREATE VIEW Normalized
AS
SELECT PersonID, Married AS EventDate, 'Married' AS Event
FROM PEOPLE
UNION ALL
SELECT PersonID, Graduated, 'Graduated'
FROM PEOPLE
UNION ALL
SELECT PersonID, "Started Job", 'Started Job'
FROM PEOPLE
UNION ALL
SELECT PersonID, "Got Famous", 'Got Famous'
FROM PEOPLE
UNION ALL
SELECT PersonID, "Got Rich", 'Got Rich'
FROM PEOPLE
go
With this, getting the required information becomes a lot easier:
SELECT a.PersonID, a.EventDate, a.Event
FROM Normalized AS a
WHERE a.EventDate = (SELECT MAX(b.EventDate)
FROM Normalized AS b
WHERE b.PeopleID = a.PeopleID)
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||thanks a lot|||Hugo,
Is it just me, or are the columns Married, Graduated, StartedJob, GotFamous,
and GotRich unlikely columns in any database?
Unlikely, of course, unless the database is academic only.
Maybe Tales simply made up these column names because they couldn't tell us
the actual columns for proprietary reasons, but I am willing to bet this is
a classroom example, and no one's job is at risk.
I believe Tales just got this newsgroup to do his/her homework.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:r3adt15gapiv1bvvkcitvvrfq33rg850d8@.
4ax.com...
> On Mon, 23 Jan 2006 02:50:28 -0700, Tales Mein wrote:
> Hi Tales,
> The person who designed that table should get fired, not you.
>

query using the record number

happy friday...
my table has 200,000 + records and I want to see the particular record which I think it is causing the problem.
How could i query 195,203rd record?
thank you, yanorooWhat problem is it causing? How did you figure out the number to begin with?|||What problem is it causing? How did you figure out the number to begin with?

oh, the record number is just an example.
actual case is -I ran dts package to import data from txt file to sql table.
The dts truncated the table and imported the records correctly (218503 record) but I can't find the data from 208775th record on the table...
I want to see the what was populated into the table from 208775th record.
And strange thing is the table has 237043 record (which is more than input)|||are you getting 237043 by doing a count(*) on the table?|||are you getting 237043 by doing a count(*) on the table?
yes, it is by count(*) on the table.|||If you are just debugging the process, then temporarily create an incrementing identity column on the table.|||If you are just debugging the process, then temporarily create an incrementing identity column on the table.

Hello,

Found out the problem was DTS prod installation error.
Thank you anyway for your suggestion.
Yanoroo|||i suggest in a situation like this to create a staging table when you transfer large amounts of data to an sql server. this way you can set the error count high and allow sql server to leave the rows causing the error in the staging table while you move the clean data to the final destination. you can then have the dts send you an email notifying you of the prob and you can clean those rows in the morning.

go to www.SQLDts.com|||I assume there is an unique field named pid in your table and you are looking for a particular record of pid(in this case 195,203rd).You can try this query--

select * from mytable where pid not in
(select top 195,202 pid from mytable)

If you have any other type of requirement that can be solved too.
Subhasish

query (deals with exclusions)

I have 2 tables. The first table is a master table with 3 fields: record id, list, and value. The second table is a lookup table that has the list and value in it, as well as fieldname. I need a query that will return a count of records in the master table that do not exist in the lookup table based on list and value. It seems straightforward but my brain doesn't seem to be working. I keep returning the count of records that don't match in the lookup table, instead of the master table. can anyone help? This was my code that isn't working:

select m.list, m.value, count(*)
from master m, lookup l
where m.list = l.list and
m.value <> l.value
and fieldname = 'BOC'
group by m.list, m.value

Thanks in advance.If the columns LIST and VALUE are the joining columns between the MASTER table and the LOOKUP table then try this query:

SELECT COUNT(*)
FROM MASTER m
WHERE NOT EXISTS
(
SELECT *
FROM LOOKUP l
WHERE l.LIST = m.LIST
AND l.VALUE = m.VALUE
)

Query

I need a little help with this query.
I am querying the value of field, but I need to add to the logic that if
there is no record then the value = 1
This is the query as I have it:
SELECT *
FROM dbo.ParametersUsers
WHERE UserId = 1713 and ParamName = 'showpopups'
How do I tell the query that "ParamValue" = 1 if no result is returned ?
Thanks.
AleksTry,
select coalesce((select top 1 ParamValue FROM dbo.ParametersUsers where
UserId = 1713 and ParamName = 'showpopups'), 1) as ParamValue
AMB
"Aleks" wrote:

> I need a little help with this query.
> I am querying the value of field, but I need to add to the logic that if
> there is no record then the value = 1
> This is the query as I have it:
> --
> SELECT *
> FROM dbo.ParametersUsers
> WHERE UserId = 1713 and ParamName = 'showpopups'
> --
> How do I tell the query that "ParamValue" = 1 if no result is returned ?
> Thanks.
> Aleks
>
>|||Hi
Probably you are looking for this:
SELECT ISNULL(ParamValue,1), ...<other values>
FROM dbo.ParametersUsers
WHERE UserId = 1713 and ParamName = 'showpopups'
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Aleks" wrote:

> I need a little help with this query.
> I am querying the value of field, but I need to add to the logic that if
> there is no record then the value = 1
> This is the query as I have it:
> --
> SELECT *
> FROM dbo.ParametersUsers
> WHERE UserId = 1713 and ParamName = 'showpopups'
> --
> How do I tell the query that "ParamValue" = 1 if no result is returned ?
> Thanks.
> Aleks
>
>|||"Aleks" schrieb:

> I need a little help with this query.
> I am querying the value of field, but I need to add to the logic that if
> there is no record then the value = 1
> This is the query as I have it:
> --
> SELECT *
> FROM dbo.ParametersUsers
> WHERE UserId = 1713 and ParamName = 'showpopups'
> --
> How do I tell the query that "ParamValue" = 1 if no result is returned ?
> Thanks.
> Aleks
>
IF EXISTS (SELECT *
FROM dbo.ParametersUsers
WHERE UserId = 1713 and ParamName = 'showpopups')
SET @.ParamValue = 1
ELSE
SELECT @.ParamValue = ParamValue
FROM dbo.ParametersUsers
WHERE UserId = 1713 and ParamName = 'showpopups'|||Sorry - of course I meant:
IF NOT EXISTS (...sql

query

Hi, I have a query to build a returned record as and order by Name:
ID, Name
5 A
1 B
3 C '?
4 D
2 E
Now, I want to add a parameter (for example ID=3) to the query (or somehow),
so the returned record only return:
ID, Name
4 D
2 E
How to do that? Thanks.Please post the text of your query, then I will have my worker bees make the
changes you request.|||Try,
select [id], [name]
from t1
where [id] > 3
order by [name]
AMB
"js" wrote:

> Hi, I have a query to build a returned record as and order by Name:
> ID, Name
> 5 A
> 1 B
> 3 C '?
> 4 D
> 2 E
>
> Now, I want to add a parameter (for example ID=3) to the query (or somehow
),
> so the returned record only return:
> ID, Name
> 4 D
> 2 E
>
> How to do that? Thanks.
>
>|||Add a WHERE clause to your query. It is possible to create a stored
procedure which accepts a parameter. The stored procedure would return data
just as your query does.
Keith
"js" <js@.someone@.hotmail.com> wrote in message
news:e1O1SpATFHA.3056@.TK2MSFTNGP14.phx.gbl...
> Hi, I have a query to build a returned record as and order by Name:
> ID, Name
> 5 A
> 1 B
> 3 C '?
> 4 D
> 2 E
>
> Now, I want to add a parameter (for example ID=3) to the query (or
> somehow), so the returned record only return:
> ID, Name
> 4 D
> 2 E
>
> How to do that? Thanks.
>
>|||Thanks...
Add a WHERE clause(where [id] > 3) to query will filter out (2, E)?
select [id], [name]
from t1
where [id] > 3
order by [name]
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:O1uUovATFHA.2996@.TK2MSFTNGP15.phx.gbl...
> Add a WHERE clause to your query. It is possible to create a stored
> procedure which accepts a parameter. The stored procedure would return
> data just as your query does.
> --
> Keith
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:e1O1SpATFHA.3056@.TK2MSFTNGP14.phx.gbl...
>|||One solution would be:
Select T.Id, T.Name
From Table As T
Where Ascii(T.Name) > Ascii('C')
Another would be
Select T.Id, T.Name
From Table As T
Where Cast(T.Name As Binary(1)) > Cast('C' As Binary(1))
Of course, by doing it this way, you'll likely get an index or table scan.
Thomas
"js" <js@.someone@.hotmail.com> wrote in message
news:e1O1SpATFHA.3056@.TK2MSFTNGP14.phx.gbl...
> Hi, I have a query to build a returned record as and order by Name:
> ID, Name
> 5 A
> 1 B
> 3 C '?
> 4 D
> 2 E
>
> Now, I want to add a parameter (for example ID=3) to the query (or somehow
),
> so the returned record only return:
> ID, Name
> 4 D
> 2 E
>
> How to do that? Thanks.
>
>|||Actually, SQL as I just discovered, SQL is smart enough to do this sort of
analysis on strings for you. So you should be able to do this:
Select T.Id, T.Name
From Table As T
Where T.Name > 'C'
Thomas
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23CLAj9ATFHA.3036@.TK2MSFTNGP10.phx.gbl...
> One solution would be:
> Select T.Id, T.Name
> From Table As T
> Where Ascii(T.Name) > Ascii('C')
> Another would be
> Select T.Id, T.Name
> From Table As T
> Where Cast(T.Name As Binary(1)) > Cast('C' As Binary(1))
> Of course, by doing it this way, you'll likely get an index or table scan.
>
> Thomas
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:e1O1SpATFHA.3056@.TK2MSFTNGP14.phx.gbl...
>|||Thanks Thomas,
I don't know 'C' but only "ID=3"

Query:
select [id], [name] from tb1
union
select [id], [name] from tb2
union
select [id], [name] from tb3
ORDER BY [name]
Output:
How to return this base the previous query:
Can I use a cursor in sql:
dim rs
sql0= "DECLARE TBs_Cursor CURSOR FOR "
sql1 = "select [id], [name] from tb1 union select [id], [name] from tb2
union select [id], [name] from tb3 ORDER BY [name]"
sql2 = sql0 + sql1 + 'OPEN Cursor, Loop if ID < 3, then delete, and return
the records'
rs.open sql2,...
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23CLAj9ATFHA.3036@.TK2MSFTNGP10.phx.gbl...
> One solution would be:
> Select T.Id, T.Name
> From Table As T
> Where Ascii(T.Name) > Ascii('C')
> Another would be
> Select T.Id, T.Name
> From Table As T
> Where Cast(T.Name As Binary(1)) > Cast('C' As Binary(1))
> Of course, by doing it this way, you'll likely get an index or table scan.
>
> Thomas
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:e1O1SpATFHA.3056@.TK2MSFTNGP14.phx.gbl...
>|||On Thu, 28 Apr 2005 11:52:44 -0400, "js" <js@.someone@.hotmail.com> wrote:

>Hi, I have a query to build a returned record as and order by Name:
>ID, Name
>5 A
>1 B
>3 C '?
>4 D
>2 E
>
>Now, I want to add a parameter (for example ID=3) to the query (or somehow)
,
>so the returned record only return:
>ID, Name
>4 D
>2 E
>
>How to do that? Thanks.
>
Hi js,
Try if this does what you want:
SELECT ID, Name
FROM MyTable
WHERE Name > (SELECT Name
FROM MyTable
WHERE Id = 3)
ORDER BY Name
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> I don't know 'C' but only "ID=3"
Not sure what you mean here.
Thomas

Query

I have duplicate records in a table based on say
OrderNum, I would like to copy 1st record for each
OrderNum from this table into a new table and then update
the OrderNum record in the 2nd table with values from
rest of the records in the 1st table, can someone help me
in doing this?
Ex.
Frist table may have -
ORder1 AreaField1 description ...
ORder1 AreaField1 Description ...
Order1 AreaField2 Description ...
ORder2 AreadField Description ...
Order2 AreaField Description ...
ORder3 AreaField Description ...
ORder4 AreaField Description ...
I would like the 2nd table to look lik -
ORder1 AreaField1+AreaField2 Description ...
ORder2 AreaField Description ...
Order3 AreaField Description ...
ORder4 AreaField Description...
Thank you very much,
-Kim
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.mseq:7748
On Thu, 12 Aug 2004 11:56:32 -0700, Kim wrote:
(snip)
Hi Kim,
I just replied to an equal post in .programming.
Please don't multi-post!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

query

I haven't done this for a while and i am stuck, any help would be appriciate
d.
I need to return the last employee id for each record in my database. Here
is my table.
Appt_ID, Employee_ID, Time_Stamp
1 5 7/25/05
2 7 7/26/05
2 12 7/30/05
3 2 8/2/05
I need the quert to return Appt_ID 1, 2, and 3 with the latest employee id.
Only 1 employee per Appt_ID. Any help would be great! Thanks!Try,
select Appt_ID, Employee_ID, Time_Stamp
from t1 as a
where Time_Stamp = (select max(b.Time_Stamp) from t1 as b where b.Appt_ID =
a.Appt_ID)
AMB
"B.A. Barakus" wrote:

> I haven't done this for a while and i am stuck, any help would be appricia
ted.
> I need to return the last employee id for each record in my database. Her
e
> is my table.
>
> Appt_ID, Employee_ID, Time_Stamp
> 1 5 7/25/05
> 2 7 7/26/05
> 2 12 7/30/05
> 3 2 8/2/05
>
> I need the quert to return Appt_ID 1, 2, and 3 with the latest employee id
.
> Only 1 employee per Appt_ID. Any help would be great! Thanks!|||Hi
you can do it as:
SELECT Appt_ID, MAX(Employee_ID)
FROM <EmployeeTable>
GROUP BY appt_ID
please let me know if you would like to know anything else.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"B.A. Barakus" wrote:

> I haven't done this for a while and i am stuck, any help would be appricia
ted.
> I need to return the last employee id for each record in my database. Her
e
> is my table.
>
> Appt_ID, Employee_ID, Time_Stamp
> 1 5 7/25/05
> 2 7 7/26/05
> 2 12 7/30/05
> 3 2 8/2/05
>
> I need the quert to return Appt_ID 1, 2, and 3 with the latest employee id
.
> Only 1 employee per Appt_ID. Any help would be great! Thanks!|||Apearently, On the basis of provided data Chandra's query works fine.
But I think that solution from Alejandro Mesa is the correct one
because Chandra, if we use MAX(Employee_ID), it will always means
Maximum for a perticular appt_ID, where as "last employee" should be in
respect to Max(Time_Stamp).|||thats true, but time stamp might take a new value when the data is updated
too. the new emplyee will have a max number.
that was my assumption
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Zero.NULL" wrote:

> Apearently, On the basis of provided data Chandra's query works fine.
> But I think that solution from Alejandro Mesa is the correct one
> because Chandra, if we use MAX(Employee_ID), it will always means
> Maximum for a perticular appt_ID, where as "last employee" should be in
> respect to Max(Time_Stamp).
>|||The employee could change internally to an existing employee. Think of the
situation as an account manager where accounts could be transfered between
then for various reasons. I just need a single appointment id, the id of th
e
current employee handing the account which would be based off the highest
date time stamp. Hope this helps.
"Chandra" wrote:
> thats true, but time stamp might take a new value when the data is updated
> too. the new emplyee will have a max number.
> that was my assumption
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Zero.NULL" wrote:
>|||Chandra, I understand, according to your assumption the query is
perfect. But as
B. A. Barakus has cleared his stand on this, I think he is loking for
such a query given by Alejandro Mesa.
Ok I try to clear it more, If his table is like this:
Appt_ID, Employee_ID, Time_Stamp
1 5 7/25/05
2 7 7/26/05
2 12 7/30/05
3 2 8/2/05
and if Employee_ID gets changed from 12 to any ID less then 12 (say 6)
for Appt_ID: 2 and Time_Stamp: 7/30/05
the data would look like:
Appt_ID, Employee_ID, Time_Stamp
1 5 7/25/05
2 7 7/26/05
2 6 7/30/05
3 2 8/2/05
now query by Alejandro Mesa will fetch you
1 5 7/25/05
2 6 7/30/05
3 2 8/2/05
where as query by Chandra gets:
1 5 7/25/05
2 7 7/30/05
3 2 8/2/05
Hope this solves the issue.

Quering Fact table using MDX Queries

Is there any way that I can query the Fact table using the MDX queries. What
I want to do is to find whether a record exists in the fact table at a
particular level.
Thanks
Akber.
That would be the Drillthrough command (Docd in books on line)...Below is
the example from BOL
DRILLTHROUGH
SELECT [Warehouse].[All Warehouses].[Canada].[BC] ON ROWS,
[Time].[1998].[Q1] ON COLUMNS,
[Product].[All Products].[Drink] ON PAGES,
[Measures].[Units Shipped] ON SECTIONS
FROM [My Cube]
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Akber" <Akber@.discussions.microsoft.com> wrote in message
news:B1B53F76-5677-4176-A03D-FF8B19F65CF1@.microsoft.com...
> Is there any way that I can query the Fact table using the MDX queries.
What
> I want to do is to find whether a record exists in the fact table at a
> particular level.
> --
> Thanks
> Akber.

Queries returning Multiple instances of the same record

I am running SQL 2005 and have created a simple database for interfacing with Visual Web Dev 2005 and Visual Studio 2005. I noticed that my applications where returning multiple instances of the same records in the queries. I went back to the SQL server and created a query, and sure enough, I'm getting each record returned 3 times. Where do I start to resolve this issue?DId you have the records already duplicated in the database tables ? Or did you just made a mistake in your join, defining the wrong joined keys. THe best thing would be to provide some information like DDL and some sample data.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

My DB and query are much simpler than what you are imagining:

The DB Structure is:

<MemberID, Int,> - Primary Key Autoincrement

<FirstName, nvarchar(30),>

<LastName, nvarchar(30),>

<Salutation, nvarchar(20),>

<MemberType, nvarchar(20),>

<IsNeighbor, tinyint,>

<Title, nvarchar(30),>

<Address, nvarchar(60),>

<Address2, nvarchar(60),>

<City, nvarchar(30),>

<State, nvarchar(2),>

<Zip, nvarchar(9),>

<Phone, nvarchar(10),>

<Email, nvarchar(50),>

<DateJoined, datetime,>

<ExpirationDate, datetime,>

<SubMemberTo, int,>

<Fax, nvarchar(10),>

<Cellphone, nvarchar(10),>

The SELECT query is:

SELECT [MemberID]

,[FirstName]

,[LastName]

,[Salutation]

,[MemberType]

,[IsNeighbor]

,[Title]

,[Address]

,[Address2]

,[City]

,[State]

,[Zip]

,[Phone]

,[Email]

,[DateJoined]

,[ExpirationDate]

,[SubMemberTo]

,[Fax]

,[Cellphone]

FROM [FriendsSQL].[dbo].[Members]

WHERE FirstName = 'ANNE' and LastName = 'REIS'

Without the WHERE clause the query returns the entire DB without duplication, however, when the WHERE clause is included the output is:

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

Notice that the single record is returned 3 times.

|||DOH! You were right. The records were duplicated. Apparently using the SET Insert Unique ON and not having the Primary Key set allowed the duplications. I've cleaned up the mess and I'll try not to shoot off any more toes. Sorry for the bother. I should have caught that one.

q; Running SP with a different user

We are using SQL2000. User insert table in one database DB1 and trigger
insert the record into another database DB2. In this scenario, is it possible
have a trigger in DB1 to execute a stored procedure in DB2 with a different
user?
SQL Server 2000 does not have the functionality to change user security
context. You might consider using cross-database chaining as I mentioned in
your other "q; user running trigger" thread.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A226A886-5748-456F-92F5-08C041CA54DB@.microsoft.com...
> We are using SQL2000. User insert table in one database DB1 and trigger
> insert the record into another database DB2. In this scenario, is it
> possible
> have a trigger in DB1 to execute a stored procedure in DB2 with a
> different
> user?

q; Running SP with a different user

We are using SQL2000. User insert table in one database DB1 and trigger
insert the record into another database DB2. In this scenario, is it possibl
e
have a trigger in DB1 to execute a stored procedure in DB2 with a different
user?SQL Server 2000 does not have the functionality to change user security
context. You might consider using cross-database chaining as I mentioned in
your other "q; user running trigger" thread.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A226A886-5748-456F-92F5-08C041CA54DB@.microsoft.com...
> We are using SQL2000. User insert table in one database DB1 and trigger
> insert the record into another database DB2. In this scenario, is it
> possible
> have a trigger in DB1 to execute a stored procedure in DB2 with a
> different
> user?

q; Running SP with a different user

We are using SQL2000. User insert table in one database DB1 and trigger
insert the record into another database DB2. In this scenario, is it possible
have a trigger in DB1 to execute a stored procedure in DB2 with a different
user?SQL Server 2000 does not have the functionality to change user security
context. You might consider using cross-database chaining as I mentioned in
your other "q; user running trigger" thread.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A226A886-5748-456F-92F5-08C041CA54DB@.microsoft.com...
> We are using SQL2000. User insert table in one database DB1 and trigger
> insert the record into another database DB2. In this scenario, is it
> possible
> have a trigger in DB1 to execute a stored procedure in DB2 with a
> different
> user?

q; INSRET/UPDATE trigger

Hi,
I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this
rollback record in the table? It seems it does and I am wondering if there i
s
any workaround.
Thanks,Jim - can you post up the trigger code. I'm wondering if there is a try
catch block and a rollback statement in the catch section? In that case you
could use print/xp_logevent depending on your actual requirement.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Paul,
Thanks you very much, no try check, here is the code:
-- duplicate record check
IF EXISTS (
SELECT *
FROM myTable i INNER JOIN con c ON i.ch = c.B_ch
WHERE i.tID=@.tID
)
Begin
SET @.ErrorD = 'Dup Record'
RAISERROR(@.ErrorD, 16, 1)
Return
End
"Paul Ibison" wrote:

> Jim - can you post up the trigger code. I'm wondering if there is a try
> catch block and a rollback statement in the catch section? In that case yo
u
> could use print/xp_logevent depending on your actual requirement.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>|||Jim,
the raiserror statement can't be doing the rollback with this severity. In
your trigger there is no reference to the INSERTED table - I'm wondering if
this is this really the trigger code? With the reference to @.tID it looks
more like stored procedure code. Also, please check to see if there are any
other triggers?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||On Fri, 29 Dec 2006 10:19:01 -0800, JIM.H. wrote:

>Hi,
>I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this
>rollback record in the table?
Hi Jim,
It definitely does not. Run the following code in Sql Server Management
Studio or Query Analyzer to see for yourself:
CREATE TABLE Test
(a int NOT NULL PRIMARY KEY);
go
CREATE TRIGGER TestTrig
ON Test AFTER INSERT
AS
RAISERROR('This is a test', 16, 1);
go
INSERT INTO Test (a)
VALUES (1);
SELECT *
FROM Test;
go
DROP TABLE Test;
go

> It seems it does and I am wondering if there is
>any workaround.
Is it possible that your client automatically requests a rollback upon
seeing the error condition? I know some clients (Access, for instance)
that make a habit of rolling back first and asking questions later.
Hugo Kornelis, SQL Server MVP|||JIM.H. wrote:
> Hi Paul,
> Thanks you very much, no try check, here is the code:
> -- duplicate record check
> IF EXISTS (
> SELECT *
> FROM myTable i INNER JOIN con c ON i.ch = c.B_ch
> WHERE i.tID=@.tID
> )
> Begin
> SET @.ErrorD = 'Dup Record'
> RAISERROR(@.ErrorD, 16, 1)
> Return
> End
>
>
Unfortunately you only posted part of the trigger code but this looks
suspiciously like your code is at fault. I suspect your trigger fails
to operate correctly when multiple rows are updated because you don't
appear to be referencing the INSERTED table properly. That might
explain some unpredictable behaviour.
If you want help to rewrite the trigger we'll need a better spec - read
my signature.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

q; INSRET/UPDATE trigger

Hi,
I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this
rollback record in the table? It seems it does and I am wondering if there is
any workaround.
Thanks,
Jim - can you post up the trigger code. I'm wondering if there is a try
catch block and a rollback statement in the catch section? In that case you
could use print/xp_logevent depending on your actual requirement.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
Thanks you very much, no try check, here is the code:
-- duplicate record check
IF EXISTS (
SELECT *
FROM myTable i INNER JOIN con c ON i.ch = c.B_ch
WHERE i.tID=@.tID
)
Begin
SET @.ErrorD = 'Dup Record'
RAISERROR(@.ErrorD, 16, 1)
Return
End
"Paul Ibison" wrote:

> Jim - can you post up the trigger code. I'm wondering if there is a try
> catch block and a rollback statement in the catch section? In that case you
> could use print/xp_logevent depending on your actual requirement.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Jim,
the raiserror statement can't be doing the rollback with this severity. In
your trigger there is no reference to the INSERTED table - I'm wondering if
this is this really the trigger code? With the reference to @.tID it looks
more like stored procedure code. Also, please check to see if there are any
other triggers?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

q:INSRET/UPDATE trigger

Hi,

I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this rollback record in the table? It seems it does and I am wondering if there is any workaround.

Thanks,

No, that shouldn't rollback INSERT/UPDATE to the table, unless you define the trigger as "INSTEAD OF" trigger. You can take a look atCREATE TRIGGER