Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Select Statement

Posted on 2008-10-08
7
Medium Priority
?
230 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 2000 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

719 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