SQL assistance

Agreed… 

Love the detail.  I have never used cross apply before.  Mwah ha ha….

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Chris Jensen
Sent: Tuesday, April 19, 2016 5:52 PM
To: Dot Krebs
Subject: Re: [Tessitura Technical Forum] SQL assistance

Nick Reilingh:

If you've never used cross apply/outer apply before, they're basically just like inner/outer joins, except instead of having a table or subquery for the entire join (which you match using an ON clause), you get to join to the entire subquery for every single row in the table you're joining to.

Nice.

Nick,

Your assessment is close.

We want one line for each customer and the last campaign to which they donated. 

I’m playing with the cross apply.  We’ll see. 

Dot

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Nick Reilingh
Sent: Tuesday, April 19, 2016 5:41 PM
To: Dot Krebs
Subject: Re: [Tessitura Technical Forum] SQL assistance

You want one row per campaign that contains data from only the most recent contribution in that campaign, right?

My approach to this would be to have my primary SELECT FROM be for VS_CAMPAIGN, and then join to that using a CROSS APPLY where the subquery does a TOP 1 ORDER BY cont_dt DESC.

If you've never used cross apply/outer apply before, they're basically just like inner/outer joins, except instead of having a table or subquery for the entire join (which you match using an ON clause), you get to join to the entire subquery for every single row in the table you're joining to.

SO it would be something like this:

SELECT a.campaign_no, a.description, topcont.*

  FROM VS_CAMPAIGN a

  CROSS APPLY ( SELECT TOP 1 *

                  FROM VS_CONTRIBUTION b

                  WHERE b.campaign_no = a.campaign_no

                  ORDER BY b.cont_dt DESC ) AS topcont

Dot

This is a quick way of doing this, it might not be the most efficient though.

SELECT *

FROM (

    SELECT *, pos = ROW_NUMBER() OVER(PARTITION BY customer_no ORDER BY customer_no, cont_dt DESC)

    FROM T_CONTRIBUTION

  ) a

WHERE a.pos = 1

Fernando Margueirat
Senior Business Analyst
The National Ballet of Canada
470 Queens Quay West
Toronto, Ontario
M5V 3K4
P: 416 345 9686 x453
F: 416 345 8323

http://national.ballet.ca

Nick,

Your assessment is close.

We want one line for each customer and the last campaign to which they donated. 

I’m playing with the cross apply.  We’ll see. 

Dot

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Nick Reilingh
Sent: Tuesday, April 19, 2016 5:41 PM
To: Dot Krebs
Subject: Re: [Tessitura Technical Forum] SQL assistance

You want one row per campaign that contains data from only the most recent contribution in that campaign, right?

My approach to this would be to have my primary SELECT FROM be for VS_CAMPAIGN, and then join to that using a CROSS APPLY where the subquery does a TOP 1 ORDER BY cont_dt DESC.

If you've never used cross apply/outer apply before, they're basically just like inner/outer joins, except instead of having a table or subquery for the entire join (which you match using an ON clause), you get to join to the entire subquery for every single row in the table you're joining to.

SO it would be something like this:

SELECT a.campaign_no, a.description, topcont.*

  FROM VS_CAMPAIGN a

  CROSS APPLY ( SELECT TOP 1 *

                  FROM VS_CONTRIBUTION b

                  WHERE b.campaign_no = a.campaign_no

                  ORDER BY b.cont_dt DESC ) AS topcont

It’s not pretty, but this is what eventually got the job done.  Thanks everyone for teaching me something new. 

SELECT T_CONTRIBUTION.customer_no, Sum(T_CONTRIBUTION.recd_amt) AS recd_amt, T_CONTRIBUTION.campaign_no, T_CAMPAIGN.description, T_CAMPAIGN.fyear INTO [#don]

FROM T_CONTRIBUTION INNER JOIN T_CAMPAIGN ON T_CONTRIBUTION.campaign_no = T_CAMPAIGN.campaign_no

where T_CAMPAIGN.description like '%individual%'

GROUP BY T_CONTRIBUTION.customer_no, T_CONTRIBUTION.campaign_no, T_CAMPAIGN.description, T_CAMPAIGN.fyear;

SELECT [#don].customer_no, Max([#don].fyear) AS MaxOffyear INTO [#maxfy]

FROM [#don]

GROUP BY [#don].customer_no;

SELECT DISTINCT [#don].customer_no, [#don].recd_amt, [#don].campaign_no, [#don].description

FROM [#maxfy] INNER JOIN [#don] ON ([#maxfy].customer_no = [#don].customer_no) AND ([#maxfy].MaxOffyear = [#don].fyear);

Dot

This is a quick way of doing this, it might not be the most efficient though.

SELECT *

FROM (

    SELECT *, pos = ROW_NUMBER() OVER(PARTITION BY customer_no ORDER BY customer_no, cont_dt DESC)

    FROM T_CONTRIBUTION

  ) a

WHERE a.pos = 1

Fernando Margueirat
Senior Business Analyst
The National Ballet of Canada
470 Queens Quay West
Toronto, Ontario
M5V 3K4
P: 416 345 9686 x453
F: 416 345 8323

http://national.ballet.ca

Nick,

Your assessment is close.

We want one line for each customer and the last campaign to which they donated. 

I’m playing with the cross apply.  We’ll see. 

Dot

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Nick Reilingh
Sent: Tuesday, April 19, 2016 5:41 PM
To: Dot Krebs
Subject: Re: [Tessitura Technical Forum] SQL assistance

You want one row per campaign that contains data from only the most recent contribution in that campaign, right?

My approach to this would be to have my primary SELECT FROM be for VS_CAMPAIGN, and then join to that using a CROSS APPLY where the subquery does a TOP 1 ORDER BY cont_dt DESC.

If you've never used cross apply/outer apply before, they're basically just like inner/outer joins, except instead of having a table or subquery for the entire join (which you match using an ON clause), you get to join to the entire subquery for every single row in the table you're joining to.

SO it would be something like this:

SELECT a.campaign_no, a.description, topcont.*

  FROM VS_CAMPAIGN a

  CROSS APPLY ( SELECT TOP 1 *

                  FROM VS_CONTRIBUTION b

                  WHERE b.campaign_no = a.campaign_no

                  ORDER BY b.cont_dt DESC ) AS topcont

Dot

Doesn’t this query return more than one record per customer if the customer made contributions to multiple campaigns that match the “like ‘%individual%’” condition? Is that what you were looking for? I understood you were looking for 1 record per customer.

Fernando Margueirat
Senior Business Analyst
The National Ballet of Canada
470 Queens Quay West
Toronto, Ontario
M5V 3K4
P: 416 345 9686 x453
F: 416 345 8323

http://national.ballet.ca

It’s not pretty, but this is what eventually got the job done.  Thanks everyone for teaching me something new. 

SELECT T_CONTRIBUTION.customer_no, Sum(T_CONTRIBUTION.recd_amt) AS recd_amt, T_CONTRIBUTION.campaign_no, T_CAMPAIGN.description, T_CAMPAIGN.fyear INTO [#don]

FROM T_CONTRIBUTION INNER JOIN T_CAMPAIGN ON T_CONTRIBUTION.campaign_no = T_CAMPAIGN.campaign_no

where T_CAMPAIGN.description like '%individual%'

GROUP BY T_CONTRIBUTION.customer_no, T_CONTRIBUTION.campaign_no, T_CAMPAIGN.description, T_CAMPAIGN.fyear;

SELECT [#don].customer_no, Max([#don].fyear) AS MaxOffyear INTO [#maxfy]

FROM [#don]

GROUP BY [#don].customer_no;

SELECT DISTINCT [#don].customer_no, [#don].recd_amt, [#don].campaign_no, [#don].description

FROM [#maxfy] INNER JOIN [#don] ON ([#maxfy].customer_no = [#don].customer_no) AND ([#maxfy].MaxOffyear = [#don].fyear);

Dot

This is a quick way of doing this, it might not be the most efficient though.

SELECT *

FROM (

    SELECT *, pos = ROW_NUMBER() OVER(PARTITION BY customer_no ORDER BY customer_no, cont_dt DESC)

    FROM T_CONTRIBUTION

  ) a

WHERE a.pos = 1

Fernando Margueirat
Senior Business Analyst
The National Ballet of Canada
470 Queens Quay West
Toronto, Ontario
M5V 3K4
P: 416 345 9686 x453
F: 416 345 8323

http://national.ballet.ca

Nick,

Your assessment is close.

We want one line for each customer and the last campaign to which they donated. 

I’m playing with the cross apply.  We’ll see. 

Dot

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Nick Reilingh
Sent: Tuesday, April 19, 2016 5:41 PM
To: Dot Krebs
Subject: Re: [Tessitura Technical Forum] SQL assistance

You want one row per campaign that contains data from only the most recent contribution in that campaign, right?

My approach to this would be to have my primary SELECT FROM be for VS_CAMPAIGN, and then join to that using a CROSS APPLY where the subquery does a TOP 1 ORDER BY cont_dt DESC.

If you've never used cross apply/outer apply before, they're basically just like inner/outer joins, except instead of having a table or subquery for the entire join (which you match using an ON clause), you get to join to the entire subquery for every single row in the table you're joining to.

SO it would be something like this:

SELECT a.campaign_no, a.description, topcont.*

  FROM VS_CAMPAIGN a

  CROSS APPLY ( SELECT TOP 1 *

                  FROM VS_CONTRIBUTION b

                  WHERE b.campaign_no = a.campaign_no

                  ORDER BY b.cont_dt DESC ) AS topcont

If you want the information for the last contribution each customer made for any campaigns that contains “individual” in its description, this should work.

SELECT a.customer_no, a.ref_no, a.recd_amt, a.campaign_no, a.description

FROM (

    SELECT aa.customer_no,

      aa.ref_no,

      recd_amt = SUM(aa.recd_amt) OVER(PARTITION BY aa.customer_no, aa.campaign_no),

      aa.campaign_no,

      ab.description,

      pos = ROW_NUMBER() OVER(PARTITION BY aa.customer_no ORDER BY aa.customer_no, aa.cont_dt DESC)

    FROM T_CONTRIBUTION aa

      JOIN T_CAMPAIGN ab ON ab.campaign_no = aa.campaign_no

    WHERE ab.description like '%individual%'

  ) a

WHERE a.pos = 1

ORDER BY customer_no

Fernando Margueirat
Senior Business Analyst
The National Ballet of Canada
470 Queens Quay West
Toronto, Ontario
M5V 3K4
P: 416 345 9686 x453
F: 416 345 8323

http://national.ballet.ca

It’s not pretty, but this is what eventually got the job done.  Thanks everyone for teaching me something new. 

SELECT T_CONTRIBUTION.customer_no, Sum(T_CONTRIBUTION.recd_amt) AS recd_amt, T_CONTRIBUTION.campaign_no, T_CAMPAIGN.description, T_CAMPAIGN.fyear INTO [#don]

FROM T_CONTRIBUTION INNER JOIN T_CAMPAIGN ON T_CONTRIBUTION.campaign_no = T_CAMPAIGN.campaign_no

where T_CAMPAIGN.description like '%individual%'

GROUP BY T_CONTRIBUTION.customer_no, T_CONTRIBUTION.campaign_no, T_CAMPAIGN.description, T_CAMPAIGN.fyear;

SELECT [#don].customer_no, Max([#don].fyear) AS MaxOffyear INTO [#maxfy]

FROM [#don]

GROUP BY [#don].customer_no;

SELECT DISTINCT [#don].customer_no, [#don].recd_amt, [#don].campaign_no, [#don].description

FROM [#maxfy] INNER JOIN [#don] ON ([#maxfy].customer_no = [#don].customer_no) AND ([#maxfy].MaxOffyear = [#don].fyear);

Dot

This is a quick way of doing this, it might not be the most efficient though.

SELECT *

FROM (

    SELECT *, pos = ROW_NUMBER() OVER(PARTITION BY customer_no ORDER BY customer_no, cont_dt DESC)

    FROM T_CONTRIBUTION

  ) a

WHERE a.pos = 1

Fernando Margueirat
Senior Business Analyst
The National Ballet of Canada
470 Queens Quay West
Toronto, Ontario
M5V 3K4
P: 416 345 9686 x453
F: 416 345 8323

http://national.ballet.ca

Nick,

Your assessment is close.

We want one line for each customer and the last campaign to which they donated. 

I’m playing with the cross apply.  We’ll see. 

Dot

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Nick Reilingh
Sent: Tuesday, April 19, 2016 5:41 PM
To: Dot Krebs
Subject: Re: [Tessitura Technical Forum] SQL assistance

You want one row per campaign that contains data from only the most recent contribution in that campaign, right?

My approach to this would be to have my primary SELECT FROM be for VS_CAMPAIGN, and then join to that using a CROSS APPLY where the subquery does a TOP 1 ORDER BY cont_dt DESC.

If you've never used cross apply/outer apply before, they're basically just like inner/outer joins, except instead of having a table or subquery for the entire join (which you match using an ON clause), you get to join to the entire subquery for every single row in the table you're joining to.

SO it would be something like this:

SELECT a.campaign_no, a.description, topcont.*

  FROM VS_CAMPAIGN a

  CROSS APPLY ( SELECT TOP 1 *

                  FROM VS_CONTRIBUTION b

                  WHERE b.campaign_no = a.campaign_no

                  ORDER BY b.cont_dt DESC ) AS topcont

You are welcome.

I was in the same position about a year ago, and I actually was looking for a solution for query very similar to the one Dot  asked about, when I discovered them. I became a big fan, because they provide a much simpler way of solving certain types of queries.

Fernando Margueirat
Senior Business Analyst
The National Ballet of Canada
470 Queens Quay West
Toronto, Ontario
M5V 3K4
P: 416 345 9686 x453
F: 416 345 8323

http://national.ballet.ca

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Nick Reilingh
Sent: Wednesday, April 20, 2016 12:40 PM
To: Fernando Margueirat <FMargueirat@national.ballet.ca>
Subject: RE: [Tessitura Technical Forum] SQL assistance

I've been meaning to learn more about window functions--thanks for a simple example!