Query help please

sheya on 12/04/2016 - 17:41
Last edited 12/04/2016 - 17:48 by 1 other user

hi

i have the below table and the outcomes i want are in the last column.

ID  period          rate    In result?
1   2015-01         1%  No
1   2015-03         2%  No
1   2015-03         2%  No
1   2016-02         2%  Yes
1   2016-02         3%  Yes
1   2017-03         4%  No
1   2017-03         4%  No
1   2017-03         4%  No
1   2017-04         4%  Yes
1   2017-04         1%  Yes
1   2017-04         4%  Yes

i have so far the below -

SELECT ID, period, count(period)
INTO temp
FROM table 1
Group by ID, period
having count(period) >1

but how do i put rate into all this?

• 

  tabz on 12/04/2016 - 19:02

  Is this your homework?

• 

  the-mal on 12/04/2016 - 21:15

  How do you want rate to appear? Do you want the granularity to be Id-Period-Rate grain or still as Id-Period grain like you have it?

  If you want the former just add 'rate' to your Select and Group By. You can leave the count on Period as it's not a distinct count so it won't make a difference as you have no Nulls.

• 

  Ryanek on 12/04/2016 - 21:32

  declare @temp table (id varchar(1), period varchar(10), rate varchar(2))
  insert into @temp select '1', '2015-01', '1%'
  insert into @temp select '1', '2015-03', '2%'
  insert into @temp select '1', '2015-03', '2%'
  insert into @temp select '1', '2016-02', '2%'
  insert into @temp select '1', '2016-02', '3%'
  insert into @temp select '1', '2017-03', '4%'
  insert into @temp select '1', '2017-03', '4%'
  insert into @temp select '1', '2017-03', '4%'
  insert into @temp select '1', '2017-04', '4%'
  insert into @temp select '1', '2017-04', '1%'
  insert into @temp select '1', '2017-04', '4%'

  ;with cte as
  (
  select id, period
  from
  (
  select distinct id, period, rate
  from @temp
  ) m
  group by id, period
  having count(0) > 1
  )

  select t.*, case when a.id is null then 'No' else 'Yes' end as [In Result]
  from @temp t
  left outer join cte a on t.id = a.id and t.period = a.period

• 

  Donaldhump on 12/04/2016 - 22:04

  with X as
  (
  Select I'd, period
  From table
  Group by I'd, period
  Having count(distinct rate) > 1
  )
  Select t.*,case when X.id is null then 'no' else 'yes'
  From table t left outer join x on t.id ……………..………

  Might be able to use dense rank function too to elimate need for two table scans

  Select t.

• 

  sheya on 12/04/2016 - 22:44

  the rule is if the rate has changed in the same month (and year) then spit it out.
  not even and odd..

  so if there is only one record for period = 2014-01 then don't bring it up.
  if there are 2 records and the rates are different bring it up. if there are 2 records and the rates are the same don't bring up.

• 

  tabz on 13/04/2016 - 07:01

  There's a few ways you can do that. Here's one that should work using nested queries:

  SELECT *
  FROM table
  WHERE (ID, period) IN
  (
  — select only those that have a rate change in the same month
  SELECT
  a.ID
  ,a.period
  FROM
  (
  — dedupe to get rid of no rate changes in the same month
  SELECT DISTINCT
  ID
  ,period
  ,rate
  FROM table
  ) a
  GROUP BY a.ID, a.period
  HAVING COUNT(*) > 1
  );