Solved

SQL Select Statement

Posted on 2008-10-08
7
208 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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 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.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

828 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