help with a query - 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

SQL Server 2008 Forums

Transact-SQL (2008)

help with a query

Author

Topic

kubiczek
Starting Member

6 Posts

Posted - 2011-06-03 : 10:53:04

Hello guys.
I have a query like that:


select count (*) from table1 where (table1.TYPE='2' and DATE_TIME between '2011-01-01 00:00:01.000' and '2011-01-30 23:59:59.000') 
   group by name
   having (MIN(date_time) < '2011-01-20 23:59:59.000')

In the table 1 there are different types: 1,2,3...
The point of the query is to count how many rows with type 2 are in the table between two dates.
I also used having clause which means that the rows will be counted only when the latest date of the first row will be the date mentioned above.
My problem is that the having clause uses the where condition type = 2 but i dont want it to use it. I want to count rows of type2 when the min(date) of type 1 is e.g. 2011-01-18 and the min(date) of type2 is 2011-01-22. I hope it's clear.
EXAMPLE:
name type date
jose 1 2011-01-18
jose 1 2011-01-22
jose 1 2011-01-25
jose 2 2011-01-21
jose 2 2011-01-23

The result shoud be 2! I got nothing because the min date of type 2 is 2011-01-21. But I want to count the rows because the first row of type 1 is before 2011-01-20.

Thank you

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-03 : 11:15:40

You aren't joining the tables.
What is the connection between table1 and table2?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kubiczek
Starting Member

6 Posts

Posted - 2011-06-03 : 11:56:04

Sorry about the table2, I forgot to delete it, I dont need it there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-06-03 : 13:47:06

[code]SELECT COUNT(CASE WHEN Type='2' THEN 1 ELSE NULL END)
FROM table
GROUP BY name
HAVING MIN(CASE WHEN Type='1' THEN date ELSE NULL END) = '2011-01-18'
AND MIN(CASE WHEN Type='2' THEN date ELSE NULL END) = '2011-01-22'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kubiczek
Starting Member

6 Posts

Posted - 2011-06-04 : 04:07:49

Thanks a lot!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-06-05 : 03:42:05

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources