Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2008R2
i applied this query over my data in SQL server for calculating difference between max and min date group by Some id, my query is as follows
 
SELECT COUNT(DIFF)
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
 
This query is producing result as
 
DIFF
10
5
10
10
5
7
 
Now i want to apply count operation over this to calculate occurring of particular difference as
 
Count
3
2
1
 
I want to know how to apply count operation over my query for that particular functionality, mentioned above(for count)
Posted 28-Aug-14 21:53pm

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
 
Check this Query :
 
 

SELECT 
--COUNT(DIFF)
Count(DATEDIFF(D, MinDate, MaxDate)/30) AS DIFF
 
FROM (
SELECT
 
   MAX(TDate) AS MaxDate,
   MIN(TDate) AS MinDate
   FROM EDATA
   GROUP BY TCard
)a
Group by DATEDIFF(D, MinDate, MaxDate)/30
 
  Permalink  
Comments
Abhishek Jaiswall at 29-Aug-14 3:21am
   
Thanks Buddy! :)
syed shanu at 29-Aug-14 3:31am
   
i check with my table its working fine :
SELECT
--COUNT(DIFF)
Count(DATEDIFF(D, MinDate, MaxDate)/30) AS DIFF
 
FROM (
SELECT
 
MAX(IN_DATE) AS MaxDate,
MIN(IN_DATE) AS MinDate
FROM Operation
GROUP BY PROJ_ID
)a
Group by DATEDIFF(D, MinDate, MaxDate)/30
 
and my result like this :
Diff
104
31
38
36
Abhishek Jaiswall at 29-Aug-14 4:24am
   
yeah, there was some other error, now its working fine. Thanks!
Abhishek Jaiswall at 29-Aug-14 4:41am
   
I have one more question,
if i want to apply some more functionality(query operations) in that query for some extended functionality, then in case where am suppose to use WHERE clause (conditions can be several).
like if i want to sort data before doing date difference operation on the basic of Name, gender etc. from that
syed shanu at 29-Aug-14 4:44am
   
You can do that try with your query if need support comment here
Abhishek Jaiswall at 29-Aug-14 5:03am
   
I applied just 1 condition as,
SELECT
--COUNT(DIFF)
Count(DATEDIFF(D, MinDate, MaxDate)/30) AS DIFF

FROM (
SELECT

MAX(TDate) AS MaxDate,
MIN(TDate) AS MinDate
FROM EDATA

Where ID>2

GROUP BY TCard
)a
Group by DATEDIFF(D, MinDate, MaxDate)/30
 
now what if i want to apply many conditions. and sort data according to 1 condition like name, age, gender etc. (1 condition at a time)
syed shanu at 29-Aug-14 5:05am
   
In condition you can add any no its nor problem and sorting as well .Just try your query if you have difficult paste it here
Abhishek Jaiswall at 29-Aug-14 5:17am
   
Here's a query
with single operation, in place of one condition of sorting data I want several but only 1 condition gets applied at a time
 
SELECT
--COUNT(DIFF)
Count(DATEDIFF(D, MinDate, MaxDate)/30) AS DIFF
 
FROM (
SELECT
 
MAX(TDate) AS MaxDate,
MIN(TDate) AS MinDate
FROM EDATA
 
Where ID>2
 
GROUP BY TCard
)a
Group by DATEDIFF(D, MinDate, MaxDate)/30

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,127
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 29 Aug 2014
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100