SQL Statement

Question

Hi,


i have a database of attendence for employees and it contian table which called "NEW" and it has Date , Time , Badgeholder fields

what i need is to count the people that attended everyday starting from the begining of the month till it's end, i need the result to be names and number of attendence

     Names     Totals
Ex.     x          20

         y           15

I trieds this SQL but it did not worked : cn1.Execute "Create View Car as SELECT badgeholder,Count(badgeholder)from new where time between '" + x + "'and'" + y + "' group by badgeholder"

those x and y is datetimepicker

Answer 1

SELECT BatchHolder, COUNT(BatchHolder) AS Totals FROM NEW 
WHERE DATE BETWEEN '11/01/2009' AND '11/30/2009'
GROUP BY BatchHolder

---

Gaurav Khanna

Answer 2

I suspect your assignment is looking for only those people who attended every day between the given range, not just if they attended.  If this is true then the sql by Khanna may not work for you.

There are a few things that shoudl be mentioned:

1)  This is squarely homework, and most answerers here strongly frown upon people asking for others to do their homework.  We are more than happy to assist with specific issues that you are getting hung up on, but not just provide an answer.

2)  This is a SQL question, not VB.NET, so you are in the wrong forum.  Here is the T-SQL forum's link:
http://social.msdn.microsoft.com/Forums/en-US/transactsql/threads

3)  You really need to avoid using table and column names that are or sound like keywords or datatypes.  This is a very bad habit that will come back to haunt you later.  So, don't name a column "Date" or "Time" because these are data types (at least in SQL2008).  You should use names that better describe what its data's purpose is...  something like "AttendanceDate" or "AttendanceTime".  While your table name "New" is not a keyword in sql (yet) it is just a terrible name for a table.  New what?  What if there were a hundred tables in this database relating to attendance tracking - what would the table "New" mean to someone seeing the DB for the first time?  If you can;t answer that question effectively then you should rename the table.

If you think I'm being ____ about this wait unitl you post it in the T-SQL forum :)  Consider changing the names before posting in that forum.

Good Luck!

Answer 3

Hi Maged,

it's not all too clear exactly what database-system you are running your query against, but this is important due to the fact that you need to process date-values which may have to be in a special format in order for the DB-sys to process them adequately.
Also, if you're using SQL-Server, you're actually not retrieving a set of values. Instead, you're attempting to create a new View-object - this is probably not what you want (and it wouldn't work if you were using i.e. Access). Also, using keywords such as Date and Time for fields in your table is a very bad idea; that said, I'd stay away from naming something NEW too, even though this isn't one of the reserved words in SQL Server.

Back to what you want and sticking with VB (as opposed to using a SQLS-view or StoredProcedure), you'll probably need to extract the beginning end end of the month that has been picked (not certain that you need this due to the mentioned DatePickers, however, it might help nontheless). Here's what you could do to extract the first and last day of a certain month (represented as dteYourDate; this could be replaced with YourDatePicker.Value):

      Dim dteYourDate As Date = Date.Today
      Dim dteBeginningOfMonth As Date = _
         dteYourDate.AddDays(dteYourDate.Day * -1 + 1)
      Dim dteEndOfMonth As Date = _
         dteBeginningOfMonth.AddMonths(1).AddDays(-1)

Now that you have the dates you need, you can build your SQL accordingly, passing the dates as SQL Server needs them (note that there is many formats that SQLS will accept, i.e. depending on the server's collation; I have found this one worked for all the collations I have come across) and process your data.

      Using cn As New System.Data.SqlClient.SqlConnection(strYourConnectionStringGoesHere)
         Dim strSQL As String = _
            "SELECT T.Badgeholder AS Names, Count(T.Badgeholder) AS Totals " & _
            "FROM dbo.NEW AS T " & _
            "WHERE [Date] BETWEEN " & _
               dteBeginningOfMonth.ToString("\'yyyyMMdd\'") & _
               " AND " & _
               dteEndOfMonth.ToString("\'yyyyMMdd\'") & " " & _
            "GROUP BY T.Badgeholder"
         Using cmd As New System.Data.SqlClient.SqlCommand(strSQL, cn)
            Using da As New System.Data.SqlClient.SqlDataAdapter(cmd)
               Dim dt As System.Data.DataTable
               da.Fill(dt)
               'Perform tasks on your data ...
            End Using
         End Using
      End Using

The sample code above places the retrieved data in a new DataTable. Of course, this could as well be a DataSet or an SqlDataReader.

Cheers,
Olaf

Answer 4

Does any body know if my computer is wrong, I see stated Visual Basic General forum

Or is everybody who have answered this question abusing the Microsoft  forums by violating the simple forum rules?

There is not anything in this question about VB (or even Net)

I have a question about cars, can that done also here?

---

Success
Cor

Answer 5

Hi Cor,

did you have a bad night?

---

Cheers,
Olaf

Answer 6

Hi Cor,

did you have a bad night?

Olaf

Night life is also no topic from this forum

---

Success
Cor

Answer 7

He's got you there Olaf  ;)

Seriously though - if Olaf's suggestion did not help, you should move the thread to the T-SQL forum where you'll get the support of a very large community dedicated solely to the subject at hand.

Answer 8

Yep, that sure was a good one, got me ROTFL. :-)

---

Cheers,
Olaf