locked
check if the field value is in last 13 month value if not assign the last 13th month begining RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi All, I have this scenario where I do not have a record in the table but iam trying to associate associate the row with a month

    SELECT

    T

    APPID,

    ROUND

    ((SUM(T.OutageMin)/60),2)ASOutageMin,

    CONVERT

    (varchar(10),dateadd(d,-(day(dateadd(m,-1,getdate()-2))),dateadd(m,-13,getdate()-1)),101)ASLast13Month, 

    CASEWHENT.ImpactMonthStart<>CONVERT(varchar(10),dateadd(d,-(day(dateadd(m,-1,getdate()-2))),dateadd(m,-13,getdate()-1)),101) 

    THENT.ImpactMonthStart ElseCONVERT(varchar(10),dateadd(d,-(day(dateadd(m,-1,getdate()-2))),dateadd(m,-13,getdate()-1)),101)

    END

    ASImpactMonthStart,

    ImpactMonthStart

    asImpactMonthStart1,

    T

    .ImpactMonthEnd


    FROM

    (

    (

    )

    for each APPID in ImpactMonthStart I want to show the last 13month values..data exists for only 5 but I want to add all the last 13 month month values to ImpactMonthStart and ImpactMonthEnd fields

    say if the last 13 month which is June 2019 there is value in the ImpactMonthStart then assign 06/01/2019 if it exists then use what is available in ImpactMonthStart - is this possible - assigning the last 13 month starting value when it does not exist so on until last 1 month - so I get 13 rows

    APPID   Outage  Last13Month, ImpactMonthStart ImpactMonStartOrg ImpactMonthEnd

    21845    18.33   06/01/2019     06/01/2019      03/01/2020              03/31/2020
    21845    2.92    06/01/2019      06/01/2019      06/01/2019               06/30/2019
    21845    1.55    06/01/2019      06/01/2019      08/01/2019                08/31/2019
    21845    3.75    06/01/2019      06/01/2019      09/01/2019                09/30/2019
    21845   119.85 06/01/2019     06/01/2019       11/01/2019                 11/30/2019

    Wednesday, July 22, 2020 12:51 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    I want to add the values likes

    06/01/2019 - last 13 month

    07/01/2019 - last 12 month

    08/01/2019 - last 11 month

    09/01/2019 - last 10 month

    10/01/2019 - last 9 month until 06/01/2020 in the column ImpactMonthStart when ImpactMonthStart has no value which means the record does not exist in the table but I want to add these values.. is this possible

    Wednesday, July 22, 2020 1:00 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    like below screenshot adding the monthstarting value even when there is no data in it

    Wednesday, July 22, 2020 1:11 AM
  • Question
    Sign in to vote
    0
    Sign in to vote 
    --Do you want the coloumn shows as next?
    SELECT cast(LASTMONTH as varchar) + '_LAST' +CAST(dense_rank() over (order by lastmonth  desc) as varchar) + 'MONTH 'as LASTMONTH from T1

    test code:

    USE TEST
GO

CREATE TABLE T1 (LASTMONTH DATE,ImpactMonthStart DATE)
INSERT INTO T1 VALUES ('2020-6-1','2020-03-01');
INSERT INTO T1 VALUES ('2020-5-1','2020-03-01');
INSERT INTO T1 VALUES ('2020-4-1','2020-03-01');
INSERT INTO T1 VALUES ('2020-3-1','2020-03-01');
INSERT INTO T1 VALUES ('2020-2-1','2020-03-01');
INSERT INTO T1 VALUES ('2020-1-1','2020-03-01');
INSERT INTO T1 VALUES ('2019-12-1','2020-03-01');
INSERT INTO T1 VALUES ('2019-11-1','2020-03-01');
INSERT INTO T1 VALUES ('2019-10-1','');
INSERT INTO T1 VALUES ('2019-9-1','');
INSERT INTO T1 VALUES ('2019-8-1','');
INSERT INTO T1 VALUES ('2019-7-1','');
INSERT INTO T1 VALUES ('2019-6-1','');
INSERT INTO T1 VALUES ('2020-6-1','2020-03-01');
INSERT INTO T1 VALUES ('2020-5-1','2020-03-01');
INSERT INTO T1 VALUES ('2019-10-1','');
INSERT INTO T1 VALUES ('2019-9-1','');
--SELECT * FROM T1
SELECT cast(LASTMONTH as varchar) + '_LAST' +CAST(dense_rank() over (order by lastmonth  desc) as varchar) + 'MONTH 'as LASTMONTH from T1

    Wednesday, July 22, 2020 8:25 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi

    Thanks for your time, no, actually this is kind of adding the data when there is no value exists for example from the below dataset for 21845 we have some data in outage column for the months of March 2020, June 2019, aug 2019, sep 2019, nov 2019 - I want to add outage as 0 for the remaining months  from the last 13 months which are - below are my last 13 months which I use for the report...for this APPID 21845 I need to add the missing outage as 0 and missing month

    June 2019,

    July 2019

    Aug 2019

    Sep 2019

    Oct 2019

    Nov 2019

    Dec 2019

    Jan 2020

    Feb 2020

    Mar 2020

    Apr 2020

    May 2020

    June 2020

    APPID   Outage  Last13Month, ImpactMonthStart ImpactMonStartOrg ImpactMonthEnd

    21845    18.33   06/01/2019     03/01/2019      03/01/2020              03/31/2020
    21845    2.92    06/01/2019      06/01/2019      06/01/2019               06/30/2019
    21845    1.55    06/01/2019      08/01/2019      08/01/2019                08/31/2019
    21845    3.75    06/01/2019      09/01/2019      09/01/2019                09/30/2019
    21845   119.85 06/01/2019       11/01/2019       11/01/2019                 11/30/2019

    null        0           06/01/2019     12/01/2019        12/01/2019                 12/31/2019    

    null        0           06/01/2019     01/01/2020          01/01/2020               01/31/2020

    null        0           06/01/2019        02/01/2020        02/01/2020               02/29/2020

    null        0          06/01/2019         03/01/2020        03/01/2020              03/31/2020

    null       0           06/01/2019           04/01/2020      04/01/2020             04/30/2020

    null       0          06/01/2019            05/01/2020      05/01/2020             05/31/2020

    null       0         06/01/2019           06/01/2020         06/01/2020           06/30/2020

     

    thanks for your time,

    Wednesday, July 22, 2020 11:59 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi, could some one please guide me on how to insert those data values when they don't exist

    when the APPID has no data in outage it will not show the month name but I want to assign 0 and assign the month that is not available for

    Thanks a lot

    Wednesday, July 22, 2020 12:56 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    For this type of question, you help yourself immensely if you post CREATE TABLE statements for your table(s) together with INSERT statements with sample data, enough to illustrate all angles of the problem. And of course, we need to know the desired result from the sample data. A short descrioption of the business rules which explains why you want that result also helps.

    This helps to clarify your question. And it permits us to copy and paste into a query window so that we can develop a tested solution.

    Without that you will get guesses and shots from the hip.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, July 22, 2020 9:43 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Same reply from your case in t-sql forum: insert-values-or-appending-values-when-there-is-no-data

    Replied by Jingyang Li

    CREATE TABLE mytable(
   APPID              INTEGER  NOT NULL  
  ,Outage             decimal(5,2)
  ,Last13Month        DATE 
  ,ImpactMonthStart  DATE 
  ,ImpactMonStartOrg  DATE 
  ,ImpactMonthEnd     DATE 
);
INSERT INTO mytable(APPID,Outage,Last13Month,ImpactMonthStart,ImpactMonStartOrg,ImpactMonthEnd) VALUES
 (21845,18.33,'6/1/2019','03/01/2019','03/01/2020','03/31/2020')
,(21845,2.92,'6/1/2019','06/01/2019','06/01/2019','06/30/2019')
,(21845,1.55,'6/1/2019','08/01/2019','08/01/2019','08/31/2019')
,(21845,3.75,'6/1/2019','09/01/2019','09/01/2019','09/30/2019')
,(21845,119.85,'6/01/2019','11/01/2019','11/01/2019','11/30/2019');


 




;with dtcte as (select Cast(dateadd(month, -n,dateadd(month,datediff(month,0,getdate()),0)) as date) dt
from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)) d(n)
)
,idcte as (
select distinct APPID from mytable )

,sourcecte as (
Select APPID, dt from dtcte, idcte)

Select s.APPID,Coalesce(Outage,0) Outage
,max(Last13Month) Over(Partition by s.APPID) Last13Month 
,Coalesce(ImpactMonthStart,dt) ImpactMonthStart 
,Coalesce(ImpactMonStartOrg,dt) ImpactMonStartOrg, 
Coalesce(ImpactMonthEnd,EOMONTH(dt)) ImpactMonthEnd
from sourcecte s left join mytable m on s.APPID=m.APPID and s.dt=m.ImpactMonthStart



drop TABLE mytable


    Thursday, July 23, 2020 3:12 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi SQLServerRaj,

    Is the issue solved?

    Is the reply helpful?Your other case  in t-sql forum: insert-values-or-appending-values-when-there-is-no-data

    is always open, you can choose to mark the helpful reply to close the case.

    BR,

    MIAOYUXI

    Friday, July 24, 2020 1:58 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi SQLServerRaj,

    Is the issue solved?

    BR,

    MIAOYUXI

    Monday, July 27, 2020 12:19 AM