May 29, 2012 at 4:18 am
Hi ,
I have a table Temp and the data as follows.
Create table Temp
(
PAN nvarchar(100),
Month int,
Year int,
inputDate datetime,
Program nvarchar(10)
)
Insert into Temp Values('123456',1,2011,'2011-01-01','P1')
Insert into Temp Values('456798',1,2011,'2011-01-02','P1')
Insert into Temp Values('789456',1,2011,'2011-01-03','P1')
Insert into Temp Values('456123',1,2011,'2011-01-04','P1')
Insert into Temp Values('147258',1,2011,'2011-01-05','P1')
Insert into Temp Values('258147',1,2011,'2011-01-06','P1')
Insert into Temp Values('369258',1,2011,'2011-01-07','P1')
Insert into Temp Values('123456',2,2011,'2011-02-01','P1')
Insert into Temp Values('456798',2,2011,'2011-02-02','P1')
Insert into Temp Values('789456',2,2011,'2011-02-03','P1')
Insert into Temp Values('159265',2,2011,'2011-02-04','P1')
Insert into Temp Values('487159',2,2011,'2011-02-05','P1')
Insert into Temp Values('123456',3,2011,'2011-03-01','P1')
Insert into Temp Values('456798',3,2011,'2011-03-02','P1')
Insert into Temp Values('159265',3,2011,'2011-03-03','P1')
Insert into Temp Values('487159',3,2011,'2011-03-04','P1')
Insert into Temp Values('745874',3,2011,'2011-03-05','P1')
Insert into Temp Values('123456',4,2011,'2011-04-06','P1')
Insert into Temp Values('456798',4,2011,'2011-04-07','P1')
Insert into Temp Values('487159',4,2011,'2011-04-08','P1')
Insert into Temp Values('745874',4,2011,'2011-04-09','P1')
Insert into Temp Values('841526',4,2011,'2011-04-10','P1')
Insert into Temp Values('365847',4,2011,'2011-04-11','P1')
I would like output in matrix format(Month wise in rows and columns of matrix). Please find attached the excel.
It should contain,
The repeated PAN's in each month
Newly added pans of each month ( That does not exists in previous months)
In the above scenario,
In Jan-2011 ---- 7 pans exists out of which 3 pans repeated in Feb-2011 and 2 Pans repeated in March and 2 pans repeated in Apr 2011.
In Feb 2011 --- 2 newly added pans(That does not exists in Jan) out of which 2 Pans repeated in March and 1 pan repeated in Apr 2011.
In Mar 2011 --- 1 newly added pan(That does not exists in Jan and Feb) out of which 1 pan repeated in Apr 2011.
In Apr 2011 -- 1 Newly added Pan(That does not exists in Jan, Feb and Mar).
Kindly let me know the procedure to achieve this.
May 29, 2012 at 6:48 am
How about
;with distinctMonths
as
(
select distinct dateadd(month,(t.[month]-1),
dateadd(year,(t.[year]-1900),0)) as MonthDate,
t.[MONTH],
t.[year]
from temp as t
),
earliestMonth
as
(
select t.PAN,min(dm.monthdate) as EarliestMonth
from distinctMonths as dm
INNER JOIN temp as t
on dm.Month = t.Month and
t.Year = dm.Year
group by t.PAN
),
PANCounts
as
(
selectem.EarliestMonth,
dm.monthdate as RepeatedInMonth,
COUNT(distinct t.PAN) as NumberOfPANs
from temp as t
inner join earliestMonth as em
on t.PAN = em.PAN
inner join distinctMonths as dm
on t.Month = dm.Month and
t.Year = dm.Year
where exists (select PAN
from earliestMonth as em
where em.EarliestMonth <= dm.MonthDate and
em.PAN = t.PAN)
group by em.EarliestMonth,dm.MonthDate
)
selectpvt.CreatedInMonth,
[2011-01-01],
[2011-02-01],
[2011-03-01],
[2011-04-01]
from (
selectcast(dm.MonthDate as date) as CreatedInMonth,
pc.RepeatedInMonth,
pc.NumberOfPANs
from distinctMonths as dm
LEFT JOIN PANCounts as pc
on dm.MonthDate = pc.earliestMonth
) as src
PIVOT (SUM(NumberOfPANs) FOR
RepeatedInMonth in ([2011-01-01],
[2011-02-01],
[2011-03-01],
[2011-04-01])) as pvt
May 29, 2012 at 7:12 am
Just to Note: not everyone will have ability (or wish) to download attached files from i-net, especially at their work places...
May 29, 2012 at 7:48 am
adlan (5/29/2012)
How about
;with distinctMonthsas
(
select distinct dateadd(month,(t.[month]-1),
dateadd(year,(t.[year]-1900),0)) as MonthDate,
t.[MONTH],
t.[year]
from temp as t
),
earliestMonth
as
(
select t.PAN,min(dm.monthdate) as EarliestMonth
from distinctMonths as dm
INNER JOIN temp as t
on dm.Month = t.Month and
t.Year = dm.Year
group by t.PAN
),
PANCounts
as
(
selectem.EarliestMonth,
dm.monthdate as RepeatedInMonth,
COUNT(distinct t.PAN) as NumberOfPANs
from temp as t
inner join earliestMonth as em
on t.PAN = em.PAN
inner join distinctMonths as dm
on t.Month = dm.Month and
t.Year = dm.Year
where exists (select PAN
from earliestMonth as em
where em.EarliestMonth <= dm.MonthDate and
em.PAN = t.PAN)
group by em.EarliestMonth,dm.MonthDate
)
selectpvt.CreatedInMonth,
[2011-01-01],
[2011-02-01],
[2011-03-01],
[2011-04-01]
from (
selectcast(dm.MonthDate as date) as CreatedInMonth,
pc.RepeatedInMonth,
pc.NumberOfPANs
from distinctMonths as dm
LEFT JOIN PANCounts as pc
on dm.MonthDate = pc.earliestMonth
) as src
PIVOT (SUM(NumberOfPANs) FOR
RepeatedInMonth in ([2011-01-01],
[2011-02-01],
[2011-03-01],
[2011-04-01])) as pvt
Thanks a lot. I have one more question related to this.
If the date ranges are between 2009-01-01 to 2011-12-31 so it has to be hardcoded in pivot query ?? the dates are dynamic.
How to achieve this.??
May 29, 2012 at 3:26 pm
Depending on your constraints (coding standards etc) it may be possible to use Dynamic SQL to do this.
Google is your friend...
This should not be done lightly - I think a good explanation of the pros and cons of dynamic SQL is at
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply