|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Query Help - Counting Dates and TimesAny help would be appreciated. This is from a table sv_call_log I am using a column called start_date The column start_date is formatted start_date ------------------------------ 2006-01-03 11:05:27.793 2006-01-04 02:32:38.683 2006-01-12 07:08:56.577 2006-01-13 16:55:05.310 2006-01-14 21:55:31.387 2006-02-01 19:42:33.390 2006-02-02 09:51:48.420 2006-02-05 10:41:06.593 2006-02-08 15:12:39.123 2006-02-09 09:52:32.153 I have been able to extract the date I like but the count never works. Is there anyway to make start_date look like 2006-01-03 11:05 (first example from start_date column) for every instance in start_date. If start_date contained the milliseconds, etc then every date would be unique. Then ultimately count how many times a date shows up four time. So if there is 2006-01-03 11:05 located four times in the table then that would be 1. Thanks. Try this:
Select convert(smalldatetime, StartDate) as 'StartDate' >From sv_Call_log Group By StartDateHaving Count(*) > 1 HTH Barry I ran this query and I know there are instances in the database were there 3
times the same date and time show up. This was happening to me also when writing my own. For some reason when using date and time it doesn't pick up duplicates. Any other ideas? Show quote "Barry" wrote: > Try this: > > Select convert(smalldatetime, StartDate) as 'StartDate' > >From sv_Call_log > Group By StartDate > Having Count(*) > 1 > > > HTH > > Barry > > What about this??
Select StartDate >From sv_Call_log Group By StartDateHaving Count(convert(varchar(17), StartDate)) > 1 SELECT CAST(start_date AS smalldatetime), COUNT(*)
FROM sv_call_log GROUP BY CAST(start_date AS smalldatetime) HAVING COUNT(*) > 1 --The duplicates where not showing from Barry's query because the GROUP BY was referencing the un-converted start_date column -- Show quote"tarheels4025" wrote: > I was wondering how to write a query to count the following date and times. > Any help would be appreciated. > > This is from a table sv_call_log > I am using a column called start_date > > The column start_date is formatted > start_date > ------------------------------ > 2006-01-03 11:05:27.793 > 2006-01-04 02:32:38.683 > 2006-01-12 07:08:56.577 > 2006-01-13 16:55:05.310 > 2006-01-14 21:55:31.387 > 2006-02-01 19:42:33.390 > 2006-02-02 09:51:48.420 > 2006-02-05 10:41:06.593 > 2006-02-08 15:12:39.123 > 2006-02-09 09:52:32.153 > > I have been able to extract the date I like but the count never works. Is > there anyway to make start_date look like 2006-01-03 11:05 (first example > from start_date column) for every instance in start_date. If start_date > contained the milliseconds, etc then every date would be unique. Then > ultimately count how many times a date shows up four time. So if there is > 2006-01-03 11:05 located four times in the table then that would be 1. Thanks. > Thanks Mark. I believe that worked.
Show quote "Mark Williams" wrote: > SELECT CAST(start_date AS smalldatetime), COUNT(*) > FROM sv_call_log > GROUP BY CAST(start_date AS smalldatetime) > HAVING COUNT(*) > 1 > > --The duplicates where not showing from Barry's query because the GROUP BY > was referencing the un-converted start_date column > > > > -- > > > "tarheels4025" wrote: > > > I was wondering how to write a query to count the following date and times. > > Any help would be appreciated. > > > > This is from a table sv_call_log > > I am using a column called start_date > > > > The column start_date is formatted > > start_date > > ------------------------------ > > 2006-01-03 11:05:27.793 > > 2006-01-04 02:32:38.683 > > 2006-01-12 07:08:56.577 > > 2006-01-13 16:55:05.310 > > 2006-01-14 21:55:31.387 > > 2006-02-01 19:42:33.390 > > 2006-02-02 09:51:48.420 > > 2006-02-05 10:41:06.593 > > 2006-02-08 15:12:39.123 > > 2006-02-09 09:52:32.153 > > > > I have been able to extract the date I like but the count never works. Is > > there anyway to make start_date look like 2006-01-03 11:05 (first example > > from start_date column) for every instance in start_date. If start_date > > contained the milliseconds, etc then every date would be unique. Then > > ultimately count how many times a date shows up four time. So if there is > > 2006-01-03 11:05 located four times in the table then that would be 1. Thanks. > > Mark question about this query. It will round up correct because lookig at
the data, it looks like thats what it does. Show quote "Mark Williams" wrote: > SELECT CAST(start_date AS smalldatetime), COUNT(*) > FROM sv_call_log > GROUP BY CAST(start_date AS smalldatetime) > HAVING COUNT(*) > 1 > > --The duplicates where not showing from Barry's query because the GROUP BY > was referencing the un-converted start_date column > > > > -- > > > "tarheels4025" wrote: > > > I was wondering how to write a query to count the following date and times. > > Any help would be appreciated. > > > > This is from a table sv_call_log > > I am using a column called start_date > > > > The column start_date is formatted > > start_date > > ------------------------------ > > 2006-01-03 11:05:27.793 > > 2006-01-04 02:32:38.683 > > 2006-01-12 07:08:56.577 > > 2006-01-13 16:55:05.310 > > 2006-01-14 21:55:31.387 > > 2006-02-01 19:42:33.390 > > 2006-02-02 09:51:48.420 > > 2006-02-05 10:41:06.593 > > 2006-02-08 15:12:39.123 > > 2006-02-09 09:52:32.153 > > > > I have been able to extract the date I like but the count never works. Is > > there anyway to make start_date look like 2006-01-03 11:05 (first example > > from start_date column) for every instance in start_date. If start_date > > contained the milliseconds, etc then every date would be unique. Then > > ultimately count how many times a date shows up four time. So if there is > > 2006-01-03 11:05 located four times in the table then that would be 1. Thanks. > > Yes. It rounds off the seconds to the nearest minute.
If you need accuracy to the second, then SELECT CONVERT(char(8), start_date, 108) AS "start time", COUNT(*) FROM sv_call_log GROUP BY CONVERT(char(8), start_date, 108) HAVING COUNT(*) > 1 -- Show quoteIf you posted to this forum through TechNet, and you found my answers helpful, please mark them as answers. "tarheels4025" wrote: > Mark question about this query. It will round up correct because lookig at > the data, it looks like thats what it does. > i think this will work...
select convert(char(16),start_date,20) from sv_call_log group by convert(char(16),start_date,20) having count(*) = 4 pls post DDL and insert statements for more clear solution  |
|||||||||||||||||||||||
Other interesting topics