QUERY

Question

Post reply

QUERY

komal145

SSCrazy Eights

Points: 9918
More actions
May 18, 2012 at 8:51 am

#256144

hi friends,
Entry_id Eventy_type_id( not unquie) Event_value( not unique)Event_datetimeusername
16pending05/17/2012 23:30ABC
16Pending letter05/17/2012 24:31DCBSA
18merge05/17/2012 24:32Adsadhsa
18EA05/17/2012 24:33Asafcsd
16pending05/17/2012 24:33Asdsac
17 archive05/17/2012 24:33sadaf
Look up table
Event_type_id Event_description
1status
6pending
7Sys_folder
I need the
columns: entry_id , newinforecievddate( date when status is first mergerd ) , pending date(date first when set to pending) , user name( who put first document status to merge )
When status = “merge” in “sys_folder” called archive
thanks for help,
komal

Viewing 14 posts - 1 through 13 (of 13 total)

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Based on your sample data (listed below for other people's convenience), what is your expected outcome?

SELECT Entry_id, Eventy_type_id, Event_value, Event_datetime, username

INTO #yourSampleData1

FROM (VALUES(1,6,'pending','05/17/2012 23:30','ABC'),

(1,6,'Pending letter','05/17/2012 24:31','DCBSA'),

(1,8,'merge','05/17/2012 24:32','Adsadhsa'),

(1,8,'EA','05/17/2012 24:33','Asafcsd'),

(1,6,'pending','05/17/2012 24:33','Asdsac'),

(1,7,'archive','05/17/2012 24:33','sadaf')

)a(Entry_id, Eventy_type_id, Event_value, Event_datetime, username);

SELECT Event_type_id, Event_description

INTO #yourSampleData2

FROM (VALUES(1,'status'),

(6,'pending'),

(7,'Sys_folder')

)a(Event_type_id, Event_description);

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

please try and make your posts a little more readable , otherwise it takes us twice as long

as far as i can make out this is what you want

what you described you wanted did not match the column names in your data - so i guessed.... if you want correct and accurate solutions then please be more precise

SELECT Entry_id, Eventy_type_id, Event_value, Event_datetime, username

INTO #yourSampleData1

FROM (VALUES(1,6,'pending','05/17/2012 23:30','ABC'),

(1,6,'Pending letter','05/17/2012 24:31','DCBSA'),

(1,8,'merge','05/17/2012 24:32','Adsadhsa'),

(1,8,'EA','05/17/2012 24:33','Asafcsd'),

(1,6,'pending','05/17/2012 24:33','Asdsac'),

(1,7,'archive','05/17/2012 24:33','sadaf')

)a(Entry_id, Eventy_type_id, Event_value, Event_datetime, username);

SELECT Event_type_id, Event_description

INTO #yourSampleData2

FROM (VALUES(1,'status'),

(6,'pending'),

(7,'Sys_folder')

)a(Event_type_id, Event_description);

select x.entry_id ,y.newin,z.pd

from #yourSampleData1 x

inner join (select entry_id,min(event_datetime) newin from #yourSampleData1 where event_value='merge' group by entry_id) y on y.entry_id=x.entry_id

inner join (select entry_id,min(event_datetime) pd from #yourSampleData1 where event_value='Pending' group by entry_id) z on z.entry_id=x.entry_id

inner join (select entry_id,username,event_datetime from #yourSampleData1 ) u on u.entry_id=y.entry_id and u.event_datetime=y.newin

where Event_value='merge' and Eventy_type_id=7

drop table #yourSampleData2

drop table #yourSampleData1

MVDBA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

just to clarify about being more precise

Entry_id Eventy_type_id( not unquie) Event_value( not unique)Event_datetimeusername

When status = “merge” in “sys_folder” called archive

did you mean when event_value="merge"

this would be a much better way of giving us the information

ps - you should thank the first responder to your query for very kindly posting a script to generate the data - he saved me some time doing it myself - it wouldn't have been hard for you to do that would it?

MVDBA

komal145 SSCrazy Eights Points: 9918 More actions · Answer 4

Sorry my criteria changed!!!

Entry_id Eventy_type_id( not unquie) Event_value( not unique)Event_datetimeusername

16pending05/17/2012 23:30ABC

16Pending letter05/17/2012 24:31DCBSA

11newinforecieevd05/17/2012 24:32Adsadhsa

18EA05/17/2012 24:33Asafcsd

16pending05/17/2012 24:33Asdsac

TABLE A scenario 1

TABLE A scenario 2

Entry_id Eventy_type_id( not unquie) Event_value( not unique)Event_datetimeusername

21carrierexception05/17/2012 23:30ABC

21newinforecieevd05/17/2012 24:32Adsadhsa

28EA05/17/2012 24:33Asafcsd

26pending05/17/2012 24:33Asdsac

Look up table

Event_type_id Event_description

1status

6pending

7Sys_folder

I need the NEWinfo data:

columns: entry_id , newinforecievddate( date when status is first “Newinforecieved”) , pending date(date first when set to pending) , user name( who put first document status to“Newinforecieved”))

When status = “newinforecieved”

Need to loop through each and every entry and get the min (pendate ) and min (new info received) date for that entry_id whose status =“newinforecieved” for “newinforecords”

Rules : doc is said to be in “ new info received” when status is “new info received”.

2) So it is said to newinfo doc: when doc moves from “pending” to “newinforecieved”

Or when “ pendingletter” to “newinfo recieved”

Or when “carrier_exception” status to “newinforecieved”

So when status went to “carrierexception” to “new info received “ we may not have pending date.

sorry my scenario changed again !!! To be more precise I gave example and explained the rules.I need to have a cursor to loop through

this scenario , so that I can check each entry and get the entry with pending and carrier exception and pending letter status

I wrote cursor but not looping properly.

Thanks,

komal

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

komal145 (5/18/2012)
Sorry my criteria changed!!!
Entry_id Eventy_type_id( not unquie) Event_value( not unique)Event_datetimeusername
16pending05/17/2012 23:30ABC
16Pending letter05/17/2012 24:31DCBSA
11newinforecieevd05/17/2012 24:32Adsadhsa
18EA05/17/2012 24:33Asafcsd
16pending05/17/2012 24:33Asdsac
TABLE A scenario 1
TABLE A scenario 2
Entry_id Eventy_type_id( not unquie) Event_value( not unique)Event_datetimeusername
21carrierexception05/17/2012 23:30ABC
21newinforecieevd05/17/2012 24:32Adsadhsa
28EA05/17/2012 24:33Asafcsd
26pending05/17/2012 24:33Asdsac
Look up table
Event_type_id Event_description
1status
6pending
7Sys_folder
I need the NEWinfo data:
columns: entry_id , newinforecievddate( date when status is first “Newinforecieved”) , pending date(date first when set to pending) , user name( who put first document status to“Newinforecieved”))
When status = “newinforecieved”
Need to loop through each and every entry and get the min (pendate ) and min (new info received) date for that entry_id whose status =“newinforecieved” for “newinforecords”
Rules : doc is said to be in “ new info received” when status is “new info received”.
2) So it is said to newinfo doc: when doc moves from “pending” to “newinforecieved”
Or when “ pendingletter” to “newinfo recieved”
Or when “carrier_exception” status to “newinforecieved”
So when status went to “carrierexception” to “new info received “ we may not have pending date.
sorry my scenario changed again !!! To be more precise I gave example and explained the rules.I need to have a cursor to loop through
this scenario , so that I can check each entry and get the entry with pending and carrier exception and pending letter status
I wrote cursor but not looping properly.
Thanks,
komal

Look with eye, look at the first response to your initial post. Do you see what that individual did for you? You need to do that for us.

Also, post your expectedd results based on the sample data, and post the code you have already written.

komal145 SSCrazy Eights Points: 9918 More actions · Answer 6

Expected:

Entry_id pendeddate newinfodate username

105/17/2012 23:3005/17/2012 24:32 ABC

2NULL 05/17/2012 23:32(doc moved from carrier exception to newinfo)ABC

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

komal145 (5/18/2012)
Expected:
Entry_id pendeddate newinfodate username
105/17/2012 23:3005/17/2012 24:32 ABC
2NULL 05/17/2012 23:32(doc moved from carrier exception to newinfo)ABC

Once more with feeling. Look at the very first post after your initial post on this thread. That is what we need you to do for us.

komal145 SSCrazy Eights Points: 9918 More actions · Answer 8

SELECT Entry_id, Eventy_type_id, Event_value, Event_datetime, username

Here it goes

INTO #yourSampleData1

FROM (VALUES(1,6,'pending','05/17/2012 23:30','ABC'),

(1,6,'Pending letter','05/17/2012 24:31','DCBSA'),

(1,1,'newinforecieved','05/17/2012 24:32','Adsadhsa'),

(1,8,'EA','05/17/2012 24:33','Asafcsd'),

(1,6,'pending','05/17/2012 24:34','Asdsac'),

(2,1,'carrierexception','05/17/2012 23:30','BASG'),

(2,1,'NEWinforecieved','05/17/2012 24:36','bashdsu')

)a(Entry_id, Eventy_type_id, Event_value, Event_datetime, username);

SELECT Event_type_id, Event_description

INTO #yourSampleData2

FROM (VALUES(1,'status'),

(6,'pending')

)a(Event_type_id, Event_description);

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 9

CELKO (5/18/2012)
There is no such crap as a “type_id”;

You really need to take a course on indexes in the Rushmore engine if you still think that, Joe.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 10

(2,1,'NEWinforecieved','05/17/2012 24:36','bashdsu')

are you taking the mick ? i think you ned to buy a new watch if you think 24:36 is a valid time

feel free to keep asking questions, but i think i'll duck out of this one until you ask in the correct format - despite my response that advises you to be more specific you are still posting questions that are unintelligable

MVDBA

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 11

michael vessey (5/21/2012)
(2,1,'NEWinforecieved','05/17/2012 24:36','bashdsu')
are you taking the mick ? i think you ned to buy a new watch if you think 24:36 is a valid time
...

Have you ever been on Mars? The day length there is 24h 37m 22sec (and 663 millisecond), so I assume the provided value is a valid sample out of Martian SQL Server version.

Is it time for admin to create a dedicated forum for it?

:hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 12

Eugene Elutin (5/21/2012)
michael vessey (5/21/2012)
(2,1,'NEWinforecieved','05/17/2012 24:36','bashdsu')
are you taking the mick ? i think you ned to buy a new watch if you think 24:36 is a valid time
...
Have you ever been on Mars? The day length there is 24h 37m 22sec (and 663 millisecond), so I assume the provided value is a valid sample out of Martian SQL Server version.
Is it time for admin to create a dedicated forum for it?
:hehe:

it would proboably be the busiest of all the forums

MVDBA

adrian.facio SSCrazy Points: 2406 More actions · Answer 13

Hi Komal,

Are you trying to achieve something like this?. It's just my guessing.

SELECT ta.EntryId,NewInfoDate = ta.Event_datetime,Pendant.Event_DateTime ,ta.UserName

FROM TableA ta

INNER JOIN TableA Pendant

ON Pendant.EntryId = ta.EntryId AND

Pendant.Event_Type_Id = 6

WHEN ta.Event_value = "newinforecieved"