SQL query

Question

Post reply

SQL query

gururajan

SSCrazy

Points: 2875
More actions
November 3, 2008 at 2:09 am

#193071

Hi all i have one table named member_summary
fields are member_code,name,date,usagelike(opening,cr_amount,dr_amount,closing(dr_amount-cr_amount))
i want select the query to display same order
example
member_code name date opening dr_amount cr_amount closing
101 raja 2008-08-01 200 500 400 300
101 raja 2008-09-01 200 500 400 300
102 rajasekar 2008-07-01 200 500 400 300
102 rajasekar 2008-08-01 200 500 400 300
102 rajasekar 2008-09-01 200 500 400 300
i have select the query 5 rows are display, but i want to 6 rows , means member_code 101 there is no entry in table 7 th month, want to like that
101raja2008-07-01200000000
i use between dates, how i i rectify this please help me.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

vivet Old Hand Points: 372 More actions · Answer 1

Try to post the Query.

You could probably archieve this by using af LEFT JOIN

gururajan SSCrazy Points: 2875 More actions · Answer 2

SELECT MEMBER_CODE,FIRST_NAME,DATE_OF_USING,

OPENING=(SELECT SUM(DR_AMOUNT-CR_AMOUNT) FROM MEMBER_SUMMARY MEM

WHERE MEM.MEMBER_CODE=MEMBER_SUMMARY.MEMBER_CODE

AND MEM.BRN_CODE=MEMBER_SUMMARY.BRN_CODE

AND MEM.COMP_CODE=MEMBER_SUMMARY.COMP_CODE),

SUM(DR_AMOUNT) AS DR_AMOUNT,

SUM(CR_AMOUNT) AS CR_AMOUNT

FROM MEMBER_SUMMARY

WHERE ACTIVE_FLG='A'

AND BRN_CODE='001'

AND COMP_CODE='001'

AND DATE_OF_USING BETWEEN '2008-08-01' AND '2008-10-30'

GROUP BY MEMBER_CODE,FIRST_NAME,DATE_OF_USING

ORDER BY MEMBER_CODE,FIRST_NAME,DATE_OF_USING

CLOSING CALCULATING BY OPENING+(DR-CR)

vivet Old Hand Points: 372 More actions · Answer 3

You could either make a UNION including the rows that are left out in your select.

Or you could start by selecting all records that you want a row displayed for, and by LEFT JOINING on the same table you will get NULL at the rows the your former SQL left out.

Something Like this:

SELECT

MS2.MEMBER_CODE,

MS2.FIRST_NAME,

MS2.DATE_OF_USING,

OPENING=(SELECT SUM(DR_AMOUNT-CR_AMOUNT) FROM MEMBER_SUMMARY MEM

WHERE MEM.MEMBER_CODE = MS2.MEMBER_CODE

AND MEM.BRN_CODE = MS2..BRN_CODE

AND MEM.COMP_CODE = MS2..COMP_CODE),

SUM(MS2.DR_AMOUNT) AS DR_AMOUNT,

SUM(MS2.CR_AMOUNT) AS CR_AMOUNT

FROM MEMBER_SUMMARY AS MS1

LEFT JOIN MEMBER_SUMMARY AS MS2 ON [Criteria] -- one-to-one maping

WHERE ACTIVE_FLG='A'

AND BRN_CODE='001'

AND COMP_CODE='001'

AND DATE_OF_USING BETWEEN '2008-08-01' AND '2008-10-30'

GROUP BY MEMBER_CODE,FIRST_NAME,DATE_OF_USING

ORDER BY MEMBER_CODE,FIRST_NAME,DATE_OF_USING

CLOSING CALCULATING BY OPENING+(DR-CR)

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 4

What do you want returned if 102 has NO entry for 2008-07-01, either?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)