Sql query help

[Darling999]

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.

 

[rrc_2015]

You have to insert ... Or  Just get result set in that fashion

[rrc_2015]

Select t1.cols, t2.cols from table1 t1 inner join table1 t2 on t1.service = t2.service

 

Join condition can be more proper based on your fields 

[mastercheif]

use lag function.

 

http://www.techonthenet.com/oracle/functions/lag.php

[mettastar]

lead lag functions try cheyi

[loveindia]

eppudu 2 records ye vastaaya man???

[loveindia]

and also how do differentiate between current month and previous month from march and april man??

[loveindia]

1 hour ago, rrc_2015 said:

Select t1.cols, t2.cols from table1 t1 inner join table1 t2 on t1.service = t2.service

 

Join condition can be more proper based on your fields 

if this ever worked let me know man... this will definitely not work...

[loveindia]

use 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
 )
SELECT *
FROM CTE a 
INNER JOIN CTE b ON a.[Service] = b.[Service] AND a.[Date] > b.[Date]
WHERE a.[Date] = '2016-04-01'

[loveindia]

you don't have to do the date = filter using where clause, I just used it for my purpose... even if you remove it you get the same result man... good luck..

[mettastar]

1 minute ago, loveindia said:

use 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
 )
SELECT *
FROM CTE a 
INNER JOIN CTE b ON a.[Service] = b.[Service] AND a.[Date] > b.[Date]
WHERE a.[Date] = '2016-04-01'

how can you hardcode date there.. it is better to use lead lag functions

[loveindia]

1 hour ago, mettastar said:

how can you hardcode date there.. it is better to use lead lag functions

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...

[Darling999]

Deniki preeceedig. Function vaadali I got the result using that. Just required rows ki ah preceding function used.

[jack9]

31 minutes ago, nenuVedava said:

Deniki preeceedig. Function vaadali I got the result using that. Just required rows ki ah preceding function used.

code paste chey.. maakkooda help avuddi ga

[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...

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';