CODEWITHSUNDEEP
sqlsql-server-2005reporting-services
vbNewbie
vbNewbie

Reputation: 3345

report builder - queries returning incorrect results

I have set a reporting project where I would like to get stats for my tables and later integrate this into a webservice. For the following queries though, I am getting incorrect results and I will note where below:

1 - Get the number of new entries for a given day

 SELECT COUNT(*) AS RecordsCount,CAST(FLOOR(CAST(dateadded AS float)) 
 AS datetime)as collectionDate 
 FROM TFeed GROUP BY CAST(FLOOR(CAST(dateadded AS float))
 AS datetime) order by collectionDate

works fine and I am able to put this in a bar graph successfully.

2 - Get the top 10 searchterms with the highest records per searchterm requested by a given client in the last 10 days

SELECT TOP 10 searchterm, clientId, COUNT(*) AS TermResults FROM TFeed 
where dateadded > getdate() - 10 GROUP BY 
searchterm,clientId order by TermResults desc

does not work If I do a query in the Database for one of those terms that returns 98 in the report, the result is 984 in the database.

3 - I need to get the number of new records per client for a given day as well.

Also I was wondering if it is possible to put these queries into one report and not individual reports for each query which is not a big deal but having to cut and paste into one doc afterwards is tedious.

Any ideas appreciated

Upvotes: 0

Views: 606

Answers (1)

clyc
clyc

Reputation: 2450

For #2,

WITH tmp as
(
 SELECT clientId, searchTerm, COUNT(1) as TermResults,
   DENSE_RANK() OVER (partition by clientId
    ORDER BY clientId, COUNT(1) DESC) as rnk
 FROM TFeed
 WHERE dateadded > GETDATE() - 10 
 GROUP BY clientId, searchterm 
)
SELECT *
FROM   tmp
WHERE rnk < 11

USE RANK() if you want to skip a rank if there are two matches (if lets say term1 and term2 have the same number of count, they are both rank 1 and the following term will be ranked 3rd instead of 2nd

For #3,
you can define multiple datasets within one report. Then you would just create three charts / table and associate those with their respective datasets

Upvotes: 2

Related Questions