CODEWITHSUNDEEP
sqlsql-serverdatabase
Harry
Harry

Reputation: 1353

MSSQL Count dates that are in the past during GROUP BY

I've got a table on a MSSQL-Server with a couple of fields:

fieldA,fieldB,fieldC,DateTimeField

now I'm doing a select like this:

SELECT fieldA,fieldB,fieldC, max(DateTimeField),min(DateTimeField),
(count(*) - count(DateTimeField)) as DateTimeFieldIsNull
FROM table 
GROUP BY fieldA, fieldB, fieldC

which is working quite well.

Now I want to count all DateTimeFields that are in the past, or in another column, that are in the future.

Is there a perfomant way? (maybe even without JOINING) ?

Thnx, Harry

Upvotes: 0

Views: 321

Answers (4)

Scorpion
Scorpion

Reputation: 4585

SELECT fieldA,fieldB,fieldC, max(DateTimeField),min(DateTimeField),
(count(*) - count(DateTimeField)) as DateTimeFieldIsNull
, COUNT(CASE WHEN DateTimeField > GETDATE()  THEN 1 END) AS 'ABC'
, COUNT(CASE WHEN DateTimeField < GETDATE()  THEN 1 END) AS 'XYZ'
FROM table 
GROUP BY fieldA, fieldB, fieldC;

Upvotes: 2

Lamak
Lamak

Reputation: 70678

Something like this?:

SELECT  fieldA, fieldB, fieldC, 
        MAX(DateTimeField), MIN(DateTimeField),
        (COUNT(*) - COUNT(DateTimeField)) as DateTimeFieldIsNull,
        SUM(CASE WHEN DateTimeField < GETDATE() THEN 1 END) DateTimeFieldInThePast,
        SUM(CASE WHEN DateTimeField > GETDATE() THEN 1 END) DateTimeFieldInTheFuture
FROM table 
GROUP BY fieldA, fieldB, fieldC

Upvotes: 1

gbn
gbn

Reputation: 432672

Just add a COUNT/CASE construct.
You get NULL from the implied ELSE which COUNT ignores

COUNT(CASE WHEN DateTimeField > GETDATE() THEN 1 END) AS FutureDates,
COUNT(CASE WHEN DateTimeField < GETDATE() THEN 1 END) AS PastDates,

Upvotes: 5

Curtis
Curtis

Reputation: 103428

You could use 2 sub-queries to get this result set:

SELECT fieldA,fieldB,fieldC, max(DateTimeField),min(DateTimeField),
(count(*) - count(DateTimeField)) as DateTimeFieldIsNull
,(SELECT COUNT(DateTimeField)
  FROM table
  WHERE DateTimeField<GETDATE()
  AND t.fieldA=fieldA
  AND t.fieldB=fieldB
  AND t.fieldC=fieldC) as PastDates
,(SELECT COUNT(DateTimeField)
  FROM table
  WHERE DateTimeField>=GETDATE()
  AND t.fieldA=fieldA
  AND t.fieldB=fieldB
  AND t.fieldC=fieldC) as FutureDates
FROM table t
GROUP BY fieldA, fieldB, fieldC

Upvotes: 1

Related Questions