by ssi
27. September 2016 15:48
set@StartDate= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)--First day of previous month
set@EndDate =DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) --Last Day of previous month
end
else
begin
set@StartDate=dateadd(dd,((datediff(dd,'17530101',getdate())/7)*7)-7,'17530101') -- Date greater than or equal to Monday of last week
set@EndDate =dateadd(dd,((datediff(dd,'17530101',getdate())/7)*7)-2,'17530101') -- Date before Saturday of last week
c53b3c31-00fb-4497-854e-ca0be26ec9c6|0|.0|96d5b379-7e1d-4dac-a6ba-1e50db561b04
Tags: SQL, DATE
SQL
by ssi
10. June 2016 07:51
public static string StripLeadingZeros(string sAlphaNumberText)
{
/*
* That will trim trailing zeros if they appear after the period in a decimal number (it will always leave a single zero).
* It also accounts for numbers that appear at the very end.
*/
string sRet = "";
sRet = Regex.Replace(sAlphaNumberText,
@"(?<=\.\d+?)0+(?=\D|$)",
String.Empty);
return sRet;
}
public static string StripWhitespace(string sAlphaNumberText)
{
/*
* sStrip Whitespace
*/
string sRet = "";
sRet = Regex.Replace(sAlphaNumberText, @"\s+", "");
return sRet;
}
public static string StripAllButNumbers(string sAlphaNumberText)
{
/*
* strip all characters except numbers
*/
string sRet = "";
sRet = Regex.Replace(sAlphaNumberText, @"[^\d]", String.Empty);
return sRet;
}
public static string StripNumbers(string sAlphaNumberText)
{
/*
* sStrip Numbers
*/
string sRet = "";
sRet = Regex.Replace(sAlphaNumberText, @"[\d-]", string.Empty);
return sRet;
}
public static string StripAllSpecialCharacters(string sAlphaNumberText)
{
/*
* Strip All Special Characters
*/
string sRet = "";
sRet = Regex.Replace(sAlphaNumberText, "[^0-9a-zA-Z]+", "");
return sRet;
}
by ssi
22. February 2016 08:04
WITH Vals AS (
SELECT [STATE]
,[Fname]
,[Lname]
,[EMAIL]
, 1 DisplayOrder
, 1 as Name_Count
FROM [ssi_tbl_NameAddress]
UNION ALL
SELECT [STATE]
,'' AS Fname
,'' AS lname
,'' as email
,2 DisplayOrder
, count(*) as Name_Count
FROM [ssi_tbl_NameAddress]
GROUP BY [STATE]
)
SELECT [STATE]
,[Fname]
,[Lname]
,[EMAIL]
,Name_Count
FROM Vals
ORDER BY [STATE],DisplayOrder
with sum()
DECLARE @Sales TABLE(
CUS_ID INT,
PRODUCT VARCHAR(20),
AMOUNT FLOAT
)
INSERT INTO @Sales SELECT 12,'A', 2.50
INSERT INTO @Sales SELECT 12,'B', 5.80
INSERT INTO @Sales SELECT 24,'A', 10.00
INSERT INTO @Sales SELECT 24,'B', 30.00
;WITH Vals AS (
SELECT cus_id,
product,
amount,
1 DisplayOrder,
SUM(amount) OVER(PARTITION BY cus_id) OrderTotal
FROM @Sales
UNION ALL
SELECT cus_id,
'Total' AS product,
SUM(amount),
2 DisplayOrder,
SUM(amount)
FROM @Sales
GROUP BY cus_id
)
SELECT cus_id,
product,
amount
FROM Vals
ORDER BY OrderTotal DESC,cus_id,DisplayOrder, product
1735fdb3-f77f-49f5-9120-c2d75d243aa9|0|.0|96d5b379-7e1d-4dac-a6ba-1e50db561b04
Tags: SQL, group by, subtotal
SQL
by ssi
21. February 2016 12:05
DELETE FROM [ssi_tbl_NameAddress]
WHERE control NOT IN (SELECT MAX(control)
FROM [ssi_tbl_NameAddress]
GROUP BY Lname,fname
/*Even if ID is not null-able SQL Server treats MAX(ID) as potentially
nullable. Because of semantics of NOT IN (NULL) including the clause
below can simplify the plan*/
HAVING MAX(control) IS NOT NULL)
4814dad4-0146-454d-834c-9ff9548c9c14|0|.0|96d5b379-7e1d-4dac-a6ba-1e50db561b04
Tags: sql
SQL
by ssi
11. February 2016 09:32
USE [LENDER_LOAN_SERVICE]
GO
/****** Object: UserDefinedFunction [dbo].[uf_ssi_GetHMDARaceString] Script Date: 02/11/2016 09:30:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[uf_ssi_GetHMDARaceString]( @LoanID int , @BRW varchar(50))
RETURNS nchar(5)
AS
BEGIN
DECLARE @Event_ID varchar(100)
DECLARE @Return varchar(500);
--set @LoanID ='1344'
DECLARE my_cursor CURSOR FOR
SELECT [_Type] FROM [HMDA_RACE] where [loanGeneral_Id] =@LoanID and [BorrowerID] = @BRW
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @Event_ID
set @Return = ''
--print @Event_ID
WHILE @@FETCH_STATUS = 0
BEGIN
if @Event_ID = 'AmericanIndianOrAlaskaNative' set @Return = @Return+ '1'
if @Event_ID = 'Asian' set @Return = @Return+ '2'
if @Event_ID = 'BlackOrAfricanAmerican' set @Return = @Return+ '3'
if @Event_ID = 'NativeHawaiianOrOtherPacificIslander' set @Return = @Return+'4'
if @Event_ID = 'White' set @Return = @Return+'5'
--else set @Return = @Return + '-'
--print @Event_ID
--print @Return
FETCH NEXT FROM my_cursor
INTO @Event_ID
END
CLOSE my_cursor
DEALLOCATE my_cursor
if (SELECT [RaceNationalOriginRefusalIndicator] FROM [GOVERNMENT_MONITORING] where [loanGeneral_Id] =@LoanID and [BorrowerID] = @BRW)=1 set @Return='6'
RETURN substring(@Return+' ',1,5)
END;
GO
105401fc-b07d-4fbc-a4c1-64d0c99e53c9|0|.0|96d5b379-7e1d-4dac-a6ba-1e50db561b04
Tags: SQL, DATE
SQL
by ssi
11. February 2016 09:28
--First of Current Month
--First of Current Month
select DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) as Firstday
,dateadd(day, -1, dateadd(month, 1, dateadd(day, 1 - day(GETDATE()), GETDATE()))) as lastday
d859f730-da5f-4129-95ce-9b3dc06fd00b|0|.0|96d5b379-7e1d-4dac-a6ba-1e50db561b04
Tags: SQL, DATE
CSharp | SQL
by ssi
1. February 2016 16:14
A SQL query walks into a bar. He approaches two tables and says, Mind if I join you?
54f2d1d5-d51c-4c7d-a003-5f935aa330e7|0|.0|96d5b379-7e1d-4dac-a6ba-1e50db561b04
Tags: Humor, SQL
SQL
by ssi
19. January 2016 09:55
23af80a6-962e-4f71-9f5c-43f725f36463|0|.0|96d5b379-7e1d-4dac-a6ba-1e50db561b04
Tags: cartoon
CSharp
by ssi
13. January 2016 10:06
https://msdn.microsoft.com/en-us/library/office/dd633709.aspx
Applies to: EWS Managed API
The Exchange Web Services (EWS) Managed API provides a simple and full-featured interface for developing and extending applications that use EWS. You can use the EWS Managed API to access EWS in versions of Exchange starting with Exchange Server 2007 Service Pack 1 (SP1), including Exchange Online.
The EWS Managed API provides user-friendly abstractions of XML messages, XML serialization, and the formation of the HTTP requests and responses that are sent between the client and server.
You can download the EWS Managed API from the Microsoft Download Center.
afc8ca8d-fd8e-441b-ae74-05886986d116|0|.0|96d5b379-7e1d-4dac-a6ba-1e50db561b04
Tags: Exchange
CSharp
by ssi
13. January 2016 09:48
DateTime dStopCLock = DateTime.Now;
TimeSpan ts = dStartCLock - dStopCLock;
double differenceInSeconds = ts.TotalSeconds;
ts = TimeSpan.FromSeconds(ts.TotalSeconds);
string sDuration = string.Format("Dur: {0:D2}h:{1:D2}m:{2:D2}s",
ts.Hours,
ts.Minutes,
ts.Seconds);
Console.WriteLine(sDuration);
5aef155f-74f4-428e-bcb0-6260f4f008eb|0|.0|96d5b379-7e1d-4dac-a6ba-1e50db561b04
Tags: Timespan
CSharp