March 14, 2010 17:37 by
SELECT C.AccountNumber, C.CustomerID, SO.OrderDate,
ROW_NUMBER() OVER (ORDER BY SO.OrderDate) AS OrderNumber
FROM Sales.Customer C
INNER JOIN Sales.SalesOrderHeader SO ON C.CustomerID = SO.CustomerID
article found here
ae5fded4-e2c9-4608-ac5f-1be48af3849f|0|.0
March 14, 2010 12:28 by
good example below on how to use the NULLIF() function
here
select sum((xtndprce-extcost)/NULLIF(xtndprce,0)) as ActualMargin
I used in a group by select |
34664e8f-5298-43cc-a47b-b953dd8537c4|0|.0
March 14, 2010 12:27 by
| select * from sysobjects where id in ( select ID from syscomments where ltrim(Rtrim([text])) like '%ProductInfo%') returns the names of procedures that contain the text string |
dabb82e3-dad6-445e-8122-be352e8aa21f|0|.0
March 14, 2010 12:23 by
| select right('00' + convert(varchar(2),month(getdate())),2) + '/' + cast(datepart(yyyy,getdate()) as varchar(4)) result 12/2009 will sort correctly |
5abe4bd6-f6cf-4741-9132-ceae3c5fe8b8|0|.0
March 14, 2010 12:18 by
|
quick and easy way to send email to many to addresses.
public static void SendMessageEmail(string eTO, string From, string Subject, string message, bool isHtml)
{
MailMessage msg = new MailMessage();
char split = Convert.ToChar(";");
string[] emTo = eTO.Split(split);
foreach (string toAdd in emTo)
{
msg.To.Add(toAdd);
}
msg.From = new MailAddress(From);
msg.Subject = Subject;
msg.Body = message;
msg.IsBodyHtml = isHtml;
SmtpClient client = new SmtpClient("mail.yourserver.com");
try
{client.Send(msg);}
catch (Exception exc) {//do something to handle error here }}
|
b4448a79-f078-4848-9737-be08ed9afabe|0|.0
March 14, 2010 12:17 by
|
using existing sql mail profile.
EXEC msdb.dbo.sp_send_dbmail
@profile_name
= 'ReportsMail',
@recipients
= @emailto,
@body
= @body,
@blind_copy_recipients
= 'add addresses here',
@subject
= @subject;
|
910c2100-52e6-437a-a080-09c878bde623|0|.0
March 14, 2010 12:17 by
CREATE FUNCTION dbo.GetWorkingDays
(
@startDate SMALLDATETIME,
@endDate SMALLDATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @range INT;
SET @range = DATEDIFF(DAY, @startDate, @endDate)+1;
RETURN
(
SELECT
@range / 7 * 5 + @range % 7 -
(
SELECT COUNT(*)
FROM
(
SELECT 1 AS d
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
) weekdays
WHERE d <= @range % 7
AND DATENAME(WEEKDAY, @endDate - d + 1)
IN
(
'Saturday',
'Sunday'
)
)
);
END
GO
|
f4de65fc-ad3f-46cf-9684-fbc6309e9648|0|.0
March 14, 2010 12:13 by
|
The sql code below will take the first character and then the rest of the character s from the space index.
john doe will be returned as jdoe. can use any delimiter in charindex. Add one to the int that charindex returns and the len(column) function to get the rest of the values.
substring([full name],1,1) + substring([full name],CHARINDEX (' ' ,[full name]) +1,len([full name])) [UserName]
|
93f17295-b2b8-44fb-869b-73651c4c0723|0|.0