locked
different date format and calculation RRS feed

  • Question

  • I have data looks like below

     

    ProductID   ShipDate(YYYYMMDD)    Report Date(YMM)

    2746890      20070110                            704

    2345670      20070113                            702

    20070717     20070617                           707

    2345899       20070610                           708

    1111111       20070611                           711

     Etc..

     

    I need to calculate the number of days between the 2 dates Ship date and report date

     Since Report Date has  only month level… if the month =1 then <= 30 days if the month is between 1 and 2 and  30 > and 60<=   and if month is more than 2 than > 60 days…) and count the products which are belong to the date range

     

    ·         Report date will only have greater than year 2000

            

    The output should look like

     

    ShipDate(month)                         <= 30                   30 >  < 60        > 60 

    Jan 2007                                        1                               0                1

    Jun  2007                                       1                              1                 1

    Etc…

     

    How can I can I do this.. since ship date and report data has different date format.. and how can I do this calculation?

     

    • Edited by SSAS_5000 Monday, July 19, 2010 3:05 PM edit
    Monday, July 19, 2010 3:05 PM

Answers

  • declare @data table
    (
      ProductID    int,
      ShipDate  datetime,
      ReportDate  int
    )
    
    insert into @data
    select  2746890,   '20070110', 704  union all
    select  2345670,   '20070113', 702  union all
    select  20070717,   '20070617', 707  union all
    select  2345899,   '20070610', 708  union all
    select  1111111,   '20070611', 711
    
    ; with cte1
    as
    (
      select  *, RptDate = dateadd(month, ReportDate % 100 - 1, dateadd(year, ReportDate / 100, '20000101'))
      from  @data
    ),
    cte2
    as
    (
      select  RptDate,
        date_range  = case  when   datediff(day, ShipDate, RptDate) <= 30
              then  '<= 30'
              when  datediff(day, ShipDate, RptDate) between 31 and 60
              then  '30 > < 60'
              when  datediff(day, ShipDate, RptDate) > 60
              then  '> 60'
              end
      from  cte1
    )
    select  *
    from  cte2
      pivot
      (
        count(date_range)
        for date_range in ([<= 30], [30 > < 60], [> 60])
      ) p
    
    /* -- RESULT
    RptDate                        <= 30    30 > < 60  > 60    
    ------------------------------------------------------ ----------- ----------- -----------
    2007-02-01 00:00:00.000                1      0      0
    2007-04-01 00:00:00.000                0      0      1
    2007-07-01 00:00:00.000                1      0      0
    2007-08-01 00:00:00.000                0      1      0
    2007-11-01 00:00:00.000                0      0      1
    
    (5 row(s) affected)
    */
    
    


    KH Tan
    • Proposed as answer by Bhudev Monday, July 19, 2010 7:35 PM
    • Marked as answer by KJian_ Monday, July 26, 2010 8:39 AM
    Monday, July 19, 2010 3:28 PM

All replies

  • declare @data table
    (
      ProductID    int,
      ShipDate  datetime,
      ReportDate  int
    )
    
    insert into @data
    select  2746890,   '20070110', 704  union all
    select  2345670,   '20070113', 702  union all
    select  20070717,   '20070617', 707  union all
    select  2345899,   '20070610', 708  union all
    select  1111111,   '20070611', 711
    
    ; with cte1
    as
    (
      select  *, RptDate = dateadd(month, ReportDate % 100 - 1, dateadd(year, ReportDate / 100, '20000101'))
      from  @data
    ),
    cte2
    as
    (
      select  RptDate,
        date_range  = case  when   datediff(day, ShipDate, RptDate) <= 30
              then  '<= 30'
              when  datediff(day, ShipDate, RptDate) between 31 and 60
              then  '30 > < 60'
              when  datediff(day, ShipDate, RptDate) > 60
              then  '> 60'
              end
      from  cte1
    )
    select  *
    from  cte2
      pivot
      (
        count(date_range)
        for date_range in ([<= 30], [30 > < 60], [> 60])
      ) p
    
    /* -- RESULT
    RptDate                        <= 30    30 > < 60  > 60    
    ------------------------------------------------------ ----------- ----------- -----------
    2007-02-01 00:00:00.000                1      0      0
    2007-04-01 00:00:00.000                0      0      1
    2007-07-01 00:00:00.000                1      0      0
    2007-08-01 00:00:00.000                0      1      0
    2007-11-01 00:00:00.000                0      0      1
    
    (5 row(s) affected)
    */
    
    


    KH Tan
    • Proposed as answer by Bhudev Monday, July 19, 2010 7:35 PM
    • Marked as answer by KJian_ Monday, July 26, 2010 8:39 AM
    Monday, July 19, 2010 3:28 PM
  • Hi please take the following string and datetime convertions into account and try to modify your script.

    declare @s varchar(10) = '20070617' --YYYYMMDD
    select convert(datetime,@s,103)
    
    declare @s2 varchar(10) = '704' -- YMM
    select '200' + substring(@s2,1,1) + substring(@s2,2,2) + '01'
    select convert(datetime,('200' + substring(@s2,1,1) + substring(@s2,2,2) + '01'),103)
    
    select DATEDIFF(dd, convert(datetime,@s,103), convert(datetime,('200' + substring(@s2,1,1) + substring(@s2,2,2) + '01'),103))
    

    I hope that helps,


    SQL Server and T-SQL Tutorials
    My Personal Site
    Our true mentor in life is science
    Monday, July 19, 2010 4:28 PM
  • You could try this, you would have to replace [YourTableName] with your correct table name:

    SELECT DISTINCT
    SUBSTRING(ltrim(A.ShipDate),5,2) + '/' + SUBSTRING(ltrim(A.ShipDate),1,4) AS ShipMonth,
    (SELECT COUNT(*) FROM YourTableName B WHERE SUBSTRING(ltrim(A.ShipDate),5,2) + '/' + SUBSTRING(ltrim(A.ShipDate),1,4) = SUBSTRING(ltrim(B.ShipDate),5,2) + '/' + SUBSTRING(ltrim(B.ShipDate),1,4) AND ABS(datediff(day,CONVERT(datetime,SUBSTRING(B.ReportDate,2,2)+'/1/200'+SUBSTRING(B.ReportDate,1,1)),CONVERT(datetime,SUBSTRING(B.ShipDate,5,2)+'/'+substring(B.Shipdate,7,2)+'/'+SUBSTRING(B.Shipdate,1,4)))) < 30) as [<30],
    (SELECT COUNT(*) FROM YourTableName B WHERE SUBSTRING(ltrim(A.ShipDate),5,2) + '/' + SUBSTRING(ltrim(A.ShipDate),1,4) = SUBSTRING(ltrim(B.ShipDate),5,2) + '/' + SUBSTRING(ltrim(B.ShipDate),1,4) AND ABS(datediff(day,CONVERT(datetime,SUBSTRING(B.ReportDate,2,2)+'/1/200'+SUBSTRING(B.ReportDate,1,1)),CONVERT(datetime,SUBSTRING(B.ShipDate,5,2)+'/'+substring(B.Shipdate,7,2)+'/'+SUBSTRING(B.Shipdate,1,4)))) BETWEEN 30 AND 60) as [30-60],
    (SELECT COUNT(*) FROM YourTableName B WHERE SUBSTRING(ltrim(A.ShipDate),5,2) + '/' + SUBSTRING(ltrim(A.ShipDate),1,4) = SUBSTRING(ltrim(B.ShipDate),5,2) + '/' + SUBSTRING(ltrim(B.ShipDate),1,4) AND ABS(datediff(day,CONVERT(datetime,SUBSTRING(B.ReportDate,2,2)+'/1/200'+SUBSTRING(B.ReportDate,1,1)),CONVERT(datetime,SUBSTRING(B.ShipDate,5,2)+'/'+substring(B.Shipdate,7,2)+'/'+SUBSTRING(B.Shipdate,1,4)))) > 60) as [>60]
    FROM
    YourTableName A

    This is ugly, but I think it will work for you.  (This is not optimized at all, but should get you the format you want.)

     

    ***This does assume that Report Date's Year is between 2000 and 2009.***

    Monday, July 19, 2010 7:05 PM