June 22, 2019 at 11:04 am
Hi,
I have the below table:
EmpID HeaderID Status XML_Payload
1 123 Failed <xml>
1 321 Pending <xml>
1 555 Pending <xml>
1 111 Failed <xml>
1 222 Pending <xml>
2 333 Failed <xml>
2 444 Pending <xml>
3 555 Failed <xml>
Can you please help me how to get results for the below scnearios dynamically?
EmpID HeaderID Status XML_Payload
1 123 Failed <xml>
1 321 Pending <xml>
1 555 Pending <xml>
2. If I supply Emp =1 and HeaderID = 111 and status = Failed, it should return the results as follows:
EmpID HeaderID Status XML_Payload
1 111 Failed <xml>
1 222 Pending <xml>
Thanks in advance, please.
June 23, 2019 at 11:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 23, 2019 at 1:37 pm
This looks a bit like a homework question. If so, what have you tried so far to solve the problem yourself? What, if you haven't tried, have you researched to try and find the solution? We're happy to help you get to the solution for your homework, but you do need to show your attempts so we can advise you where you went wrong; not do your homework for you.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 23, 2019 at 2:43 pm
Hi,
Below is my Query.
SELECT
EmpID,
HeaderID,
Status,
Time
FROM
<TableName>
WHERE
EmpID = '1'
AND Status = 'PENDING'
AND ROW_ID <= ISNULL( (
SELECT
ROW_ID
FROM
(
SELECT
ROW_NUMBER() over(partition by EmpID
ORDER BY
ROW_ID) AS Sl_No,
ROW_ID
FROM
<TableName>
WHERE
EmpID = '1'
AND HeaderID = '123'
AND Status = 'FIXED'
)
A
WHERE
A.Sl_No = 2),
(
SELECT
MAX(ROW_ID)
FROM
<TableName>
WHERE
EmpID = '123'
)
)
When I supply EmpId and HeaderId i should get only 'Pending' records.
Below is my expected results, please:
Thanks
June 24, 2019 at 2:49 pm
The logic for the rows that you want to return is unclear. For instance, if you supply "Emp = 1 and HeaderID = 123 and Status = Failed" why should you return HeaderID 321 and 555 when they clearly do not equal 123 and also have status "Pending" instead of "Failed"?
Also, the expected results for your original post contradict the expected results for your second post. Furthermore, your expected results for the second post contain a status that does not exist in the sample data and does not describe how to calculate that status from the sample date.
Your query also references fields that are not included in your sample data (RowID and Time) which will almost certainly be required to produce your expected results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply