Column Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Column Question

Author

Topic

jtsiller
Starting Member

4 Posts

Posted - 2006-06-28 : 22:34:16

I'm using a subform to display data stored in SQL Server. The dilema I'm having is the scrollbar is getting quite small, or a lot longer to scroll to the end. I have eight columns that all store a currency value. I decided to utilize one drop down and one currency column to replace the others. Even though it works better, I'm running into other problems. Pulling calculations on the form is much more complicated; I can no longer use sum([value name]) in the application. The one currency column hosts all currency values and the only distinguishing difference is the type drop down.

How can I transfer the amount to the proper column in SQL Server based off the type column? Is this the best approach? Is there another solution? If so, please point me in the right direction.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-06-29 : 03:35:00

I'm seriusly confused...I don't think I understood anything whatsoever of your problem...is it possible that you rephrase? Maybe give some examples?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

jtsiller
Starting Member

4 Posts

Posted - 2006-06-29 : 08:28:02

RefundAmount money column
PaymentAmount money column
AdjustmentAmount money column
PrepayAmount money column

RefundTMID Nvarchar column
PaymentTMID Nvarchar column
AdjustmentTMID Nvarchar column
PrepayTMID Nvarchar column

As you can see these are similar columns. I'm now using
Amount money column
AmountTMID column

I'm trying to find the best way to return calculated values for each group, for a specific batch on the front end. Is there an easy way of doing this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-29 : 08:59:58

Define batch and group, please. If possible, post some sample data here together with expected output based on the provided sample data.
Help us help you.

Is it this you want to accomplish?

SELECT		'Refund' Batch
		RefundTMID TMID,
		SUM(RefundAmount) Amount
FROM		MyTable
GROUP BY	RefundTMID

UNION ALL

SELECT		'Payment'
		PaymentTMID,
		SUM(PaymentAmount)
FROM		MyTable
GROUP BY	PaymentTMID

UNION ALL

SELECT		'Adjustment'
		AdjustmentTMID,
		SUM(AdjustmentAmount)
FROM		MyTable
GROUP BY	AdjustmentTMID

UNION ALL

SELECT		'Prepay'
		PrepayTMID,
		SUM(PrepayAmount)
FROM		MyTable
GROUP BY	PrepayTMID

Or a cross-tab/pivot like this?

SELECT		z.TMID,
		ISNULL(SUM(Re.RefundAmount), 0) 'Refund',
		ISNULL(SUM(Pa.PaymentAmount), 0) 'Payment',
		ISNULL(SUM(Ad.AdjustmentAmount), 0) 'Adjustment',
		ISNULL(SUM(Pr.PrepayAmount), 0) 'Prepay'
FROM		(
			SELECT		RefundTMID TMID
			FROM		MyTable
			UNION
			SELECT		PaymentTMID
			FROM		MyTable
			UNION
			SELECT		AdjustmentTMID
			FROM		MyTable
			UNION
			SELECT		PrepayTMID
			FROM		MyTable
		) z
LEFT JOIN	MyTable Re ON z.TMID = Re.RefundTMID
LEFT JOIN	MyTable Pa ON z.TMID = Pa.PaymentTMID
LEFT JOIN	MyTable Ad ON z.TMID = Ad.AdjustmentTMID
LEFT JOIN	MyTable Pr ON z.TMID = Pr.PrepayTMID
GROUP BY	z.TMID
ORDER BY	z.TMID

Peter Larsson
Helsingborg, Sweden

jtsiller
Starting Member

4 Posts

Posted - 2006-06-29 : 09:33:19

Thank you for helping.

A batch is basically a unique key. Within the batch I can have multiple refunds, prepays, payments, adjustments, etc..... I'm defining refunds, prepays, payments, and adjustments as the grouped portions. I'm only wanting to find the totals of the groups per a unique batch.

Here is a stored procedure I have.

CREATE PROCEDURE dbo.procCountTotals

@BMIBatch Nvarchar(8),

@TotalRefund money OUTPUT,
@TotalPrepay money OUTPUT,
@TotalAdjustment money OUTPUT,
@TotalPayment money OUTPUT

AS

SELECT @TotalRefund = ISNULL(SUM(Amount), 0)
FROM dbo.Refunds
WHERE (Type = N'Refund') AND (Batch = @Batch)

SELECT @TotalPrepay = ISNULL(SUM(Amount), 0)
FROM dbo.Refunds
WHERE ((Type = N'Reverse Prepay') OR (Type = N'Apply Prepay') ) AND (BMIBatch = @Batch)

SELECT @TotalAdjustment = ISNULL(SUM(Amount), 0)
FROM dbo.Refunds
WHERE (Type = N'Adjustment') AND (BMIBatch = @Batch)

SELECT @TotalPayment = ISNULL(SUM(Amount), 0)
FROM dbo.Refunds
WHERE (Type = N'JV') AND (BMIBatch = @Batch)

Select @TotalRefund, @TotalPrepay, @TotalAdjustment, @TotalPayment
GO

The stored procedure works beautifully, but it gets a little clumbsy programming the front end. This is why I was hoping to transfer information from the amount column to the desired group(refund, prepay, adjustment, payment), based on the type that was selected. This would allow me to skip having to call a procedure.

Example in vba I could use something like
Select Case Me.cboType

Case "Refund"
Me.Amount = Me.Refund
Case "Prepay"
Me.Amount = Me.Prepay
etc..............

Doing this would allow me to calculate the columns on the front end without calling something from the backend. I'm hoping to do something similar on the back end. How can I copy the amount to the desired refund, prepay, adjustment, payment based off the users type selection.

I hope this makes since. I do appreciate the help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-29 : 14:47:43

Then why don't you help us helping you with posting the complete DDL here?
How are we supposed to help you if we don't get all the information the first place, such as batch column?

1. Post complete DDL here
2. Post some sample data here
3. Post the expected output here based on the provided sample data

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources