• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

Consolidate sql query pull

What I ahve is a sql query that pull personnel attendance and puts it to a datagrid, the way it populates now alot of extra data the user does not need to see.  How can I consolidate the dates or list them with on one name.  Currently this is how the data pulls.

  strSSN        sidstrNAME_IND           dtAttendance            status
010101010          John Doe          2009-05-20 00:00:00.000      Tech
010101010          John Doe          2009-05-21 00:00:00.000      Tech
111111111         Sims Harry         2009-05-20 00:00:00.000      Tech
111111111         Sims Harry         2009-05-21 00:00:00.000      Tech
222222222        BORGY James         2009-12-22 00:00:00.000      Tech
222222222        BORGY James         2009-12-23 00:00:00.000      Tech
222222222         BORGY James        2009-12-24 00:00:00.000      Tech
222222222         BORGY James        2009-12-25 00:00:00.000      Tech
333333333         Dougy Houser       2009-03-27 00:00:00.000    TECH PERM
333333333         Dougy Houser       2009-03-28 00:00:00.000    TECH PERM
333333333         Dougy Houser       2009-03-29 00:00:00.000    TECH PERM
333333333         Dougy Houser       2009-03-30 00:00:00.000    TECH PERM

This is how I would like it to show in the datagrid or something Similar to it.
 strSSN        sidstrNAME_IND           dtAttendance            status
010101010          John Doe          2009-05-20 00:00:00.000      Tech
                                 2009-05-21 00:00:00.000      
111111111         Sims Harry         2009-05-20 00:00:00.000      Tech
                               2009-05-21 00:00:00.000    
222222222        BORGY James         2009-12-22 00:00:00.000      Tech
                             2009-12-23 00:00:00.000      
                                   2009-12-24 00:00:00.000      
                                2009-12-25 00:00:00.000      
333333333         Dougy Houser       2009-03-27 00:00:00.000    TECH PERM
                             2009-03-28 00:00:00.000    
                             2009-03-29 00:00:00.000    
                              2009-03-30 00:00:00.000    

select	distinct ap.strSSN, 
		s.sidstrNAME_IND, 
		s.sidstrGR_ABBR_CODE,
		ap.dtAttendance ,
		case when sidstrTech_SVC_Code in ('M', 'R', 'S', 'T', 'U', 'Z') then 
		case when sidstrACT_STAT_PROG in ('5', 'A', 'E', 'F', 'N', 'R', 'S', 'T') then 'AGR' else 'Tech' end 
		else 'M-Day' end as status 
from	tblAssignedPersonnel as ap INNER JOIN 
		tblSIDPERS s on ap.strSSN = s.sidstrSSN_SM INNER JOIN
where	s.sidstrTECh_SVC_CODE IN ('M', 'R', 'S', 'T', 'U', 'Z') AND

Open in new window

0
kdeutsch
Asked:
kdeutsch
  • 9
  • 8
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
why dont you handle this at the front end
0
 
kdeutschAuthor Commented:
aneeshattingal

Not sure what you mean, if you mean in the datagrid, I do  direct fill to it and what comes from sql query goes right into it.  Otherwise I bind my sql right to grid.

 myDataTable = New DataTable
        myDataTable = getData(sql)
       
        myDataGrid.DataSource = myDataTable
        myDataGrid.DataBind()
0
 
HainKurtSr. System AnalystCommented:
first sort by strSSN, dtAttendance desc. add this to your query

sort by strSSN, dtAttendance desc

after getting your data, put into a table

dim dt as datatable = GetData(...) ' you should have something similar to this

dim dt as datatable = GetData(...)

dim strSSN as string =""
for each r in dt.rows
  if (strSSN = r("strSSN")) then
    r("strSSN") = ""
    r("sidstrNAME_IND") = ""
    ...
  else
    strSSN = r("strSSN")
  end if
next

myGrid.datasource = dt
mygrid.DataBind()

Open in new window

0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
kdeutschAuthor Commented:
Ok I see what you are doing,
I put in my code like so, but I am getting a blue underline under row, I tried to Dim it but still not working, what am I doing wrong.

 myDataTable = New DataTable
        myDataTable = getData(sql)

        Dim strSSN As String = ""
        For Each row In myDataTable.Rows
            If (strSSN = r("strSSN")) Then
                r("strSSN") = ""
                r("sidstrNAME_IND") = ""
            Else
                strSSN = r("strSSN")
            End If
        Next


        myDataGrid.DataSource = myDataTable
        myDataGrid.DataBind()
0
 
HainKurtSr. System AnalystCommented:
fix to above: use "ORDER BY strSSN, dtAttendance desc" not sort by ;)

other option is to do this on db side...
add row_number() to query as

Q1 : select row_number() as r1, distinct ap.strSSN, ... ORDER BY strSSN, dtAttendance desc
Q2 : select row_number() as r2, distinct ap.strSSN, ... ORDER BY strSSN, dtAttendance desc

then use

select
(case when q1.strSSN=q2.strSSN then "" else q1.strSSN end) as strSSN,
... same for all other columns except dtAttendance,
q1.dtAttendance
from (Q1) as q1 left join (Q2) as q2 onq1.r1+1=q2.r2
order by ORDER BY q1.strSSN, q1.dtAttendance desc

and no code change ;)
 
0
 
HainKurtSr. System AnalystCommented:
kdeutsch, you posted the same thing ;) after 7 minutes...
0
 
kdeutschAuthor Commented:
Hi,
It tells me r is not accesswable because it is private.
0
 
HainKurtSr. System AnalystCommented:
you should use same variable

in your code, you should use row

For Each row In myDataTable.Rows
            If (strSSN = row("strSSN")) Then
                row("strSSN") = ""
                row("sidstrNAME_IND") = ""
            Else
                strSSN = row("strSSN")
            End If
        Next

Open in new window

0
 
HainKurtSr. System AnalystCommented:
use this too


Imports System.Data
...

        Dim myDataTable As DataTable
        For Each row As DataRow In myDataTable.Rows
...

Open in new window

0
 
kdeutschAuthor Commented:
Think Going to try other solution everything I try it keeps telling me

1. name row is not declared
2. row is not accessable in this context because it is private
0
 
HainKurtSr. System AnalystCommented:
full code...
        Dim strSSN As String = ""
        For Each row As DataRow In myDataTable.Rows
            If (strSSN = row("strSSN")) Then
                row("strSSN") = ""
                row("sidstrNAME_IND") = ""
            Else
                strSSN = row("strSSN")
            End If
        Next

Open in new window

0
 
HainKurtSr. System AnalystCommented:
no no no, lets finish this solution first ;)
0
 
kdeutschAuthor Commented:
Hi,
Tried the Imports, but that still gives me the name row is not declared underline.
0
 
kdeutschAuthor Commented:
Ok,
Got this to work, but now On my Grid side its telling me that the datasource dtAttendance does not exist.

 Dim MyDataTable As DataTable = getData(sql)
        Dim row As DataRow

        Dim strSSN As String = ""
        For Each row In myDataTable.Rows
            If (strSSN = row("strSSN")) Then
                row("strSSN") = ""
                row("sidstrNAME_IND") = ""
            Else
                strSSN = row("strSSN")
            End If
        Next
0
 
HainKurtSr. System AnalystCommented:
you dont need to use

Dim row As DataRow

since

For Each row As DataRow In myDataTable.Rows

should be ok... now dtAttendance issue... you may not have that record in your query, or some spelling error somewhere...
Dim MyDataTable As DataTable = getData(sql)

Dim strSSN As String = ""
For Each row As DataRow In myDataTable.Rows
  If (strSSN = row("strSSN")) Then
    row("strSSN") = ""
    row("sidstrNAME_IND") = ""
  Else
    strSSN = row("strSSN")
  End If
Next

myDataGrid.DataSource = myDataTable
myDataGrid.DataBind()

Open in new window

0
 
kdeutschAuthor Commented:
Hi,
I see either way works for calling out the datarow but yours is easier on the eyes.  I got it working.  I forgot that I Converted the date but did not call it dtAttendance agian.

 Dim MyDataTable As DataTable = getData(sql)

        Dim strSSN As String = ""
        For Each row As DataRow In MyDataTable.Rows
            If (strSSN = row("strSSN")) Then
                row("strSSN") = ""
                row("sidstrNAME_IND") = ""
                row("status") = ""
                row("sidstrGR_ABBR_CODE") = ""
            Else
                strSSN = row("strSSN")
            End If
        Next
0
 
HainKurtSr. System AnalystCommented:
so you are done ;)
0
 
kdeutschAuthor Commented:
Excellant learned something new and vital to alot of things I can do easier.  Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now