You are here: Re: Need help running query on data/time field « MsSQL Server « IT news, forums, messages
Re: Need help running query on data/time field

Posted by Techhead on 02/08/07 20:42

On Feb 8, 1:51 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> You have to use COUNT for number of records, not SUM. You can use SUM to
> summarize a value if needed. Here is an example:
>
> CREATE TABLE #Test(mydate datetime, myvalue int)
>
> INSERT INTO #Test VALUES(DATEADD(hour, 2, getdate()), 2)
> INSERT INTO #Test VALUES(DATEADD(hour, 3, getdate()), 3)
> INSERT INTO #Test VALUES(DATEADD(hour, 4, getdate()), 4)
>
> SELECT COUNT(*) AS counts, SUM(myvalue) AS total
> FROM #Test
> WHERE mydate >= DATEDIFF(day, 0, getdate())
> AND mydate < DATEDIFF(day, 0, getdate() + 1)
>
> DROP TABLE #Test
>
> Regards,
>
> Plamen Ratchevhttp://www.SQLStudio.com

Thank you. COUNT was what I was looking for... sorry. Can I take this
one step further? I need to subtract the COUNT results of 1 query from
the COUNT results of another query.

Here are my 2 queries:

SELECT COUNT (*) FROM TABLE.RECORDS WHERE DATEFIELD >= DATEDIFF(day,
0, getdate()) AND DATEFIELD < DATEDIFF(day, 0, getdate() + 1)AND
RECORD_TYPE = '1'

SELECT COUNT (*) FROM TABLE.RECORDS WHERE DATEFIELD >= DATEDIFF(day,
0, getdate()) AND DATEFIELD < DATEDIFF(day, 0, getdate() + 1)AND
RECORD_TYPE = '2'

I need to subtract the results from query 2 from query 1

Once I get this, I am set.

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация