Sunday, August 3, 2014

Intersect

The Intersect functionality is of the three Venn Collection functionality's (EXCEPT, INTERSECT, UNION).
Intersect will give all the members of SET1 which are also in SET2. By default it will remove any duplicates.
SELECT
    INTERSECT        (
            {
                 [Date].[Calendar Year].&[2010]
                ,[Date].[Calendar Year].&[2012]
                ,[Date].[Calendar Year].&[2013]
                ,[Date].[Calendar Year].&[2013]
            } //SET 1
            ,
            {
                  [Date].[Calendar Year].&[2012]
                 ,[Date].[Calendar Year].&[2013]
                 ,[Date].[Calendar Year].&[2013]
                 ,[Date].[Calendar Year].&[2014]
            } //SET 2
          ) ON COLUMNS,
        [Measures].[Sales Amount] on ROWS
FROM
[Adventure Works]
Will get you:
image
(Only [CY 2012] and [CY2013] are in both sets)
If we add the ALL flag duplicates will be visible:
SELECT
    INTERSECT
        (
            {
                 [Date].[Calendar Year].&[2010]
                ,[Date].[Calendar Year].&[2012]
                ,[Date].[Calendar Year].&[2013]
                ,[Date].[Calendar Year].&[2013]
            } //SET 1
            ,
            {
                  [Date].[Calendar Year].&[2012]
                 ,[Date].[Calendar Year].&[2013]
                 ,[Date].[Calendar Year].&[2013]
                 ,[Date].[Calendar Year].&[2014]
            } //SET 2
            , ALL 
        ) ON COLUMNS,
        [Measures].[Sales Amount] on ROWS
FROM
[Adventure Works]
image
([CY2013] is a duplicate, it doesn’t matter in which set it’s a duplicate.)
Have Fun And Till Next Time

1 comment:

  1. The best live casino sites | Lucky Club
    Lucky Club has the best live dealer casino. Get all you need to know about online casino games and bonuses available luckyclub at the best online casinos in the UK.

    ReplyDelete