Hello,
I have JobHead Table (criteria: JobReleased = True). I want to link a subquery to the jobhead
Subquery table = Chglog (link Jobhead.JobNumb to Chglog.Key1) where Chglog.logtext contains “JobReleased: False -> True”.
I was able to get all this info. My issue is that some jobs were released then un-released and released back again. I want to get only thr first release log text. Is there a way to get only first log text by logdatestamp?
i tried “TOP” 1 but that gave me the last released job only. I want it for all the jobs but only 1st entry
Thanks… I did what you mentioned. It does give me top 1 but i need top 1 for all the jobs. This method only gives me the top job only.
I wanted all jobs from Jobhead where Jobhead.Released = True.
And i want ONLY the 1st line from Chglog.LogText table where “JobReleased: False -> True”
When i sort by datetime and TOP 1, i only get 1 job. I want all the jobs with their 1st Logtext which is “JobReleased: False -> True”
Your best bet is to do a CTE to get the first record of each job, or you could do an inner select subquery (a subquery that returns a single field and is used as a subselect)
Try this before you go the CTE route. This is the second part of what Jose is saying, sorry to repeat.
If you are just looking for a date this will work. If you are looking for information in the logText this will be a good start.
Create a query that returns the ChgLog information you are looking for.
One that shows all jobnum where Job Released to true and the DateStampedOn field.
Group this query results by job and select the MIN(DateStampedOn). This will give your query results of JobNum and Date (first time JobReleased = True). This will be your inner query of the chglog table. Then you can use the subquery to grab the information from the chglog table.
Be careful with the date field incase the field was changed multiple time on the same day. You will need to make sure you are grabbing the correct one. The timestamp is in the LogText. This could get complicated quickly.