Hello,
I am having trouble in getting the expected results. I have two tables as below. I need Idno,transdt,cd,cycdt,amt from joining two tables. The criteria is that if the transdt greater than same month of cycdt then we need get the next month cycdt and corresponding amount for that, if it is less than or equal to same months cycdt then get the same months cycdt and amt. Cd is dummy field which can be anything. I am using sqlserver 8.0
table1
idno,trandt,cd
12345,04/15/2005,cd1
12345,04/15/2005,cd2
12345,04/22/2005,cd3
12345,07/03/2005,cd4
12345,09/10/2005,cd5
3421,03/05/2005,cd6
3421,05/06/2005,cd7
3421,07/04/2005,cd8
3421,07/15/2005,cd9
3421,09/15/2005,cd10
idno,cycdt,amt
12345,02/10/2005,15.43
12345,03/13/2005,40.84
12345,04/18/2005,10.10
12345,05/24/2005,13.00
12345,06/16/2005,20.89
12345,07/18/2005,12.12
12345,08/17/2005,10.89
12345,09/17/2005,12.87
12345,10/16/2005,13.89
3421,05/10/2005,15.00
3421,06/11/2005,20.00
3421,07/11/2005,14.15
3421,08/12/2005,15.54
Expected result.
12345,04/15/2005,cd1,04/18/2005,10.10
12345,04/15/2005,cd2,04/18/2005,10.10
12345,04/22/2005,cd3,05/24/2005,13.00
12345,07/03/2005,cd4,07/18/2005,12.12
12345,09/10/2005,cd5,09/17/2005,12.87
3421,05/06/2005,cd7,05/10/2005,15.00
3421,07/04/2005,cd8,07/11/2005,14.15
3421,07/15/2005,cd9,08/12/2005,15.54
I really appreciate if someone can give solution for this using a query (SQL server,Access,Foxpro) is fine.
I am able to do this oracle using count(trandt) over(partition trandt order by 1) as cnt.
and cnt=1.
If u want I can post that too for getting any idea.
I need more information. I would assume that idno is the primary key on both tables, except that it is not unique for each row. If the key is the idno and the date column on each table, then you will not be able to perform this query as you won't be able to join the two tables together. I will attempt the query, but I am not sure it is what you want without knowing more information. Please reply to let me know whether this answere your question, or whether you need more help. As you either want the current month's cycdt or the next months, I suggest join to two copies of the second table, one joining on the same month, one joining on the subsequent month:
select
case t1.transdt > t2a.cycdt
then t2b.cycdt -- get next month's cycdt
else t2a.cycdt -- get this month's cycdt
end as cycdt,
case t1.transdt > t2a.cycdt
then t2b.amt -- get next month's amt
else t2a.amt -- get next month's amt
end as amt
from table1 as t1
join table2 as t2a -- current month join
on t2a.idno = t1.idno and month(t2a.cycdt) = month(t1.transdt)
join table2 as t2b -- joins to next month.
on t2b.idno = t1.idno and month(t2b.cycdt) = month(t1.transdt) + 1
-- The second join may need to be an outer join, as will restrict result set to
-- include only dates upto the month before the latest month. I don't know if
-- this is going to be a problem for you
For more T-SQL tips and advice, visit my blog:
|||Whoops, forgot the first three columns.
select
t1.Idno,
t1.transdt,
t1.cd,
case
when t1.transdt > t2a.cycdt then t2b.cycdt -- get next month's cycdt
else t2a.cycdt -- get this month's cycdt
end as cycdt,
case when t1.transdt > t2a.cycdt then t2b.amt -- get next month's amt
else t2a.amt -- get next month's amt
end as amt
from table1 as t1
join table2 as t2a -- current month join
on t2a.idno = t1.idno and month(t2a.cycdt) = month(t1.transdt)
join table2 as t2b -- joins to next month.
on t2b.idno = t1.idno and month(t2b.cycdt) = month(t1.transdt) + 1
|||First, I really appreciate you in taking time to write the query.
Basically, its not a formal table but just set of two result set and need to produce report out of it as one time. so, I am not making it as any primary key or something. But Idno is the main link between the tables.
Also, this is test tables as my original tables have 3million in table1 and 500k in table2.
The solution you had given works fine as long as there are no missing cycdt in between. But if one cycdt misses the query is not pulling the proper records.
(E.g) try removing the record 12345,05/24/05 from table2.
Let me know if you need more info.
I have this in Oracle but not able to use "Over - Partition" thing since I am using SQL server 8.0
Here it is in Oracle.
SELECT IDNO,TRANDT,IDNO1,CYCDT,AMT FROM
(SELECT IDNO,TRANDT,IDNO1,CYCDT,AMT,COUNT(TRANDT) OVER(PARTITION BY TRANDT ORDER BY 1) CNT
FROM
( SELECT T1.IDNO IDNO,T1.TRANDT,T2.IDNO IDNO1,T2.CYCDT,T2.AMT
FROM tab1 T1,tab2 T2 WHERE T1.IDNO=T2.IDNO )
WHERE (TO_CHAR(TRANDT,'MM') = TO_CHAR(CYCDT,'MM') AND
TO_CHAR(TRANDT,'DD') < TO_CHAR(CYCDT,'DD')) OR
(TO_NUMBER(TO_CHAR(TRANDT,'MM'))+1 = TO_CHAR(CYCDT,'MM'))
ORDER BY 1,2,4
)
WHERE TO_CHAR(TRANDT,'MM') = TO_CHAR(CYCDT,'MM') OR CNT=1ORDER BY 1 DESC,2,4
No comments:
Post a Comment