troubleshooting Question

SQL count function

Avatar of kdeutsch
kdeutschFlag for United States of America asked on
SQL
12 Comments1 Solution465 ViewsLast Modified:
My current sql syntax works fine, but what I need to trhough in it is the next filed called dtAttendance.  One person can have many attendance days.  So what I need my sql to do is count the dt attendance for selected individual and then every day of attendance of hat individual.  Following is my current code.  Right now the query only returns the last date of attendance, not all of them and then I need it to count as a seperate column.  Thanks

Private Sub ddlPerNames_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlPerNames.SelectedIndexChanged
        Dim Pername As String = ddlPerNames.SelectedValue

        sql = "select  s.sidstrSSN_SM, s.sidstrGR_ABBR_CODE + '/' + s.sidstrPAY_GR as Rank, case when s.sidstrTECH_SVC_CODE in ('M', 'R', 'S', 'T', 'U', 'Z') then " _
                & "case when s.sidstrACT_STAT_PROG in ('5', 'A', 'E', 'F', 'N', 'R', 'S', 'T') then 'AGR' else 'TECH' end else 'M-day' end, " _
                & "s.sidstrST_ADDR + ',' + s.sidstrADDR_CITY + ',' + s.sidstrSTATES_US + ',' + s.sidstrZIP_CODE as address, p.dtattendance from " _
                & "cms.dbo.tblSIDPERS as s INNER JOIN saddotnet.dbo.tblAssignedPersonnel as p on p.strSSN = s.sidstrSSN_SM where " _
                & "sidstrNAME_Ind like '" & Pername & "'"


        myDataTable = New DataTable
        myDataTable = getData(sql)

        lblName.Text = ddlPerNames.SelectedValue
        ddlPerNames.Visible = False

        lblSSN.Text = myDataTable.Rows(0)(0)
        lblRank.Text = myDataTable.Rows(0)(1)
        lblStatus.Text = myDataTable.Rows(0)(2)
        lblHOR.Text = myDataTable.Rows(0)(3)
        LblDutyDays.Text = myDataTable.Rows(0)(4)

    End Sub
ASKER CERTIFIED SOLUTION
Mark Wills
Topic Advisor
Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros