05.txt

仍然是SQL语言学习版 可以致用的一些文章

5-1
Select Distinct
	ShippedDate as "出货日",
	Datepart(dw ,  ShippedDate) as "星期",
	Datepart(mm ,  ShippedDate) as "月",
	Datepart(yy ,  ShippedDate) as "年",
	Datepart(qq ,  ShippedDate) as "季",
	Datepart(dy ,  ShippedDate) as "年天",
	Datepart(dw ,  ShippedDate) as "周休",
	Datepart(year ,  ShippedDate) + '-' + DateName(month , ShippedDate) as "年_月",
	Datepart(wk ,  ShippedDate) as "年_周",
	Datepart(mm ,  ShippedDate) as "半年度"
From Orders 
Where ShippedDate is Not Null

5-2

Function Main()
Select Case DTSSource("星期")
	Case 1
		DTSDestination("星期") = 7
	Case 2
		DTSDestination("星期") = 1
	Case 3
		DTSDestination("星期") = 2
	Case 4
		DTSDestination("星期") = 3
	Case 5
		DTSDestination("星期") = 4
	Case 6
		DTSDestination("星期") = 5
	Case 7

		DTSDestination("星期") = 6
End Select
Main = DTSTransformStat_OK
End Function

5-3

Function Main()
Select Case DTSSource("周休")
	Case 1 , 7
		DTSDestination("周休") = 1
	Case Else
		DTSDestination("周休") = null
End Select
	Main = DTSTransformStat_OK
End Function

5-4

Function Main()
Select Case DTSSource("半年度")
	Case 1 , 2 , 3 , 4 , 5 , 6
		DTSDestination("半年度") = "上半年度"
	Case Else 
		DTSDestination("半年度") = "下半年度"

End Select
	Main = DTSTransformStat_OK
End Function

5-5

Function Main()
	If IsNull(DTSSource("Region")) Then
		DTSDestination("州省") = "其他"
	Else
		DTSDestination("州省") = DTSSource("Region")
	End If
	Main = DTSTransformStat_OK
End Function

5-6

Select Products.ProductID,
	Products.ProductName AS 产品名称,
	Suppliers.CompanyName AS 供应商,
	Categories.CategoryName AS 产品类别,
	Products.UnitPrice AS 单价
FROM Products INNER JOIN Categories
ON Products.CategoryID = Categories.categoryID
INNER JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID

5-7

Select EmployeeID,
	LastName + ' ' + FirstName AS 员工姓名,
	DATEDIFF(Year , BirthDate , GETDATE() ) AS 年龄层,
	DATEDIFF(Year , BirthDate , GETDATE() ) AS 年龄,
	DATEDIFF(Year , HireDate , GETDATE() ) AS 年薪层,
	DATEDIFF(Year , HireDate , GETDATE() ) AS 年薪
FROM Employees

5-8

Function Main()
If DTSSource("年龄层")<= 30 then
	DTSDestination("年龄层")="20岁以下"

elseif DTSSource("年龄层")< 50 then
	DTSDestination("年龄层")="30岁~50岁"
elseif DTSSource("年龄")< 60 then
	DTSDestination("年龄层")="50岁~60岁"
else
	DTSDestination("年龄层")="60岁以上"
end if	
Main=DTSTransformStat_OK
End Function

5-9

Function Main()
If DTSSource("年薪层")<= 5 then
	DTSDestination("年薪层")="5年以内"
else
	DTSDestination("年薪层")="5年以上"
end if	
Main=DTSTransformStat_OK
End Function

5-10

SELECT NW_mart.dbo.Time_Dim.时间序号,
	NW_mart.dbo.Customer_Dim.客户序号,
	NW_mart.dbo.Shipper_Dim.发货人序号,
	NW_mart.dbo.Product_Dim.产品序号,

	NW_mart.dbo.Employee_Dim.员工序号,
	Orders.RequiredDate AS 客户需求日,
	(( Orders.Freight / 
	  ( Select Sum(Quantity) 
            From  [Order Details] 
            Where OrderID = Orders.OrderID)
	  ) * [Order Details].Quantity
	) AS 运费,
	([Order Details].Unitprice * [Order Details].Quantity) -
	   ( ( [Order Details].Discount ) *
		  ([Order Details].Unitprice * [Order Details].Quantity)
	) AS 销售金额,
	[Order Details].Quantity AS 销售数量,
	( ( [Order Details].Discount) *	
	    ([Order Details].Unitprice*[Order Details].Quantity)
	) AS 折扣
	FROM Orders INNER JOIN [Order Details]
	ON Orders.OrderID = [Order Details].OrderID
	INNER JOIN NW_mart.dbo.Product_Dim
	ON [Order Details].ProductID = NW_mart.dbo.Product_Dim.产品编号
	INNER JOIN NW_mart.dbo.Customer_Dim
	ON Orders.CustomerID = NW_mart.dbo.Customer_Dim.客户编号
	INNER JOIN NW_mart.dbo.Time_Dim
	ON Orders.ShippedDate = NW_mart.dbo.Time_Dim.出货日
	INNER JOIN NW_mart.dbo.Shipper_Dim
	ON Orders.ShipVia = NW_mart.dbo.Shipper_Dim.发货人编号
	INNER JOIN NW_mart.dbo.Employee_Dim
	ON Orders.EmployeeID = NW_mart.dbo.Employee_Dim.员工编号
	WHERE ( Orders.ShippedDate IS NOT NULL )

5-11

Option Explicit

'local variable(s) to hold property value(s)
Private mvarDatabaseName As Variant 'local copy
Private mvarServerName As Variant 'local copy
Private mvarCubeName As Variant 'local copy
Public Function Process(ByRef strlErr As String) As Boolean
' returns False if an error occurred -- error text returned.

    ' DEVELOPER: Make sure the VB project references
    '            "Microsoft Decision Services Objects"
    Dim objlServer As New DSO.Server
    Dim objlDB As DSO.MDStore
    Dim objlCube As DSO.MDStore
    Dim intlDBCounter As Integer
    Dim intlCubeCounter As Integer
    
    ' Since most of the DSO calls are passed things by reference,
    ' we will make a copy of the reference data
    Dim strlServerName As String
    Dim strlDatabaseName As String
    Dim strlCubeName As String
    
    strlServerName = mvarServerName
    strlDatabaseName = mvarDatabaseName
    strlCubeName = mvarCubeName
    
    ' Default exit conditions
    strlErr = "" ' no error found
    Process = True
    
    ' ------------------------------------------------------
    ' Now the real work
    
    'Bind to the server
    'KCH Modified Start
    'If Not objlServer.Init(strlServerName, strlErr) Then
    '    Process = False
    '    Set objlServer = Nothing
    '    Exit Function
    'End If
    'KCH Modified End

    objlServer.Connect (strlServerName)
    
    ' Now locate the database and cube -- then process
    ' Step through the databases in the server's MDStores
    For intlDBCounter = 1 To objlServer.MDStores.Count
        Set objlDB = objlServer.MDStores(intlDBCounter)
        If UCase(objlDB.Name) = UCase(strlDatabaseName) Then
            'Step through the cubes in the database's MDStores
            For intlCubeCounter = 1 To objlDB.MDStores.Count
                Set objlCube = objlDB.MDStores(intlCubeCounter)
                If UCase(objlCube.Name) = UCase(strlCubeName) Then
    ' Ok -- we have the database and cube. Process it.
    
                    objlCube.Process (processFull)
                    
                    Set objlServer = Nothing
                    Exit Function ' we did OK -- everything worked!
                End If
            Next intlCubeCounter
            strlErr = "Cube (" & strlCubeName & ") not found in Database: " & strlDatabaseName & ", Server: " & strlServerName
            Process = False
            objlServer.UnlockAllObjects
            Set objlServer = Nothing
            Exit Function
        End If
    Next intlDBCounter
    strlErr = "Database (" & strlDatabaseName & ") not found in Server: " & strlServerName
    Process = False
    objlServer.UnlockAllObjects
    Set objlServer = Nothing
    Exit Function
    
End Function

Public Property Let CubeName(ByVal vData As Variant)
'used when assigning a value to the property, on the left side of an assignment.
'Syntax: X.CubeName = 5
    mvarCubeName = vData
End Property

Public Property Set CubeName(ByVal vData As Variant)

'used when assigning an Object to the property, on the left side of a Set statement.
'Syntax: Set x.CubeName = Form1
    Set mvarCubeName = vData
End Property

Public Property Get CubeName() As Variant
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.CubeName
    If IsObject(mvarCubeName) Then
        Set CubeName = mvarCubeName
    Else
        CubeName = mvarCubeName
    End If
End Property

Public Property Let ServerName(ByVal vData As Variant)
'used when assigning a value to the property, on the left side of an assignment.
'Syntax: X.ServerName = 5
    mvarServerName = vData
End Property

Public Property Set ServerName(ByVal vData As Variant)
'used when assigning an Object to the property, on the left side of a Set statement.
'Syntax: Set x.ServerName = Form1
    Set mvarServerName = vData
End Property

Public Property Get ServerName() As Variant
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.ServerName
    If IsObject(mvarServerName) Then
        Set ServerName = mvarServerName
    Else
        ServerName = mvarServerName
    End If
End Property

Public Property Let DatabaseName(ByVal vData As Variant)
'used when assigning a value to the property, on the left side of an assignment.
'Syntax: X.DatabaseName = 5
    mvarDatabaseName = vData
End Property

Public Property Set DatabaseName(ByVal vData As Variant)
'used when assigning an Object to the property, on the left side of a Set statement.
'Syntax: Set x.DatabaseName = Form1
    Set mvarDatabaseName = vData
End Property

Public Property Get DatabaseName() As Variant
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.DatabaseName
    If IsObject(mvarDatabaseName) Then
        Set DatabaseName = mvarDatabaseName
    Else
        DatabaseName = mvarDatabaseName
    End If
End Property

5-12

Private Function CheckNames() As Boolean

    CheckNames = True ' by default ok
    
    If Trim(txtServer.Text) = "" Then
        MsgBox "You must provide a server name.", vbCritical, "Missing value"
        CheckNames = False
    End If
    
    If Trim(txtDatabase.Text) = "" Then
        MsgBox "You must provide a database name.", vbCritical, "Missing value"
        CheckNames = False
    End If
    

    If Trim(txtCube.Text) = "" Then
        MsgBox "You must provide a cube name.", vbCritical, "Missing value"
        CheckNames = False
    End If

End Function

Private Sub btnProcess_Click()
    Dim objlPC As ProcessCube.clsProcessCube
    Dim blnlResult As Boolean
    Dim strlErr As String
    Dim datlStart As Date
    Dim strlStatus As String
    Dim vbStyle As VbMsgBoxStyle
     
    Set objlPC = CreateObject("ProcessCube.clsProcessCube")

    ' Check the names and setup required parameters
    If Not CheckNames Then Exit Sub
    objlPC.ServerName = Trim(txtServer.Text)
    objlPC.DatabaseName = Trim(txtDatabase.Text)
    objlPC.CubeName = Trim(txtCube.Text)
    
    ' display the hourglass
    DoEvents ' stablize the screen
    Screen.MousePointer = vbHourglass
    DoEvents
    
    ' Get start time and start the processing the cube
    datlStart = Now
    blnlResult = objlPC.Process(strlErr)
        
    ' clear the hourglass
    DoEvents
    Screen.MousePointer = vbDefault
    DoEvents

    ' Ok -- now check the returned status code
    ' The default is that everything was Ok...
    strlStatus = "Successful"
    vbStyle = vbInformation
    ' If we had a problem processing the cube

    If Not blnlResult Then
        strlStatus = strlErr
        vbStyle = vbCritical
    End If
    
    ' Tell the world our condition
    MsgBox "Process cube result: " & strlStatus & vbCrLf & _
        "Runtime was " & Format(Now - datlStart, "n:ss") & _
        " elapsed time", vbStyle, "Results"
        
    End ' Always exit the program since we found problems
        ' reusing the object. There appears to be an issue
        ' with 'unbinding' from the server.
    
End Sub

Private Sub Form_Load()
    ' center the form
    Me.Move (Screen.Width - Me.Width) / 2, (Screen.Height - Me.Height) / 2

End Sub