Sql query help

[mettastar]

On 5/27/2016 at 3:11 PM, loveindia said:

in the other comment i posted if you see man, you don't need the date actually... i posted with date because I just wrote it that way to see it.. Lead or Lag will not work in this case.. please prove me wrong and I would like to see it too...

Tharvatha idi run chesi chudu @loveindia

 

CREATE TABLE EXAMPLE AS (
 SELECT '2016-03-01' AS Date, 'Internet' AS Service, 'March' AS CurrMonth, 10 AS CURR_COUNTS
 UNION ALL
 SELECT '2016-04-01', 'Internet', 'April', 10
 UNION ALL
 SELECT '2016-02-01', 'Internet', 'Feb', 11
  UNION ALL
 SELECT '2016-01-01', 'Internet', 'Jan', 11
 );
SELECT *,
Lag(Date) over(partition by service order by date) as LG
FROM EXAMPLE ;

[loveindia]

3 hours ago, mettastar said:

Tharvatha idi run chesi chudu @loveindia

 

CREATE TABLE EXAMPLE AS (
 SELECT '2016-03-01' AS Date, 'Internet' AS Service, 'March' AS CurrMonth, 10 AS CURR_COUNTS
 UNION ALL
 SELECT '2016-04-01', 'Internet', 'April', 10
 UNION ALL
 SELECT '2016-02-01', 'Internet', 'Feb', 11
  UNION ALL
 SELECT '2016-01-01', 'Internet', 'Jan', 11
 );
SELECT *,
Lag(Date) over(partition by service order by date) as LG
FROM EXAMPLE ;

nenu run chesta okay man... but before that nuvvu run chesaavaa??? Lag and Lead functions use chesedi manam running totals alanti vaati kosam... when you use a lag / lead operation, first record will automatically become a null.. what will you do for that?? next did you see the output he requested man, just okka date kaadu kaavalsindi man, all columns, including date, count etc.., in your case you have to write different lag statements for each column man... which I don't think is a correct way of doing... I hope you understood where I am coming from man...

[loveindia]

4 hours ago, mettastar said:

nuvvu ichina query lo what happens if you have 12 months of data? in your self join for december it will give 11 output records.. @loveindia

 

Run below and tell me how many records u r getting for April

WITH CTE AS (
 SELECT '2016-03-01' AS [Date], 'Internet' AS [Service], 'March' AS CurrMonth, 10 AS CURR_COUNTS
 UNION ALL
 SELECT '2016-04-01', 'Internet', 'April', 10
 UNION ALL
 SELECT '2016-02-01', 'Internet', 'Feb', 11
  UNION ALL
 SELECT '2016-01-01', 'Internet', 'Jan', 11
 )
SELECT *
FROM CTE a 
INNER JOIN CTE b ON a.[Service] = b.[Service] AND a.[Date] > b.[Date]
WHERE a.[Date] = '2016-04-01';

don't use the date man... nenu just edo code copy paste chesanu, next daantlo remove the date ani post chesaaga... yes and ofcourse aa date ki dateadd vaadi 1 month maatram back vellelaaga join raaskovaali man... thats the idea there, thats why I asked him everytime two records ye untaaya or more ani...

[loveindia]

@mettastar see this man.. edo tondarlo april date copy paste chesanu man.. adey pattukunnav nuvvu main essence vadilesaav...

see this man...

WITH CTE AS (
 SELECT '2016-03-01' AS [Date], 'Internet' AS [Service], 'March' AS CurrMonth, 10 AS CURR_COUNTS
 UNION ALL
 SELECT '2016-04-01', 'Internet', 'April', 10
 UNION ALL
 SELECT '2016-02-01', 'Internet', 'Feb', 11
  UNION ALL
 SELECT '2016-01-01', 'Internet', 'Jan', 11
 )
SELECT *
FROM CTE a 
LEFT JOIN CTE b ON a.[Service] = b.[Service] AND DATEADD(MONTH, -1, a.[Date]) = b.[Date]

[former]

On 5/27/2016 at 0:03 PM, nenuVedava said:

I have a situation where I need to Insert 2 rows(little info from second row) into single row.

 

 

 

Data looks like this:

 

 

 

Date                 Service      CurrMonth  CURR_COUNTS

 

 

 

2016-03-01      Internet     March                    10

 

 

 

2016-04-01      Internet      April                      10

 

 

 

I need a result set in this way: have to show the latest month date and put the Curr month as prev month in single row with corresponding counts.

 

 

 

DATE                  Service                 Current Month     PrevMonth    CURR_M_COUNTS       PREV_M _COUNTS

 

 

 

2016-04-01       Internet                April                     March                       10                              10

 

 

 

 

 

Please help.

 

 

 

Neku always only 2 month rows ee vuntaya ? Please find below:

With source_tab as ( 

Select date,source, currmonth as month, counts, row_number() partition by (source, date description) as source_month of

From table name

)

Select

    A.date as curr_date,

    A.Source,

    A.month as curr_month,

    B.month as prev_month,

    A.count as curr_month_counts,

    B.count as prev_month_counts

From source_tab A, Source_tab B

Where A.source = B.source

And A.source_monthid =1 

And B.source_monthid =2

;

[former]

25 minutes ago, loveindia said:

@mettastar see this man.. edo tondarlo april date copy paste chesanu man.. adey pattukunnav nuvvu main essence vadilesaav...

see this man...

WITH CTE AS (
 SELECT '2016-03-01' AS [Date], 'Internet' AS [Service], 'March' AS CurrMonth, 10 AS CURR_COUNTS
 UNION ALL
 SELECT '2016-04-01', 'Internet', 'April', 10
 UNION ALL
 SELECT '2016-02-01', 'Internet', 'Feb', 11
  UNION ALL
 SELECT '2016-01-01', 'Internet', 'Jan', 11
 )
SELECT *
FROM CTE a 
LEFT JOIN CTE b ON a.[Service] = b.[Service] AND DATEADD(MONTH, -1, a.[Date]) = b.[Date]

Nice. It will give all months curr & prev months values. 

[mettastar]

18 hours ago, loveindia said:

@mettastar see this man.. edo tondarlo april date copy paste chesanu man.. adey pattukunnav nuvvu main essence vadilesaav...

see this man...

WITH CTE AS (
 SELECT '2016-03-01' AS [Date], 'Internet' AS [Service], 'March' AS CurrMonth, 10 AS CURR_COUNTS
 UNION ALL
 SELECT '2016-04-01', 'Internet', 'April', 10
 UNION ALL
 SELECT '2016-02-01', 'Internet', 'Feb', 11
  UNION ALL
 SELECT '2016-01-01', 'Internet', 'Jan', 11
 )
SELECT *
FROM CTE a 
LEFT JOIN CTE b ON a.[Service] = b.[Service] AND DATEADD(MONTH, -1, a.[Date]) = b.[Date]

@loveindia  uncle nuvvu first inner join chesinav.. now u changed ur query..

sare nee kotha query lo kuda first record ki null ey vastadi ga?

 

 

 

[loveindia]

18 hours ago, mettastar said:

@loveindia  uncle nuvvu first inner join chesinav.. now u changed ur query..

sare nee kotha query lo kuda first record ki null ey vastadi ga?

 

 

 

@mettastar man, I will still stick to my inner join query only man... nee query prakaram raavali antey left join raasanu... as per the TS ouput aitey I will use inner join only... if you see his requirement well, he is saying 2 records ni 1 ga cheyali ani... so there is no matter of previous record and that's why we don't need to use LAG / LEAD and also by using that you are writing in 4 different times of LAG() for 4 different columns...

[Quickgun_murugan]

34 minutes ago, loveindia said:

@mettastar man, I will still stick to my inner join query only man... nee query prakaram raavali antey left join raasanu... as per the TS ouput aitey I will use inner join only... if you see his requirement well, he is saying 2 records ni 1 ga cheyali ani... so there is no matter of previous record and that's why we don't need to use LAG / LEAD and also by using that you are writing in 4 different times of LAG() for 4 different columns...

GP

[mtkr]

 

create table #counts

(

DateService date,

CurrMonth varchar(10),

CURRCOUNTS varchar(10)

)

 

 

insert into #counts values( '2016-04-01', 'April', '10')

insert into #counts values( '2016-03-01', 'March', '10')

 

 

 

select a.DateService, a.CurrMonth, b.CurrMonth, a.CURRCOUNTS, b.CURRCOUNTS

from

(SELECT t1.*, t2.rwn, t2.dtserviceyr

FROM #counts t1

INNER JOIN

    (

SELECT MAX(DateService) AS MaxDateTime, YEAR(dateservice) as dtserviceyr, RANK() OVER (PARTITION BY year(DateService) ORDER BY DateService desc) rn, ROW_NUMBER() OVER(ORDER BY DateService desc) rwn

    FROM #counts

        GROUP BY DateService) t2

ON

t1.DateService = t2.MaxDateTime

and t2.rn = 1

)a

inner join

(

SELECT t3.*, t4.rwn, t4.dtserviceyr

FROM #counts t3

INNER JOIN

    (

SELECT MAX(DateService) AS MaxDateTime, YEAR(dateservice) as dtserviceyr, RANK() OVER (PARTITION BY year(DateService) ORDER BY DateService desc) rn, ROW_NUMBER() OVER(ORDER BY DateService desc) rwn

    FROM #counts

        GROUP BY DateService) t4

ON t3.DateService = t4.MaxDateTime

and t4.rn = 2

)b

on a.dtserviceyr = b.dtserviceyr

 

Result:

DateService    CurrMonth    CurrMonth    CURRCOUNTS    CURRCOUNTS

2016-04-01    April           March          10             10

 

 

[loveindia]

14 minutes ago, mtkr said:

 

create table #counts

(

DateService date,

CurrMonth varchar(10),

CURRCOUNTS varchar(10)

)

 

 

insert into #counts values( '2016-04-01', 'April', '10')

insert into #counts values( '2016-03-01', 'March', '10')

 

 

 

select a.DateService, a.CurrMonth, b.CurrMonth, a.CURRCOUNTS, b.CURRCOUNTS

from

(SELECT t1.*, t2.rwn, t2.dtserviceyr

FROM #counts t1

INNER JOIN

    (

SELECT MAX(DateService) AS MaxDateTime, YEAR(dateservice) as dtserviceyr, RANK() OVER (PARTITION BY year(DateService) ORDER BY DateService desc) rn, ROW_NUMBER() OVER(ORDER BY DateService desc) rwn

    FROM #counts

        GROUP BY DateService) t2

ON

t1.DateService = t2.MaxDateTime

and t2.rn = 1

)a

inner join

(

SELECT t3.*, t4.rwn, t4.dtserviceyr

FROM #counts t3

INNER JOIN

    (

SELECT MAX(DateService) AS MaxDateTime, YEAR(dateservice) as dtserviceyr, RANK() OVER (PARTITION BY year(DateService) ORDER BY DateService desc) rn, ROW_NUMBER() OVER(ORDER BY DateService desc) rwn

    FROM #counts

        GROUP BY DateService) t4

ON t3.DateService = t4.MaxDateTime

and t4.rn = 2

)b

on a.dtserviceyr = b.dtserviceyr

 

Result:

DateService    CurrMonth    CurrMonth    CURRCOUNTS    CURRCOUNTS

2016-04-01    April           March          10             10

 

 

endi man ee racha... 

[nenu_devudni]

On 5/19/2016 at 4:12 PM, loveindia said:

;WITH CTE AS(
SELECT ucbcust_cust_code cust_code, 
           REPLACE(ucbcust_last_name,'|','') last_name,           
           upper(ucrtele_phone_area), 
           upper(ucrtele_phone_number),
           upper(ucrtele_phone_ext), 
           upper(ucrtele_tele_code),
           ROW_NUMBER() OVER(PARTITION BY ucbcust_cust_code ORDER BY ucrtele_tele_code) AS RNUM
      FROM ucbcust, ucrtele
      where ucbcust_cust_code = ucrtele_cust_code (+)
      and ucbcust_cust_code = 1
    ORDER BY DECODE (ucrtele_tele_code, 'BU', 'AA', 'HM', 'AB', 'ZZ')
)
SELECT *
FROM CTE 
WHER Rnum = 1

 

something like this will help you man...

gp 

 

[loveindia]

7 minutes ago, nenu_devudni said:

gp 

 

aa post enti ee post enti... aa query enti ee query enti man... 

[mtkr]

9 minutes ago, loveindia said:

endi man ee racha... 

first selecting current rec... nxt selecting prev rec... thn ovr all combining two recs... 

[loveindia]

27 minutes ago, mtkr said:

first selecting current rec... nxt selecting prev rec... thn ovr all combining two recs... 

aa type programming BCBS lo nadustadi man, bayata nadavadu...