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.no | month | Total Present | Total Register |
1 | Jan-15 | 80 | 100 |
2 | Feb-15 | 85 | 100+10=110 |
3 | Mar-15 | 100 | 110 |
5 | Apr-15 | 105 | 110 |
6 | Jun-15 | 105 | 110 |
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 wainner 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...