November 3, 2008 at 2:09 am
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.
November 3, 2008 at 2:23 am
Try to post the Query.
You could probably archieve this by using af LEFT JOIN
November 3, 2008 at 2:34 am
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)
November 3, 2008 at 3:42 am
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)
November 3, 2008 at 10:16 pm
What do you want returned if 102 has NO entry for 2008-07-01, either?
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply