August 19, 2014 at 11:22 am
Hi,
I have created a common table expression. The first query is meant to select a particular months worth of data. The second query is meant to select the following months data but only for the subscribers (MSISDN's) that appeared in the first month.
The query returns result for month 1 but ignores the second month. If I remove the last inner join to the CTE (INNER JOIN Test_Table tt ON tt.MSISDN = B.SUBSCRIBER_NUMBER) then I get a full list for both months but obviously this isn't limited to those MSISDN's in the first month.
The second issue that I'm having is that I can't select a sample of say only a 1000 MSISDN's from the first month...
WITH Test_Table (Report_Month, MSISDN, SUB_SEGMENT)
AS
(
Select
R.Report_Month,
R.MSISDN,
DV.SUB_SEGMENT
FROM REVENUE.MONTHLY_VOUCHER_NEW PARTITION (VOUCHER_201406) R
INNER JOIN SUBSCRIBER.DAILY_DV_NEW PARTITION (DV_20140701) DV ON (R.MSISDN = DV.SUBSCRIBER_NUMBER)
WHERE DV.SUB_SEGMENT = 'DIAMOND'
UNION ALL
Select
A.Report_Month,
A.MSISDN,
B.SUB_SEGMENT
FROM REVENUE.MONTHLY_VOUCHER_NEW PARTITION (VOUCHER_201407) A
INNER JOIN SUBSCRIBER.DAILY_DV_NEW PARTITION (DV_20140801) B ON (A.MSISDN = B.SUBSCRIBER_NUMBER)
INNER JOIN Test_Table tt ON tt.MSISDN = B.SUBSCRIBER_NUMBER
WHERE B.SUB_SEGMENT = 'DIAMOND'
)
Select * from Test_Table
Any help would be appreciated!
August 19, 2014 at 11:49 am
Can you provide table structures and sample data that will illustrate the issue?
This will help people see the issue and better be equipped to help you.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 19, 2014 at 12:06 pm
Sure thing...
create table dbo.test_create-- creates empty table
(
Report_Monthint,
MSISDNfloat,
SUB_SEGMENTvarchar(100),
)
insert into dbo.test_create (Report_Month, MSISDN,SUB_SEGMENT)
VALUES (201406,0721312144,'Silver'),
(201406,0521312144,'Silver'),
(201406,0621312144,'Gold'),
(201406,0821312144,'Silver'),
(201406,0921312144,'Silver'),
(201407,0721312144,'Silver'),
(201407,0521312144,'Silver'),
(201407,0621312144,'Gold'),
(201407,0777892144,'Silver'),
(201407,972649133,'Silver')
The final result should only have MSISDN's:
721312144
521312144
621312144
As these are the only ones that exist in the first month but not in the second.
Thanks
August 19, 2014 at 12:09 pm
Which table is test_create supposed to represent?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 19, 2014 at 12:17 pm
Its meant to be a combination of two months worth of data.
If an MSISDN is in both months then the final output would be:
Report_Month || MSISDN || SUB_SEGEMNT
201406 || 721312144 || Silver
201407 || 721312144 || Silver
etc.
August 19, 2014 at 12:20 pm
I mean, does the test_create table that you provided represent
REVENUE.MONTHLY_VOUCHER_NEW
or
SUBSCRIBER.DAILY_DV_NEW PARTITION
or
Test_Table
from your original query that you posted?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 19, 2014 at 12:26 pm
Oh sorry, it represents SUBSCRIBER.DAILY_DV_NEW PARTITION
The other table is has some sort of usage which has been aggregated. This is the REVENUE.MONTHLY_VOUCHER_NEW
create table dbo.test_create_2-- creates empty table
(
Report_Monthint,
MSISDNfloat,
Usagefloat
)
insert into dbo.test_create_2 (Report_Month, MSISDN,Usage)
VALUES (201406,0721312144,1213),
(201406,0521312144,8674),
(201406,0621312144,43245),
(201406,0821312144,6653),
(201406,0921312144,9997),
(201407,0721312144,82654),
(201407,0521312144,38462),
(201407,0621312144,89053),
(201407,0777892144,355677),
(201407,972649133,34567)
Select * from dbo.test_create_2
August 19, 2014 at 1:21 pm
mic.con87 (8/19/2014)
Its meant to be a combination of two months worth of data.If an MSISDN is in both months then the final output would be:
Report_Month || MSISDN || SUB_SEGEMNT
201406 || 721312144 || Silver
201407 || 721312144 || Silver
etc.
I am confused about the above, because earlier you said:
The final result should only have MSISDN's:
721312144
521312144
621312144
As these are the only ones that exist in the first month but not in the second
The former statement said that 721312144 was in the first month but not the second. Could we get some clarification?
August 19, 2014 at 1:41 pm
I get even more confused as I reread your posts and the sample tables and data you posted.
I'm not sure if what you posted matches your original post, and I am not talking just in the naming of the tables.
What would really help is if you would create a set of scripts that creates and populates the tables involved, and then rewrite the code you posted originally to use those tables to demonstrate the problem.
Also, if you could post a table and populate it directly with what the actual results should be based on the sample tables and data.
To make sure these all work before posting, run them yourself in an empty database. This will give you confidence that you are showing us what is going on, and we will be sure to have working code and sample data with which to work.
August 19, 2014 at 1:50 pm
mic.con87 (8/19/2014)
Its meant to be a combination of two months worth of data.If an MSISDN is in both months then the final output would be:
Report_Month || MSISDN || SUB_SEGEMNT
201406 || 721312144 || Silver
201407 || 721312144 || Silver
etc.
Using this as a basis for an answer and your test tables and sample data, the following answers this post:
declare @FirstMonth int = 201406,
@SecondMonth int = 201407;
with CombinedData as (
select
MSISDN
from
dbo.test_create
where
Report_Month = @FirstMonth
intersect
select
MSISDN
from
dbo.test_create
where
Report_Month = @SecondMonth
)
select
*
from
dbo.test_create tc
where
exists (select * from CombinedData cd where cd.MSISDN = tc.MSISDN)
order by
tc.MSISDN,
tc.Report_Month;
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply