Solved

SQL Select Statement

Posted on 2008-10-08
7
225 Views
Last Modified: 2010-04-21
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
Comment
Question by:SasDev
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22673533
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
 
LVL 1

Author Comment

by:SasDev
ID: 22673588
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
 
LVL 5

Accepted Solution

by:
Cvijo123 earned 500 total points
ID: 22673605
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 1

Author Closing Comment

by:SasDev
ID: 31504430
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
 
LVL 1

Author Comment

by:SasDev
ID: 22673808
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
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22673882

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
 
LVL 1

Author Comment

by:SasDev
ID: 22673912
Good Call. Thanks for the knowledge transfer!
-- Traci
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question