RiddleMeThis
MIS
Hi Guys,
I'm hoping someone can help here because this has caused a lot of head scratching over the past few weeks.
I know of the PIVOT function to switch rows into columns, but this means the columns also become rows, therefore the row number is fixed, I would like to create a report that will grow in both the X and the Y axis each month (creating a stairstep report).
For example, the month an account was created would be down the left side, and the month a payment was made would be across the top.
The best I can come up with at the moment is a monthly column manually added to a seperate Excel spreadsheet by using the following code to report on the count of all payments the previous month, grouped by account creation month:
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,getdate())), 0)
SET @EndDate = DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
SELECT CONVERT(VARCHAR(3), tblAccount.CreateDate,100)
+ ' '
+ CONVERT(VARCHAR(4), tblAccount.CreateDate,111)
, COUNT(tblPayment.PaymentID)
FROM tblAccount
INNER JOIN tblPayment ON tblAccount.AccountID = tblPayment.AccountID
WHERE tblPayment.PayDate BETWEEN @StartDate AND @EndDate
GROUP BY CONVERT(VARCHAR(3), tblAccount.CreateDate,100)
+ ' '
+ CONVERT(VARCHAR(4), tblAccount.CreateDate,111)
Any Ideas? I'll be away over the weekend but thanks in advance to anyone who can provide input.
I'm hoping someone can help here because this has caused a lot of head scratching over the past few weeks.
I know of the PIVOT function to switch rows into columns, but this means the columns also become rows, therefore the row number is fixed, I would like to create a report that will grow in both the X and the Y axis each month (creating a stairstep report).
For example, the month an account was created would be down the left side, and the month a payment was made would be across the top.
The best I can come up with at the moment is a monthly column manually added to a seperate Excel spreadsheet by using the following code to report on the count of all payments the previous month, grouped by account creation month:
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,getdate())), 0)
SET @EndDate = DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
SELECT CONVERT(VARCHAR(3), tblAccount.CreateDate,100)
+ ' '
+ CONVERT(VARCHAR(4), tblAccount.CreateDate,111)
, COUNT(tblPayment.PaymentID)
FROM tblAccount
INNER JOIN tblPayment ON tblAccount.AccountID = tblPayment.AccountID
WHERE tblPayment.PayDate BETWEEN @StartDate AND @EndDate
GROUP BY CONVERT(VARCHAR(3), tblAccount.CreateDate,100)
+ ' '
+ CONVERT(VARCHAR(4), tblAccount.CreateDate,111)
Any Ideas? I'll be away over the weekend but thanks in advance to anyone who can provide input.