Showing posts with label proc. Show all posts
Showing posts with label proc. Show all posts

Sunday, March 25, 2012

Distinct issue

Hi

I wanna write a proc that returns Distinct CustomerID's in a table and returns result in output parameter

When i try this, i get error - incorrect syntax near distinct. Any ideas??

ALTER PROCEDURE proc_Report_CountCustomers_Sept
(
@.CustCount int OUTPUT
)
AS
SET NOCOUNT ON
select
@.CustCount = distinct(CustomerID)
from
Orders
Where
OrderDate > '2006-09-01' and OrderDate < '2006-10-01'

hi,

what you want to return, count of distinct ids or distinct ids itself ?

regards,

satish

|||

hehe.. er ofcourse. I wanted to count the distinct ids, but i didnt count

i worked it out

cheers brother

ALTER PROCEDURE proc_Report_CountCustomers_Sept
(
@.CustCount int OUTPUT
)
AS
SET NOCOUNT ON
SELECT
@.CustCount =Count(DISTINCT(CustomerID))
FROM
Orders
WHERE
OrderDate > '2006-09-01' and OrderDate < '2006-10-01'

|||

good you got it working :),

cheers ,

satish

Thursday, March 22, 2012

Distinct Filter in SQL Reporting Services

Hello All,

I've got a stored proc that I can't change that creates a quite large
dataset and takes in 5 parameters. I need to be able to have each of
the parameters selectable from a drop down box so that as you go
through the list of parameters you restrict the results. I have it
able to load the parameters but I end up with this:

Location-
LONDON
LONDON
LONDON
NEW YORK
NEW YORK
NEW YORK
NEW YORK
TORONTO
TORONTO
...

Any idea how i can filter the list of locations by distinct? from
within SQL Reporting Services. I tried a temp tbl in query analyser
and that works but SQL reporting services doesn't like it.Nevermind, It seemed to work the second time i tried to run the query
in reporting services.

BEGIN

CREATE TABLE #temptbl2
(
EnddateVARCHAR(50),
ProjectNumber VARCHAR(50),
ProjectManager VARCHAR(50),
resourceManager VARCHAR(50),
CostCentreVARCHAR(50),
WorkInProgressFLOAT
)

INSERT INTO #temptbl2
EXECUTE jmpwipreportbasic '09/09/2009'

SELECT DISTINCT costcentre FROM #temptbl2

DROP TABLE #temptbl2

END

Maybe this will help someone else.

Sunday, March 11, 2012

Displaying multiple datasets in one report

I have a store procs that return more that one result set i.e. multiple
selects in the store proc. The report always picks out the first result set
it finds. How can I get a report to show the other result sets that the store
proc generates? Rewriting the store procs is not an option due to time
constraints.Bad news. Currently RS only works with the first recordset. If you can't
rewrite the sp's, you might be able to create separate wrapper sp's that
return the individual recordsets.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Mike S" <MikeS@.discussions.microsoft.com> wrote in message
news:B3FCBC28-EE83-4FA6-AF23-6838DA338954@.microsoft.com...
>I have a store procs that return more that one result set i.e. multiple
> selects in the store proc. The report always picks out the first result
> set
> it finds. How can I get a report to show the other result sets that the
> store
> proc generates? Rewriting the store procs is not an option due to time
> constraints.