how to make sql pivot with visual basic 6

[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

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

[dilettante]

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.

[Shaggy Hiker]

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?

[ChrisE]

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

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

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