Count Records Between two dates

Hi all,

I've got a quick question.

How would I count the number of records between two dates.

I started with something like this.

SELECT COUNT(*) AS COUNT, dtAdded
FROM tSurveyPerson
WHERE (dtAdded BETWEEN '2004-03-01' AND '2004-04-01')
GROUP BY dtAdded

but as you probably all know this ain't right. I would like to get just the number of records.

ThanksLeave off the GROUP BY.

-PatP|||got it...

SELECT COUNT(dtAdded) AS numRecords
FROM tSurveyPerson
WHERE (dtAdded BETWEEN '2004-03-01' AND '2004-04-01')

Thanks Patsql

Count on a date column

All,
I need to count dates in a column but how can i cut of the time hour minutes and seconds?
I need to rport how many records hav been added on one date...
con someone help me getting on the richt track?
regards
select count(*)
from tbl
where convert(varchar(8),dte,112) = '20040515'
select count(*)
from tbl
where dte >= '20040515' and dte < '20040515'
select dte = convert(varchar(8),dte,112), num = count(*)
from tbl
group by convert(varchar(8),dte,112)
order by convert(varchar(8),dte,112)
one of those should help.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Count days between to dates group by month

Hi i have an database that looks like this:
http://www.cyren.no/diagram3.gif
And the output here http://www.cyren.no/ods
(Se picture)
I want to get an query where a user can search in a date range.
The output should be like this:
Department(ANavn)
JANUAR 2003
VF54423(RegNr) 21.01.2003(GjelderFra) 23.01.2003(SiOppforsikringFra)
3(AntDays)
VF54422(RegNr) 21.01.2003(GjelderFra) 23.03.2003(SiOppforsikringFra)
9(AntDays)
SUM JANUAR 12 days 2 cars
FEBRUARY 2003
VF54422(RegNr) 21.01.2003(GjelderFra) 23.03.2003(SiOppforsikringFra)
30(AntDays)
SUM JANUAR 30 days 1 car
MARS 2003
VF54422(RegNr) 21.01.2003(GjelderFra) 23.03.2003(SiOppforsikringFra)
23(AntDays)
SUM JANUAR 23 days 1 car
The error is if a car has been made inactive and the make a new post
with the same regnr. Or the sp works fine...
CREATE PROCEDURE sp_aktive
(
@.datoFOM datetime,
@.datoTOM datetime,
@.avdeling int
)
AS
Declare @.period as datetime
Set @.period =3D @.datoFOM
if(object_id('tempdb.dbo.#temp')<>1)
drop table #temp
if(object_id('tempdb.dbo.#temp2')<>1)
drop table #temp2
--SET dateformat dmy
If(object_id('tempdb.dbo.#periods')<>1)
drop table #periods
Select @.period as dato
into #periods
while @.Period < @.datoTOM
begin
Select @.Period =3D dateadd(month,1,@.period)
Insert into #periods
Select @.period
End
SELECT fkbravdeling, a.SiOppForsikringFra,
case when year(b.dato) =3D year(gjelderFra) and month(b.dato) =3D
month(gjelderFra) then
gjelderfra
else
b.dato
end as gjelderfra, a.Regnr
into #temp
FROM
cyren.tblbiler a
join
(select distinct cast('01' + datename(month, gjelderFra) + cast(year(
gjelderFra) as varchar) as datetime) as dato from cyren.tblbiler) b
on b.dato between cast('01' + datename(month, gjelderFra) + cast(year(
gjelderFra) as varchar) as datetime) and
coalesce(SiOppForsikringFra,'01jan2050')
join #periods c
on b.dato =3D c.dato
select *
into #temp2
from
(
SELECT anavn,
sum(
case when month(SiOppForsikringFra) =3D month(gjelderfra) and
year(SiOppForsikringFra) =3D year(gjelderfra)then
DateDiff(d, gjelderfra, SiOppForsikringFra) +1
else
DateDiff(d, gjelderfra,
dateadd(day, -1, '01' + datename(month, dateadd(month,1,
gjelderfra)) + cast(year(dateadd(month,1, gjelderfra)) as varchar)) +1
)
end
) as AntDays,
'Antall aktive biler denne m=E5ned' as regnr, count(*) as AntCars,
year(gjelderfra) as [year], month(gjelderfra) as [month],
datename(month,gjelderfra) + ' ' + cast(year(gjelderfra) as varchar)
as dato,
2 as [order]
FROM
#temp a join
cyren.tblAvdeling
on FKBRAvdeling=3DAID
WHERE fkbravdeling=3D@.avdeling AND gjelderfra between @.datoFOM and
@.datoTOM
group by anavn, month(gjelderfra), year(gjelderfra),
datename(month,gjelderfra) + ' ' + cast(year(gjelderfra) as varchar)
) a
union
(
SELECT anavn,
case when month(SiOppForsikringFra) =3D month(gjelderfra) and
year(SiOppForsikringFra) =3D year(gjelderfra)then
DateDiff(d, gjelderfra, SiOppForsikringFra) +1
else
DateDiff(d, gjelderfra,
dateadd(day, -1, '01' + datename(month, dateadd(month,1, gjelderfra))
+ cast(year(dateadd(month,1, gjelderfra)) as varchar)) + 1
)
end AntDays,
Regnr, 1 as AntCars ,year(gjelderfra) as [year], month(gjelderfra) as
[month],
datename(month,gjelderfra) + ' ' + cast(year(gjelderfra) as varchar)
as dato,
1 as [order]
FROM
#temp a join
cyren.tblAvdeling b
on FKBRAvdeling=3DAID
WHERE fkbravdeling=3D@.avdeling AND gjelderfra between @.datoFOM and
@.datoTOM
)
SELECT a.*, BID, GjelderFra, SiOppForsikringFra, Aktiv
FROM #Temp2 a
left join cyren.tblbiler b
on a.regnr =3D b.regnr and b.Fkbravdeling=3D@.avdeling--Denne er kun hvis
den skal kj=F8res p=E5 avdeling
order by anavn, [year], [month], [order]
GOOn 16 Jan 2006 05:04:07 -0800, web@.cyren.no wrote:

>Hi i have an database that looks like this:
>http://www.cyren.no/diagram3.gif
>And the output here http://www.cyren.no/ods
>(Se picture)
>
>I want to get an query where a user can search in a date range.
>
>The output should be like this:
(snip)
>The error is if a car has been made inactive and the make a new post
>with the same regnr. Or the sp works fine...
>CREATE PROCEDURE sp_aktive
(snip)
Hi web,
First: never use sp_ as the first three characters for your stored
procedure names. This prefixed is resserved for system stored
procedures. SQL Server will look for them in the master database first,
then in the current database. Apart from the performance hit, you'll run
into much more severe problems if MS adds a system stored procedure with
the same name in the next release or service pack...
Second: I don't understand what exactly it is you're asking. I've seen
the picture of the table structure, and the ~20,000 row report you've
posted links to (and I even have a feeling that I understand what some
of the Norwewgian words mean <g> ). But I fail to see what you want. And
the highly complex code in the stored proc you posted doesn't help
either.
A better way to describe your problem would be to post:
* CREATE TABLE statements for the tables involved. You may simplify them
by omitting irrelevant columns (and translating remaining column names
to English would not be a bad idea either), but please do include all
constraints and properties.
* INSERT statements for a few rows of sample data. The amount of data
should be enough to illustrate the problem you're trying to solve, but
not more. Of course, they should run against the CREATE TABLE statements
you post.
* Expected output (based on the posted sample data, of course).
* And a description of why the sample data has to yield the results you
posted - in other words, a description of the actual busniess problem
that you're trying to solve.
Check out www.aspfaq.com/5006 for some useful hints on how to assemble
the info for this follow-up post.
Hugo Kornelis, SQL Server MVP

Count and Dates

Hello All,

Ok, I had an excellent answer when I put up my previous post. It solved my problem but a new problem arose. I need your expertise once again.

I'm working in SQL Server 2005 (sp2) environment. I have a table containing a tires life, beginning from when it was fitted, stored and reused until it was scrapped as shown below;

TireID axle position taken_from taken_to date 6006 1 2 H D 10/6/2003 10:45 6006 1 2 D D 10/8/2003 8:30 6006 1 2 D D 10/30/2003 13:30 6006 1 2 D D 11/28/2003 8:30 6006 1 2 D D 12/29/2003 11:30 6006 1 2 D E 1/27/2004 8:30 6006 2 6 E D 2/14/2004 11:45 6006 2 6 D D 2/28/2004 8:30 6006 2 6 D D 3/30/2004 8:30 6006 2 6 D D 4/28/2004 8:30 6006 2 6 D E 5/7/2004 8:30 6006 2 5 E D 5/12/2004 10:45 6006 2 5 D D 5/28/2004 8:30 6006 2 5 D D 5/28/2004 11:00 6006 2 5 D D 6/30/2004 8:30 6006 2 5 D D 7/6/2004 8:30 6006 2 5 D D 7/28/2004 8:30 6006 2 5 D E 8/3/2004 8:40 6006 2 4 E D 8/6/2004 10:45 6006 2 4 D D 8/28/2004 8:30 6006 2 4 D D 9/4/2004 8:30 6006 2 4 D E 9/9/2004 8:30 6006 2 5 E D 10/4/2004 10:45 6006 2 5 D E 10/29/2004 8:30 6006 2 5 E D 11/4/2004 9:45 6006 2 5 D D 11/22/2004 8:30 6006 2 5 D D 11/29/2004 8:30 6006 2 5 D D 11/29/2004 11:00 6006 2 5 D C 12/7/2004 8:30

H means fitted new

D means is running

E means Store

C means scrapped

From excel, difference between fitted new date and scrapped date is 10270 hours (approx) and

Actual running time is 8891 hours (when not in storage).

So, i need to query this table to produce tireID, Count of Es from the "table_from" column and actual running time as shown below.

TireID Count taken_from E's Running time 6006 5 8891

If you need to know, Its about a research Im doing about the effect of tire rotation on tire life.

Thanks in advance.

Try this one:

select
TireId,
Count(taken_from),
datediff(d, (select top 1 [date] from Tires where TireId = d.TireId order by [date]), (select top 1 [date] from Tires where TireId = d.TireId order by [date] desc))
from Tires d
group by d.TireId

It is not very efficient, but it works. I named the table [Tires].

|||

Hamdawed,

Please, next time post DDL statements (table definition, including constraints and indexes, and insert statements) so we do not have to take our time reproducing your environment. The help should be in both ways. Try:

Code Snippet

create table dbo.t1 (

TireID int not null,

axle int not null,

position int not null,

taken_from char(1) not null,

taken_to char(1) not null,

date datetime not null

)

go

set nocount on

insert into dbo.t1 values(6006, 1, 2, 'H', 'D', '10/6/2003 10:45')

insert into dbo.t1 values(6006, 1, 2, 'D', 'D', '10/8/2003 8:30')

insert into dbo.t1 values(6006, 1, 2, 'D', 'D', '10/30/2003 13:30')

insert into dbo.t1 values(6006, 1, 2, 'D', 'D', '11/28/2003 8:30')

insert into dbo.t1 values(6006, 1, 2, 'D', 'D', '12/29/2003 11:30')

insert into dbo.t1 values(6006, 1, 2, 'D', 'E', '1/27/2004 8:30')

insert into dbo.t1 values(6006, 2, 6, 'E', 'D', '2/14/2004 11:45')

insert into dbo.t1 values(6006, 2, 6, 'D', 'D', '2/28/2004 8:30')

insert into dbo.t1 values(6006, 2, 6, 'D', 'D', '3/30/2004 8:30')

insert into dbo.t1 values(6006, 2, 6, 'D', 'D', '4/28/2004 8:30')

insert into dbo.t1 values(6006, 2, 6, 'D', 'E', '5/7/2004 8:30')

insert into dbo.t1 values(6006, 2, 5, 'E', 'D', '5/12/2004 10:45')

insert into dbo.t1 values(6006, 2, 5, 'D', 'D', '5/28/2004 8:30')

insert into dbo.t1 values(6006, 2, 5, 'D', 'D', '5/28/2004 11:00')

insert into dbo.t1 values(6006, 2, 5, 'D', 'D', '6/30/2004 8:30')

insert into dbo.t1 values(6006, 2, 5, 'D', 'D', '7/6/2004 8:30')

insert into dbo.t1 values(6006, 2, 5, 'D', 'D', '7/28/2004 8:30')

insert into dbo.t1 values(6006, 2, 5, 'D', 'E', '8/3/2004 8:40')

insert into dbo.t1 values(6006, 2, 4, 'E', 'D', '8/6/2004 10:45')

insert into dbo.t1 values(6006, 2, 4, 'D', 'D', '8/28/2004 8:30')

insert into dbo.t1 values(6006, 2, 4, 'D', 'D', '9/4/2004 8:30')

insert into dbo.t1 values(6006, 2, 4, 'D', 'E', '9/9/2004 8:30')

insert into dbo.t1 values(6006, 2, 5, 'E', 'D', '10/4/2004 10:45')

insert into dbo.t1 values(6006, 2, 5, 'D', 'E', '10/29/2004 8:30')

insert into dbo.t1 values(6006, 2, 5, 'E', 'D', '11/4/2004 9:45')

insert into dbo.t1 values(6006, 2, 5, 'D', 'D', '11/22/2004 8:30')

insert into dbo.t1 values(6006, 2, 5, 'D', 'D', '11/29/2004 8:30')

insert into dbo.t1 values(6006, 2, 5, 'D', 'D', '11/29/2004 11:00')

insert into dbo.t1 values(6006, 2, 5, 'D', 'C', '12/7/2004 8:30')

set nocount off

go

;with cte_1

as

(

select

TireID,

axle,

position,

taken_from,

taken_to,

date,

row_number() over(order by date) as rn

from

dbo.t1

),

cte_2

as

(

select

a.TireID,

a.axle,

a.position,

a.taken_from,

a.taken_to,

a.date,

isnull(datediff(minute, b.date, a.date), 0) as minutes

from

cte_1 as a

left join

cte_1 as b

on a.TireID = b.TireID

and a.rn = b.rn + 1

)

select

TireID,

sum(case when taken_from = 'E' then 1 else 0 end) as [Count taken_from E's],

ceiling(sum(case when taken_from = 'E' then 0 else minutes end) / 60.00) as [Running time]

from

cte_2

group by

TireID

go

drop table dbo.t1

go

AMB

|||

Correction,

Change the row_number() function to:

row_number() over(partition by TireID order by date) as rn

I am sure you have more tires in that table, don't you?

AMB

|||

Hi,

Sorry for my inefficiency in setting up this help request. I promise to make things much clearer the next time.

Well, your code works great when I copy and paste but I tried breaking it down to fit the real table in the database im using and it produces errors.

The example i gave was just a subset of the real table and this table includes information about 130000 tires.

So, my question is: I have this table in a database in SQL Server 2005 just like the example I gave but with more tire information. Im not creating any tables but just querying a table (using views) in a certain database.Which part of your code im i going to use, analyse?

I know the "create" part is not necessary in my environment but when I eliminate it I get errors.

Also, theres an error that "OVER" clause is not supported by SQL Server 2005. Why is that?

Thanks

|||

Yeah, i got like 130000 tires in that table in a database. Sorry for the troubles.

The code you produced works great but I need to fit that to the real table in the database.

I wish I can put up the whole table up or even share my computer screen to see the real problem.

Im working on the correction. More suggestions as to how to do the modification to your code to fit the real table is welcome.

Thanks

|||

Can you check the compatibility level of that db?

exec sp_cmptlevel your_db_name

go

If should be 90 in order to use new features from SQL Server 2005.

AMB

|||

I received the following error when I checked the compatibility level;

Msg 2812, Level 16, State 62, Line 1

Could not find stored procedure 'sp_cmptlevel'.

I have been able to fit your code to my whole table and it works but only when i copy paste in 'new query'. When I try to use it in views it produces the error that 'OVER CONSTRUCT IS NOT SUPPORTED'.

Thanks

|||

Sorry, I posted the incorrect name for that sp. I meant sp_dbcmptlevel.

Can you post what you are trying to do?

Example:

Code Snippet

use northwind

go

create view dbo.v1

as

with cte

as

(

select

CustomerID, OrderID, OrderDate,

row_number() over(partition by CustomerID order by OrderDate, OrderID) as rn

from

dbo.orders

)

select CustomerID, OrderID, OrderDate, rn

from cte

go

select CustomerID, OrderID, OrderDate, rn

from dbo.v1

order by CustomerID, rn

go

drop view dbo.v1

go

AMB

|||

Hi,

The current compatibility level is 90 when I checked.

The view was created when i applied your example. But, no diagrams were created and it still produces that "OVER" sql construct is not supported.

I will like to post what i want to do but how?

sorry for my naiveness.

|||

Ok,

Basically, what I need to see is a diagram of the table with joins in the "diagrams" pane when working in views.

The code you created for me gets the information I want but does not produce a diagram because of the OVER thing.

The audience Im going to present this to understand basic ER diagrams and links and not SQL codes.

Please, I will appreciate it very much if an alternate query can be written without the OVER construct. If not, never mind. You have been very helpful.

Thanks

|||

Ok, now I understand your problem. You are trying to create the query using "Query Designer". Well, you can create the view using T-SQL and use a third party tool, like Visio, to create the ER diagram. The "Query Designer" seems not to allow OVER() clause in the construction of a query, so better to learn how to do it using T-SQL.

AMB

|||

Yeah, right on. I should have used the lingo ''Query designer''. Im learning. Will I be right if i assume there is no other way to create a query in the "Query designer" to get me the same information.

Thank you for your time and patience

|||

Sorry, but I do not use "Query Designer" much, so it is little what I can tell you about its features.

AMB

Cost of Comparing Dates, ignoring Time part

Hi, i would like to know what is better when trying to compare two data fields one of them has the format yyyymmdd and then the another is yyyymmdd hh:mms

I need to ignore the time part but when I use this ...

where Convert(CHAR(8),Year(date)*10000+ Month(date)*100+Day(date))
between @.date_ini and @.date_fin

I have also tried this....

where convert(varchar(12), date, 112 )
between @.date_ini and @.date_fin

@.date_ini and @.date_fin are varchar '20070301'

The proble is that the time to execute the query is exagerated

would you pleas help me?

Try:

where date >= @.date_ini
and date < convert(datetime, @.date_fin) + 1

The performance of your query should be improved if you have an index on your "date" field. Also, I have assumed that you have stoerd your field as an SQL datetime field and made the assumption that when you say that you wat @.date_ini and @.date_fin to be the same date that you are wanting all records on that date to be selected.

I do not use the BETWEEN operator because BETWEEN would also select data that occurs exactly on midnight of the next day.

|||

Thank you Kent Waldrop

It works better