Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I need a help i getting following output from the query .

SELECT
      ARG_CONSUMER,
      cast(ARG_TOTALAMT as float)/100 AS 'Total',
      (SELECT SUM(cast(DAMT as float))/100 FROM DEBT WHERE DDATE >= ARG.ARG_ORIGDATE AND DDATE <= ARG.ARG_LASTPAYDATE AND DTYPE IN ('CSH','CNTP','DDR','NBP') AND DCONSUMER = ARG.ARG_CONSUMER
        ) AS 'Paid'
FROM
     ARGMASTER ARG
WHERE ARG_STATUS = '1'

Current output is a list of all records...

But what i want to achieve here is

count of arg consumers
Total of ARG_TOTALAMT
total of that subquery  PAID
difference between PAID & Total amount.

I am able to achieve first two i.e. count of consumers & total of ARG _ TOTALAMT... but i am confused about sum of of ...i.e.

sum (SELECT SUM(cast(DAMT as float))/100 FROM DEBT WHERE DDATE >= ARG.ARG_ORIGDATE AND DDATE <= ARG.ARG_LASTPAYDATE AND DTYPE IN ('CSH','CNTP','DDR','NBP') AND DCONSUMER = ARG.ARG_CONSUMER) AS 'Paid'

Please advice

share|improve this question
    
Should be on StackOverflow as it is a coding question –  gbn Jan 31 '11 at 6:06
add comment

1 Answer 1

Changing the subquery to an OUTER APPLY

SELECT
      COUNT(DISTINCT ARG_CONSUMER) AS [count of arg consumers],
      cast(ARG_TOTALAMT as float)/100 AS [Total],
      foo.Paid,
      cast(ARG_TOTALAMT as float)/100 - foo.Paid AS [difference between PAID & Total amount]
FROM
     ARGMASTER ARG
     OUTER APPLY
     (
     SELECT
         SUM(cast(D.DAMT as float))/100 AS Paid
     FROM DEBT D
     WHERE
          DDATE >= ARG.ARG_ORIGDATE AND D.DDATE <= ARG.ARG_LASTPAYDATE
          ANDD. DTYPE IN ('CSH','CNTP','DDR','NBP') AND D.DCONSUMER = ARG.ARG_CONSUMER
     ) foo
WHERE ARG.ARG_STATUS = '1'
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.