0

Need your help on getting the sql query for the below data

TableName: EMP_DB

EMPID   Flag    Processing Date
ABC1    yes      Current Date
ABC1     No      Old Date
DEF1    Yes      Current Date
DEF1     No      Old Date
GHI1    Yes      Old Date
JKL1    yes      Current Date
JKL1     No      Old Date

i need to select only EMP ID for the above table for which Flag is set to Yes and Processing Date is old Date. i.e In the above table be having 2 records for each Emp, i need to select record for which employee is having only one record and for which processing date is old date and flag is set to Yes.

Regards, San

user3451428
  • 11
  • 2
  • 8
    That's nice. Good luck figuring it out. Did you have a question? – Marc B Mar 23 '14 at 05:00
  • Hi Marc, yes i need the sql query for the above requirments – user3451428 Mar 23 '14 at 05:03
  • 1
    In `sql server` or `mysql` ? – Bharadwaj Mar 23 '14 at 05:05
  • 2
    SO really isn't the place to say "Here's what I need, do my work/homework for me". Show what you have tried and what has worked or not worked and what you need help with. – grantmcconnaughey Mar 23 '14 at 05:05
  • hi All, I am extremely sorry, in middle of one meeting will respond back on what I have tried and what is my requirements. PS: thnks for the users-jih,grantmc,crescent moon for your respone. – user3451428 Mar 23 '14 at 09:16
  • Hi below is the query i had tried: select EMPID,COUNT(EMPID) AS NumberOfTimes from EMP_DB where Processing Date< DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE()) AND flag='0' GROUP BY EMPID HAVING (COUNT(EMPID)<2) O/P i was getting was all emp records which was having processing date= old date, though i had given count(empid)<2 – user3451428 Mar 23 '14 at 11:10
  • i dont no about sql. related to my work, sometimes i do use it for extracting the reports from DB.in this case its manily a select statement and reuired fields halong with date range. to arrive at the above query itself i had spend some good time(~2days) going through various sql sites i am not having much time to work on this. so just thought of asking exprts so that atleast i can get the directions on which commands i need to use. but looks like, in this forum there are some users who will be cheking whether this is homework type question or not... – user3451428 Mar 23 '14 at 11:10
  • @ Marc B, m hasan, Lorenz Meyer, simoco, Uwe Keim : i saw my question has been put [on hold].. surprised to see this. i went through my question once agin, i had explained in simple terms and technically on what to be the desired O/P..... dont no why this question was put as Hold, whether this forum requires Business justification as well on when this requirements will be useful? i thought this forum is to share the knowledge/ideas with others and help people who are new to the technology – user3451428 Mar 23 '14 at 11:26
  • Not showing what you've tried so far is typically viewed as a no-no on stackoverflow. Your question was worded like "here's what I need, do my work for me". In the future, show what you've tried so we can help you fix mistakes instead of doing the whole thing for you. – grantmcconnaughey Mar 23 '14 at 14:11
  • Also, you should probably look into getting an SQL book. That query is fairly trivial and should definitely not take you 2 days to come up with. Sam's Teach Yourself SQL in 10 Minutes is fairly short and to the point, and would be worth picking up. – grantmcconnaughey Mar 23 '14 at 14:15
  • @grantmc:hi grantmc, pls see the above commnet i had posted on the query i had tried and o/p it was getting... fyi i am pasting that comment once again:Hi below is the query i had tried: select EMPID,COUNT(EMPID) AS NumberOfTimes from EMP_DB where Processing Date< DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE()) AND flag='0' GROUP BY EMPID HAVING (COUNT(EMPID)<2) O/P i was getting was all emp records which was having processing date= old date, though i had given count(empid)<2 – user3451428 Mar 23 '14 at 15:16
  • I saw that. In the future, that needs to go in your question right off the bat. – grantmcconnaughey Mar 23 '14 at 15:21
  • @grantmc:i dont no why you r stressing on the point that i have posted this question without giving a try... when i had posted this question in the next comment i was about post on the query i had tried but suddenly i got some other work, so coudnt check on this again... and from my perspective forum is to discuss and to share the knowledge and not like users posting the questions to get their homework done, i had never thought that forum will be used for this... – user3451428 Mar 23 '14 at 15:24
  • @grantmc:i work on diff technology, building that query i had tried on my own by going through the sql basics it took 2 days for me, i dint say this will take 2 days for sql experts or for the ones who know sql... – user3451428 Mar 23 '14 at 15:25
  • I'm not stressing on it, just letting you know that if you're wondering why this was put on hold then, well, that's why. – grantmcconnaughey Mar 23 '14 at 15:30
  • @grantmc: done, will take care this advise in future.... my bad, i coudnt access net after i had posted this question... – user3451428 Mar 23 '14 at 15:35

3 Answers3

1

Honestly the design of your structure seems quite awful. You seem to be using a varchar to store boolean concepts instead of bit.

Regarding dates, I guess you're simplifying your question but be careful that comparing dates could be tricky.

So, I'm just gonna answer your question as stated:

select emp.EMPID
from EMP_DB emp
left join EMP_DB empNo on empNo.EMPID=emp.EMPID and lower(empNo.Flag) = 'No'
where lower(emp.Flag) = 'yes' and emp.[Processing Date] = 'Old Date' and empNo is null

Edit: Take seriously the advices from other users to share what you have tried.

Edit2: Quite hard to understand your question, I make another flavour of @grantmc answer.

Ignacio Hagopian
  • 126
  • 1
  • 8
1

I think this is what you are looking for?

I interpreted the question as you want to make sure the employee doesn't have another record in the table where the flag is "No", so this query checks for that.

SELECT E1.empid 
FROM   emp_db E1 
WHERE  lower(E1.flag) = 'yes' 
       AND E1.[processing date] = 'Old Date' 
       AND NOT EXISTS (SELECT 'X' 
                       FROM   emp_db E2 
                       WHERE  E1.empid = E2.empid 
                              AND lower(E2.flag) = 'no')

In your test table, this query will only pick up the employee with ID GHI1, as all of the others have an entry where flag = "No"

grantmcconnaughey
  • 10,130
  • 10
  • 37
  • 66
0

Try this:

select distinct EMPID
from EMP_DB
where Flag ='yes'
and Processing Date = 'Old Date'
Choudhury Saadmaan Mahmid
  • 1,265
  • 2
  • 29
  • 53
  • 2
    occams says this is the right explanation – Mudassir Hasan Mar 23 '14 at 05:21
  • @mhasan I don't understand what you meant by 'occams' here, as I didn't use occam language here. – Choudhury Saadmaan Mahmid Mar 23 '14 at 05:42
  • 1
    He's saying your answer is the shortest meaning that Occam's Razor would suggest this is the right answer. – grantmcconnaughey Mar 23 '14 at 05:49
  • @Crescent Moon: hi, no this query will return alll the emp records which are having processing date =old date. for each emp there will be 2 rows in DB one in which one of the record will be having processing date as current date with flag as yes and other record having processing date as old date with flag as No. but there are few cases in which there will be only record for an employee having procesing date old date with flag as Yes. i need to identify only emp record which are having only one row with processing date as old date, flag=yes – user3451428 Mar 23 '14 at 11:20
  • @user3451428 I don't get you. You said in your question that you wanted only 'one' row for each EMPID with Processing Date = 'Old Date' and Flag = 'yes', didn't you? 'select distinct' is designed for that purpose, to eliminate multiple similar rows and fetch only one row. You need to be more specific and clear in your question. – Choudhury Saadmaan Mahmid Mar 23 '14 at 11:44
  • @Crescent Moon: hi, pls see the question i had posted(last 2 lines)i need to select only EMP ID for the above table for which Flag is set to Yes and Processing Date is old Date. i.e In the above table be having 2 records for each Emp, i need to select record for which employee is having only one record and for which processing date is old date and flag is set to Yes. – user3451428 Mar 23 '14 at 14:02
  • 1
    @user3451428 I got your point now. Sorry for not being able to understand before. In your case, the most appropriate solution is the one provided by grantmc, where he used the sql 'not exists' query. This will make sure that you don't have any other records of the selected EMPID where flag = 'no' and processing date = 'current date'. So, thanks to grantmc. You can also check out this post - http://stackoverflow.com/questions/18197366/how-to-use-not-exists-in-a-sql-query-with-w3schools :) – Choudhury Saadmaan Mahmid Mar 24 '14 at 08:48