# different date format and calculation

• ### 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 Monday, July 19, 2010 3:05 PM edit
Monday, July 19, 2010 3:05 PM

• ```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 Monday, July 19, 2010 7:35 PM
• Marked as answer by 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 Monday, July 19, 2010 7:35 PM
• Marked as answer by 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