kdeutsch
asked on
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
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
why dont you handle this at the front end
ASKER
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()
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()
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
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()
ASKER
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()
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()
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 ;)
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 ;)
kdeutsch, you posted the same thing ;) after 7 minutes...
ASKER
Hi,
It tells me r is not accesswable because it is private.
It tells me r is not accesswable because it is private.
you should use same variable
in your code, you should use row
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
use this too
Imports System.Data
...
Dim myDataTable As DataTable
For Each row As DataRow In myDataTable.Rows
...
ASKER
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
1. name row is not declared
2. row is not accessable in this context because it is private
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
no no no, lets finish this solution first ;)
ASKER
Hi,
Tried the Imports, but that still gives me the name row is not declared underline.
Tried the Imports, but that still gives me the name row is not declared underline.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
so you are done ;)
ASKER
Excellant learned something new and vital to alot of things I can do easier. Thanks