select Product, EXP(sum(log(abs(nullif(Value, 0))))) * (1+2*(cast(sum(sign(Value)-1)/2 as int) % 2)) * min(abs(sign(value)))
from @PRODUCT group by Product
select Exp(Sum((case Abs(column) when 0 then 0 else Log(Abs(column)) end)))*(case Min(abs(column)) when 0 then 0 else 1 end)*(1-2*(Sum( ( case when column>=0 then 0 else 1 end) ) % 2)) column
Wednesday, February 11, 2009
Tuesday, January 20, 2009
dropdown menu onclick show div hide div
<html>
<head>
<title>Script Demo Gops</title>
<style>
#d1, #d2, #d3{display:none;}
</style>
<script language="javascript">
var oldD="";
function show(o){
if(oldD!="") oldD.style.display='none';
if(o.selectedIndex>0){
var d=document.getElementById(o[o.selectedIndex].value);
d.style.display='block';
oldD=d;
}
}
</script>
</head>
<body>
<select onchange="show(this)">
<option>--Choose--</option>
<option value="d1">Layer 1</option>
<option value="d2">Layer 2</option>
<option value="d3">Layer 3</option>
</select>
<div id="d1">
this is a div1<br>
this is a div1<br>
this is a div1<br>
this is a div1<br>
this is a div1<br>
</div>
<div id="d2">
this is a div2<br>
this is a div2<br>
this is a div2<br>
this is a div2<br>
this is a div2<br>
</div>
<div id="d3">
this is a div3<br>
this is a div3<br>
this is a div3<br>
this is a div3<br>
this is a div3<br>
</div>
</body>
</html>
Tuesday, December 2, 2008
Convert Number To Words
Option Explicit
' Function for conversion of a Currency to words
' Parameter - accept a Currency
' Returns the number in words format
'*************************************************
Function CurrencyToWord(ByVal MyNumber)
Dim Temp
Dim Rupees, Paisa As String
Dim DecimalPlace, iCount
Dim Hundreds, Words As String
ReDim Place(9) As String
Place(0) = " Thousand "
Place(2) = " Lakh "
Place(4) = " Crore "
Place(6) = " Arab "
Place(8) = " Kharab "
On Error Resume Next
' Convert MyNumber to a string, trimming extra spaces.
MyNumber = Trim(Str(MyNumber))
' Find decimal place.
DecimalPlace = InStr(MyNumber, ".")
' If we find decimal place...
If DecimalPlace > 0 Then
' Convert Paisa
Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
Paisa = " and " & ConvertTens(Temp) & " Paisa"
' Strip off paisa from remainder to convert.
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
' Convert last 3 digits of MyNumber to ruppees in word.
Hundreds = ConvertHundreds(Right(MyNumber, 3))
' Strip off last three digits
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
iCount = 0
Do While MyNumber <> ""
'Strip last two digits
Temp = Right(MyNumber, 2)
If Len(MyNumber) = 1 Then
Words = ConvertDigit(Temp) & Place(iCount) & Words
MyNumber = Left(MyNumber, Len(MyNumber) - 1)
Else
Words = ConvertTens(Temp) & Place(iCount) & Words
MyNumber = Left(MyNumber, Len(MyNumber) - 2)
End If
iCount = iCount + 2
Loop
CurrencyToWord = "Rupees " & Words & Hundreds & Paisa
End Function
' Conversion for hundreds
'*****************************************
Private Function ConvertHundreds(ByVal MyNumber)
Dim Result As String
' Exit if there is nothing to convert.
If Val(MyNumber) = 0 Then Exit Function
' Append leading zeros to number.
MyNumber = Right("000" & MyNumber, 3)
' Do we have a hundreds place digit to convert?
If Left(MyNumber, 1) <> "0" Then
Result = ConvertDigit(Left(MyNumber, 1)) & " Hundreds "
End If
' Do we have a tens place digit to convert?
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & ConvertTens(Mid(MyNumber, 2))
Else
' If not, then convert the ones place digit.
Result = Result & ConvertDigit(Mid(MyNumber, 3))
End If
ConvertHundreds = Trim(Result)
End Function
' Conversion for tens
'*****************************************
Private Function ConvertTens(ByVal MyTens)
Dim Result As String
' Is value between 10 and 19?
If Val(Left(MyTens, 1)) = 1 Then
Select Case Val(MyTens)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else
' .. otherwise it's between 20 and 99.
Select Case Val(Left(MyTens, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
' Convert ones place digit.
Result = Result & ConvertDigit(Right(MyTens, 1))
End If
ConvertTens = Result
End Function
Private Function ConvertDigit(ByVal MyDigit)
Select Case Val(MyDigit)
Case 1: ConvertDigit = "One"
Case 2: ConvertDigit = "Two"
Case 3: ConvertDigit = "Three"
Case 4: ConvertDigit = "Four"
Case 5: ConvertDigit = "Five"
Case 6: ConvertDigit = "Six"
Case 7: ConvertDigit = "Seven"
Case 8: ConvertDigit = "Eight"
Case 9: ConvertDigit = "Nine"
Case Else: ConvertDigit = ""
End Select
End Function
' Function for conversion of a Currency to words
' Parameter - accept a Currency
' Returns the number in words format
'*************************************************
Function CurrencyToWord(ByVal MyNumber)
Dim Temp
Dim Rupees, Paisa As String
Dim DecimalPlace, iCount
Dim Hundreds, Words As String
ReDim Place(9) As String
Place(0) = " Thousand "
Place(2) = " Lakh "
Place(4) = " Crore "
Place(6) = " Arab "
Place(8) = " Kharab "
On Error Resume Next
' Convert MyNumber to a string, trimming extra spaces.
MyNumber = Trim(Str(MyNumber))
' Find decimal place.
DecimalPlace = InStr(MyNumber, ".")
' If we find decimal place...
If DecimalPlace > 0 Then
' Convert Paisa
Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
Paisa = " and " & ConvertTens(Temp) & " Paisa"
' Strip off paisa from remainder to convert.
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
' Convert last 3 digits of MyNumber to ruppees in word.
Hundreds = ConvertHundreds(Right(MyNumber, 3))
' Strip off last three digits
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
iCount = 0
Do While MyNumber <> ""
'Strip last two digits
Temp = Right(MyNumber, 2)
If Len(MyNumber) = 1 Then
Words = ConvertDigit(Temp) & Place(iCount) & Words
MyNumber = Left(MyNumber, Len(MyNumber) - 1)
Else
Words = ConvertTens(Temp) & Place(iCount) & Words
MyNumber = Left(MyNumber, Len(MyNumber) - 2)
End If
iCount = iCount + 2
Loop
CurrencyToWord = "Rupees " & Words & Hundreds & Paisa
End Function
' Conversion for hundreds
'*****************************************
Private Function ConvertHundreds(ByVal MyNumber)
Dim Result As String
' Exit if there is nothing to convert.
If Val(MyNumber) = 0 Then Exit Function
' Append leading zeros to number.
MyNumber = Right("000" & MyNumber, 3)
' Do we have a hundreds place digit to convert?
If Left(MyNumber, 1) <> "0" Then
Result = ConvertDigit(Left(MyNumber, 1)) & " Hundreds "
End If
' Do we have a tens place digit to convert?
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & ConvertTens(Mid(MyNumber, 2))
Else
' If not, then convert the ones place digit.
Result = Result & ConvertDigit(Mid(MyNumber, 3))
End If
ConvertHundreds = Trim(Result)
End Function
' Conversion for tens
'*****************************************
Private Function ConvertTens(ByVal MyTens)
Dim Result As String
' Is value between 10 and 19?
If Val(Left(MyTens, 1)) = 1 Then
Select Case Val(MyTens)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else
' .. otherwise it's between 20 and 99.
Select Case Val(Left(MyTens, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
' Convert ones place digit.
Result = Result & ConvertDigit(Right(MyTens, 1))
End If
ConvertTens = Result
End Function
Private Function ConvertDigit(ByVal MyDigit)
Select Case Val(MyDigit)
Case 1: ConvertDigit = "One"
Case 2: ConvertDigit = "Two"
Case 3: ConvertDigit = "Three"
Case 4: ConvertDigit = "Four"
Case 5: ConvertDigit = "Five"
Case 6: ConvertDigit = "Six"
Case 7: ConvertDigit = "Seven"
Case 8: ConvertDigit = "Eight"
Case 9: ConvertDigit = "Nine"
Case Else: ConvertDigit = ""
End Select
End Function
Wednesday, November 26, 2008
Changing Crystalreport database connection at runtime
CrxReport.Database.Tables(1).ConnectionProperties.Item("Data source") = strserver
CrxReport.Database.Tables(1).ConnectionProperties.Item("User ID") = struser
CrxReport.Database.Tables(1).ConnectionProperties.Item("Initial Catalog") = strdatabase
CrxReport.Database.Tables(1).ConnectionProperties.Item("password") = strpassword
CrxReport.Database.Tables(1).ConnectionProperties.Item("User ID") = struser
CrxReport.Database.Tables(1).ConnectionProperties.Item("Initial Catalog") = strdatabase
CrxReport.Database.Tables(1).ConnectionProperties.Item("password") = strpassword
Thursday, November 20, 2008
insert word file into the current document
m_WordApp.Selection.InsertFile filename:=(App.Path & "\premium info link.doc"), _
Range:="", ConfirmConversions:=False, Link:=False, Attachment:=False
Range:="", ConfirmConversions:=False, Link:=False, Attachment:=False
Wednesday, November 19, 2008
Word macro to find and replace text with a image file
With m_WordApp.Selection.Find
.ClearFormatting
.Text = fvalue
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
Do While .Execute
m_WordApp.Selection.InlineShapes.AddPicture _
filename:=(App.Path & "\ABC HMO_CA.jpg"), _
LinkToFile:=False, SaveWithDocument:=True
m_WordApp.Selection.Collapse wdCollapseEnd
Loop
End With
.ClearFormatting
.Text = fvalue
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
Do While .Execute
m_WordApp.Selection.InlineShapes.AddPicture _
filename:=(App.Path & "\ABC HMO_CA.jpg"), _
LinkToFile:=False, SaveWithDocument:=True
m_WordApp.Selection.Collapse wdCollapseEnd
Loop
End With
Monday, November 3, 2008
How to use CASE in ORDER BY?
The following script sorts upper case addresses alphabetically followed by address lines starting with digits and finally lower case or non-alpha characters:
Use AdventureWorks;
Go
Select
AddressLine1,
AddressLine2=isnull(AddressLine2,''),
City,
[State] = sp.StateProvinceCode,
PostalCode,
Country = cr.Name
From Person.[Address] a
Join Person.StateProvince sp
On a.StateProvinceID = sp.StateProvinceID
Join Person.CountryRegion cr
On sp.CountryRegionCode = cr.CountryRegionCode
Order by
(Case
When Ascii([AddressLine1]) between 65 and 90 then 0 -- Upper case alpha
When Ascii([AddressLine1]) between 48 and 57 then 1 -- Digits
Else 2
End), AddressLine1, City
Go
Partial results:
| AddressLine1 | AddressLine2 | City | State | PostalCode | Country |
| Adirondack Factory Outlet | Lake George | NY | 12845 | United States | |
| Alderstr 1849 | Braunschweig | NW | 38001 | Germany | |
| Alderstr 2577 | Poing | SL | 66041 | Germany | |
| Alderstr 2646 | Saarlouis | SL | 66740 | Germany | |
| Alderstr 27 | Offenbach | SL | 63009 | Germany |
Subscribe to:
Posts (Atom)