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

Abhishek Jaiswal · Answer

Thanks! :)

Ramesh Maruthi · Answer

Declare @a TABLE(diffBY30 DATETIME) INSERT INTO @a(diffBY30) Select Convert(int DateDiff(DMaxDate 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

Abhishek Jaiswal · Answer

How to apply count operation over this query for calculating number of same DIFF such as if after diff values are 10 210103 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

Abhishek Jaiswal · Answer

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

Ramesh Maruthi · Answer

Declare @a TABLE(diffBY30 DATETIME) INSERT INTO @a(diffBY30) Select Convert(int DateDiff(DMaxDate 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