I Need to Discard Days of Month and Make Duplicate Months as Distinct

Question

SELECT  e.firstName,t.setDate, w.wageAmount
         FROM TimeSheet t
         INNER JOIN Employee e
         ON e.employeeID = t.employeeID
         INNER JOIN Salary s
         ON s.salaryID = t.salaryID
         INNER JOIN Wage w
         ON w.wageID = s.wageID
         INNER JOIN EmpHiringInfo ehf
         ON ehf.EmpHiringInfoID = s.EmpHiringInfoID
         INNER JOIN WageType wt
         ON wt.wageTypeID = w.wageTypeID
         WHERE (wt.wageTypeID = 19) AND (ehf.isActive = 1 AND s.isActive = 1
         AND ehf.employeeID = 300) AND (CONVERT(varchar, t.setDate, 23) BETWEEN '2000-08-02' AND '2020-08-04')

By the way, the wage is only one for every month, so the wage is repetitive. So for the month 11,2010 wage is 3600 regardless how many days in 11,2010 ...

The above sample produce the following sample code:

I need to gather all similar month/year into one cell discarding days and time. For example 2010-11-12 and 2010-11-26 has to be contracted to one cell as 2010-11 (in any format).

Any help is appreciated.

Answer 1

You can try something like the following:

SELECT  e.firstName
,CONVERT(CHAR(7),t.setDate,102)
,SUM(w.wageAmount)
FROM ...

GROUP BY e.firstName, 
,CONVERT(CHAR(7),t.setDate,102)

Answer 2

To Unify all the dates to months you can do something like this:

dateadd(mm,datediff(mm,0,t.setDate),0)

But you will need to group by this is you want to do aggregations:

select e.firstName,dateadd(mm,datediff(mm,0,t.setDate),0), sum(w.wageAmount)
from ...
group by e.firstName, dateadd(mm,datediff(mm,0,t.setDate),0)

Answer 3

There is a DatePart function in T-SQL that is able to retrieve the date and the year of a specific date.

Your query could look something like this :

SELECT  e.firstName, 
DATEPART(yy, t.setDate) AS Year, 
DATEPART(mm, t.setDate) AS Month, 
MAX(w.wageAmount) AS wageAmount
FROM TimeSheet t
     INNER JOIN Employee e
     ON e.employeeID = t.employeeID
     INNER JOIN Salary s
     ON s.salaryID = t.salaryID
     INNER JOIN Wage w
     ON w.wageID = s.wageID
     INNER JOIN EmpHiringInfo ehf
     ON ehf.EmpHiringInfoID = s.EmpHiringInfoID
     INNER JOIN WageType wt
     ON wt.wageTypeID = w.wageTypeID
     WHERE (wt.wageTypeID = 19) AND (ehf.isActive = 1 AND s.isActive = 1
     AND ehf.employeeID = 300) AND 
     (CONVERT(varchar, t.setDate, 23) BETWEEN '2000-08-02' AND '2020-08-04')
GROUP BY e.firstName, DATEPART(yy, t.setDate), DATEPART(mm, t.setDate)