September 23, 2015 at 8:29 am
I'm trying to figure out how to do the following:
Number of People receiving their second speeding ticket during this time frame
4 Jun 06 -3 Jun 07
4 Jun 07 -3 Jun 08
4 Jun 08 -3 Jun 09
4 Jun 09 -3 Jun 10
4 Jun 10 -3 Jun 11
4 Jun 11 -3 Jun 12
The table would contain historical data and look something like this
CREATE TABLE [dbo].[test](
[person_id] [NCHAR](10) NULL,
[ticket_date] [DATE] NULL,
[ticket] [BIT] NULL
) ON [PRIMARY]
GO
Any assistance would be greatly appreciated Thanks
September 23, 2015 at 8:40 am
jon.wilson (9/23/2015)
I'm trying to figure out how to do the following:Number of People receiving their second speeding ticket during this time frame
4 Jun 06 -3 Jun 07
4 Jun 07 -3 Jun 08
4 Jun 08 -3 Jun 09
4 Jun 09 -3 Jun 10
4 Jun 10 -3 Jun 11
4 Jun 11 -3 Jun 12
The table would contain historical data and look something like this
CREATE TABLE [dbo].[test](
[person_id] [NCHAR](10) NULL,
[ticket_date] [DATE] NULL,
[ticket] [BIT] NULL
) ON [PRIMARY]
GO
Any assistance would be greatly appreciated Thanks
Split your time frames into startdate and enddate, then left join timeframes to test on ticketdate between startdate and enddate.
John
September 23, 2015 at 8:42 am
Would you need something like this?
CREATE TABLE dbo.TimeFrames(
StartDate date,
EndDate date
);
INSERT INTO dbo.TimeFrames
VALUES
('4 Jun 06', '3 Jun 07'),
('4 Jun 07', '3 Jun 08'),
('4 Jun 08', '3 Jun 09'),
('4 Jun 09', '3 Jun 10'),
('4 Jun 10', '3 Jun 11'),
('4 Jun 11', '3 Jun 12');
SELECT * FROM dbo.TimeFrames
CREATE TABLE [dbo].[test](
[person_id] [NCHAR](10) NULL,
[ticket_date] [DATE] NULL,
[ticket] [BIT] NULL
) ON [PRIMARY]
SELECT person_id,
COUNT(*)
FROM dbo.test t
JOIN dbo.TimeFrames tf ON t.ticket_date BETWEEN tf.StartDate AND tf.EndDate
GROUP BY person_id
HAVING COUNT(*) > 1;
GO
DROP TABLE TimeFrames
DROP TABLE test
September 23, 2015 at 8:47 am
Similar to above but I thought I would still post it as I was in the middle of doing it anyways 🙂
SET NOCOUNT ON
DECLARE @TicketRange TABLE
(
DateFrom DATE ,
DateTo DATE
)
INSERT INTO @TicketRange
( DateFrom, DateTo )
VALUES ( '2006-06-04', '2007-06-03' ),
( '2007-06-04', '2008-06-03' ),
( '2008-06-04', '2009-06-03' ),
( '2009-06-04', '2010-06-03' ),
( '2010-06-04', '2011-06-03' ),
( '2011-06-04', '2012-06-03' )
DECLARE @TicketsIssued TABLE
(
person_ID NCHAR(10) NULL ,
ticket_date DATE NULL ,
ticket BIT NULL
)
INSERT INTO @TicketsIssued
( person_ID, ticket_date, ticket )
VALUES ( N'Test1', '2006-06-23', 1 ),
( N'Test2', '2007-06-23', 1 ),
( N'Test3', '2009-07-03', 1 ),
( N'Test4', '2009-06-23', 1 ),
( N'Test1', '2007-03-23', 1 )
SELECT tr.DateFrom ,
tr.DateTo ,
ti.person_ID,
COUNT(ti.person_ID) AS TicketCount
FROM @TicketRange AS tr
INNER JOIN @TicketsIssued AS ti ON ti.ticket_date BETWEEN tr.DateFrom AND tr.DateTo
GROUP BY tr.DateFrom ,
tr.DateTo,
ti.person_ID
HAVING COUNT(ti.person_ID) >1
September 23, 2015 at 9:06 am
I think we're close, I'm looking to count the number of people who have received their 2nd ticket during the given time. Not the number of tickets per individual.
Thanks
EDIT: to clarify
10 people received their second ticket between '2006-06-04', '2007-06-03'
12 people received their second ticket between '2007-06-04', '2008-06-03'
etc...
September 23, 2015 at 9:14 am
Quick and dirty:
SET NOCOUNT ON
DECLARE @TicketRange TABLE
(
DateFrom DATE ,
DateTo DATE
)
INSERT INTO @TicketRange
( DateFrom, DateTo )
VALUES ( '2006-06-04', '2007-06-03' ),
( '2007-06-04', '2008-06-03' ),
( '2008-06-04', '2009-06-03' ),
( '2009-06-04', '2010-06-03' ),
( '2010-06-04', '2011-06-03' ),
( '2011-06-04', '2012-06-03' )
DECLARE @TicketsIssued TABLE
(
person_ID NCHAR(10) NULL ,
ticket_date DATE NULL ,
ticket BIT NULL
)
INSERT INTO @TicketsIssued
( person_ID, ticket_date, ticket )
VALUES ( N'Test1', '2006-06-23', 1 ),
( N'Test2', '2007-06-23', 1 ),
( N'Test3', '2009-07-03', 1 ),
( N'Test4', '2009-06-23', 1 ),
( N'Test1', '2007-03-23', 1 );
WITH MoreThanOneTicketByPerson
AS ( SELECT tr.DateFrom ,
tr.DateTo ,
ti.person_ID ,
COUNT(ti.person_ID) AS TicketCount
FROM @TicketRange AS tr
INNER JOIN @TicketsIssued AS ti ON ti.ticket_date BETWEEN tr.DateFrom AND tr.DateTo
GROUP BY tr.DateFrom ,
tr.DateTo ,
ti.person_ID
HAVING COUNT(ti.person_ID) > 1
)
SELECT CAST(COUNT(*) AS NCHAR(2)) + CASE WHEN COUNT(*) = 1 THEN ' person'
ELSE 'people'
END
+ ' received their second ticket between '
+ CAST(mtotbp.DateFrom AS CHAR(10)) + ' and '
+ CAST(mtotbp.DateTo AS CHAR(10))
FROM MoreThanOneTicketByPerson mtotbp
GROUP BY mtotbp.DateFrom ,
mtotbp.DateTo
Edit : Formatted output
September 23, 2015 at 9:33 am
Thanks, I think I can work with this. One quick question. Suppose the ticket column gets a value of 1 if it is a speeding ticket and a value of 0 if it is any other type of ticket. Wouldn't I just use "where ti.ticket = 1" in the CTE query def.
September 23, 2015 at 9:34 am
Yes, you would 🙂
WHERE ti.ticket = 1
September 23, 2015 at 9:36 am
Thanks for the help
September 23, 2015 at 10:11 am
Perhaps I'm missing something, but if you want to look specifically at the second ticket, then COUNT is the wrong aggregate. Try the following.
WITH Ticket_Number AS (
SELECT ti.person_ID, ticket_date, ROW_NUMBER() OVER(PARTITION BY ti.person_ID ORDER BY ti.ticket_date) AS ticket_number
FROM @TicketsIssued ti
)
SELECT COUNT(tn.person_ID), tr.DateFrom, tr.DateTo
FROM Ticket_Number AS tn
INNER JOIN @TicketRange AS tr
ON tn.ticket_date BETWEEN tr.DateFrom AND tr.DateTo
WHERE tn.ticket_number = 2
GROUP BY tr.DateFrom, tr.DateTo
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 24, 2015 at 2:20 am
drew.allen (9/23/2015)
Perhaps I'm missing something, but if you want to look specifically at the second ticket, then COUNT is the wrong aggregate. Try the following.
WITH Ticket_Number AS (
SELECT ti.person_ID, ticket_date, ROW_NUMBER() OVER(PARTITION BY ti.person_ID ORDER BY ti.ticket_date) AS ticket_number
FROM @TicketsIssued ti
)
SELECT COUNT(tn.person_ID), tr.DateFrom, tr.DateTo
FROM Ticket_Number AS tn
INNER JOIN @TicketRange AS tr
ON tn.ticket_date BETWEEN tr.DateFrom AND tr.DateTo
WHERE tn.ticket_number = 2
GROUP BY tr.DateFrom, tr.DateTo
Drew
I like that. It did not cross my mind to do it that way.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply