RcBuilder@walla.com
call centre: 054-5614020

SQL Categories

Posted by: RcBuilder  /   Category: CTE / DBA / SQL / SQL Function / Stored Procedure / System Function   /   No Comments
27
Nov
2014

SQL Categories - OPTION 1 - declare @tbl table(Id int,Name nvarchar(50),Parent int,SubCategories nvarchar(500)) insert into @tbl select 1,'111',0,'' union all select 2,'222',1,',2,5,6,7,13,14,' union all select 3,'333',1,'' union all select 4,'444',1,'' union all select 5,'555',2,'' union all select 6,'666',2,'' union all select 7,'777',2,',7,13,14,' union all select 8,'888',3,'' union all select 9,'999',3,'' union all select 10,'aaa',3,'' union all select 11,'bbb',3,'' union all select 12,'ccc',3,'' union all select 13,'ddd',7,'' union all select 14,'eee',7,'' ------------------------------------ declare @Id int set.

Read more

SQL FOR XML EXPLICIT

Posted by: RcBuilder  /   Category: DBA / FOR XML / SQL / XML   /   No Comments
09
Nov
2014

SQL FOR XML EXPLICIT SELECT 1 AS 'Tag' ,NULL AS 'Parent' ,NULL AS 'XML!1' ,NULL AS 'Company!2!Code' ,NULL AS 'Company!2!Name' ,NULL AS 'Department!3!Code' ,NULL AS 'Department!3!Name' UNION ALL SELECT 2 AS 'Tag' ,1 AS 'Parent' ,NULL AS 'XML!1' ,RTRIM(Code) AS 'Company!2!Code' ,Name AS 'Company!2!Name' ,NULL AS 'Department!3!Code' ,NULL AS 'Department!3!Name' FROM Company UNION ALL SELECT 3 AS 'Tag' ,2 AS 'Parent' ,NULL AS 'XML!1' ,RTRIM(c.Code) AS 'Company!2!Code' ,NULL AS 'Company!2!Name' ,RTRIM(d.Code) AS 'Department!3!Code' ,d.Name AS 'Department!3!Name' FROM Company c INNER JOIN Department d ON.

Read more

LINQ 2 SQL

Posted by: RcBuilder  /   Category: ADO.NET / button / Button / DBA / div / GridView / HTML / LINQ 2 SQL / SOURCE CODE / SQL   /   No Comments
09
Nov
2014

LINQ 2 SQL STEPS 1. add new Item -> Linq To SQL Classes (.dbml) 2. open server explorer -> create new connection to the desired dataBase 3. drag-drop the tables you want to map to objects remark: you can also drag stored Procedures, views and more ... 4. add -> associations (create the connections between the tables - foreign keys) 5. use.

Read more

SQL PIVOT

Posted by: RcBuilder  /   Category: DATES / DBA / SQL / SQL Data Types / SQL Function / Stored Procedure   /   No Comments
07
Oct
2014

SQL PIVOT creates an horizontal group by. MUST define the groups names. structure pivot(<aggregation function> FOR <group by column> IN (<group by column values>)) USING example 1: declare @tbl1 table(Name nvarchar(10), Quantity int) insert into @tbl1 SELECT 'Roby', 100 UNION ALL SELECT 'Avi', 40 UNION ALL SELECT 'Avi', 60 UNION ALL SELECT 'Avi', 15 UNION ALL SELECT 'Ron', 50 UNION ALL SELECT 'Roby', 77 UNION ALL SELECT 'Roby', 12 UNION.

Read more

SQL COUNT NULLIF

Posted by: RcBuilder  /   Category: DBA / SQL / SQL Function / Stored Procedure / System Function   /   No Comments
28
Sep
2014

SQL COUNT NULLIF // nullif return null if the condition is true // use this technique to count multiple counters in a single group by clause declare @tbl table(Id int, isOK bit, Grade tinyint) insert into @tbl select 1, 1, 50 union all select 1, 1, 75 union all select 4, 0, 25 union all select 6, 1, 54 union all select 6, 0,.

Read more

SQL aggregation with NULL values

Posted by: RcBuilder  /   Category: Aggregation / DBA / SQL   /   No Comments
28
May
2014

SQL aggregation functions with null values all aggregation sql functions count only non null rows!! therefore, we can use this rule in order to filter out unwanted rows examples: // sum only firstDeposit rows SUM(case when firstDeposit = 0 then null else USDValue end) // count only firstDeposit rows // if firstDeposit = 0 return NULL COUNT(NULLIF(0,.

Read more

SQL UNION Example

Posted by: RcBuilder  /   Category: DBA / SQL / SQL Function / Stored Procedure   /   No Comments
28
May
2014

SQL UNION Example declare @t1 table(Name varchar(20)) insert into @t1 select 'ROBY' UNION ALL select 'STEVE' UNION ALL select 'YOSSI' UNION ALL select 'RON' UNION ALL select 'OFIR' declare @t2 table(Name varchar(20)) insert into @t2 select 'TAL' UNION ALL select 'ITAY' UNION ALL select 'SHIRLY' ;with temp(Name) AS( select * from @t1 UNION ALL select * from @t2 ) select * from.

Read more

SQL CTE with insert into Example

Posted by: RcBuilder  /   Category: CTE / DBA / SQL / Stored Procedure   /   No Comments
28
May
2014

SQL CTE with insert into Example declare @Depositors table(BURowID int, FTDDepositors int, Depositors int) ;with CTE_Depositors(OwningBusinessUnit, AccountId, FTDDepositors, Depositors) AS( select OwningBusinessUnit, lv_accountid, COUNT(case when lv_firsttimedeposit = 0 then null else 1 end), COUNT(1) from Leadercapital_MSCRM.dbo.lv_monetarytransactionBase group by OwningBusinessUnit, lv_accountid ) insert into @Depositors select BU.RowID, COUNT(D.FTDDepositors), COUNT(D.Depositors) from @BusinessUnits BU, CTE_Depositors D where BU.BusinessUnitId = D.OwningBusinessUnit -- join -- group by.

Read more

SQL CTE with UNION Example

Posted by: RcBuilder  /   Category: CTE / DBA / SQL / SQL Function / Stored Procedure   /   No Comments
28
May
2014

SQL CTE with UNION Example declare @TradingDataOrders table(TPName nvarchar(100), TradingVolume money) ;with CTE_trades(TPName, TradingVolume) AS( select CAST(ClientNumber AS varchar(10)), Volume from dbo.ClosedTrades where (OpenTime between @dtFrom and @dtTo) OR (CloseTime between @dtFrom and @dtTo) UNION ALL select CAST(ClientNumber AS varchar(10)), Volume from dbo.OpenTrades where OpenTime between @dtFrom and @dtTo ) insert into @TradingDataOrders select * from.

Read more
12
  • פיתוח מערכות
  • פתרונות טכנולוגים
  • קידום אתרים
  • בניית אתרים