Generating a daily statistic report SQL

Hi,

I Have a table below.

Query

PKEY id int

name varchar(128)

date_add DateTime

What is the SQL statement to get the number of query on each day?

the output should be date and quantity. There should still be an output even if there is no query on that day.

The only way I can think of is by a table-value UDF. (rough design)

function(startdate, enddate)

{

for each day from start to end

insert into result select count(*) from Query where date_add = currentDate

return

}

Is there a more efficient way to do this?

Thanks,

Max

Something like this will do.

Code Snippet

select dateadd(day,datediff(day,0,date_add),0) [day], count(*) [quantity]

from your_table_goes_here

where date_add between @.startdate and @.enddate

group by dateadd(day,datediff(day,0,date_add),0)

If you need to return "0" for unaccount date, you will need a calendar (or number) table to seed the date to be calculated by.

Generate Reports

How I make to generate reports of the replicated data ?
The report must have the format : origin,destination,datetime,table name,
and records of the table.
Thanks
FDB,
in order to answer correctly, I'd like to know a bit more about:
(1) what type of replication you are using.
(2) origin is presumably the first computer to add/update the record? If so
then this will need to be an additional field with a default.
(3) destination - is this any different than the table you're examining?
(4) datetime - is this the time the record was originally added ot the time
the record was added due to replication
Is this report a list of records or statistical aggregates?
TIA,
Paul Ibison
|||I am using Merge replication.
I have two sql servers in distinct sites, and each site make changes to
data independently.
This report is a list of records, for I check which data they had been
inserted in both databases.
The purpose is I to know if all the data had been transferred correctly.
Thanks.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eH%23OpOXJEHA.3596@.tk2msftngp13.phx.gbl...
> FDB,
> in order to answer correctly, I'd like to know a bit more about:
> (1) what type of replication you are using.
> (2) origin is presumably the first computer to add/update the record? If
so
> then this will need to be an additional field with a default.
> (3) destination - is this any different than the table you're examining?
> (4) datetime - is this the time the record was originally added ot the
time
> the record was added due to replication
> Is this report a list of records or statistical aggregates?
> TIA,
> Paul Ibison
>
|||OK - thanks for the explanation. In that case you might like to look at "validating data" in BOL. There is a section towards the bottom of the page on merge replication. You can choose to use rowcounts only, rowcounts and checksums, or rowcounts and compa
ring binary checksums (all Subscribers must be running SQL Server 2000 to use this option). There is a replication alert which fires if validation is failed (Replication: Subscriber has failed data validation). This can be set up in EM, using stored procs
or using the agent parameters - details in BOL.
HTH,
Paul Ibison