 |
| |
วนลูป DISTINCT ให้เทียบเฉพาะค่า Year กับ Month ได้หรือเปล่า |
| |
 |
|
|
 |
 |
|
ไม่ได้คับ หรือต้องมาแปลงก่อน เขียน sql ทีเดียวไม่ได้
|
 |
 |
 |
 |
| Date :
2012-02-14 11:41:13 |
By :
imperator |
|
 |
 |
 |
 |
|
|
 |
 |
|
 |
 |
| |
|
|
|
| |
|
sql 2008 r2 ครับ
ไม่ได้แฮะ
Code (ASP)
<select id="month_fueling" name="month_fueling" >
<%
Dim sql,rstemp,month_fueling
Set rstemp = Server.CreateObject("ADODB.Recordset")
sql = "select convert(varchar,y)+'/'+convert(varchar,m) as ym from (SELECT DISTINCT year(fill_date) as y,month(fill_date) as m FROM wguses) as a order by y,m desc "
rstemp.open sql,sConn
While Not rstemp.EOF
datey=rstemp("y")
datem=rstemp("m")
%>
<option value="<% =datey&datem %>">
<% =datey %> - <% =datem %>
</option>
<%
rstemp.MoveNext
Wend
set rstemp = nothing
%>
</select>
|
| |
|
|
| |
| Date :
2012-02-14 13:21:24 |
By :
imperator |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
Code (ASP)
<select id="month_fueling" name="month_fueling" >
<%
Dim sql,rstemp,month_fueling
Set rstemp = Server.CreateObject("ADODB.Recordset")
sql = "SELECT DISTINCT year(fill_date) as y,month(fill_date) as m FROM wguses order by y,m desc"
rstemp.open sql,sConn
While Not rstemp.EOF
datey=rstemp("y")
datem=rstemp("m")
%>
<option value="<% =datey&datem %>">
<% =datey %> - <% =datem %>
</option>
<%
rstemp.MoveNext
Wend
set rstemp = nothing
%>
</select>
ลองดูครับ
|
| |
|
|
| |
| Date :
2012-02-14 13:29:36 |
By :
Thep |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
เปลี่ยนมาใช้อันนี้
Code (ASP)
sql = "SELECT DISTINCT year(fill_date) as y,month(fill_date) as m FROM wguses order by year(fill_date),month(fill_date) desc"
|
| |
|
|
| |
| Date :
2012-02-14 13:30:37 |
By :
Thep |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ได้นะคับ แต่วุ่นวายมากเลยตอนเอาไปเทียบกับค่าใน DB แล้ว List record ออกมา
Code (ASP)
sql = "SELECT DISTINCT top 4 fill_date FROM wguses order by fill_date desc "
อันนี้พอจะแทรกอะไรเข้าไปได้ไหมให้ ไม่สนใจค่า date เทียบแต่ค่า month กับ year
|
| |
|
|
| |
| Date :
2012-02-14 15:08:45 |
By :
imperator |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ตอนเอาไปเทียบกับค่าใน DB แล้ว List record ออกมา
ขอดู Code หน่อยดิ
|
| |
|
|
| |
| Date :
2012-02-15 09:30:23 |
By :
Thep |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ตอนนี้ใช้ตัวนี้วนลูป เดือน ปี ครับ
Code (ASP)
<select id="month_fueling" name="month_fueling" >
<%
Dim sql,rstemp,d1,d2,month_fueling
Set rstemp = Server.CreateObject("ADODB.Recordset")
sql = "SELECT TOP 4 year(fill_date) AS yr, month(fill_date) AS mt FROM wguses GROUP BY year(fill_date) , month(fill_date) ORDER BY yr DESC, mt DESC "
rstemp.open sql,sConn
While Not rstemp.EOF
%>
<option value="<% =rstemp("mt")&","&rstemp("yr") %>">
<% =Monthname(rstemp("mt")) %> - <% =rstemp("yr")%>
</option>
<%
rstemp.MoveNext
Wend
%>
</select>
_______________________________________________________________________________________
อันนี้เทียบ DB
Code (ASP)
Set rstemp = Server.CreateObject("ADODB.Recordset")
sql = "SELECT *, ISNULL(qty,0) AS qty2, ISNULL(unit,0) As unit2 FROM wguses Where month(fill_date) = '"&mon_split(0)&"' and year(fill_date) = '"&mon_split(1)&"' ORDER BY fill_date "
mon_split = Split(request.form ("month_fueling"),",")
rstemp.open sql,sConn,1,3
ติดปัญหาว่า....บรรทัด sql Error :
Microsoft VBScript runtime error '800a0009'
Subscript out of range: '[number: 0]'
หาไม่เจอว่า error จากตรงไหน
|
| |
|
|
| |
| Date :
2012-02-15 09:51:05 |
By :
imperator |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
Code (ASP)
mon_split = Split(request.form("month_fueling"),",")
sql = "SELECT *, ISNULL(qty,0) AS qty2, ISNULL(unit,0) As unit2 FROM wguses Where month(fill_date) = '"&mon_split(0)&"' and year(fill_date) = '"&mon_split(1)&"' ORDER BY fill_date "
rstemp.open sql,sConn,1,3
ลองดูครับ
|
| |
|
|
| |
| Date :
2012-02-15 10:27:11 |
By :
Thep |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ขอบคุณครับ rep บนแต่ปัญหาคือ Array Empty
ผมเลยเอา if check ก่อนสั่งให้ Split
เพื่อให้ค่า request.form ("month_fueling") ไม่ว่าง
ด้านล่างคือ OK แล้วนะครับ แต่ไม่รู้มันทำได้ดีกว่านี้รึป่าว เพิ่งหัดเขียน
____________________________________________________________________________________
วนลูป เดือน ปี
Code (ASP)
<select id="month_fueling" name="month_fueling" >
<%
Dim sql,rstemp,d1,d2,month_fueling
Set rstemp = Server.CreateObject("ADODB.Recordset")
sql = "SELECT TOP 4 year(fill_date) AS yr, month(fill_date) AS mt FROM wguses GROUP BY year(fill_date) , month(fill_date) ORDER BY yr DESC, mt DESC "
rstemp.open sql,sConn
While Not rstemp.EOF
If request.form("month_fueling") = rstemp("mt")&"/"&rstemp("yr") Then
strSel = "selected"
Else
strSel = ""
End IF
%>
<option value="<% =rstemp("mt")&"/"&rstemp("yr") %>" <% =strSel %>>
<% =Monthname(rstemp("mt")) %> - <% =rstemp("yr")%>
</option>
<%
rstemp.MoveNext
Wend
Set rstemp = nothing
%>
</select>
____________________________________________________________________________________
Check for List Record
Code (ASP)
<%
Set rstemp = Server.CreateObject("ADODB.Recordset")
If request.form ("month_fueling") <> "" then
mon_split = Split(request.form ("month_fueling"),"/")
sql = "SELECT *, ISNULL(qty,0) AS qty2, ISNULL(unit,0) As unit2 FROM wguses Where month(fill_date) = '"&mon_split(0)&"' and year(fill_date) = '"&mon_split(1)&"' "
rstemp.open sql,sConn,1,3
While not rstemp.EOF
.
.
.
rstemp.movenext
Wend
Set rstemp = nothing
End If
%>
|
ประวัติการแก้ไข
2012-02-15 10:48:41
| |
|
|
| |
| Date :
2012-02-15 10:46:42 |
By :
imperator |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
<select id="month_fueling" name="month_fueling" >
<%
Dim sql,rstemp,d1,d2,month_fueling
Set rstemp = Server.CreateObject("ADODB.Recordset")
sql = "select convert(varchar,y)+'-'+case when m < 10 then '0' else '' end+''+convert(varchar,m) as ym from
(SELECT DISTINCT year(fill_date) as y,month(fill_date) as m FROM wguses) as a order by y,m desc"
rstemp.open sql,sConn
While Not rstemp.EOF
If request.form("month_fueling") = rstemp("ym") Then
strSel = "selected"
Else
strSel = ""
End IF
%>
<option value="<%=rstemp("ym") %>" <% =strSel %>>
<%=Monthname(right(rstemp("ym"),2))%> - <%=left(rstemp("ym"),4)%>
</option>
<%
rstemp.MoveNext
Wend
Set rstemp = nothing
%>
</select>
____________________________________________________________________________________
Check for List Record
<%
Set rstemp = Server.CreateObject("ADODB.Recordset")
If request.form("month_fueling") <> "" then
sql = "SELECT *, ISNULL(qty,0) AS qty2, ISNULL(unit,0) As unit2 FROM wguses Where convert(char(6),fill_date,112) = '"&replace(request.form("month_fueling"),"-","")&"' "
rstemp.open sql,sConn,1,3
While not rstemp.EOF
.
.
.
rstemp.movenext
Wend
Set rstemp = nothing
End If
%>
|
| |
|
|
| |
| Date :
2012-02-15 11:10:17 |
By :
Thep |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
sql = "SELECT *, TOP 4 year(fill_date) AS yr, month(fill_date) AS mt FROM wguses WHERE spemp_code='"&request.form("emp_id")&"' GROUP BY year(fill_date) , month(fill_date) ORDER BY yr DESC, mt DESC "
เงื่อนไขแบบนี้ SELECT *, TOP 4 year(fill_date) AS yr, month(fill_date) AS mt ...
มันขัดใจกันยังไงเหลอครับ รันไม่ออกเลย
คืออยากได้ค่า record อื่นๆด้วยจะได้ไม่ต้องเปิด sql หลายๆครั้ง
ปกติ SELECT TOP 4 year(fill_date) AS yr, month(fill_date) AS mt ...
มันรันออกปกติ
|
| |
|
|
| |
| Date :
2012-02-15 16:08:43 |
By :
imperator |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
เพราะว่า คาสั่ง GROUP BY จะต้องระบุ filed ที่ต้องการ GROUP ลงไป
SELECT TOP 4 year(fill_date) AS yr, month(fill_date) AS mt FROM wguses GROUP BY year(fill_date) , month(fill_date) ORDER BY yr DESC, mt DESC
อย่างนี้ไม่ได้
SELECT *, TOP 4 year(fill_date) AS yr, month(fill_date) AS mt FROM wguses WHERE spemp_code='"&request.form("emp_id")&"' GROUP BY year(fill_date) , month(fill_date) ORDER BY yr DESC, mt DESC
|
| |
|
|
| |
| Date :
2012-02-16 08:20:53 |
By :
Thep |
|
|
| |
|
|
|
|
|
| |
|
|
|
|
|
