I'd rather use the CROSS APPLY approach to avoid "climbing up" two levels for each article node to get the name element. Performance impact can be significant depending on the...

Koen Verbeeck (1/4/2012)

---

Allright, I missed a few weeks of the thread because my wife gave birth to a wonderful son. (and thus killing my regular sleeping pattern)

Did I miss anything...

I think I finally understood what you're looking for...

Will this be closer?

;

WITH cte AS

(

SELECT

LOB,

[Period] = [App Week],

[Bin Type] = BinTypes.BinType,

[Bin] = -- one row per original temp table

CASE

WHEN...

Would you please change the sample data you posted to reflect the columns you're using in your latest reply (e.g. [Homeowner Bin])?

The problem you describe seems to different to the code you've posted.

In the code provided the two sections don't have enough in common: one is using the CUBE operator whereas...

Here's the "straight forward" approach without the temp tables:

;

WITH cte AS

(

SELECT

LOB,

[Period] = [App Week],

[Bin Type] = 'Time to Fund',

[Bin] = [Time to Fund Bin],

Applications = SUM(Applications)

FROM #APPS AS a

GROUP BY

a.LOB,

[App...

I think there's an easier solution to do it. But I'd like to test it before posting to make sure the code works as intended.

Therefore I'd like you to provide...

Are you looking for something along those lines?

;

WITH cte AS

(

SELECT CAST(impressionDate AS DATE) AS supplier_date, count(*) AS impression_cnt

FROM [tbl_supplier_impressions]

GROUP BY CAST(impressionDate AS DATE)

),

cte2 AS

(

SELECT ...

Based on your expected output requested eariler:

date | impressions | clicks | supplier id

12/2011 | 10 | 2 | guid

11/2011 | 8 | 5 | guid

10/2011 | 20 | 4...

Based on your sampe data, what would be your expected result?

It's partially unclear since your expected result is grouped by month but also showing supplierid as part of the result...

In that article is a section describing how to insert sample data (INSERT INTO ... SELECT) 😉

As mentioned earlier: please read and follow the advice given in the first article in my signature.

We're all volunteers here trying to help. So it would be great if you...

A reference table with UserId and ItemsId (both integer values) with a clustered index on UserId and ItemsId should be easy enough to query and should perform pretty good (assuming...

Please provide DDL and sample data as well as expected output and what you've tried so far.

For a detailed description on how to provide data in a ready to use...

duplicate post. no replies please (see original post)