Select from sql table MONTh sum

Goran Bibic

8y730SQL

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?

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
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 Bibic8y ago
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]";
Nitin Sontakke8y ago
OR you can use directly selected values in the where clause as (DEMO ONLY) shown below:

Int32 month = <-- assign selected value of the month drop-down to this variable.
Int32 year = <-- assign selected value of the year drop-down to this variable.

and....

where month([vrijemeprijave]) = ' + month.ToString() + ' and year([vrijemeprijave]) = ' + year.ToString()
Nitin Sontakke8y ago
Just, THINK, THINK AND THINK. And be logical and you will know what to do.
Nitin Sontakke8y ago
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 Bibic8y ago
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 Sontakke8y ago
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)