-2

Employees daily late minutes stored in one table(Attndate) and I want to calculate sum of Individual employees late mins then insert into another table(LateMuster) using sql server stored procedure

And Important I want to get late mins between 2 dates like '2017-09-01' and '2017-09-08'

Attndate - Table

enter image description here

Expected Output - LateMuster-Table

LateMuster - Table

finally I will use this table to crystal report(using vb.net)

Note :- I am Using sql server 2005, and visual studio 2005

  • If you only want late minutes between 2017-09-01 and 2017-09-08, why does your expected output have 31 days of columns? – Scott Hodgin Sep 8 '17 at 12:15
  • @ScottHodgin '2017-09-01' and '2017-09-08' is for example, 31 means 31 days(1 month) – Relax Sep 8 '17 at 12:17
  • So, your destination table has 31 column days, but you only want your insert query to insert days 1 through 8 since you are only interested in late minutes between 2017-09-01 and 2017-09-08? I'm assuming your destination table has your individual 'days' columns defined to allow nulls? – Scott Hodgin Sep 8 '17 at 12:20
  • Yes, it can be allow nulls, Today 08-Sep-2017 so i need only from '2017-09-01' and '2017-09-08', if no values on 02-sep-2017 can store null values – Relax Sep 8 '17 at 12:27
  • And what do you want to happen tomorrow or the next day? Now, it seems there is 'current-date' logic involved and not just two hard-coded dates. – Scott Hodgin Sep 8 '17 at 12:29
0

Assumptions:

  • the question is tagged with sql-server-2005 and according to the MSSQL documentation, CTEs and PIVOT tables are not supported until sql-server-2008
  • the question is also tagged with stored-procedures so I'm guessing some procedural coding is acceptable (ie, this doesn't have to be a single query)

For this answer we'll look at populating a #temp table and then dynamically build a query to 'pivot' the data into the 31 columns/days ...

I took the liberty of adding some data for August so we'd have a wrap-around on the month, plus a couple double entries for day 1 of the month (Aug 01, Sep 01):

drop table if exists Attndate;

create table Attndate
(EMPCODE     int
,EMPNAME     varchar(30)
,[DATE]      date
,InTime      time
,LateMins    int);

insert into AttnDate (EMPCODE, EMPNAME,[DATE],InTime,LateMins) values
(1,'John' ,'08/01/2017','9:17', 7),
(2,'David','08/01/2017','9:18', 8),
(3,'Kumar','08/01/2017','9:12', 2),
(4,'Jack' ,'08/01/2017','9:18', 8),

(1,'John' ,'08/26/2017','9:27',17),
(2,'David','08/26/2017','9:20',10),
(3,'Kumar','08/26/2017','9:17', 7),
(4,'Jack' ,'08/26/2017','9:13', 3),

(5,'Rose' ,'08/28/2017','9:31',21),

(2,'David','08/29/2017','9:28',18),

(3,'Kumar','08/30/2017','9:23',23),

(4,'Jack' ,'08/31/2017','9:59',49),

(1,'John' ,'09/01/2017','9:30',20),
(2,'David','09/01/2017','9:25',15),
(3,'Kumar','09/01/2017','9:12', 2),
(4,'Jack' ,'09/01/2017','9:15', 5),

(5,'Rose' ,'09/02/2017','9:36',26),
(2,'David','09/02/2017','9:18', 8),
(3,'Kumar','09/02/2017','9:13', 3),
(4,'Jack' ,'09/02/2017','9:51',41),

(1,'John' ,'09/03/2017','9:30', 4),
(3,'Kumar','09/03/2017','9:30',13);

We'll populate our #rollup table with [DATE] converted to day of month plus LateMins summed up by day of month:

drop table if exists #rollup;

select  EMPCODE,
        EMPNAME,
        day([DATE]) as dom,
        sum(LateMins) as lm
into    #rollup
from    Attndate
where   [Date] between '2017-08-01' and '2017-09-30'
group by EMPCODE,
         EMPNAME,
         day([DATE])

Now we use a looping construct to build our 'pivot' query:

declare  @query varchar(max),
         @ctr   int

select @ctr=1,
       @query = 'select distinct r1.EMPCODE, r1.EMPNAME'

while @ctr < 32
begin
    select @query = @query + char(10) + ', (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=' + convert(varchar(2),@ctr) + ') as [' + convert(varchar(2),@ctr) + ']'
    select @ctr   = @ctr + 1
end

set @query = @query + char(10) + ', (select sum(lm) from #rollup r2 where r2.EMPCODE=r1.EMPCODE) as [Total Late Mins]' + char(10) +' from  #rollup r1 order by 1,2'

print @query

execute (@query)

The print @query displays our 'pivot' query as:

select distinct r1.EMPCODE, r1.EMPNAME
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=1) as [1]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=2) as [2]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=3) as [3]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=4) as [4]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=5) as [5]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=6) as [6]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=7) as [7]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=8) as [8]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=9) as [9]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=10) as [10]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=11) as [11]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=12) as [12]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=13) as [13]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=14) as [14]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=15) as [15]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=16) as [16]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=17) as [17]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=18) as [18]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=19) as [19]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=20) as [20]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=21) as [21]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=22) as [22]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=23) as [23]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=24) as [24]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=25) as [25]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=26) as [26]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=27) as [27]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=28) as [28]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=29) as [29]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=30) as [30]
, (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=31) as [31]
, (select sum(lm) from #rollup r2 where r2.EMPCODE=r1.EMPCODE) as [Total Late Mins]
 from  #rollup r1 order by 1,2

And the execute (@query) statement generates our 'pivot' table:

EMPCODE | EMPNAME |    1 |    2 |    3 |    4 |    5 |    6 |    7 |    8 |    9 |   10 |   11 |   12 |   13 |   14 |   15 |   16 |   17 |   18 |   19 |   20 |   21 |   22 |   23 |   24 |   25 |   26 |   27 |   28 |   29 |   30 |   31 | Total Late Mins
------- | ------- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---------------
      1 | John    |   27 | null |    4 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null |   17 | null | null | null | null | null |              48
      2 | David   |   23 |    8 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null |   10 | null | null |   18 | null | null |              59
      3 | Kumar   |    4 |    3 |   13 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null |    7 | null | null | null |   23 | null |              50
      4 | Jack    |   13 |   41 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null |    3 | null | null | null | null |   49 |             106
      5 | Rose    | null |   26 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null |   21 | null | null | null |              47

Here's a fiddle for this answer.

| improve this answer | |
0

Setting aside potential limitations of sql-server-2005, here's a solution that dynamically builds a 'pivot' query anchored by a CTE.

I took the liberty of adding some data for August so we'd have a wrap-around on the month, plus a couple double entries for day 1 of the month (Aug 01, Sep 01):

drop table if exists Attndate;

create table Attndate
(EMPCODE     int
,EMPNAME     varchar(30)
,[DATE]      date
,InTime      time
,LateMins    int);

insert into AttnDate (EMPCODE, EMPNAME,[DATE],InTime,LateMins) values
(1,'John' ,'08/01/2017','9:17', 7),
(2,'David','08/01/2017','9:18', 8),
(3,'Kumar','08/01/2017','9:12', 2),
(4,'Jack' ,'08/01/2017','9:18', 8),

(1,'John' ,'08/26/2017','9:27',17),
(2,'David','08/26/2017','9:20',10),
(3,'Kumar','08/26/2017','9:17', 7),
(4,'Jack' ,'08/26/2017','9:13', 3),

(5,'Rose' ,'08/28/2017','9:31',21),

(2,'David','08/29/2017','9:28',18),

(3,'Kumar','08/30/2017','9:23',23),

(4,'Jack' ,'08/31/2017','9:59',49),


(1,'John' ,'09/01/2017','9:30',20),
(2,'David','09/01/2017','9:25',15),
(3,'Kumar','09/01/2017','9:12', 2),
(4,'Jack' ,'09/01/2017','9:15', 5),

(5,'Rose' ,'09/02/2017','9:36',26),
(2,'David','09/02/2017','9:18', 8),
(3,'Kumar','09/02/2017','9:13', 3),
(4,'Jack' ,'09/02/2017','9:51',41),

(1,'John' ,'09/03/2017','9:30', 4),
(3,'Kumar','09/03/2017','9:30',13);

Dynamically build our 'pivot' query with the assistance of a CTE:

declare  @query varchar(max),
         @ctr   int

select @ctr=1,
       @query = 
'with rollup as
(select  EMPCODE,
         EMPNAME,
         day([DATE]) as dom,
         sum(LateMins) as lm
 from    Attndate
 where   [Date] between ''2017-08-01'' and ''2017-09-30''
 group by EMPCODE,
          EMPNAME,
          day([DATE]))
select distinct r1.EMPCODE, r1.EMPNAME'

while @ctr < 32
begin
    select @query = @query + char(10) + ', (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=' + convert(varchar(2),@ctr) + ') as [' + convert(varchar(2),@ctr) + ']'
    select @ctr   = @ctr + 1
end

select @query = @query + char(10) + ', (select sum(lm) from rollup r2 where r2.EMPCODE=r1.EMPCODE) as [Total Late Mins]' + char(10) +' from  rollup r1 order by 1,2'

print @query

execute (@query)
  • the CTE (rollup) converts [DATE]'s into days of the month (dom), as well as sums up LateMins (lm)
  • the sub-query (rollup r2) gives us the final column of our result set [Total Late Mins]

The print @query displays our 'pivot' query as::

with rollup as
(select  EMPCODE,
         EMPNAME,
         day([DATE]) as dom,
         sum(LateMins) as lm
 from    Attndate
 where   [Date] between '2017-08-01' and '2017-09-30'
 group by EMPCODE,
          EMPNAME,
          day([DATE]))
select distinct r1.EMPCODE, r1.EMPNAME
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=1) as [1]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=2) as [2]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=3) as [3]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=4) as [4]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=5) as [5]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=6) as [6]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=7) as [7]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=8) as [8]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=9) as [9]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=10) as [10]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=11) as [11]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=12) as [12]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=13) as [13]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=14) as [14]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=15) as [15]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=16) as [16]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=17) as [17]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=18) as [18]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=19) as [19]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=20) as [20]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=21) as [21]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=22) as [22]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=23) as [23]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=24) as [24]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=25) as [25]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=26) as [26]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=27) as [27]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=28) as [28]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=29) as [29]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=30) as [30]
, (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=31) as [31]
, (select sum(lm) from rollup r2 where r2.EMPCODE=r1.EMPCODE) as [Total Late Mins]
 from  rollup r1 order by 1,2

And the execute (@query) statement generates our 'pivot' table:

EMPCODE | EMPNAME |    1 |    2 |    3 |    4 |    5 |    6 |    7 |    8 |    9 |   10 |   11 |   12 |   13 |   14 |   15 |   16 |   17 |   18 |   19 |   20 |   21 |   22 |   23 |   24 |   25 |   26 |   27 |   28 |   29 |   30 |   31 | Total Late Mins
------- | ------- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---------------
      1 | John    |   27 | null |    4 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null |   17 | null | null | null | null | null |              48
      2 | David   |   23 |    8 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null |   10 | null | null |   18 | null | null |              59
      3 | Kumar   |    4 |    3 |   13 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null |    7 | null | null | null |   23 | null |              50
      4 | Jack    |   13 |   41 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null |    3 | null | null | null | null |   49 |             106
      5 | Rose    | null |   26 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null |   21 | null | null | null |              47

Here's a fiddle for this answer.

| improve this answer | |
0

Setting aside potential limitations of sql-server-2005, here's a solution that uses a CTE and a PIVOT statement.

I took the liberty of adding some data for August so we'd have a wrap-around on the month, plus a couple double entries for day 1 of the month (Aug 01, Sep 01):

drop table if exists Attndate;

create table Attndate
(EMPCODE     int
,EMPNAME     varchar(30)
,[DATE]      date
,InTime      time
,LateMins    int);

insert into AttnDate (EMPCODE, EMPNAME,[DATE],InTime,LateMins) values
(1,'John' ,'08/01/2017','9:17', 7),
(2,'David','08/01/2017','9:18', 8),
(3,'Kumar','08/01/2017','9:12', 2),
(4,'Jack' ,'08/01/2017','9:18', 8),

(1,'John' ,'08/26/2017','9:27',17),
(2,'David','08/26/2017','9:20',10),
(3,'Kumar','08/26/2017','9:17', 7),
(4,'Jack' ,'08/26/2017','9:13', 3),

(5,'Rose' ,'08/28/2017','9:31',21),

(2,'David','08/29/2017','9:28',18),

(3,'Kumar','08/30/2017','9:23',23),

(4,'Jack' ,'08/31/2017','9:59',49),


(1,'John' ,'09/01/2017','9:30',20),
(2,'David','09/01/2017','9:25',15),
(3,'Kumar','09/01/2017','9:12', 2),
(4,'Jack' ,'09/01/2017','9:15', 5),

(5,'Rose' ,'09/02/2017','9:36',26),
(2,'David','09/02/2017','9:18', 8),
(3,'Kumar','09/02/2017','9:13', 3),
(4,'Jack' ,'09/02/2017','9:51',41),

(1,'John' ,'09/03/2017','9:30', 4),
(3,'Kumar','09/03/2017','9:30',13);

The CTE/PIVOT query:

with rollup as
(select  EMPCODE,
         EMPNAME,
         day([DATE]) as dom,
         sum(LateMins) as lm
 from    Attndate
 where   [Date] between '2017-08-01' and '2017-09-30'
 group by EMPCODE,
          EMPNAME,
          day([DATE]))

select EMPCODE,
       EMPNAME,
       [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31], 
       (select sum(r2.lm) from rollup r2 where r2.EMPCODE=pt.EMPCODE) as [Total Late Mins]
from   (select EMPCODE, EMPNAME,lm,dom from rollup r1) as dt
        pivot
        (sum(lm) for dom in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
       ) as pt
order by 1,2
  • the CTE (rollup) converts [DATE]'s into days of the month (dom), as well as sums up LateMins (lm)
  • for the PIVOT clause we'll issue a sum(lm); this doesn't change our results but allows us to meet the PIVOT's requirement for an aggregate function
  • the sub-query (rollup r2) gives us the final column of our result set [Total Late Mins]

And the results of the query:

EMPCODE | EMPNAME |    1 |    2 |    3 |    4 |    5 |    6 |    7 |    8 |    9 |   10 |   11 |   12 |   13 |   14 |   15 |   16 |   17 |   18 |   19 |   20 |   21 |   22 |   23 |   24 |   25 |   26 |   27 |   28 |   29 |   30 |   31 | Total Late Mins
------- | ------- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---------------
      1 | John    |   27 | null |    4 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null |   17 | null | null | null | null | null |              48
      2 | David   |   23 |    8 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null |   10 | null | null |   18 | null | null |              59
      3 | Kumar   |    4 |    3 |   13 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null |    7 | null | null | null |   23 | null |              50
      4 | Jack    |   13 |   41 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null |    3 | null | null | null | null |   49 |             106
      5 | Rose    | null |   26 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null |   21 | null | null | null |              47

Here's a fiddle for this answer.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.