SQL Statement help

[Tweak155]

Hey guys,

I hope I'm an established member around here at this point, so I'm just going to straight up ask the question . I'm expecting this to be cake for any real SQL dev.

To break it down to the simplest data points, basically what I want to figure out are 2 things with one query statement, although it is not required if it makes sense to do 2.

Essentially I have employee ID's with dates with some additional data that makes each date unique to each employee, simple set that will outline my scenario below:

EMPID   DATE     VAL
1       1/1/15    1
1       1/2/15    2
2       1/1/15    1

I have a specified date range of 1/1/15 to 1/2/15 so I want to pull all the employee ID's, dates & values for matching date ranges - but one special caveat. Notice how Employee ID 2 does not have an entry for 1/2/15? I want all of their rows excluded because they don't have data for my ending date of 1/2/15. So in this case, I should only receive the 2 rows back for Employee ID 1.

Hope that makes sense... the only solutions I could think of involved filtering data some how after it was retrieved... can you do this in a single query? I am a SQL novice and only do real basic data retrieval so this could be trivial.

 

[Jaydip]

So is you filter criteria includes a list of dates or just a particular date? anyways it should be like this
select * from emp
where empdate between to_date('1/1/15 ,'MM/DD/YYYY') AND to_date('1/2/15 ','MM/DD/YYYY') and your exclusion criteria

 

[Tweak155]

So is you filter criteria includes a list of dates or just a particular date? anyways it should be like this
select * from emp
where empdate between to_date('1/1/15 ,'MM/DD/YYYY') AND to_date('1/2/15 ','MM/DD/YYYY') and your exclusion criteria

Just a from and to date. So it could be 1/1/15 to 1/3/15 and both employees 1 and 2 would be excluded.

Perhaps it's not clear, but what would you write for your exclusion criteria...

 

[theknight571]

If I understand your question properly, I think this might work. It worked on your supplied sample data.

SET @StartDate = '2015-01-01';
SET @EndDate = '2015-01-02';
SET @NumDays = DATEDIFF(@EndDate,@StartDate)+1;

SELECT a.*
FROM anandtest a, 
     (SELECT EMPID, COUNT(EMPID) AS TheCount
     FROM anandtest
     WHERE DATE BETWEEN @StartDate AND @EndDate
     GROUP BY EMPID) b
WHERE b.TheCount = @NumDays
  AND a.EMPID = b.EMPID

Edit to add that this is for MySQL. The syntax may differ for other versions of SQL.

 

[KB]

This is what I think you are talking about, but I could be confusing your requirements.
This is SQLServer code:

SELECT * FROM table WHERE EMPID IN(SELECT EMPID FROM table WHERE DATE = '1/2/2015')

 

[Tweak155]

This is what I think you are talking about, but I could be confusing your requirements.
This is SQLServer code:

SELECT * FROM table WHERE EMPID IN(SELECT EMPID FROM table WHERE DATE = '1/2/2015')

This looks like it could be close, as long as i can add something like this:

SELECT * FROM table WHERE EMPID IN(SELECT EMPID FROM table WHERE DATE = '1/2/2015') AND DATE >='1/1/15' AND DATE <='1/2/15'

I will try this out when I can... does this look like a valid SQL statement?

 

[Tweak155]

If I understand your question properly, I think this might work. It worked on your supplied sample data.

SET @StartDate = '2015-01-01';
SET @EndDate = '2015-01-02';
SET @NumDays = DATEDIFF(@EndDate,@StartDate)+1;

SELECT a.*
FROM anandtest a, 
     (SELECT EMPID, COUNT(EMPID) AS TheCount
     FROM anandtest
     WHERE DATE BETWEEN @StartDate AND @EndDate
     GROUP BY EMPID) b
WHERE b.TheCount = @NumDays
  AND a.EMPID = b.EMPID

Edit to add that this is for MySQL. The syntax may differ for other versions of SQL.

Unfortunately I should have indicated there could be gaps... so if EmpID 1 has 1/1/15 and 1/3/15 and the end date is 1/3/15, I should get EmpID1 back with 2 rows of data. I think the other suggestion may work (it makes sense in my head at least)... I will test it when I can.

 

[uclabachelor]

SELECT * FROM (
    SELECT COUNT(date) AS count, EMPID FROM table WHERE
    date >=date1 AND date <=date2 GROUP BY EMPID
)
AS t1 
WHERE t1.count 
IN(
    SELECT DATEDIFF(DAYS, '6/1/15', '6/5/15') AS Days
)

The first inner query gets the count of rows for employees between the two dates.

The second inner query counts the number of days between two dates.

The main query gets only the rows from the first inner query where the number of entries matches the number of days.

This only works if each empid has only one entry per day. If there are more than one entry per day, the DISTINCT keyword has to be added to the first inner query.

Edit: TheKnight has it correct and it should work, however requires a stored procedure to set the date variables.

 

[theknight571]

Unfortunately I should have indicated there could be gaps... so if EmpID 1 has 1/1/15 and 1/3/15 and the end date is 1/3/15, I should get EmpID1 back with 2 rows of data. I think the other suggestion may work (it makes sense in my head at least)... I will test it when I can.

Ok.. the following should find all EMPIDs that have at least one entry with the start date and one entry with the end date.

It will only return the entries for each EMPID with dates between the start and end. My previous example would have returned all entries for each EMPID regardless of the start and end dates.

SET @StartDate = '2015-01-01';
SET @EndDate = '2015-01-03';

SELECT a.*
FROM anandtest a, 
     (SELECT EMPID, SUM(IF(DATE = @StartDate, 1, 0)) AS SD,  SUM(IF(DATE = @EndDate, 1, 0)) AS ED
     FROM anandtest
     WHERE DATE = @StartDate 
        OR DATE = @EndDate
     GROUP BY EMPID) b
WHERE b.SD >=1
  AND b.ED >=1
  AND a.EMPID = b.EMPID
  AND DATE BETWEEN @StartDate AND @EndDate

If you only want them if they have a single entry for the start and single entry for the end dates, then you would change the b.SD >= 1 to b.DS = 1... same for the b.ED.

I just used the variables so you didn't have to type the dates repeatedly... you could substitute the date itself for @StartDate and @EndDate.

Also at least with PHP and MySQL you might be able to build your query using: "Start Transaction" and "Commit", for example:

//Note:  I have not tested this, just providing it as an example based on a similar
//query I use for PHP and MSSQL where I set the variables without a stored procedure.

$mysqlQuery = "START TRANSACTION
     SET @StartDate = '".$startDate."';
     SET @EndDate = '".$endDate."';

     SELECT a.*
     FROM anandtest a, 
          (SELECT EMPID, SUM(IF(DATE = @StartDate, 1, 0)) AS SD,  SUM(IF(DATE = @EndDate, 1, 0)) AS ED
          FROM anandtest
          WHERE DATE = @StartDate 
             OR DATE = @EndDate
          GROUP BY EMPID) b
     WHERE b.SD >=1
       AND b.ED >=1
       AND a.EMPID = b.EMPID
       AND DATE BETWEEN @StartDate AND @EndDate
     COMMIT";

 

[Tweak155]

This looks like it could be close, as long as i can add something like this:

SELECT * FROM table WHERE EMPID IN(SELECT EMPID FROM table WHERE DATE = '1/2/2015') AND DATE >='1/1/15' AND DATE <='1/2/15'

I will try this out when I can... does this look like a valid SQL statement?

This style query ended up working for me, thanks for all the help guys!