Thursday, March 15, 2012

Diffrence between CROSS APPLY and OUTER APPLY of SQL XML


Sample XML :
DECLARE @XML XML =
'<Root>
<UserInfo Id="1" Name="Name 1">
<CityInfo Id="2" City="City 1" />
<CityInfo Id="3" City="City 2" >
<PrefInfo Id="33" Name="Show Whether" />
</CityInfo>
</UserInfo>
<UserInfo Id="2" Name="Name 2">
<CityInfo Id="4" City="City 3" />
<CityInfo Id="5" City="City 4" >
<PrefInfo Id="33" Name="Show Temprature" />
</CityInfo>
</UserInfo>
</Root>'


Cross Apply : Cross apply is basically use inner join of two xml collection
SELECT
UserInfo.value('@Id', 'BIGINT') as UserId
, UserInfo.value('@Name', 'VARCHAR(20)') as UserName
, CityInfo.value('@Id', 'BIGINT') as CityId
, CityInfo.value('@City', 'VARCHAR(20)') as CityName
, PrefInfo.value('@Id', 'BIGINT') as PrefId
, PrefInfo.value('@Name', 'VARCHAR(20)') as PrefName
FROM @xml.nodes('/Root/UserInfo')e(UserInfo)
CROSS APPLY UserInfo.nodes('CityInfo')b(CityInfo)
CROSS APPLY CityInfo.nodes('PrefInfo')c(PrefInfo)

Output :

UserId UserName CityId CityName PrefId PrefName
-------- --------- ------- --------- -------- ----------------
1 Name 1 3 City 2 33 Show Whether
2 Name 2 5 City 4 33 Show Temprature

Outer Apply : Outer apply is basically use left outer join of two xml collection
SELECT
UserInfo.value('@Id', 'BIGINT') as UserId
, UserInfo.value('@Name', 'VARCHAR(20)') as UserName
, CityInfo.value('@Id', 'BIGINT') as CityId
, CityInfo.value('@City', 'VARCHAR(20)') as CityName
, PrefInfo.value('@Id', 'BIGINT') as PrefId
, PrefInfo.value('@Name', 'VARCHAR(20)') as PrefName
FROM @xml.nodes('/Root/UserInfo')e(UserInfo)
CROSS APPLY UserInfo.nodes('CityInfo')b(CityInfo)
OUTER APPLY CityInfo.nodes('PrefInfo')c(PrefInfo)

Output :
UserId UserName CityId CityName PrefId PrefName
------- --------- ------- --------- ------- ----------------
1 Name 1 2 City 1 NULL NULL
1 Name 1 3 City 2 33 Show Whether
2 Name 2 4 City 3 NULL NULL
2 Name 2 5 City 4 33 Show Temprature

Wednesday, March 14, 2012

How to Handle button click event in jquery

Code :

<script type="text/javascript" language="javascript">
$(document).ready(function () {
$("[id*='btnSave']").click(function (event) {
event.preventDefault();
if (confirm("Want to save data?")) {
$(this).unbind('click').click()
}
});

});
</script>

Tuesday, September 20, 2011

jQuery Selector


#No Syntax Description
---------------------------------------------------------------------------------------
1. $(this) Current HTML element
2. $(".intro") All elements with class="intro"
3. $("#intro") The first element with id="intro"
4. $("p") All <p> elements
5. $("p.intro") All <p> elements with class="intro"
6. $("p#intro") All <p> elements with id="intro"
7. $("p#intro:first-child") The first <p> element with id="intro"
8. $("ul li:first-child") The first <li> element of each <ul>
9. $("ul li:last-child") The last <li> element of each <ul>
10. $("ul li:nth-child(4)") The fourth <li> element of each <ul>
11. $("div#intro .head") All elements with class="head" and id="intro" of DIV
12. $("[href*='User']") All elements with href contains "User"
13. $("[href^='User']") All elements with href start with "User"
14. $("[href$='.html']") All elements with an href attribute
that ends with ".html"
15. $("[href*='User'] div") AND condition for Getting all element
which have href contains "User" and inner element div
16. $("[href*='User'],div") OR condition for Getting all element
which have href contains "User" or div element
17. $("[href!='UserInfo.html']") NOT condition for Getting all element
which have href not equle to "UserInfo.html"
18. $("div > input#User") Getting all element which have a parent element is
DIV and next element is INPUT have a id User
19. $("div").find("input#User") Getting all element of parent element is
DIV and child element is INPUT have a id User
20. $("div").not(".UserInfo, #UserId") Getting all div element which not have a
class USERINFO or id is USERID

Monday, August 29, 2011

Rounding date in SQL query


Query :
SELECT
DATEADD( MI, DATEDIFF( MI, 0, DATEADD( SS, 30, Dates.SampleDate) ), 0) AS RoundedDate
FROM
(
SELECT SampleDate = CONVERT( DATETIME, '8/29/2011 12:59:29.998')
UNION ALL
SELECT SampleDate = CONVERT( DATETIME, '8/29/2011 12:59:30.000')
) Dates

Results :

RoundedDate
-----------------------
2011-08-29 12:59:00.000
2011-08-29 13:00:00.000

Friday, July 1, 2011

Use of Rollup, Grouping, Grouping_ID and Cube in Group By


ROLLUP = Generates the simple GROUP BY aggregate rows
, plus subtotal or super-aggregate rows
, and also a grand total row.
GROUPING = Indicates whether a specified column expression in a GROUP BY list is aggregated or not.
GROUPING_ID = Is a function that computes the level of grouping.
CUBE = Generates simple GROUP BY aggregate rows, the ROLLUP super-aggregate rows, and cross-tabulation rows.

Sample Data

DECLARE @Sales TABLE (Year INT, Quarter VARCHAR(50), SalesPerson VARCHAR(50), Amount FLOAT)

INSERT INTO @Sales values
(1998, 'Q1', 'SalePerson1', 123)
, (1998, 'Q2', 'SalePerson2', 234)
, (1998, 'Q3', 'SalePerson4', 345)
, (1998, 'Q4', 'SalePerson3', 556)
, (1999, 'Q1', 'SalePerson1', 623)
, (1999, 'Q2', 'SalePerson2', 734)
, (1999, 'Q3', 'SalePerson3', 845)
, (1999, 'Q4', 'SalePerson4', 956)

Example 1 : (Single field in grouping)

SELECT
[Year]
, AVG(Amount) as Average
, GROUPING([Year]) as [YearRollUp?]
FROM
@Sales
GROUP BY
[Year] WITH ROLLUP

Output :

Year Average YearRollUp?
----------- ---------------------- -----------
1998 314.5 0
1999 789.5 0
NULL 552 1

Example 2 : (Multipal field in grouping)

SELECT
[Year]
, Quarter
, AVG(Amount) as Average
, GROUPING( [Year] ) as [YearRollUp?]
FROM
@Sales
GROUP BY
GROUPING SETS (( [Year], Quarter), ( [Year] ), ())

Output :

Year Quarter Average YearRollUp?
----------- ------- ---------------------- -----------
1998 Q1 123 0
1998 Q2 234 0
1998 Q3 345 0
1998 Q4 556 0
1998 NULL 314.5 0
1999 Q1 623 0
1999 Q2 734 0
1999 Q3 845 0
1999 Q4 956 0
1999 NULL 789.5 0
NULL NULL 552 1

Example 3 :( Example of Grouping_ID )

SELECT
[Year]
, Quarter
, AVG(Amount) as Average
, GROUPING_ID( [Year], Quarter) as [x]
FROM
@Sales
GROUP BY
GROUPING SETS (( [Year], Quarter), ( [Year] ), ( [Quarter] ), ())

Output :

Year Quarter Average x
----------- ------- ---------------------- -----------
1998 Q1 123 0
1999 Q1 623 0
NULL Q1 373 2
1998 Q2 234 0
1999 Q2 734 0
NULL Q2 484 2
1998 Q3 345 0
1999 Q3 845 0
NULL Q3 595 2
1998 Q4 556 0
1999 Q4 956 0
NULL Q4 756 2
NULL NULL 552 3
1998 NULL 314.5 1
1999 NULL 789.5 1

Example 4:(Example of Cube)

SELECT
[Year]
, [Quarter]
, AVG(Amount) as Average
, GROUPING_ID( [Year], [Quarter]) as [x]
FROM
@Sales
GROUP BY
CUBE( [Year], [Quarter])

Output :

Year Quarter Average x
----------- ------- ---------------------- -----------
1998 Q1 123 0
1999 Q1 623 0
NULL Q1 373 2
1998 Q2 234 0
1999 Q2 734 0
NULL Q2 484 2
1998 Q3 345 0
1999 Q3 845 0
NULL Q3 595 2
1998 Q4 556 0
1999 Q4 956 0
NULL Q4 756 2
NULL NULL 552 3
1998 NULL 314.5 1
1999 NULL 789.5 1

Thursday, June 30, 2011

Query with static output

Query :



select * from
(
values (1, 2), (2, 3), (3, 4)
) as MyTable(Col1, Col2)

Output :


Col1 Col2
----------- -----------
1 2
2 3
3 4

Thursday, April 28, 2011

Genrate table to ENUM


DECLARE @TableName  VARCHAR(20) = 'tblFileType'        -------------> type table name

DECLARE @ValueField VARCHAR(20) = 'Id'                -------------> type ValueField Name

DECLARE @NameField  VARCHAR(20) = 'Name'            -------------> type NAme Field Name



BEGIN

    DECLARE @SqlQuery VARCHAR(MAX) = 

    'SELECT ''public enum enum' + REPLACE( @TableName, 'tbl''')  + ''' AS enum 

    UNION ALL

    SELECT ''{''

    UNION ALL

    SELECT ( REPLACE('
 + @NameField + ', '' '', '''') + '' = '' + CAST( ' + @ValueField + ' AS VARCHAR(10) ) ) + '','' FROM ' + @TableName

    + ' UNION ALL SELECT ''}'' ' 

    

    PRINT @SqlQuery

    EXECUTE( @SqlQuery )

    -- SELECT Name + ' = ' + CAST( Id AS VARCHAR(10) ) + ',' FROM tblUserRoll

END