|
View previous topic :: View next topic
|
| Author |
Message |
arvind.m
Active User
Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad
|
|
|
|
Hi All,
can any one help me with the below requirement:
I have two table: Table1 and Table2
Table1 has customer card# and organisation to which it is linked and the linked date
Table1
card#
org_id
linke_dt
active_status
Table2 has transation details done by customer.
Table2
pedriod_key
card#
org_id
store#
purchase_amt
and the Requirement is :
[1] I need all the card# which are active and the link date should be between the supplid date. for example between 20090301 and 20090331 (YYYYMMDD).
[2] Also need the transaction done by the above card# between Period_key between supplied dates which is same as above.
[3] Also there might be chance that Card# might not have done any transaction between the period_key but are active and linked the date period. So i also need the card# putting the Null or 0 to the purchase amount.
I prepared a query with left outer join but it is not working.
| Code: |
SELECT A.CARD_NUM
,A.ORG_ID
,COALESCE(A.LINK_SOURCE_CD,' ')
,COALESCE(B.GROUP_ID,0)
,COALESCE(B.CONTRIBUTION_AMT,0)
,COALESCE(B.PURCHASE_PTS_AMT,0)
,A.CREATE_TMSTP
FROM Table1 A LEFT OUTER JOIN
Table2 B
ON A.CARD_NUM= B.CARD_NUM
WHERE (A.CREATE_TMSTP BETWEEN
'01-MAR-09' AND '31-MAR-09')
AND A.ACTIVE_STATUS = 'A'
AND A.ORG_ID = B.ORG_ID
AND B.GROUP_ID IN (40299,128258)
AND (B.PERIOD_KEY BETWEEN
'01-MAR-09' AND '31-MAR-09')
|
|
|
| Back to top |
|
 |
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| remove the join condition A.ORG_ID = B.ORG_ID , your requirement does not mention anything about using this col for join .... all details are on card# right ? |
|
| Back to top |
|
|
arvind.m
Active User
Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad
|
|
|
|
| one card# can link with different org_id. So transaction for card# which is in table 1 and 2 and also the org_id matching. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
as per your requirement 1 where is the date range on link_date ?
Please let us know what problem are you facing ? |
|
| Back to top |
|
|
arvind.m
Active User
Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad
|
|
|
|
Sorry the Create_tmspt is the link date here and the other (period_key) date is transaction done date range.
Problem i'm facing:
the query is running good. But when i give the date range 20090301 and 20090331 i'm not getting anything in the result set. But when i run seperate query on table1 there are card# linked on the date range.
So i need help to modify query/or new query to get the desired result. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
Try the below query
| Code: |
SELECT A.CARD_NUM
,A.ORG_ID
,COALESCE(A.LINK_SOURCE_CD,' ')
,COALESCE(B.GROUP_ID,0)
,COALESCE(B.CONTRIBUTION_AMT,0)
,COALESCE(B.PURCHASE_PTS_AMT,0)
,A.CREATE_TMSTP
FROM Table1 A LEFT OUTER JOIN
Table2 B
ON A.CARD_NUM= B.CARD_NUM
AND A.ORG_ID = B.ORG_ID
WHERE (A.CREATE_TMSTP BETWEEN
'01-MAR-09' AND '31-MAR-09')
AND A.ACTIVE_STATUS = 'A'
AND B.GROUP_ID IN (40299,128258)
AND (B.PERIOD_KEY BETWEEN
'01-MAR-09' AND '31-MAR-09')
|
|
|
| Back to top |
|
|
arvind.m
Active User
Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad
|
|
|
|
| no records fetched. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Check whether the date format is correct ... post the query which gave results from table A ... |
|
| Back to top |
|
|
arvind.m
Active User
Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad
|
|
|
|
| Code: |
select * from Table1
where create_tmstp between '01-mar-09' and '31-mar-09' and ACTIVE_status='A'
|
|
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
does:
| Code: |
SELECT *
FROM TABLE2
WHRE PERIOD_KEY BETWEEN '01-MAR-09' AND '31-MAR-09'
|
select anything when executed? |
|
| Back to top |
|
|
arvind.m
Active User
Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad
|
|
|
|
| No there are no records selected from Table2. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
No there are no records selected from Table2.
|
well since that is line from your sql,
maybe therein lies the problem |
|
| Back to top |
|
|
arvind.m
Active User
Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad
|
|
|
|
Yes. But my requirement is that if Card# is linked in the date range and have not done any transaction should also come in the result set. For this reason i used LEFT OUTER JOIN.
Can any please provide me a SQL with my requirement mention above. |
|
| Back to top |
|
|
|
|
