kiran karthik Posted August 13, 2015 Report Posted August 13, 2015 table structure like I_S S_N I_F_N T_N FUNC_DATE DATE_LOAD COUNT IS1 SN1 IFN1 TN1 13-Aug-15 13-Aug-15 2954792 IS1 SN1 IFN2 TN1 12-Aug-15 12-Aug-15 345 IS1 SN1 IFN3 TN1 11-Aug-15 11-Aug-15 345252 IS1 SN1 IFN4 TN1 10-Aug-15 10-Aug-15 28234 IS1 SN1 IFN5 TN1 07-Aug-15 07-Aug-15 6372 i want to find prev business day count for each record I_S S_N I_F_N T_N FUNC_DATE DATE_LOAD COUNT PREV_COUNT IS1 SN1 IFN1 TN1 13-Aug-15 13-Aug-15 2954792 345 IS1 SN1 IFN2 TN1 12-Aug-15 12-Aug-15 345 345252 IS1 SN1 IFN3 TN1 11-Aug-15 11-Aug-15 345252 28234 IS1 SN1 IFN4 TN1 10-Aug-15 10-Aug-15 28234 6372 IS1 SN1 IFN5 TN1 07-Aug-15 07-Aug-15 6372 0 I don't want to use lag() n lead() Thanks in advance
k2s Posted August 13, 2015 Report Posted August 13, 2015 table structure like I_S S_N I_F_N T_N FUNC_DATE DATE_LOAD COUNT IS1 SN1 IFN1 TN1 13-Aug-15 13-Aug-15 2954792 IS1 SN1 IFN2 TN1 12-Aug-15 12-Aug-15 345 IS1 SN1 IFN3 TN1 11-Aug-15 11-Aug-15 345252 IS1 SN1 IFN4 TN1 10-Aug-15 10-Aug-15 28234 IS1 SN1 IFN5 TN1 07-Aug-15 07-Aug-15 6372 i want to find prev business day count for each record I_S S_N I_F_N T_N FUNC_DATE DATE_LOAD COUNT PREV_COUNT IS1 SN1 IFN1 TN1 13-Aug-15 13-Aug-15 2954792 345 IS1 SN1 IFN2 TN1 12-Aug-15 12-Aug-15 345 345252 IS1 SN1 IFN3 TN1 11-Aug-15 11-Aug-15 345252 28234 IS1 SN1 IFN4 TN1 10-Aug-15 10-Aug-15 28234 6372 IS1 SN1 IFN5 TN1 07-Aug-15 07-Aug-15 6372 0 I don't want to use lag() n lead() Thanks in advance
loveindia Posted August 14, 2015 Report Posted August 14, 2015 Change your keys accordingly man.... DECLARE @TABLE TABLE( I_S VARCHAR(3), S_N VARCHAR(3), I_F_N VARCHAR(4), T_N VARCHAR(3), FUNC_DATE DATE, DATE_LOAD DATE, [COUNT] INT ) INSERT INTO @TABLE VALUES ('IS1','SN1','IFN1','TN1','2015/08/13', '2015/08/13',2954792) ,('IS1','SN1','IFN2','TN1','2015/08/12', '2015/08/12',345) ,('IS1','SN1','IFN3','TN1','2015/08/11', '2015/08/11',345252) ,('IS1','SN1','IFN4','TN1','2015/08/10', '2015/08/10',28234) ,('IS1','SN1','IFN5','TN1','2015/08/07', '2015/08/07',6372) ;WITH CTE AS( select *, ROW_NUMBER() OVER (PARTITION BY I_S, S_N ORDER BY DATE_LOAD) AS Rnum from @TABLE ) SELECT a.I_S, a.S_N, a.I_F_N, a.T_N, a.FUNC_DATE, a.DATE_LOAD, a.[COUNT], ISNULL(b.[count], 0) AS Prev_Count FROM CTE a LEFT JOIN CTE b ON a.I_S = b.I_S AND a.Rnum = b.Rnum + 1 ORDER BY DATE_LOAD DESC
loveindia Posted August 14, 2015 Report Posted August 14, 2015 K K lu P lu raayakapotey code raasi help cheyochu kada man... nee project peru enti man bcbs lo?
manjunath455 Posted August 14, 2015 Report Posted August 14, 2015 Change your keys accordingly man.... DECLARE @TABLE TABLE(I_S VARCHAR(3), S_N VARCHAR(3),I_F_N VARCHAR(4),T_N VARCHAR(3),FUNC_DATE DATE,DATE_LOAD DATE,[COUNT] INT) INSERT INTO @TABLE VALUES ('IS1','SN1','IFN1','TN1','2015/08/13', '2015/08/13',2954792),('IS1','SN1','IFN2','TN1','2015/08/12', '2015/08/12',345),('IS1','SN1','IFN3','TN1','2015/08/11', '2015/08/11',345252),('IS1','SN1','IFN4','TN1','2015/08/10', '2015/08/10',28234),('IS1','SN1','IFN5','TN1','2015/08/07', '2015/08/07',6372) ;WITH CTE AS(select *, ROW_NUMBER() OVER (PARTITION BY I_S, S_N ORDER BY DATE_LOAD) AS Rnumfrom @TABLE)SELECT a.I_S, a.S_N, a.I_F_N, a.T_N, a.FUNC_DATE, a.DATE_LOAD, a.[COUNT], ISNULL(b.[count], 0) AS Prev_CountFROM CTE aLEFT JOIN CTE b ON a.I_S = b.I_S AND a.Rnum = b.Rnum + 1ORDER BY DATE_LOAD DESC Ba SQL ante vuntav ga thread lo :D
loveindia Posted August 14, 2015 Report Posted August 14, 2015 Ba SQL ante vuntav ga thread lo :D nenu unna thread loney SQL untadi man...
ronitreddy Posted August 14, 2015 Report Posted August 14, 2015 nenu unna thread loney SQL untadi man... thoda kottindi saalu man..Naku oka job sudu ..
kiran karthik Posted August 14, 2015 Author Report Posted August 14, 2015 Change your keys accordingly man.... DECLARE @TABLE TABLE( I_S VARCHAR(3), S_N VARCHAR(3), I_F_N VARCHAR(4), T_N VARCHAR(3), FUNC_DATE DATE, DATE_LOAD DATE, [COUNT] INT ) INSERT INTO @TABLE VALUES ('IS1','SN1','IFN1','TN1','2015/08/13', '2015/08/13',2954792) ,('IS1','SN1','IFN2','TN1','2015/08/12', '2015/08/12',345) ,('IS1','SN1','IFN3','TN1','2015/08/11', '2015/08/11',345252) ,('IS1','SN1','IFN4','TN1','2015/08/10', '2015/08/10',28234) ,('IS1','SN1','IFN5','TN1','2015/08/07', '2015/08/07',6372) ;WITH CTE AS( select *, ROW_NUMBER() OVER (PARTITION BY I_S, S_N ORDER BY DATE_LOAD) AS Rnum from @TABLE ) SELECT a.I_S, a.S_N, a.I_F_N, a.T_N, a.FUNC_DATE, a.DATE_LOAD, a.[COUNT], ISNULL(b.[count], 0) AS Prev_Count FROM CTE a LEFT JOIN CTE b ON a.I_S = b.I_S AND a.Rnum = b.Rnum + 1 ORDER BY DATE_LOAD DESC thanks bhayya, vl try this
4Vikram Posted August 14, 2015 Report Posted August 14, 2015 Ba SQL ante vuntav ga thread lo :D nenu unna thread loney SQL untadi man... SQL ante Love vuncl, Love vuncle ante SQL man
ronitreddy Posted August 14, 2015 Report Posted August 14, 2015 SQL ante Love vuncl, Love vuncle ante SQL man mAri nuvvu vuncle?
4Vikram Posted August 14, 2015 Report Posted August 14, 2015 mAri nuvvu vuncle? ivani chepadanki evaro okaru undali kada neeku, adi nene
loveindia Posted August 14, 2015 Report Posted August 14, 2015 SQL ante Love vuncl, Love vuncle ante SQL man
kiran karthik Posted August 15, 2015 Author Report Posted August 15, 2015 Change your keys accordingly man.... DECLARE @TABLE TABLE( I_S VARCHAR(3), S_N VARCHAR(3), I_F_N VARCHAR(4), T_N VARCHAR(3), FUNC_DATE DATE, DATE_LOAD DATE, [COUNT] INT ) INSERT INTO @TABLE VALUES ('IS1','SN1','IFN1','TN1','2015/08/13', '2015/08/13',2954792) ,('IS1','SN1','IFN2','TN1','2015/08/12', '2015/08/12',345) ,('IS1','SN1','IFN3','TN1','2015/08/11', '2015/08/11',345252) ,('IS1','SN1','IFN4','TN1','2015/08/10', '2015/08/10',28234) ,('IS1','SN1','IFN5','TN1','2015/08/07', '2015/08/07',6372) ;WITH CTE AS( select *, ROW_NUMBER() OVER (PARTITION BY I_S, S_N ORDER BY DATE_LOAD) AS Rnum from @TABLE ) SELECT a.I_S, a.S_N, a.I_F_N, a.T_N, a.FUNC_DATE, a.DATE_LOAD, a.[COUNT], ISNULL(b.[count], 0) AS Prev_Count FROM CTE a LEFT JOIN CTE b ON a.I_S = b.I_S AND a.Rnum = b.Rnum + 1 ORDER BY DATE_LOAD DESC vl the below one work out SELECT B.*, (SELECT COUNT FROM TABLE_NAME WHERE I_S=B.I_S AND T_N=B.T_N AND S_N= B.S_N AND TO_CHAR(FUNCTIONAL_DATE, 'DD-MON-YY HH24:MI') = TO_CHAR(B.PREV_DAY, 'DD-MON-YY HH24:MI') ) PREV_COUNT FROM (select A.*,TO_DATE(to_char(decode(to_char(A.functional_date-1,'DY'), to_char( to_date('20000102', 'yyyymmdd' ), 'DY') , A.functional_date-3, A.functional_date-1), 'DD-MON-YY HH24:MI'), 'DD-MON-YY HH24:MI') prev_day from TABLE_NAME A) B;
Recommended Posts