Select from SQL for workers work hours monthly when chose frome combo box
- string saveStaff = "SELECT [radnik] as Radnik,SUM(cast((CASE WHEN DATEPART(WEEKDAY, vrijemeprijave) in (1, 7) then 0 else (CASE WHEN(DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) > 8 THEN 8 ELSE(DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) END) end)as float)) AS radni_sati,"
- + "SUM(cast((CASE WHEN DATEPART(WEEKDAY, vrijemeodjave) in (1, 7) then(DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) else(case when(DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) > 8 then((DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) - 8) ELSE 0 END) end)as float)) AS 'Prekovremeni'"
- + "from[prijava_radnika]"
- + "where MONTH([vrijemeprijave]) = MONTH('" + mjeseccomboBox.Text.ToString() + "') "
- + "group by[radnik]";
- private void punjenje_comboboxa_mjesecgodina()
- {
- mjeseccomboBox.DataSource = CultureInfo.InvariantCulture.DateTimeFormat
- .MonthNames.Take(12).ToList();
- mjeseccomboBox.SelectedItem = CultureInfo.InvariantCulture.DateTimeFormat
- .MonthNames[DateTime.Now.AddMonths(-1).Month - 1];
- godinacomboBox.DataSource = Enumerable.Range(1983, DateTime.Now.Year - 1983 + 1).ToList();
- godinacomboBox.SelectedItem = DateTime.Now.Year;
- }
Bryian TanPosted May 20, 2018, 9:36 PM
Here a quick work around. Assuming, mjeseccomboBox.Text returning (January, February, March, ...). Change this line MONTH([vrijemeprijave]) to DATENAME(month, [vrijemeprijave])