Solved

how to get max record and count from same table?

Posted on 2013-01-25
9
510 Views
Last Modified: 2013-01-29
i have employee table

Employeeid   name

A1111          AAA

A1112         AA3

 

and   i have empIN  table

Employeeid    Attemptdate                                             SQTY    

A1111            2012-02-06 10:26:22.673                            1

A1111            2012-02-06 10:26:22.674                            2

A1111            2013-01-18  08:26:122.550                         8

 

A1112            2013-01-21 10:42:47.720                             7

A1112            2013-01-21 10:42:47.721                             8

A1112            2012-10-28 12:42:48.621                             1

A1112             2012-10-28 12:42:48.622                           8

A1112             2012-10-28 12:42:48.623                            5

A1112              2012-12-30  12:42:48.622                          8

A1112                2012-10-30 12:42:48.623                         5

 

using this 2 tables i need the following output- how can i achieve this using sql qurey

LastAttempt= his last attempt   max(date)

NoOfAttempts= NO  of  attempts  (A1111 made only two attempts, one is on 2012-02-06 & 2013-01-18 ) - take only one record per day , do not consider time )

OutPut:

Employeeid    code         LastAttempt                               NoOfAttempts        

A1111             AAA      2013-01-18 08:26:122.550              2

A1112            AA3        2013-01-21 10:42:47.721                3
0
Comment
Question by:Varshini S
[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
  • 3
  • 3
  • 3
9 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 38820282
Hi,

select
    EmployeeID
    , code
    , max( AttemptDate )
    , sum( SQTY )
from table
group by
    EmployeeID
    , code
;

let me know if you want a fuller & tested example

HTH
  David
0
 
LVL 11

Accepted Solution

by:
SANDY_SK earned 500 total points
ID: 38820384
Try this
select b.Employeeid , name ,max(datein) as lastAttempt,count(*) as NoOfAttempts from(
select distinct convert(varchar, Attemptdate, 105) datein,Employeeid from empIN
) as tab inner join employee b on tab.Employeeid = b.Employeeid group by b.Employeeid,name

Open in new window

0
 
LVL 35

Expert Comment

by:David Todd
ID: 38820420
Hi SANDY_SK

count( * ) doesn't match the supplied sample data, where SQTY column has a quantity - I used sum( sqty ) to return this value.

I could have misread things as I commonly do

Regards
  David
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Closing Comment

by:Varshini S
ID: 38821400
awosome

Thank You
0
 

Author Comment

by:Varshini S
ID: 38825678
select b.Employeeid , name ,max(datein) as lastAttempt,count(*) as NoOfAttempts from(
select distinct convert(varchar, Attemptdate, 105) datein,Employeeid from empIN
) as tab inner join employee b on tab.Employeeid = b.Employeeid group by b.Employeeid,name where where tab.NoOfAttempts =0

where condition returns wrong result in the following scenario

where tab.NoOfAttempts =0

When i give the above condition the NoOfAttempts  column shows value 1. I am not able to find the customer those who are not yet  made any attempt. What's wrong in the script ?
0
 
LVL 11

Expert Comment

by:SANDY_SK
ID: 38825795
The above query will return only the employees who have logged in, you change the inner join to  a right join, it will include all the employees from the employee table, but then because of the group by, the count will always return 1 hence add a is null on the time it will work.

Use this...

select b.Employeeid , name ,max(datein) as lastAttempt,count(*) as NoOfAttempts from(
select distinct convert(varchar, Attemptdate, 105) datein,Employeeid from empIN
) as tab right join employee b on tab.Employeeid = b.Employeeid where datein is null
group by b.Employeeid,name 

Open in new window

0
 
LVL 35

Expert Comment

by:David Todd
ID: 38826085
Hi,

Can you post some sample data.

I fear that Sandy's latest attempt will also not give you what you are looking for 0 count( * ) will almost never ever return 0. That is, if the row doesn't exist - a necessary condition for count to be zero - then the row wont be in the result set - with the query the way it is written.

HTH
  David

PS My guess
select 
    b.Employeeid 
    , name 
    , max( Attemptdate ) as lastAttempt
    , count( Attemptdate ) as NoOfAttempts 
from(
    select 
        Attemptdate
        ,Employeeid 
    from empIN
) as tab 
right join employee b 
    on tab.Employeeid = b.Employeeid 
where 
    datein is null
group by 
    b.Employeeid
    ,name  
;

Open in new window

0
 
LVL 11

Expert Comment

by:SANDY_SK
ID: 38826614
Hi dtodd,

I am aware the count will always return a value >= 1, if i understand it correctly, his only intention is to find list of users who has not logged in, nothing to do with the count.

Pls correct me if i am wrong.
0
 

Author Comment

by:Varshini S
ID: 38830560
dtodd- You are correct, If the user does not have record in empIN table - it needs to show like
Employeeid    code         LastAttempt                               NoOfAttempts        
A1112                AA3          NULL                                                  0
A1111               AAA       2013-01-18 08:26:122.550                  2
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL: Wrong Result 7 38
Suppress if value zero or NULL in crystal report 2 41
Begin Transaction 12 25
Sql Query to return all values in Capital letters 2 8
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

734 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