Pivot Statement?
How would I get the following data
afsAmount afsDate cnt
2295 20110101 1
2295 20110201 1
2295 20110301 1
1000 20111101 1
1000 20111201 1
To look like this?
numCnt amount startdate endDate
3 2295 20110101 20110301
2 1000 20111101 20111201
afsAmount afsDate cnt
2295 20110101 1
2295 20110201 1
2295 20110301 1
1000 20111101 1
1000 20111201 1
To look like this?
numCnt amount startdate endDate
3 2295 20110101 20110301
2 1000 20111101 20111201
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
damerval:
Meant to include this in the last statement.
You can see that the start date of row 2 fals in between the start and end date of row 1...
can't have that.
Meant to include this in the last statement.
You can see that the start date of row 2 fals in between the start and end date of row 1...
can't have that.
SELECT ID, NAME, BIRTH, [3] as CR1, [4] as CR1_NAME, [5] as CR2, [6] as CR2_NAME [7] as CT1, [8] as CT1_NAME , [9] as CK1, [10] as CK1_NAME
FROM
(SELECT Table1.Id, Table1.Name, Table1.Birth, Table2.Attr, Table2.IName
FROM Tabl1 left outer join Table2 on Table1.ID = Table2.ID) p
PIVOT
(
COUNT (ID)
FOR ATTR IN
( [3], [5], [7], [9] )
AND INAME IN
( [4], [6], [8], [10] )
) AS pvt
ORDER BY pvt.D;
FROM
(SELECT Table1.Id, Table1.Name, Table1.Birth, Table2.Attr, Table2.IName
FROM Tabl1 left outer join Table2 on Table1.ID = Table2.ID) p
PIVOT
(
COUNT (ID)
FOR ATTR IN
( [3], [5], [7], [9] )
AND INAME IN
( [4], [6], [8], [10] )
) AS pvt
ORDER BY pvt.D;
select distinct COUNT(afsAmount)over(parti
min(afsDate)over(partition
max(afsDate)over(partition
from tab1
min(afsDate)over(partition
max(afsDate)over(partition
from tab1
...huh?
ASKER
damerval:
You got me on the right track...I wound up modifying your code and using in a fetch to build my text.
Points being awarded shortly
You got me on the right track...I wound up modifying your code and using in a fetch to build my text.
Points being awarded shortly
ASKER
Placed me on the correct track.
ASKER
Almost but not quite...
It's possible to have 3 payments of 2295 over 3 month, then 1 payment 0f 1000 on the 4th month.
then 8 payments of 2295
Your select...lumps the 2295's all together without regard to date sequence.