group by using dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

group by using dates

Author

Topic

kumar.raja
Starting Member

2 Posts

Posted - 2010-10-23 : 23:12:46

The issue i am facing is that i have this table with two columns, date and another column with certain codes as shown below.

date Code
25\06\2009 A
23\07\2010 B
20\11\2009 D
18\08\2008 C
14\10\2010 A
14\09\2010 E
14\03\2010 D
14\03\2009 D
14\01\2009 D
13\08\2010 E
13\01\2009 E
12\08\2010 F
10\06\2009 A
09\09\2009 B
07\12\2009 C
06\10\2009 A
05\10\2009 F
05\07\2008 D
02\02\2009 E
01\08\2009 F
01\07\2009 G
01\01\2008 D

Now what i actually want from this table is that for each date in column 1, i would want to know the code from column b that has appeared most within one year of the date that i have considered.
For example, if i take the first date 25\06\2009 the codes that have appeared within a year of that would be D (thrice), E(twice) and F (once). So D wins as the code that appears most. I would like to know the code that would give the winnig code for each date in column 1.

for some reason, my query seems to be returning nothing.

select t1.date,t1.code,count (1) as count_code from table t1
inner join table t2 (nolock) on t1.date=t2.date
where t2.date between dateadd (year,-1,t1.date) and t1.date
group by t1.date

i see the error in my code as i am writing this, but i couldn't find out a solution either.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-24 : 00:45:19

What is the data type of the date/time column? And do you really have back slashes in there instead of forward slashes?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

kumar.raja
Starting Member

2 Posts

Posted - 2010-10-24 : 02:03:43

the datetime data is in the datetime format.

no there are no backslashes. i wrote down the data.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-10-24 : 13:48:11

there is an error in your code:

try like this:



select 
t1.date
,t1.code
,count (1) as count_code 
from table t1
inner join table t2 with (nolock) 
on t1.date=t2.date
where 
t2.date between dateadd (year,-1,t1.date)  and t1.date
group by t1.date,t1.code

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-10-25 : 17:16:37

Please don't use reserved words for data element names. You did not say how to handle ties, so let's get all the counts by codes:

SELECT E1.event_date,
SUM(CASE WHEN E1.foobar_code = 'A' THEN 1 ELSE 0 END)AS a_code_cnt,
SUM(CASE WHEN E1.foobar_code = 'B' THEN 1 ELSE 0 END)AS b_code_cnt,
SUM(CASE WHEN E1.foobar_code = 'C' THEN 1 ELSE 0 END)AS c_code_cnt,
SUM(CASE WHEN E1.foobar_code = 'D' THEN 1 ELSE 0 END)AS d_code_cnt,
SUM(CASE WHEN E1.foobar_code = 'E' THEN 1 ELSE 0 END)AS e_code_cnt,
SUM(CASE WHEN E1.foobar_code = 'F' THEN 1 ELSE 0 END)AS f_code_cnt
FROM Events AS E1,
Events AS E2
WHERE E2.event_date BETWEEN DATEADD (YEAR, -1, E1.event_date) AND E1.event_date
GROUP BY E1.event_date;

Also, Standard SQL uses only the "YYYY-MM-DD HH:MM:SS.sss.." format.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

rahuldhoble
Starting Member

3 Posts

Posted - 2010-10-26 : 01:26:14

In the year of 25\06\2009 the codes D appeared twice only,
if this is right then query suggested by jcelko is right .

Regards,
Rahul Dhoble
Sr.Software Engineer
Mumbai

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources