Please give me your suggestions on Report Query - Very Urgent

I need to disaplay number of Active Agencies on monthwise in one of my report. I have tbl_Agency table with ActiveDate and ActiveFlag. ActiveDate column contains always first Activation Date. If any chances in the agencies(update/delete) the same record will move to tbl_AgencyHistory table.

"If an agency is inactivated in September 10th, inactivated all of October, and then reactivated November 10th - the agency would be counted in September, not in October and counted in November"

ActiveDate column has always first activation date, I could not meet this requirement. This is very urgent issue, Could you please help me on this.

Thanks,

Malar

Malar:

You want a monthly summary such as:

Month ActiveCt
-- -
2006 January 27
2006 February 30
2006 March 45
2006 April 55
2006 May 61
2006 August 27
2006 September 29
2006 October 35
2006 November 60
2006 December 63

The source tables need to include both the home table plus the history table?

Dave

|||

Malar:

Please review the following scenario and verify if I understand your update process correctly:

Acme Coyote Co gets first activitated on 8/15:

tbl_agency: agencyName='Acme Coyote Co', activeFlag='Y', activenDate='8/15/6'
( there are no history records for 'Acme Coyote Co'

Acme Coyote Co goes inactive on 9/10:

tbl_agency: agencyName='Acme Coyote Co', activeFlag='N', activeDate='9/10/6'
tbl_agencyHist: seq=1, agencyName='Acme Coyote Co', activeFlag='Y', activeDate='8/15/6'

Acme Coyote Co re-activates on 11/07:

tbl_agency: agencyName='Acme Coyote Co', activeFlag='Y', activeDate='11/7/6'
tbl_agencyHist: seq=1, agencyName='Acme Coyote Co', activeFlag='Y', activeDate='8/15/6'
tbl_agencyHist: seq=2, agencyName='Acme Coyote Co', activeFlag='N', activeDate='9/10/6'

|||

Hi Mugambo,

Thanks for your reply. I need to display out put like this.

Year Month ExistingActiveCt NewActiveCt
-- -
2006 January 27 3
2006 February 30 15
2006 March 45 10
2006 April 55 6
2006 May 61 0
2006 August 27 2
2006 September 29 6
2006 October 35 25
2006 November 60 3
2006 December 63 0

Above your query explanation is correct. We have ModifiedOn column in both the tables. If any of Agency is inactivated, tbl_Agency table in ModifiedOn is updated with current date. The previous record is in historty table ModifiedOn also updated with current date.

Thanks,

Malar

|||

Malar:

Please forgive the long-winded jazz that follows. This went much more over-the-top for me that I had planned. First, I use my iterator table -- a table of numbers -- as part of the routine. The iterator table can implemented as:

-- -
-- I use my iterator table as a read-only untility table from time to time.
-- The best write-up for this type of thing is given as a table of numbers
-- at the website:
--
-- http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
-- -
if exists
( select 0 from sysobjects
where type = 'U'
and id = object_id ('dbo.SMALL_ITERATOR')
)
drop table dbo.SMALL_ITERATOR
go

create table dbo.SMALL_ITERATOR
( iter smallint not null
constraint PK_SMALL_ITERATOR primary key
)
go

-- -
-- Here, we use the master "spt_values" table to load initial values into
-- the small iterator table.
--
-- NOTE:
--
-- Use of the spt_values in this situation is an undocumented SQL Server
-- feature. In fact, SQL Server 2000 has values 0-255 while SQL Server
-- 2005 has values 0-2047 so beware! This "built-in table of numbers" can
-- be dangerous!
--
-- I restrict the domain of numbers here 0-255 for the sake of providing a
-- little more safety.
-- -
truncate table SMALL_ITERATOR

insert into small_iterator
select number from master.dbo.spt_values
where name is null
and number <= 255

insert into small_iterator
select 256 * j.iter + i.iter
from small_iterator i
inner join small_iterator j
on j.iter > 0
and j.iter <= 128
and 256 * j.iter + i.iter <= 32767

delete from small_iterator where iter = 0

select count(*) [count],
min (iter) [min iterator],
max (iter) [max iterator]
from SMALL_ITERATOR

go

dbcc dbreindex (small_iterator, '', 100)
go

update statistics small_iterator
go

exec sp_recompile small_iterator
go

The actual report code:

-- -
-- Hang on to your hat and holy moly this is nasty.
--
-- I guess what bugs me most about all of this has to do with the UNIONs
-- that are necessary because not all of the records are retained in the
-- history table.
--
-- Notice that there are at least a couple of benefits to be derived by
-- keeping all records -- even a copy of the current record -- in the
-- history table:
--
-- (1) The UNIONS for this report and for any other report that must
-- account for history are eliminated
-- (2) Elimination the UNIONS means that these types of report processes,
-- which likely process by table scan, now no longer have to impact
-- the current record table. This can eliminate a heavy kind of
-- contention away from the primary table
-- -
set nocount on
set ansi_warnings off

declare @.matrixYr char (4) set @.matrixYr = '2006'
declare @.baseDt datetime set @.baseDt = '1/1/'+@.matrixYr

--select @.matrixYr as [@.matrixYr],
-- @.baseDt as [@.baseDt]

-- -
-- Generate mockup table for testing. uncomment the other insert
-- statements to test with more data.
-- -
declare @.agency table
( agency_id integer not null primary key,
agencyName varchar (30) not null,
activeFlag char (1) not null,
activeDate datetime not null
)

declare @.agencyHist table
( agency_id integer not null,
seq integer not null,
agencyName varchar (30) not null,
activeFlag char (1) not null,
activeDate datetime not null
)

insert into @.agency values (1, 'Acme Coyote Co', 'Y', '11/7/6')
insert into @.AgencyHist values (1, 1, 'Acme Coyote Co', 'Y', '1/31/6')
insert into @.agencyHist values (1, 2, 'Acme Coyote Co', 'N', '2/14/6')
insert into @.agencyHist values (1, 3, 'Acme Coyote Co', 'Y', '4/17/6')
insert into @.agencyHist values (1, 4, 'Acme Coyote Co', 'N', '4/19/6')
insert into @.AgencyHist values (1, 5, 'Acme Coyote Co', 'Y', '8/15/6')
insert into @.agencyHist values (1, 6, 'Acme Coyote Co', 'N', '9/10/6')

/*
insert into @.agency values (2, 'Ralph''s Plumbing', 'N', '5/7/6')
insert into @.AgencyHist values (2, 1, 'Ralph''s Plumbing', 'Y', '12/7/5')

insert into @.agency values (3, 'Tricky Ricky Taffy', 'Y', '9/7/6')
insert into @.AgencyHist values (3, 1, 'Tricky Ricky Taffy', 'Y', '9/9/5')
insert into @.agencyHist values (3, 2, 'Tricky Ricky Taffy', 'N', '4/19/6')

insert into @.agency values (4, 'Gopher Tunnelling', 'Y', '7/14/6')

insert into @.agency values (5, 'Random Certainty Co', 'Y', '12/1/5')

insert into @.agency values (11, 'Bad Records 1', 'N', '4/21/6')
insert into @.AgencyHist values (11, 1, 'Bad Records 1', 'Y', '2/14/6')
insert into @.agencyHist values (11, 2, 'Bad Records 1', 'Y', '2/15/6')

insert into @.agency values (12, 'Bad Records 2', 'N', '7/7/6')
insert into @.AgencyHist values (12, 1, 'Bad Records 2', 'Y', '6/1/6')
insert into @.agencyHist values (12, 2, 'Bad Records 2', 'N', '6/1/6')

insert into @.agency values (13, 'Bad Records 3', 'N', '11/24/6')
insert into @.AgencyHist values (13, 1, 'Bad Records 3', 'Y', '9/14/6')
insert into @.agencyHist values (13, 2, 'Bad Records 3', 'Y', '10/21/6')
insert into @.agencyHist values (13, 3, 'Bad Records 3', 'N', '10/29/6')
*/

--print '-- @.AGENCY --'
--select * from @.agency
--select * from @.agencyHist

-- -
-- This is what scalds me the most. I don't care for the union
-- in combination with the self left join of the complement of the union.
--
-- It might be a good idea to use NOLOCK hints on each of these tables
-- since this is a report.
--
-- If you always get ZERO scrubbed records, the scrubbing routine can be
-- eliminated. To elimnate the scrub routine change the target table
-- of the next insert from @.rawInterval to @.interval and remove the code
-- that moves scrubbed records from the @.rawInterval table into the
-- @.interval table.
-- -
declare @.rawInterval table
( agency_id integer not null,
startDate datetime not null,
endDate datetime not null,
primary key (endDate, startDate, agency_id)
)

--insert into @.interval -- ONLY if the data is CLEAN!
insert into @.rawInterval
select a.agency_id,
a.activeDate as startDate,
isnull (min (b.activeDate), '12/31/3999 23:59:59.997') as endDate
from ( select agency_id,
agencyName,
activeDate
from @.agency
where activeFlag = 'Y'
union
select agency_id,
agencyName,
activeDate
from @.agencyHist
where activeFlag = 'Y'
) a

left join
( select agency_id,
agencyName,
activeDate
from @.agency
where activeFlag <> 'Y'
union
select agency_id,
agencyName,
activeDate
from @.agencyHist
where activeFlag <> 'Y'
) b
on a.agency_id = b.agency_id
and a.activeDate < b.activeDate
group by a.agency_id, a.activeDate

-- print '- RAW INTERVAL -'
-- select * from @.rawInterval

-- -
-- Scrub out the records that cause the problems
--
-- This section can be eliminated if the data is always clean.
-- -

declare @.interval table
( agency_id integer not null,
startDate datetime not null,
endDate datetime
)

insert into @.interval
select a.agency_id,
a.startDate,
a.endDate
from @.rawInterval a
inner join
( select s.agency_id,
min (s.startDate) as min_startDate,
endDate
from @.rawInterval s
group by s.agency_id, s.endDate
) b
on a.agency_id = b.agency_id
and a.startDate = b.min_startDate

--print '-- SCRUBBING: --'
--select * from @.interval

select ( select count(*) from @.rawInterval ) -
( select count(*) from @.interval ) as [Scrubbed Records]

-- -
-- After we do all that hand waving we now write this to an output summary
-- to hold the information. This information will be mergede with the
-- "first time" agencies after the "first time" agency information is
-- compiled.
--
-- Note that the select for this insert uses the "small_iterator" table
-- -
declare @.output table
( period varchar (15) not null,
monthBasis datetime not null,
ExistingActiveCt integer not null
)

insert into @.output
select period,
monthBasis,
sum ( case when i.agency_id is null then 0 else 1 end ) as existingActiveCt
from ( select left (@.matrixYr + ' ' + datename (month,
dateadd (month, iter - 1, @.baseDt)), 15) as period,
dateadd (month, iter - 1, @.baseDt) as monthBasis
from small_iterator (nolock)
where iter <= 12
) m
left join @.interval i
on dateadd (month, 1, m.monthBasis) > i.startDate
and ( m.monthBasis <= i.endDate or i.endDate is null )
group by monthBasis, period
order by monthBasis, period

-- -
-- Picking up the first time agencies looks a little better. But we are
-- still dealing with the same union.
-- -
declare @.firstTime table
( monthBasis datetime not null,
newActiveCt integer not null
)

insert into @.firstTime
select convert (datetime, activationMonth + '/1/' + activationYear),
newActiveCt
from ( select convert (varchar(2), month (activeDate)) as activationMonth,
convert (varchar(4), year (activeDate)) as activationYear,
count (*) as newActiveCt
from ( select agency_id,
min (activeDate) as activeDate
from ( select agency_id,
min(activeDate) as activeDate
from @.agency
where activeFlag = 'Y'
group by agency_id
union
select agency_id,
min (activeDate)
from @.agencyHist
where activeFlag = 'Y'
group by agency_id
) j
group by agency_id
having min (activeDate) >= @.baseDt
) x
group by convert (varchar(2), month (activeDate)),
convert (varchar(4), year (activeDate))
) act

--select * from @.firstTime

-- -
-- And after all of that work we get a small report.
--
-- What a way to run a railroad.
-- -
select a.period,
a.ExistingActiveCt,
isnull (b.NewActiveCt, 0) as NewActiveCt
from @.output a
left outer join @.firstTime b
on a.monthBasis = b.monthBasis
order by a.monthBasis

set ansi_warnings on

-- -
-- Output with "Acme Coyote Co" only
-- -

-- Scrubbed Records
-- -
-- 0

-- period ExistingActiveCt NewActiveCt
-- - --
-- 2006 January 1 1
-- 2006 February 1 0
-- 2006 March 0 0
-- 2006 April 1 0
-- 2006 May 0 0
-- 2006 June 0 0
-- 2006 July 0 0
-- 2006 August 1 0
-- 2006 September 1 0
-- 2006 October 0 0
-- 2006 November 1 0
-- 2006 December 1 0

-- -
-- Output with all records
-- -

-- Scrubbed Records
-- -
-- 2

-- period ExistingActiveCt NewActiveCt
-- - --
-- 2006 January 4 1
-- 2006 February 5 1
-- 2006 March 4 0
-- 2006 April 5 0
-- 2006 May 2 0
-- 2006 June 2 1
-- 2006 July 3 1
-- 2006 August 3 0
-- 2006 September 5 1
-- 2006 October 4 0
-- 2006 November 4 0
-- 2006 December 4 0

|||

Thanks Mugambo,

I have not used iteration. Because I need only 12 rows. Remaining logic is the same.

Thanks,

Malar

set nocount on
set ansi_warnings off

-- -
-- Generate mockup table for testing. uncomment the other insert
-- statements to test with more data.
-- -

declare @.agency table
( AgencyID integer not null primary key,
AgencyName varchar (30) not null,
ActiveInd BIT not null,
ActivationDt datetime not null
)
declare @.agencyHist table
( AgencyID integer not null,
seq integer not null, --primary key
AgencyName varchar (30) not null,
ActiveInd char (1) not null,
ActivationDt datetime not null
)

insert into @.agency values (1, 'Acme Coyote Co', 1, '9/10/6')
insert into @.AgencyHist values (1, 1, 'Acme Coyote Co', 0, '1/15/6')
insert into @.AgencyHist values (1, 2, 'Acme Coyote Co', 1, '1/31/6')
insert into @.agencyHist values (1, 3, 'Acme Coyote Co', 0, '2/14/6')
insert into @.agencyHist values (1, 4, 'Acme Coyote Co', 1, '4/17/6')
insert into @.agencyHist values (1, 5, 'Acme Coyote Co', 0, '4/19/6')
insert into @.AgencyHist values (1, 6, 'Acme Coyote Co', 1, '8/15/6')
insert into @.AgencyHist values (1, 7, 'Acme Coyote Co', 0, '9/10/6')
insert into @.agency values (2, 'Ralph''s Plumbing', 1, '7/7/6')
insert into @.AgencyHist values (2, 8, 'Ralph''s Plumbing', 0, '5/7/5')
insert into @.AgencyHist values (2, 9, 'Ralph''s Plumbing', 1, '5/7/6')
insert into @.AgencyHist values (2, 10, 'Ralph''s Plumbing', 0, '7/7/6')
insert into @.agency values (3, 'Tricky Ricky Taffy', 1, '4/19/6')
insert into @.AgencyHist values (3, 11, 'Tricky Ricky Taffy', 0, '9/9/5')
insert into @.AgencyHist values (3, 12, 'Tricky Ricky Taffy', 1, '10/9/5')
insert into @.agencyHist values (3, 13, 'Tricky Ricky Taffy', 0, '4/19/6')
insert into @.agency values (4, 'Gopher Tunnelling', 1, '7/14/6')
insert into @.agency values (5, 'Random Certainty Co', 1, '12/1/5')

/*insert into @.agency values (11, 'Bad Records 1', 0, '4/21/6')
insert into @.AgencyHist values (11, 14, 'Bad Records 1', 0, '2/14/6')
insert into @.agencyHist values (11, 15, 'Bad Records 1', 1, '4/21/6')

insert into @.agency values (12, 'Bad Records 2', 0, '7/7/6')
insert into @.AgencyHist values (12, 16, 'Bad Records 2', 1, '6/1/6')
insert into @.agencyHist values (12, 17, 'Bad Records 2', 1, '7/7/6')
insert into @.agency values (13, 'Bad Records 3', 0, '11/24/6')
insert into @.AgencyHist values (13, 18, 'Bad Records 3', 1, '9/14/6')
insert into @.agencyHist values (13, 19, 'Bad Records 3', 0, '10/21/6')
insert into @.agencyHist values (13, 20, 'Bad Records 3', 1, '11/24/6')
*/
--print '-- @.AGENCY --'
-- select * from @.agency
-- select * from @.agencyHist
DECLARE
@.StartDate DATETIME,
@.EndDate DATETIME,
@.TempFromDate DATETIME,
@.TempToDate DATETIME,
@.TotalRows INT,
@.LoopCount INT,
@.FromYear INT,
@.ToYear INT,
@.FromMonth INT,
@.ToMonth INT

DECLARE @.tempOutput TABLE
(
CountId INT IDENTITY(1,1) PRIMARY KEY,
Mnth CHAR(3),-- 1 to 12
Yr INT, -- 4 digit
StartDate DATETIME,
EndDate DATETIME
)

SELECT @.FromYear =2006,
@.ToYear =2006,
@.FromMonth =1,
@.ToMonth =12

SELECT @.StartDate = CAST(LTRIM(RTRIM(CAST(@.FromMonth AS CHAR(2)))) + '-01-' + CAST(@.FromYear AS CHAR(4)) AS DATETIME)

SELECT @.EndDate = CAST(LTRIM(RTRIM(CAST(@.ToMonth AS CHAR(2)))) + '-'
+ CAST(DAY(DATEADD(d, -DAY(DATEADD(m,1,CAST(CAST(@.ToMonth AS CHAR(2)) + '-01-'
+ CAST(@.ToYear AS CHAR(4)) AS DATETIME))),DATEADD(m,1,CAST(CAST(@.ToMonth AS CHAR(2))
+ '-01-' + CAST(@.ToYear AS CHAR(4)) AS DATETIME)))) AS CHAR(2)) + '-'
+ CAST(@.ToYear AS CHAR(4)) + ' 23:59:59.000' AS DATETIME)

SELECT @.LoopCount = 1
,@.TotalRows = DATEDIFF(Month, @.StartDate, @.EndDate) + 1
,@.TempFromDate = @.StartDate

SELECT @.TempToDate = CAST(LTRIM(RTRIM(CAST(DATEPART(Month,@.StartDate) AS CHAR(2)))) + '-'
+ CAST(DAY(DATEADD(d, -DAY(DATEADD(m,1,@.StartDate)),DATEADD(m,1,@.StartDate))) AS CHAR(2)) + '-'
+ CAST(LTRIM(RTRIM(DATEPART(Year,@.StartDate))) AS CHAR(4)) + ' 23:59:59.000' AS DATETIME)

-- Insert required number of rows
WHILE @.LoopCount <= @.TotalRows
BEGIN
INSERT INTO @.tempOutput
SELECT LEFT(CONVERT(CHAR(10),@.TempFromDate,107),3), YEAR(@.TempFromDate), @.TempFromDate, @.TempToDate
SET @.LoopCount = @.LoopCount + 1
SET @.TempFromDate = DATEADD(Month,1,@.TempFromDate)
SET @.TempToDate = CAST(LTRIM(RTRIM(CAST(DATEPART(Month,@.TempFromDate) AS CHAR(2)))) + '-' + CAST(DAY(DATEADD(d, -DAY(DATEADD(m,1,@.TempFromDate)),DATEADD(m,1,@.TempFromDate))) AS CHAR(2)) + '-' + CAST(LTRIM(RTRIM(DATEPART(Year,@.TempFromDate))) AS CHAR(4))
+ ' 23:59:59.000' AS DATETIME)

END
-- print temporary table with 12 records with start date and end date
-- select * from @.tempOutput

declare @.rawInterval table
( AgencyID integer not null,
startDate datetime not null,
endDate datetime not null,
primary key (endDate, startDate, AgencyID)
)
--insert into @.interval -- ONLY if the data is CLEAN!
insert into @.rawInterval
select a.AgencyID,
a.ActivationDt as startDate,
isnull (min (b.ActivationDt), '12/31/3999 23:59:59.997') as endDate
from ( select AgencyID,
AgencyName,
ActivationDt
from @.agency
where ActiveInd = 1
union
select AgencyID,
AgencyName,
ActivationDt
from @.agencyHist
where ActiveInd <> 1
) a
left join
( select AgencyID,
AgencyName,
ActivationDt
from @.agency
where ActiveInd <> 1
union
select AgencyID,
AgencyName,
ActivationDt
from @.agencyHist
where ActiveInd = 1
) b
on a.AgencyID = b.AgencyID
and a.ActivationDt < b.ActivationDt
group by a.AgencyID, a.ActivationDt
-- print '- RAW INTERVAL -'
--select * from @.rawInterval

-- New Agency Code
declare @.firstTime table
( monthBasis datetime not null,
newActiveCt integer not null
)

insert into @.firstTime
select convert (datetime, activationMonth + '/1/' + activationYear),
newActiveCt
from ( select convert (varchar(2), month (ActivationDt)) as activationMonth,
convert (varchar(4), year (ActivationDt)) as activationYear,
count (AgencyID) as newActiveCt
from ( select AgencyID,
min (ActivationDt) as ActivationDt
from ( select AgencyID,
min(ActivationDt) as ActivationDt
from @.agency
where ActiveInd = 1
group by AgencyID
union
select AgencyID,
min (ActivationDt)
from @.agencyHist
where ActiveInd = 1
group by AgencyID
) j
group by AgencyID
having min (ActivationDt) >= @.StartDate
) x
group by convert (varchar(2), month (ActivationDt)),
convert (varchar(4), year (ActivationDt))
) act
-- print new agencies
-- select * from @.firstTime

-- exisitng agency code
declare @.output table
( period varchar (15) not null,
monthBasis datetime not null,
ExistingActiveCt integer not null,
Mnth char(3) not null
)

insert into @.output
select period,
monthBasis,
sum ( case when i.AgencyID is null then 0 else 1 end ) as existingActiveCt,
Mnth
from (
select Mnth,Yr as period ,StartDate as monthBasis from @.tempOutput
) m
left join @.rawInterval i
on dateadd (month, 1, m.monthBasis) > i.startDate
and ( m.monthBasis <= i.endDate or i.endDate is null )
group by monthBasis, period, Mnth
order by monthBasis, period, Mnth

-- print existing agencies
-- select * from @.output

-- existing agencies and new agencies
select a.period,
a.ExistingActiveCt,
isnull (b.NewActiveCt, 0) as NewActiveCt,
a.monthBasis,
a.Mnth
from @.output a
left outer join @.firstTime b
on a.monthBasis = b.monthBasis
order by a.monthBasis