Getting Started with Adobe After Effects - Part 6: Motion Blur
Ask
Ask questions, discuss or help others by answering
Related Posts · View All
SQL Server 146
TSQL 76
SSRS 71
SSIS 68
XML 57

Top Categories · View All
SQL Server 146
TSQL 76
SSRS 71
SSIS 68
XML 57

sql group

Jan 24 2013 12:00AM by anuvarshini   

i have employee table

Emplyeeid   name

A1111          AAA

A1112         AA3

 

and   i have empIN  table

Emplyeeid         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 )

Emplyeeid       code         LastAttempt                               NoOfAttempts       

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

A1112            AA3        2013-01-21 10:42:47.721                3

 

Submitted under: Microsoft SQL Server · TSQL ·  ·  · 


anuvarshini
354 · 0% · 117

2 Replies

  • select e.Employeeid ,min(e.name) ,max(ei.Attemptdate) ,count(distinct datediff(d,0,ei.Attemptdate)) from Employee e inner join EmpIN ei on e.Employeeid = ei.Employeeid group by e.Employeeid

    commented on Jan 26 2013 9:23AM
    Leszek Gniadkowski
    8 · 18% · 5729
  • .

    SELECT EmpIN.Emplyeeid, Name,
    MAX(Attemptdate)LastAttempt
    ,(SELECT COUNT(DISTINCT CONVERT(VARCHAR,Attemptdate,110) ) FROM EmpIN N WHERE N.Emplyeeid=EmpIN.Emplyeeid
    GROUP BY Emplyeeid  )NoOfAttempts         
     FROM EmpIN 
    JOIN Employee ON Employee.Emplyeeid=EmpIN.EmplyeeID
    Group BY EmpIN.Emplyeeid,Name
    
    commented on Feb 6 2013 11:26PM
    jasmyjs
    229 · 1% · 199

Your Reply


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]