Select from sql table MONTh sum

Question

My code working fine for all table records....i need to specified record for mont from combobox

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]
group by [radnik]

Some help?

Nitin Sontakke · Answer

OR you can use directly selected values in the where clause as (DEMO ONLY) shown below: Int32 month = Int32 year = and.... where month([vrijemeprijave]) = ' + month.ToString() + ' and year([vrijemeprijave]) = ' + year.ToString()

Nitin Sontakke · Answer

Just, THINK, THINK AND THINK. And be logical and you will know what to do.

Nitin Sontakke · Answer

First, it doesn't work like that. Second, and (NEVER EVER FORGET THIS!) never use UI controls values directly in your sql query. PLEASE first copy it somewhere (that is, in some variable). ALWAYS REMEMBER THIS. You have separate month drop-down list and a separate year drop-down list. You will have to take year (selected value from drop down list), month (selected value from drop-down list) and 1 as a fixed value for the day and create a datetime variable first from this information. Then use that datetime variable in your WHERE clause.

Goran Bibic · Answer

I am try this, and work.. string saveStaff = " declare @filterDate date; 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(GETDATE()) " + "group by[radnik]" ; .I need just to conect with comobox in line 4 "where MONTH([vrijemeprijave]) = MONTH('" + mjeseccomboBox.Text.ToString() + "') " "where MONTH([vrijemeprijave]) = MONTH('" + mjeseccomboBox.Text.ToString() + "') " Combobox fill 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; } I have some mistake...

Nitin Sontakke · Answer

Please look into the datepart sql server function. I don't think you will need just a month, to be technically correct, you would also need year. https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017 When you have a date successfully passed on to the sql server procedure, you can apply the where clause as usual. For example, declare @filterDate date select * from [prijava_radnika] where datepart( month , vrijemeprijave) = datepart( month , @filterDate) and datepart( year , vrijemeprijave) = datepart( year , @filterDate)

Nitin Sontakke · Answer

Of course it is used in the query. Can you not see it? But I told you to initialize those variables, you didn't do it. Please my response carefully!

Goran Bibic · Answer

I am try this Int32 month; Int32 year; And this value not used aniwhere string saveStaff = " declare @filterDate date; 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.ToString() + ' and year([vrijemeprijave]) = ' + year.ToString()" + "group by[radnik]" ;