Need to add dup rows to sp

I have a sp that is used for membership cards, there is a column Number_of_Cards. We enter the number of cards a member wants. I need to create duplicate rows in my results based on the number entered in this field for each member. Anyone have any Ideas how to generate dup rows based on this column?

Thanks for any thoughts,Well, I don't like the sounds of it ;-) , but this is how you would do it in your sproc:

DECLARE @.LoopCount int
SELECT @.LoopCount = 1
WHILE @.LoopCount <= @.CardCount
BEGIN
INSERT INTO...
SET @.LoopCount = @.LoopCount + 1
END

Note that Transact-SQL does not have a FOR...NEXT loop construct. You need to make do with a WHILE loop.

Terri|||Thank you, I will give it a try. I know this sounds bad, but it is for a merge to membership cards and I need to do it this way to use Microsoft Word for the process.

Thanks again.|||I have been working with the example and have run into an issue. I thought you might have an idea. I think the problem is that the first loop does the INTO temp_Cards then the next loop fails because the temp_Cards table already exists.

CREATE PROCEDURE dbo.eP_CardProcess2 (@.Start datetime,@.End datetime,@.LoopCount int)
AS SELECT @.LoopCount =1
WHILE @.LoopCount <= (Select dbo.tblPledges.Number_Of_Cards From dbo.tblPledges Where (dbo.tblPledges.StartDate BETWEEN @.Start AND @.End) AND (dbo.tblPledges.Print_Card = 1) AND (dbo.tblPledges.Cards_Printed=0))
BEGIN
SELECT dbo.tblPledges.Number_Of_Cards,dbo.tblPledges.Card_Start_Date,dbo.tblPledges.Card_End_Date,dbo.tblPledges.Alternate_Card_Name,dbo.tblGivers.Email,dbo.tblPledges.RenewalDate INTO #temp_Cards
FROM dbo.tblPledges INNER JOIN dbo.tblClient ON dbo.tblPledges.Client_ID = dbo.tblClient.Client_ID WHERE (dbo.tblPledges.StartDate BETWEEN @.Start AND @.End) AND (dbo.tblPledges.Print_Card = 1) AND (dbo.tblPledges.Cards_Printed=0)
SET @.LoopCount = @.LoopCount + 1
END

sql

need MDX help on calculated weighted average based on time

I am working on a project using financial data (chart of accounts) and I need to create a calculated member(measure).

The measure is the dollar amount for the balance sheet accounts as they roll up over the time dimension, they need to be weighted by the number of days in the period. So instead of it being a normal average it should be weighted by the days in the period.

I have Number of days in Month, Quarter, and Year as attributes in the Time Dimension([DaysInMonth], [DaysInQuarter], [DaysInYear])

Here is an example:

Qtr 1 = (Jan. balance x 31)+(Feb bal x 28)+(mar bal x 31)/total number of days in the period (90 in this example)

My Measure Name is Amount and My time Dimension/Heirarchy is [DimTime].[Calendar Time] respectively.

Has anyone created a similar measure? Can you show me the MDX? This is a tight timeline and any help would be GREATLY appreciated!

DRR

While I'm certain you can pull this query off with MDX, I wonder if you might be better served by storing the component values as measures. Think about it this way, no matter where you are in the cube, you will always have to pull balance data from the lowest level, multiply it by a number specific to that month, and then roll up both the number of days in the months affected and the weighted balance before then doing a division operation. I think you might run into performance problems.

So instead, I'd suggest creating two measures. One is DaysInReportingPeriod. The other is WeightedBalance which is your balance times days. Create these in your relational data warehouse or in the DSV. Then, add the measures to the cube with aggregation set to SUM and hide them (Set Visible=False). Then, all you have to do is create a calculation that divides [Measures].[WeightedBalance] by [Measures].[DaysInReportingPeriod].

Good luck,
Bryan