SQL query

Question

hello!

Problem: I want to select max and min date from the table for every row(GROUPING BY id) then perform date difference operation over it. Afterwards i want to divide that diff with 30 and store in another table. HOW TO DO THAT ?
eX TABLE

Name	Date	ID
A	5/3/14	1
B	6/4/14	2
A	5/4/14	1
C	11/8/14	3
D	6/7/14	4
C	7/7/14	3
D	9/9/14	4

my QUERY
SELECT INSERT INTO <TAB2>
SELECT DATEDIFF/30 AS DIFFFROM
(SELECT MAX(DATE),  MIN(DATE) FROM <TAB1>
GROUP BY ID) ;


but its not working pls suggest how to do date

Answer 1

Thanks!
:)

Answer 2

Declare  @a TABLE(diffBY30 DATETIME)

 INSERT INTO @a(diffBY30)

 Select

  Convert(int,  DateDiff(D,MaxDate, MinDate))/30 as diff

  from(

  SELECT(Select CAST( Max

  (o.Created) AS DATETIME)) AS MaxDate

  ,(select CAST( MIN(o.Created) AS DATETIME)) as MinDate

  from [Order] AS o)A

  select * from @a
Group by diffBY30

Answer 3

How to apply count operation over this query for calculating number of same DIFF,
such as if after diff values are 10, 2,10,10,3
then it give result after count that 10 is 3 times, 2 is once etc.

my query is

SELECT 

MaxDate,

MinDate,

DATEDIFF(D, MinDate, MaxDate)/30 AS DIFF

FROM (

SELECT 

   MAX(TDate) AS MaxDate, 

   MIN(TDate) AS MinDate 

   FROM EDATA

   GROUP BY TCard

   

)a 

Answer 4

Heyy Ramesh!

what if i want to store that diff into same table but in a new column ?

Answer 5

 Declare  @a TABLE(diffBY30 DATETIME)

 INSERT INTO @a(diffBY30)

 Select

  Convert(int,  DateDiff(D,MaxDate, MinDate))/30 as diff

  from(

  SELECT(Select CAST( Max

  (o.Created) AS DATETIME)) AS MaxDate

  ,(select CAST( MIN(o.Created) AS DATETIME)) as MinDate

  from [Order] AS o)A

  select * from @a

In this example @a table is your Tab2