How to use ROW_NUMBER in a CTE (Common Table Expression)?

Execute the following script in Query Editor to demonstrate the use of ROW_NUMBER function with PARTITION BY. The TOP 10 selects the largest orders for each customer.

USE AdventureWorks

GO

WITH cteTotalDueSorted

AS

(

SELECT

Customer = s.Name,

s.CustomerID,

SalesOrderID,

OrderDate,

TotalDue='$'+convert(varchar,TotalDue,1),

SeqNo = ROW_NUMBER() OVER (

PARTITION BY soh.CustomerID

ORDER BY TotalDue DESC)

FROM Sales.SalesOrderHeader AS soh

JOIN Sales.Store s

ON soh.CustomerID = s.CustomerID

)

SELECT

Customer,

ItemNo=cte.SeqNo,

SalesOrderID,

OrderDate = convert(char(10), OrderDate,111),

TotalDue

FROM cteTotalDueSorted cte

WHERE cte.SeqNo <= 10

ORDER BY CustomerID, ItemNo

Partial Results:

Customer	ItemNo	SalesOrderID	OrderDate	TotalDue
A Bike Store	1	45283	2002/2/1	$31,972.17
A Bike Store	2	46042	2002/5/1	$29,418.53
A Bike Store	3	44501	2001/11/1	$22,152.24
A Bike Store	4	43860	2001/8/1	$12,381.08
Progressive Sports	1	46976	2002/8/1	$8,727.11
Progressive Sports	2	47997	2002/11/1	$4,682.69

Macro to extract the highlighted Text in the document

Sub ScratchMacro()
Dim list
list = ""
Dim oRng As Word.Range
Set oRng = ActiveDocument.Range
With oRng.Find
.Highlight = True
While .Execute
'MsgBox (oRng.Text)
list = ""
list = list & oRng.Text & "~"
Wend
End With
MsgBox (list)
End Sub

Disable Addressbars in Popups

Add the site viewing to the trusted sites tab in security option

SQL Query to select first day and last day of month

declare @date datetime, @first datetime, @last datetime
set @date = getdate()
set @first = cast(month(@date) as varchar(2)) + '/1/' + cast(year(@date) as varchar(4))
set @last = dateadd(d, -1, dateadd(m, 1, @first))
select @first, @last

select dateadd(mm,datediff(mm,0,getdate()),0)
select dateadd(ms,-3,dateadd(mm,datediff(mm,0,getdate())+1,0))
select dateadd(ms,- 3,dateadd(mm,0,dateadd(mm,datediff(mm,0,getdate())+1,0)))

Export Stored Procedure results to text file

Use the BCP command.... You will need to have access to xp_cmdshell which is an advanced setting in sp_configure...
exec master..xp_cmdshell 'bcp "exec myDB..uMyProc" queryout c:\umytable.txt -T -c'

Type Lib Not Found in global.asa

Open a blank project in visual interdev...add the the references included in the global.asa files and replace the meta tags with the new tags in the project created global.asa file


Also allow permission for ASP extensions in webserver extensions in IIS to load ASP pages

Word found locked fields during the update. Word cannot update locked fields.

To change the wrapping style of the drawing canvas, follow these steps:
Click the drawing canvas. Notice that the Drawing Canvas toolbar appears.
On the Drawing Canvas toolbar, click Text Wrapping, and then click any wrapping style other than In Line With Text.

From the Menu bar, select Edit > Select all.From the keyboard, press and hold Ctrl +Shift + F11