Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Select Statement

Posted on 2008-10-08
7
Medium Priority
?
236 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 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

773 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