view problem - 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

Transact-SQL (2000)

view problem

Author

Topic

shubhada
Posting Yak Master

117 Posts

Posted - 2006-07-12 : 01:16:56

I have one table with following data

ID1 ID2 Amt LineNo Date
03280601 27691 50.0000 1 2006-01-15 00:00:00
03280601 27691 1000.0000 2 2006-01-15 00:00:00
03280601 27691 100.0000 3 2006-03-15 00:00:00
03280601 27691 100.0000 4 2006-03-30 00:00:00
as per the requirement data should display in report like

ID1 ID2 TotalAmt AddPayAmt
03280601 27691 1250.0000 200.0000

here where clause will pass by vc++ code

where Date between '2006-03-01 00:00:00'
and '2006-03-31 00:00:00'

for this I have created one view

alter view vcmAddVarPayAmt
as
select PaymentsDetail. ID1,
PaymentsDetail.ID2,
sum(PaymentsDetail.Amt) AddPayAmt,
vcmPaymentHistorySummary.PaymntAmt,
from PaymentsDetail join vcmPaymentHistorySummary on
PaymentsDetail.ID1 = vcmPaymentHistorySummary.ID1 and
PaymentsDetail.ID2 = vcmPaymentHistorySummary.ID2
group by PaymentsDetail.ClaimID,
PaymentsDetail.EventID,vcmPaymentHistorySummary.PaymntAmt

so o/p of this view is

ID1 ID2 AddPayAmt Amt
-------------------- -------------- --------------------- ---------------------
03280601 27691 1250.0000 1250.0000

here Amt is correct .I am taking from some other view but AddPayAmt is wrong (it should be 200,sum of last 2 record ) because it is depend on date range and that date range is pass by vc++ code..
so please tell me how I can find out the AddPayAmt

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-12 : 02:05:08

Did you use this?

Select * from vcmAddVarPayAmt
where Date between '2006-03-01 00:00:00'
and '2006-03-31 00:00:00'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources