Geek Answers Handbook

Correctly use the Count function

In the Enrollment_Changes table, the specified phone model is the phone in which the subscriber changed FROM to this date.

If subsequent changes to Enrollment_Changes do not occur, the phone that changed TO is listed in the P_Enrollment table

For example, subscriber 12345678 entered on 1/5/2011 with RAZR. 11/1/2011 he changed FROM SIZE. You can see that it changed TO with the next transaction to Enrollment_Changes on 05/19/2012.

How would you find a submarine count first credited to the iPhone 3?

Here is the code I created to create the tables

Create Tables: TBL 1

USE [Test2] GO /****** Object: Table [dbo].[P_ENROLLMENT] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[P_ENROLLMENT]( [Subid ] [float] NULL, [Enrollment_Date] [datetime] NULL, [Channel] [nvarchar](255) NULL, [Region] [nvarchar](255) NULL, [Active_Status] [float] NULL, [Drop_Date] [datetime] NULL, [Phone_Model] [nvarchar](255) NULL ) ON [PRIMARY] GO

TBL 2

 USE [Test2] GO /****** Object: Table [dbo].[ENROLLMENT_CHANGES] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ENROLLMENT_CHANGES]( [Subid] [float] NULL, [Cdate] [datetime] NULL, [Phone_Model] [nvarchar](255) NULL ) ON [PRIMARY] GO

Insert TBL1

 INSERT INTO [P_ENROLLMENT]([Subid ], [Enrollment_Date], [Channel], [Region], [Active_Status], [Drop_Date], [Phone_Model]) VALUES(12345678, '2011-01-05 00:00:00', 'Retail', 'Southeast', 1, NULL, 'iPhone 4'); INSERT INTO [P_ENROLLMENT]([Subid ], [Enrollment_Date], [Channel], [Region], [Active_Status], [Drop_Date], [Phone_Model]) VALUES(12346178, '2011-03-13 00:00:00', 'Indirect Dealers', 'West', 1, NULL, 'HTC Hero'); INSERT INTO [P_ENROLLMENT]([Subid ], [Enrollment_Date], [Channel], [Region], [Active_Status], [Drop_Date], [Phone_Model]) VALUES(12346679, '2011-05-19 00:00:00', 'Indirect Dealers', 'Southeast', 0, '2012-03-15 00:00:00', 'Droid 2'); INSERT INTO [P_ENROLLMENT]([Subid ], [Enrollment_Date], [Channel], [Region], [Active_Status], [Drop_Date], [Phone_Model]) VALUES(12347190, '2011-07-25 00:00:00', 'Retail', 'Northeast', 0, '2012-05-21 00:00:00', 'iPhone 4'); INSERT INTO [P_ENROLLMENT]([Subid ], [Enrollment_Date], [Channel], [Region], [Active_Status], [Drop_Date], [Phone_Model]) VALUES(12347701, '2011-08-14 00:00:00', 'Indirect Dealers', 'West', 1, NULL, 'HTC Hero'); INSERT INTO [P_ENROLLMENT]([Subid ], [Enrollment_Date], [Channel], [Region], [Active_Status], [Drop_Date], [Phone_Model]) VALUES(12348212, '2011-09-30 00:00:00', 'Retail', 'West', 1, NULL, 'Droid 2'); INSERT INTO [P_ENROLLMENT]([Subid ], [Enrollment_Date], [Channel], [Region], [Active_Status], [Drop_Date], [Phone_Model]) VALUES(12348723, '2011-10-20 00:00:00', 'Retail', 'Southeast', 1, NULL, 'Southeast'); INSERT INTO [P_ENROLLMENT]([Subid ], [Enrollment_Date], [Channel], [Region], [Active_Status], [Drop_Date], [Phone_Model]) VALUES(12349234, '2012-01-06 00:00:00', 'Indirect Dealers', 'West', 0, '2012-02-14 00:00:00', 'West'); INSERT INTO [P_ENROLLMENT]([Subid ], [Enrollment_Date], [Channel], [Region], [Active_Status], [Drop_Date], [Phone_Model]) VALUES(12349745, '2012-01-26 00:00:00', 'Retail', 'Northeast', 0, '2012-04-15 00:00:00', 'HTC Hero'); INSERT INTO [P_ENROLLMENT]([Subid ], [Enrollment_Date], [Channel], [Region], [Active_Status], [Drop_Date], [Phone_Model]) VALUES(12350256, '2012-02-11 00:00:00', 'Retail', 'Southeast', 1, NULL, 'iPhone 4'); INSERT INTO [P_ENROLLMENT]([Subid ], [Enrollment_Date], [Channel], [Region], [Active_Status], [Drop_Date], [Phone_Model]) VALUES(12350767, '2012-03-02 00:00:00', 'Indirect Dealers', 'West', 1, NULL, 'Sidekick'); INSERT INTO [P_ENROLLMENT]([Subid ], [Enrollment_Date], [Channel], [Region], [Active_Status], [Drop_Date], [Phone_Model]) VALUES(12351278, '2012-04-18 00:00:00', 'Retail', 'Midwest', 1, NULL, 'iPhone 3'); INSERT INTO [P_ENROLLMENT]([Subid ], [Enrollment_Date], [Channel], [Region], [Active_Status], [Drop_Date], [Phone_Model]) VALUES(12351789, '2012-05-08 00:00:00', 'Indirect Dealers', 'West', 0, '2012-07-04 00:00:00', 'iPhone 3'); INSERT INTO [P_ENROLLMENT]([Subid ], [Enrollment_Date], [Channel], [Region], [Active_Status], [Drop_Date], [Phone_Model]) VALUES(12352300, '2012-06-24 00:00:00', 'Retail', 'Midwest', 1, NULL, 'Droid 2'); INSERT INTO [P_ENROLLMENT]([Subid ], [Enrollment_Date], [Channel], [Region], [Active_Status], [Drop_Date], [Phone_Model]) VALUES(12352811, '2012-06-25 00:00:00', 'Retail', 'Southeast', 1, NULL, 'Sidekick');

Insert TBL2

 INSERT INTO [ENROLLMENT_CHANGES]([Subid], [Cdate], [Phone_Model]) VALUES(12345678, '2011-11-01 00:00:00', 'RAZR'); INSERT INTO [ENROLLMENT_CHANGES]([Subid], [Cdate], [Phone_Model]) VALUES(12346178, '2012-01-07 00:00:00', 'HTC Hero'); INSERT INTO [ENROLLMENT_CHANGES]([Subid], [Cdate], [Phone_Model]) VALUES(12348723, '2012-01-28 00:00:00', 'RAZR'); INSERT INTO [ENROLLMENT_CHANGES]([Subid], [Cdate], [Phone_Model]) VALUES(12350256, '2012-02-21 00:00:00', 'Blackberry Bold'); INSERT INTO [ENROLLMENT_CHANGES]([Subid], [Cdate], [Phone_Model]) VALUES(12349745, '2012-05-05 00:00:00', 'HTC Hero'); INSERT INTO [ENROLLMENT_CHANGES]([Subid], [Cdate], [Phone_Model]) VALUES(12345678, '2012-05-19 00:00:00', 'Palm Pre'); INSERT INTO [ENROLLMENT_CHANGES]([Subid], [Cdate], [Phone_Model]) VALUES(12347190, '2012-05-20 00:00:00', 'HTC Hero'); INSERT INTO [ENROLLMENT_CHANGES]([Subid], [Cdate], [Phone_Model]) VALUES(12350256, '2012-05-21 00:00:00', 'Blackberry Bold'); INSERT INTO [ENROLLMENT_CHANGES]([Subid], [Cdate], [Phone_Model]) VALUES(12349234, '2012-06-04 00:00:00', 'Palm Pre'); INSERT INTO [ENROLLMENT_CHANGES]([Subid], [Cdate], [Phone_Model]) VALUES(12346178, '2012-06-05 00:00:00', 'iPhone 3'); INSERT INTO [ENROLLMENT_CHANGES]([Subid], [Cdate], [Phone_Model]) VALUES(12350767, '2012-06-10 00:00:00', 'iPhone 3');
+7
source share
3 answers

For counting

 select COUNT(*) Total from ( select e.*, rn = row_number() over (partition by e.subid order by c.cdate desc), first_model = coalesce(c.phone_model, e.phone_model) from [P_ENROLLMENT] e left join [ENROLLMENT_CHANGES] c on c.subid = e.subid ) x where rn=1 and first_model = 'iPhone 3'

For all entries
 select * from ( select e.*, rn = row_number() over (partition by e.subid order by c.cdate desc), first_model = coalesce(c.phone_model, e.phone_model) from [P_ENROLLMENT] e left join [ENROLLMENT_CHANGES] c on c.subid = e.subid ) x where rn=1 and first_model = 'iPhone 3' order by subid
+4
source

You want to know if the first entry is in the iPhone 3 table. Something like this:

 select count(*) from (select e.*, row_number() over (partition by subid order by enrollment_date) as seqnum from p_enrollment e ) e where seqnum = 1 and phone_model = 'iPhone 3'
+1
source

Perhaps I think too simply, but would not fulfill what you are looking for?

 SELECT Phone_Model , COUNT(*) AS Initially_Enrolled FROM p_enrollment GROUP BY Phone_Model

(working SQLFiddle: http://sqlfiddle.com/#!3/68258/4 )

or

 SELECT COUNT(*) AS Initially_Enrolled FROM p_enrollment WHERE Phone_Model = 'iPhone 3'

(working SQLFiddle: http://sqlfiddle.com/#!3/68258/3 )

Since you only want the initial registration, the ENROLLMENT_CHANGES table does not matter.

+1
source

More articles:


All Articles