[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

SQL Select Statement

I want to display the distinct date and id for the records in this result set. The records I need are the records with the greatest incremental id number for a particular day. So, I only want one record to show up for each date. For Instance, I want to display a list of dates an employee had off and the latest reason as to why he had it off. The latest reason would be determined by the highest (unid) incremental number.
Below is a list of a particular days an employee had off and the sdid code indicating why:

select   unid,  sdate, sdid
from sickDays
where employeenum = '1501'
and datepart(yyyy, sdate) = '2008'

unid         sdate                                       sdid
9490      2008-06-16 00:00:00.000      17
10014      2008-08-01 00:00:00.000      17
10015      2008-08-15 00:00:00.000      17
10016      2008-08-14 00:00:00.000      17
10017      2008-08-15 00:00:00.000      3
10018      2008-08-29 00:00:00.000      17
10019      2008-08-13 00:00:00.000      17
10020      2008-08-14 00:00:00.000      17
10021      2008-08-15 00:00:00.000      17
10022      2008-09-19 00:00:00.000      17
10023      2008-10-03 00:00:00.000      17
10024      2008-11-28 00:00:00.000      17
10025      2008-12-26 00:00:00.000      17
10785      2008-08-15 00:00:00.000      18

Below is the desired result set: As you can see 2008-08-15 only shows up once and has the highest unid code
unid         sdate                                       sdid
9490      2008-06-16 00:00:00.000      17
10014      2008-08-01 00:00:00.000      17
10016      2008-08-14 00:00:00.000      17
10018      2008-08-29 00:00:00.000      17
10019      2008-08-13 00:00:00.000      17
10020      2008-08-14 00:00:00.000      17
10022      2008-09-19 00:00:00.000      17
10023      2008-10-03 00:00:00.000      17
10024      2008-11-28 00:00:00.000      17
10025      2008-12-26 00:00:00.000      17
10785      2008-08-15 00:00:00.000      18

I tried to attempt this myself with the following but I haven't got it quite right. 2008-08-15 still shows up twice.

select   distinct   a.sdate, a.sdid
from sickDays a
join sickdays b on
b.sdate = a.sdate
where a.employeenum = '1501'
and datepart(yyyy, a.sdate) = '2008' and a.unid > b.unid  and a.sdid = b.sdid

Result set:
sdate                                       sdid
2008-06-16 00:00:00.000      17
2008-08-01 00:00:00.000      17
2008-08-13 00:00:00.000      17
2008-08-14 00:00:00.000      17
2008-08-15 00:00:00.000      17
2008-08-15 00:00:00.000      18
2008-08-29 00:00:00.000      17
2008-09-19 00:00:00.000      17
2008-10-03 00:00:00.000      17
2008-11-28 00:00:00.000      17
2008-12-26 00:00:00.000      17


0
SasDev
Asked:
SasDev
  • 4
  • 3
1 Solution
 
Cvijo123Commented:
u mean something like this:

select  
a.sdate,
a.sdid,
max( a.unid) as unid
from sickDays a
join sickdays b on
b.sdate = a.sdate
where a.employeenum = '1501'
and datepart(yyyy, a.sdate) = '2008' and a.unid > b.unid  and a.sdid = b.sdid
group by
a.sdate,
 a.sdid
0
 
SasDevAuthor Commented:
I am still getting 8-15 twice with that code.

sdate                                      sdid          unid
2008-06-16 00:00:00.000      17      9490
2008-08-01 00:00:00.000      17      10014
2008-08-13 00:00:00.000      17      10019
2008-08-14 00:00:00.000      17      10020
2008-08-15 00:00:00.000      17      10021
2008-08-29 00:00:00.000      17      10018
2008-09-19 00:00:00.000      17      10022
2008-10-03 00:00:00.000      17      10023
2008-11-28 00:00:00.000      17      10024
2008-12-26 00:00:00.000      17      10025
2008-08-15 00:00:00.000      18      10785
0
 
Cvijo123Commented:
what sdid u wanna to show ?
 u can use max(sdid) too if u need to get only 1 row per date u need to choose what kind of sdid u need to display (for unid u said its highiest)
 
 this one will get only 1 row for dates and highiest numbers for sdid and unid;

select  
a.sdate,
max(a.sdid) as sdid,
max( a.unid) as unid
from sickDays a
join sickdays b on
b.sdate = a.sdate
where a.employeenum = '1501'
and datepart(yyyy, a.sdate) = '2008' and a.unid > b.unid  and a.sdid = b.sdid
group by
a.sdate
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
SasDevAuthor Commented:
Thank you so much! This took me forever before I finally submitted a question. I feel like a dork that you were able to do it so quickly, but I guess thats what this site intended for...to help the beginners. Thanks again,

-- Traci
0
 
SasDevAuthor Commented:
After a second glance I realized that it wouldn't work for me to select the max on sdid because it shouldn't matter what the sdid is (it could be 3, 17, or 18) but what matters is selecting the max unid. Here is how I got it, thanks to the help of Cvigo123:
select    distinct sdate, max(unid)as unid
into #sickday
from sickDays
where employeenum = '1501'
and datepart(yyyy, sdate) = '2008'
group by sdate
select a.sdate, b.sdid
from #sickday a
join sickdays b on
a.unid = b.unid

Result Set Obtained:
sdate                                       sdid
2008-06-16 00:00:00.000      17
2008-08-01 00:00:00.000      17
2008-08-13 00:00:00.000      17
2008-08-14 00:00:00.000      17
2008-08-15 00:00:00.000      18
2008-08-29 00:00:00.000      17
2008-09-19 00:00:00.000      17
2008-10-03 00:00:00.000      17
2008-11-28 00:00:00.000      17
2008-12-26 00:00:00.000      17
0
 
Cvijo123Commented:

u dont need to insert your results into temp table becouse it will require SQL server to write and read to your disk (I/O traffic), instead u should use subquery like this:

      
SELECT
      a.sdate,
      b.sdid
FROM
      ( SELECT  
                  sdate,
                  max ( unid ) AS unid
            FROM
                  sickDays
            WHERE
                  employeenum = '1501'
                  And datepart(      yyyy,      sdate      ) = '2008'
            GROUP BY
                  sdate
      ) a
JOIN sickdays b      
      ON a.unid = b.unid
0
 
SasDevAuthor Commented:
Good Call. Thanks for the knowledge transfer!
-- Traci
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now