Karuta’s ASP & M$ SQLserver

Dicas, códigos e outras coisinhas de meus estudos para não esquecer…

Archive for outubro \30\UTC 2016

SQL Server – Running large script files

Posted by karuta em outubro 30, 2016

This tool (Big SQL Script File Runner) on CodePlex will run any size script file with log and GUI.

Anúncios

Posted in sql server | Leave a Comment »

SQL Server: Uso Prático do OVER() para agrupar

Posted by karuta em outubro 23, 2016

select customerID, productID, orderDate, orderAmount
from Orders

customerID  productID   orderDate               orderAmount
----------- ----------- ----------------------- ---------------------
1           1           2007-01-01 00:00:00.000 20.00
1           2           2007-01-02 00:00:00.000 30.00
1           2           2007-01-05 00:00:00.000 23.00
1           3           2007-01-04 00:00:00.000 18.00
2           1           2007-01-03 00:00:00.000 74.00
2           1           2007-01-06 00:00:00.000 34.00
2           2           2007-01-08 00:00:00.000 10.00

(7 row(s) affected)
select customerID,  productID, orderDate, orderAmount, 
      sum(orderAmount) OVER (Partition by CustomerID) as Total
from Orders

customerID  productID   orderDate               orderAmount   Total
----------- ----------- ----------------------- ------------- ---------
1           1           2007-01-01 00:00:00.000 20.00         91.00
1           2           2007-01-02 00:00:00.000 30.00         91.00
1           2           2007-01-05 00:00:00.000 23.00         91.00
1           3           2007-01-04 00:00:00.000 18.00         91.00
2           1           2007-01-03 00:00:00.000 74.00         118.00
2           1           2007-01-06 00:00:00.000 34.00         118.00
2           2           2007-01-08 00:00:00.000 10.00         118.00

(7 row(s) affected)

 

 

Posted in sql server, store procedure | Leave a Comment »

ASP Inverse Function Server.HTMLEncode (string)

Posted by karuta em outubro 21, 2016

<%
Private Function HTMLDecode(byVal encodedstring)
Dim tmp, i
tmp = encodedstring
tmp = Replace( tmp, “%20″, ” ” )
tmp = Replace( tmp, “%22″,”””” )
tmp = Replace( tmp, “&quot;”, chr(34) )
tmp = Replace( tmp, “&lt;” , chr(60) )
tmp = Replace( tmp, “&gt;” , chr(62) )
tmp = Replace( tmp, “&amp;” , chr(38) )
tmp = Replace( tmp, “&nbsp;”, chr(32) )
For i = 1 to 255
tmp = Replace( tmp, “&#” & i & “;”, chr( i ) )
Next
HTMLDecode = tmp
End Function
%>

Posted in asp | Leave a Comment »