Venkat Govind

Venkat Govind

  • 1.2k
  • 568
  • 67.6k

how to calculate monthly total present and current month Tot

Jun 3 2015 3:31 AM

I have three Table 1.Table is Vendor_tbl,2.Employee_tbl,3.Attendance_tbl

table 1(vendor Table) Structure

UniqueId]
,[VendorCode]
,[VendorName],[VStatus]
,[Createdate]
,[Createdby]
,[Updatedate]
,[Updatedby]
FROM [dbo].[mstr_vendor]

======================================================================================

table 2(Employee_tbl)structure

[UniqueId]
,[WorkforceId]
,[BarcodeId]
,[VendorCode]
,[FirstName]
,[LastName]
,[Mobile]

,[WFStatus]
,[DeactivateOn]
,[Createdate]
,[Createdby]
,[Updatedate]
,[Updatedby]

FROM [WMS].[dbo].[mstr_workforce]

table 3 Structure

[UniqueID]
,[WorkforceID]
,[AttendanceDate]
,[InTime]
,[OutTime]
,[CreateDate]
,[CreatedBy]
,[UpdateDate]
,[UpdatedBy]
FROM [dbo].[Attendance_tbl]


i want to monthly wise vendor performance

my where condition is between createddate '01/01/2015' and '01/06/2015' Vendor Code='0001'

i want show below like how get the details from above

S.nomonthTotal PresentTotal Register
1Jan-1580100
2Feb-1585100+10=110
3Mar-15100110
5Apr-15105110
6Jun-15105110


Note:jan 2015 the vendor register 100 employees jan total reg is 100, feb month added extra 10 employees total feb month is 110 this total need continue with upcoming month


Please provide Solution I try different way but i am not get exact ans,below querys is i am using

select CONVERT(CHAR(4), AttendanceDate, 100) AS MonthYear,
COUNT(AttendanceDate),COUNT(CONVERT(CHAR(4), wf.Createdate, 100)) as Total from workforce_attendance as wa
inner join mstr_workforce as wf on wf.WorkforceId=wa.WorkforceID where wf.VendorCode='LTSBV000002' and DATEADD(D, 0, DATEDIFF(D, 0,wf.Createdate)) Between '01/01/2015' and '06/03/2015'
group by wf.VendorCode, CONVERT(CHAR(4), AttendanceDate, 100)



Thanking You,

VEnkaT...