how to make sql pivot with visual basic 6
hello sir, iam still new in vb 6
can someone help me to
how to code make if selected date from 2017-10-01 - 2017-10-06 (there are 5 day +1 = 6) when click push button idaily added from idaily1 idaily2 idaily3 idaily4 idaily5 idaily6
i am still using manual code to show idaily, how to make otomatic with the range date
i use code
Code:
Option Explicit
Dim iDaily As Integer
Dim vSubTotal As Double
Public sQuery As String
Public pConnection As New ADODB.Connection
Dim rs As New Recordset
Dim conn As New ADODB.Connection
Private Sub openDB()
Set rs = New ADODB.Recordset
Set conn = New ADODB.Connection
conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSpi;persist security Info=false;Initial Catalog=test"
End Sub
Private Sub PushButton1_Click()
OseTextBox1.Text = DateTimePicker2.Value - DateTimePicker1.Value
End Sub
Private Sub PushButton2_Click()
FlatEdit1.Text = OseDateEnd.Value - OseDateStart
FlatEdit1.Text = FlatEdit1.Text + 1
iDaily = FlatEdit1.Text
lblLabel1.Caption = iDaily
Call openDB
rs.Open "select * from (select distinct id,item,whouse,st from testdrive )src pivot ( sum(st) for whouse in ([A],[B],[C],[iDaily1],[iDaily2],[iDaily3],[iDaily4],[iDaily5],[iDaily6]) ) piv ", conn, adOpenStatic, adLockReadOnly
Set ReportControl.DataManager.DataSource = rs
ReportControl.DataManager.DataBind
'Set other ReportControl options
ReportControl.PaintManager.VerticalGridStyle = xtpGridSolid
ReportControl.PaintManager.HorizontalGridStyle = xtpGridSolid
ReportControl.AllowEdit = False
ReportControl.FocusSubItems = False
ReportControl.AllowColumnRemove = False
ReportControl.PaintManager.FixedRowHeight = False
ReportControl.AllowColumnSort = False
ReportControl.FreezeColumnsCount = 3
link image view my program
image
Re: how to make sql pivot with visual basic 6
Don't say "SQL" when you mean "SQL Server" they are two entirely different things.
You might get more and better help more quickly if you post database questions to the database forum.
Re: how to make sql pivot with visual basic 6
I'm not yet convinced that the question is about the SQL itself. It seems like it's a question that is kind of in the middle, but I'm WAY too rusty on some of the technology being used there. Is this a pivot issue, or an issue about getting the range?
Re: how to make sql pivot with visual basic 6
Quote:
Originally Posted by
aguswibowo
hello sir, iam still new in vb 6
can someone help me to
how to code make if selected date from 2017-10-01 - 2017-10-06 (there are 5 day +1 = 6) when click push button idaily added from idaily1 idaily2 idaily3 idaily4 idaily5 idaily6
i am still using manual code to show idaily, how to make otomatic with the range date
i use code
Code:
Option Explicit
Dim iDaily As Integer
Dim vSubTotal As Double
Public sQuery As String
Public pConnection As New ADODB.Connection
Dim rs As New Recordset
Dim conn As New ADODB.Connection
Private Sub openDB()
Set rs = New ADODB.Recordset
Set conn = New ADODB.Connection
conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSpi;persist security Info=false;Initial Catalog=test"
End Sub
Private Sub PushButton1_Click()
OseTextBox1.Text = DateTimePicker2.Value - DateTimePicker1.Value
End Sub
Private Sub PushButton2_Click()
FlatEdit1.Text = OseDateEnd.Value - OseDateStart
FlatEdit1.Text = FlatEdit1.Text + 1
iDaily = FlatEdit1.Text
lblLabel1.Caption = iDaily
Call openDB
rs.Open "select * from (select distinct id,item,whouse,st from testdrive )src pivot ( sum(st) for whouse in ([A],[B],[C],[iDaily1],[iDaily2],[iDaily3],[iDaily4],[iDaily5],[iDaily6]) ) piv ", conn, adOpenStatic, adLockReadOnly
Set ReportControl.DataManager.DataSource = rs
ReportControl.DataManager.DataBind
'Set other ReportControl options
ReportControl.PaintManager.VerticalGridStyle = xtpGridSolid
ReportControl.PaintManager.HorizontalGridStyle = xtpGridSolid
ReportControl.AllowEdit = False
ReportControl.FocusSubItems = False
ReportControl.AllowColumnRemove = False
ReportControl.PaintManager.FixedRowHeight = False
ReportControl.AllowColumnSort = False
ReportControl.FreezeColumnsCount = 3
link image view my program
image
Hi,
if you want to declare a Range (from a Date) in a Pivot table you have to declare it.
example..
sorry this is German but I will try to make it clear.
the first Line states the Parameters for the search
KW is a number = Calander week
FJahr is a number = the Year
Code:
PARAMETERS KW Long, FJahr Long;
TRANSFORM Sum([Order Details].Quantity) AS [Summe von Quantity]
SELECT Products.ProductName
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
WHERE (((Year([OrderDate]))=[FJahr]) AND ((Format([OrderDate],"ww"))=[KW]))
GROUP BY Products.ProductName, Format([OrderDate],"ww")
PIVOT Format([OrderDate],"dddd") In ("Montag","Dienstag","Mittwoch","Donnerstag","Freitag","Samstag","Sonntag");
I use the OrderDate and Group it to "ww" for the Calendar week
I want the PIVOT Format to show the Days in that Week and Year= ("Monday","Tuesday", etc....")
hope it Helps
regards
Chris
