CODEWITHSUNDEEP
c#javascriptjquerysql
user3195896
user3195896

Reputation:

count the number of rows between two dates

In my database i have a column name called IsStaff which has a return value as a bit. So staff in a company can have a illness(1) or that staff had no illness(0). How would i write a sql query that can count all the numbers of 1's and 0's between a specific date's and represent it in a jquery table. This is what i have done:

 public List<Staff> Method(string Date1, string Date2)
    {
        DateTime d = Convert.ToDateTime(Date1);
        string date1 = d.ToLongDateString();

        DateTime dd = Convert.ToDateTime(Date2);
        string date2 = dd.ToLongDateString();

        List<Staff> LBD = new List<Staff>();
        SqlConnection conn = new SqlConnection etc...
        SqlCommand command = new SqlCommand(@"SELECT * From TableName 
                                             WHERE Cast([Time] AS DATE) > @Time 
                                                 AND CAST([Time] AS DATE) < @Time2 
                                             ORDER BY Time Desc", conn);
        command.Parameters.AddWithValue("@Time", date1);
        command.Parameters.AddWithValue("@Time2", date2);

        conn.Open();
        SqlDatadata data = command.Executedata();
        while (data.Read())
        {
            Staff l = new Staff();
            l.IsStaff = data["IsStaff"].ToString();
            l.Name = data["Name"].ToString();
            ........
            LBD.Add(l);
        }
        conn.Close();
        return LBD;
    }

i can successfully get the data between two dates but how do i get total number of time a specific staff is been ill?

function Table(data) {
        var table = '<table><tr><th>Name</th><th>Sum of ill staff</th><th>sum of none ill staff</th>';
        var rowID = 0;
        for (var staff in data) {

            var row = '<tr class=\'staff-row\'id=\'' + data[student].StaffID + '\'</tr>';
            row += '<td>' + data[staff].Name+ '</td>';
            row += '<td>' + data[staff].IsStaff + '</td>';
            row += '<td>' + data[staff].IsStaff + '</td>'
            rowID++;
            table += row;
        }

        table += '</table>';
        $('#displayTable').html(table);

    }

This is my dynamic generated table, first column is 'Name' which displays all the staff, second column is 'Sum of ill staff' that should display a staff who been ill for a specific date and final column is 'sum of none ill staff' that should display a staff who been not ill Q1 - what would be my sql query for counting a number of staff? Q2 - how do i add all 1's up and display it on my table?

Upvotes: 0

Views: 1058

Answers (2)

mnshahab
mnshahab

Reputation: 780

To count all the numbers of 1's and 0's between a specific date's use:

    SELECT StaffID, count(IsStaff) From TableName                          
    WHERE Cast([Time] AS DATE) > @Time    
      AND CAST([Time] AS DATE) < @Time2 
    GROUP BY StaffID 
    ORDER BY count(IsStaff) desc

To get total number of time a specific staff is been ill, use:

    SELECT StaffID, sum(IsStaff) From TableName                          
    WHERE Cast([Time] AS DATE) > @Time    
      AND CAST([Time] AS DATE) < @Time2 
    GROUP BY StaffID
    ORDER BY sum(IsStaff) desc

Upvotes: 0

Nizam
Nizam

Reputation: 4699

Why don´t you compute the values in the SQL?

        SqlCommand command = new SqlCommand(@"SELECT StaffID, Name, sum(IsStaff), 
                                              sum(case when IsStaff = 1 then 0 else 1 end) 
                                               From TableName 
                                             WHERE Cast([Time] AS DATE) > @Time 
                                                 AND CAST([Time] AS DATE) < @Time2 
                                             GROUP BY StaffID, Name
                                             ORDER BY Time Desc", conn);

or use Linq to get the values computed from the list of Staff.

Upvotes: 1

Related Questions