find matching sets of rows

Given an ID (column B), I need to find which IDs have identical data.

That is, given '200', I want the desired result to be:
100

The idea is that the system sees that id=200 has 5 records with the
indicated data in cols C and D.

It should then find any other ids with the exact same data for those
columns.

Note, in this case, both 200 and 100 have (30:1, 30:2, 30:3, 40:4,
40:5) so they match. 300 and 400 should NOT be returned.

Any bright ideas out there? Thanks!

DECLARE @.a TABLE(A int, B int, C int, D int)
DECLARE @.b TABLE(A int, B int, C int, D int)

INSERT INTO @.a (A, B, C, D) VALUES (1, 100, 30, 1)
INSERT INTO @.a (A, B, C, D) VALUES (2, 100, 30, 2)
INSERT INTO @.a (A, B, C, D) VALUES (3, 100, 30, 3)
INSERT INTO @.a (A, B, C, D) VALUES (4, 100, 40, 4)
INSERT INTO @.a (A, B, C, D) VALUES (5, 100, 40, 5)

INSERT INTO @.a (A, B, C, D) VALUES (6, 200, 30, 1)
INSERT INTO @.a (A, B, C, D) VALUES (7, 200, 30, 2)
INSERT INTO @.a (A, B, C, D) VALUES (8, 200, 30, 3)
INSERT INTO @.a (A, B, C, D) VALUES (9, 200, 40, 4)
INSERT INTO @.a (A, B, C, D) VALUES (10, 200, 40, 5)

INSERT INTO @.a (A, B, C, D) VALUES (11, 300, 30, 1)
INSERT INTO @.a (A, B, C, D) VALUES (12, 300, 30, 2)
INSERT INTO @.a (A, B, C, D) VALUES (13, 300, 40, 3)
INSERT INTO @.a (A, B, C, D) VALUES (14, 400, 40, 4)
INSERT INTO @.a (A, B, C, D) VALUES (15, 400, 40, 5)

SELECT * FROM @.afigital wrote:
> Given an ID (column B), I need to find which IDs have identical data.
> That is, given '200', I want the desired result to be:
> 100
> The idea is that the system sees that id=200 has 5 records with the
> indicated data in cols C and D.
> It should then find any other ids with the exact same data for those
> columns.
> Note, in this case, both 200 and 100 have (30:1, 30:2, 30:3, 40:4,
> 40:5) so they match. 300 and 400 should NOT be returned.
> Any bright ideas out there? Thanks!
>
> DECLARE @.a TABLE(A int, B int, C int, D int)
> DECLARE @.b TABLE(A int, B int, C int, D int)
> INSERT INTO @.a (A, B, C, D) VALUES (1, 100, 30, 1)
> INSERT INTO @.a (A, B, C, D) VALUES (2, 100, 30, 2)
> INSERT INTO @.a (A, B, C, D) VALUES (3, 100, 30, 3)
> INSERT INTO @.a (A, B, C, D) VALUES (4, 100, 40, 4)
> INSERT INTO @.a (A, B, C, D) VALUES (5, 100, 40, 5)
> INSERT INTO @.a (A, B, C, D) VALUES (6, 200, 30, 1)
> INSERT INTO @.a (A, B, C, D) VALUES (7, 200, 30, 2)
> INSERT INTO @.a (A, B, C, D) VALUES (8, 200, 30, 3)
> INSERT INTO @.a (A, B, C, D) VALUES (9, 200, 40, 4)
> INSERT INTO @.a (A, B, C, D) VALUES (10, 200, 40, 5)
> INSERT INTO @.a (A, B, C, D) VALUES (11, 300, 30, 1)
> INSERT INTO @.a (A, B, C, D) VALUES (12, 300, 30, 2)
> INSERT INTO @.a (A, B, C, D) VALUES (13, 300, 40, 3)
> INSERT INTO @.a (A, B, C, D) VALUES (14, 400, 40, 4)
> INSERT INTO @.a (A, B, C, D) VALUES (15, 400, 40, 5)
> SELECT * FROM @.a

Thanks for posting the sample data. It really does help however if you
include KEYS with your DDL. Your table doesn't seem to have a key - all
the columns are nullable. That may make your problem a lot harder to
solve.

Assuming you can rewrite the table variable as:

DECLARE @.a TABLE(A int, B int, C int, D int, PRIMARY KEY (b,c,d));

Then you can do:

DECLARE @.i INT ;
SET @.i = 100 ;

SELECT B.b
FROM @.a AS A
JOIN @.a AS B
ON A.b = @.i
AND A.c = B.c
AND A.d = B.d
AND B.b <> @.i
GROUP BY B.b
HAVING COUNT(*)=
(SELECT COUNT(*)
FROM @.a
WHERE b = @.i);

If I'm wrong and you don't have such a key then it's not clear how you
want to handle duplicates. Here's a different example, assuming that A
is the key and that duplicates are significant, i.e. you want the same
number of rows in each set identified by column B:

SELECT B.b
FROM @.a AS A
JOIN @.a AS B
ON A.b = @.i
AND A.c = B.c
AND A.d = B.d
AND B.b <> @.i
GROUP BY B.b
HAVING COUNT(DISTINCT A.a)=
(SELECT COUNT(DISTINCT a)
FROM @.a
WHERE b = @.i);

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Try:

SELECT b.B
FROM @.a a
join @.a b on b.C = a.C
and b.D = a.D
where a.B = 200
and b.B <> 200
group by
b.B
having
count (*) = (select count (*) from @.a where B = 200)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"figital" <mharen@.gmail.com> wrote in message
news:1144788286.896210.141080@.i40g2000cwc.googlegr oups.com...
Given an ID (column B), I need to find which IDs have identical data.

That is, given '200', I want the desired result to be:
100

The idea is that the system sees that id=200 has 5 records with the
indicated data in cols C and D.

It should then find any other ids with the exact same data for those
columns.

Note, in this case, both 200 and 100 have (30:1, 30:2, 30:3, 40:4,
40:5) so they match. 300 and 400 should NOT be returned.

Any bright ideas out there? Thanks!

DECLARE @.a TABLE(A int, B int, C int, D int)
DECLARE @.b TABLE(A int, B int, C int, D int)

INSERT INTO @.a (A, B, C, D) VALUES (1, 100, 30, 1)
INSERT INTO @.a (A, B, C, D) VALUES (2, 100, 30, 2)
INSERT INTO @.a (A, B, C, D) VALUES (3, 100, 30, 3)
INSERT INTO @.a (A, B, C, D) VALUES (4, 100, 40, 4)
INSERT INTO @.a (A, B, C, D) VALUES (5, 100, 40, 5)

INSERT INTO @.a (A, B, C, D) VALUES (6, 200, 30, 1)
INSERT INTO @.a (A, B, C, D) VALUES (7, 200, 30, 2)
INSERT INTO @.a (A, B, C, D) VALUES (8, 200, 30, 3)
INSERT INTO @.a (A, B, C, D) VALUES (9, 200, 40, 4)
INSERT INTO @.a (A, B, C, D) VALUES (10, 200, 40, 5)

INSERT INTO @.a (A, B, C, D) VALUES (11, 300, 30, 1)
INSERT INTO @.a (A, B, C, D) VALUES (12, 300, 30, 2)
INSERT INTO @.a (A, B, C, D) VALUES (13, 300, 40, 3)
INSERT INTO @.a (A, B, C, D) VALUES (14, 400, 40, 4)
INSERT INTO @.a (A, B, C, D) VALUES (15, 400, 40, 5)

SELECT * FROM @.a|||The key is Column A (ident). Sorry for not providing more complete ddl.
I will check out these suggestions, thanks!|||DECLARE @.a TABLE(A int IDENTITY(1,1) PRIMARY KEY, B int, C int, D int);

INSERT INTO @.a (B, C, D) VALUES (100, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (100, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (100, 30, 3)
INSERT INTO @.a (B, C, D) VALUES (100, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (100, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (200, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (200, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (200, 30, 3)
INSERT INTO @.a (B, C, D) VALUES (200, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (200, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (300, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (300, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (300, 40, 3)
INSERT INTO @.a (B, C, D) VALUES (400, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (400, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (500, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (500, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (500, 30, 3)
INSERT INTO @.a (B, C, D) VALUES (500, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (500, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (500, 31, 6)

--SELECT * FROM @.a

DECLARE @.i INT ;
SET @.i = 200 ;

-- solution

The above solutions return 500 even though it contains _6_ records and
200 contains 5 records.|||Our solutions fall into the category of "Relational Division". In both
solutions, we allow for a remainder. What you want is exact division.
Here's a solution for exact division:

SELECT a.B
FROM @.a a
left
join @.a b on b.C = a.C
and b.D = a.D
and b.B = 200
where a.B <> 200
group by
a.B
having
count (distinct a.D) = (select count (distinct D) from @.a where B = 200)

If A is an identity, you could use count (distinct A) where applicable.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"figital" <mharen@.gmail.com> wrote in message
news:1144792950.602964.118760@.g10g2000cwb.googlegr oups.com...
DECLARE @.a TABLE(A int IDENTITY(1,1) PRIMARY KEY, B int, C int, D int);

INSERT INTO @.a (B, C, D) VALUES (100, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (100, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (100, 30, 3)
INSERT INTO @.a (B, C, D) VALUES (100, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (100, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (200, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (200, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (200, 30, 3)
INSERT INTO @.a (B, C, D) VALUES (200, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (200, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (300, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (300, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (300, 40, 3)
INSERT INTO @.a (B, C, D) VALUES (400, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (400, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (500, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (500, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (500, 30, 3)
INSERT INTO @.a (B, C, D) VALUES (500, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (500, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (500, 31, 6)

--SELECT * FROM @.a

DECLARE @.i INT ;
SET @.i = 200 ;

-- solution

The above solutions return 500 even though it contains _6_ records and
200 contains 5 records.|||Tom and David,

Thank you very much for your help!

I had to add a check because of the left join but otherwise, awesome!

SELECT a.B
FROM @.a a
left join @.a b on b.C = a.C
and b.D = a.D
and b.B = @.i
where a.B <> @.i and NOT (B.C IS NULL OR B.D IS NULL)
group by
a.B
having
count (distinct a.D) = (select count (distinct D) from @.a where B =
@.i)|||Oh, OK. The input data didn't have nulls, so I didn't go there. Glad you
now have a solution. :-)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"figital" <mharen@.gmail.com> wrote in message
news:1144797933.088430.155480@.g10g2000cwb.googlegr oups.com...
Tom and David,

Thank you very much for your help!

I had to add a check because of the left join but otherwise, awesome!

SELECT a.B
FROM @.a a
left join @.a b on b.C = a.C
and b.D = a.D
and b.B = @.i
where a.B <> @.i and NOT (B.C IS NULL OR B.D IS NULL)
group by
a.B
having
count (distinct a.D) = (select count (distinct D) from @.a where B =
@.i)

Find Current Date Time Span SQL

Any body have an Idea on this... Last problem of my Calendar Application.

To keep my calendar loading fast and efficent I have a SP that loads data to a datatable to read from c#.

My sp reads this

(
@.ID int,
@.DateStart datetime,
@.DateEnd datetime
)
AS

SELECT JobDateStart, JobDateEnd, EstimateDate, cLastName,
cCity, ContractPrice, JobDescription, Status
FROM Customers
WHERE (ID = @.ID AND JobDateStart Between @.DateStart AND @.DateEnd + 1
OR JobDateEnd Between @.DateStart AND @.DateEnd + 1)

Now My problem is that the DateStart and DateEnd values are used to pull information from a DB. So lets say that I have a job that starts on November 30th and ends on January 3rd.
My calendar will display the record on the 30th day of Nov and on the 1st through 3rd fo Jan, however when I look at December there is NO DATA there. which is the expected result because the dates do not fall within the start and ends dates.

My question is how query the database to pull back my month spanding record without pulling back all the data in the database for performace?

My thought is something like the date span falls into the range between the start and end, but I cannot find anything????? Please help, I know it is probaly simple right??Found the answer, just took THINKING LESS rather than more..

add and OR statement like this, it will get the 1 day events and current month event and the spanning events.

SELECT JobDateStart, JobDateEnd, EstimateDate, cLastName,
cCity, ContractPrice, JobDescription, Status
FROM Customers
WHERE (ID = @.ID AND
/*FIX SPANNING MONTHS / YEAR JOBS*/
@.DateStart BETWEEN JobDateStart AND JobDateEnd +1
/*SELECT 1 DAY OR 1 MONTH JOBS*/
OR JobDateStart Between @.DateStart AND @.DateEnd + 1)

It really was that easy. Less load on the db so I can loop through the DataTable and do my sorting with no effect on the DB.

Financial function

Hello,

I seek a solution for the cacule of YIELD TO MATURITY of the bonds. In Excel av YIELD() but SQL server ?

Do you have an idea?

Thank you

I do not know the exact logic of the function but you should be able to code the same in a UDF or procedure in SQL Server. In SQL Server 2005, you can implement the same using C#/VB.NET function/procedure. Alternatively, you can invoke the Excel function via OLE automation or pass-through query provided you have to data in a Excel sheet. This approach however is not that pretty and probably overkill.

Anyway, what is the reason for trying to implement the function in SQL Server? You can get the data from SQL Server into Excel easily and perform the computation there. You can then persist results back to the database if you want to. And you can automate this whole process using say DTS or SSIS package for example.

|||SET NOCOUNT ON
CREATE TABLE Cash_Flows
(
cashflow DECIMAL(8,2),
valuta DATETIME
)
INSERT INTO Cash_Flows VALUES (-95.92,'20060115')
INSERT INTO Cash_Flows VALUES (5,'20070115')
INSERT INTO Cash_Flows VALUES (5,'20080115')
INSERT INTO Cash_Flows VALUES (5,'20090115')
INSERT INTO Cash_Flows VALUES (105,'20100115')

GO

CREATE FUNCTION yield_to_maturity(@.issue_date SMALLDATETIME)
RETURNS DECIMAL(15,14)
AS BEGIN
DECLARE @.ytm_tmp FLOAT
DECLARE @.ytm FLOAT
DECLARE @.pv_tmp FLOAT
DECLARE @.pv FLOAT

SET @.ytm_tmp = 0
SET @.ytm = 0.1
SELECT @.pv_tmp = SUM(cashflow) FROM Cash_Flows
SET @.pv = (SELECT SUM(cashflow/POWER(1.0+@.ytm,(DATEDIFF(month,@.issue_date, valuta)* 30 + DAY(valuta)-DAY(@.issue_date)
- CASE WHEN(@.issue_date)>=30 AND DAY(valuta) = 31 THEN 1 ELSE 0 END )/
360.0 )) FROM Cash_Flows)

WHILE ABS(@.pv) >= 0.000001
BEGIN
DECLARE @.t FLOAT
SET @.t = @.ytm_tmp
SET @.ytm_tmp = @.ytm
SET @.ytm = @.ytm + (@.t-@.ytm)*@.pv/(@.pv-@.pv_tmp)
SET @.pv_tmp = @.pv
SET @.pv = (SELECT SUM(cashflow/POWER(1.0+@.ytm,(DATEDIFF(month,@.issue_date, valuta) * 30 + DAY(valuta)-DAY(@.issue_date)
- CASE WHEN(@.issue_date)>=30 AND DAY(valuta) = 31 THEN 1 ELSE 0 END
) /360.0))
FROM Cash_Flows)
END
RETURN @.ytm
END
GO

SELECT dbo.yield_to_maturity('20060115')
DROP FUNCTION yield_to_maturity
DROP TABLE Cash_Flows
GO
SET NOCOUNT OFF

The fundamental principle of every asset valuation is that the fair value of an asset equals the present value of its cash flows. So, every asset valuation consists more or less of these three steps.
1. Estimate the expected cash flows
2. Determine one or more appropriate discount rates, that you will use to discount the cash flows
3. Calculate Present Value of 1. using 2.

While this is a nice exercise, the above covers only the most basic cases. You can only calculate plain vanilla bonds without embedded derivatives with that formula. Also, it doesn't take into account common day conventions like ISMA 251 or money market conventions. The formula rather uses a simple 30/360 day convention, which nowadays isn't common anymore in Europe. You would need to extend that on your own.
Calculating the YTM is an iterative process while not really is what SQL Server excels at. Like UC said, you would rather do this in a spreadsheet application with a specialised Add-In or some other front-end language|||Isn't the sigature automatically inserted anymore?
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

Final Attemp -Sql Stored Procedure Tough Question

I have a procedure I need to get the values out of..I am using outputs...I have no idea why it wont work.....I need all values listed in the select part of procedure...

CREATE procedure dbo.Appt_Login_NET
(
@.LoginName nvarchar(15),
@.Password NvarChar(15),
@.UserName nvarchar(15)Output,
@.UserPassword nvarchar(15)Output,
@.UserClinic nvarchar(3)Output,
@.UserTester bit Output
)
as
select
UserName,
UserPassword,
UserClinic,
UserTester
from
Clinic_users
where
UserName = @.LoginName
and
UserPassword = @.Password

GO

my vb.net code to retrive this info is

Private Sub Button1_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.ServerClick
Dim con As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmd As New SqlCommand
Dim parmuser As SqlParameter
Dim parmus As SqlParameter
Dim parmpass As SqlParameter
Dim parmtest As SqlParameter
Dim struser As String
Dim strpass As String
Dim strclinic As String
Dim strnames As String
Dim tester As String
strpass = txtPass.Value
struser = txtUser.Value
cmd = New SqlCommand("Appt_Login_NET", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@.LoginName", struser)
cmd.Parameters.Add("@.Password", strpass)
parmus = cmd.Parameters.Add("@.UserName", SqlDbType.NVarChar)
parmus.Size = 15
parmus.Direction = ParameterDirection.Output
parmuser = cmd.Parameters.Add("@.UserClinic", SqlDbType.NVarChar)
parmuser.Size = 3
parmuser.Direction = ParameterDirection.Output
parmpass = cmd.Parameters.Add("@.UserPassword", SqlDbType.NVarChar)
parmpass.Size = 15
parmpass.Direction = ParameterDirection.Output
parmtest = cmd.Parameters.Add("@.UserTester", SqlDbType.Bit)
parmtest.Size = 1
parmtest.Direction = ParameterDirection.Output

con.Open()
cmd.ExecuteNonQuery()
If Not IsDBNull(cmd.Parameters("@.UserName").Value) Then
Label1.Text = cmd.Parameters("@.UserName").Value()
Else
Label1.Text = "No Results Found"
End If

con.Close()
End Sub

Why does this always show as "DBNUll" I get nothing when I debug any of my parm variables.I searched the SQl Server and in Query analyzer instead of the output variables in the procedure being just outputs they are input/outputs...............What does it take to get this working? Do I need a conversion datatype I would prefer I gain the values and store them in variables.....CREATE procedure dbo.Appt_Login_NET
(
@.LoginName nvarchar(15),
@.Password NvarChar(15)
)
as
select
UserName,
UserPassword,
UserClinic,
UserTester
from
Clinic_users
where
UserName = @.LoginName
and
UserPassword = @.Password
------------

You are trying to get all the information based on user/pass information passed into the SP. Then you won't need to have the other parameters in your SP since they are fields in your table.. Data contained in the table will get returned based on the user/pass credentials that were provided.

I am sure you were getting an error on the other parameters since you didn't provide data to those...

Then using a DataReader just use the field name to get the values out of them

Dim reader as SqlDataReader = command.ExecuteReader(...)

IF reader.Read THEN
string UserName = CType(reader["UserName"], string)
END IF|||So I should leave that procedure the way I had it like you have it and then how exactlly do I extract for example the User Clinic if I used this data reader thing.How might I set that up.....Thanks|||UserClinic is the clinic at which this user works then you can do the same thing I did in that IF statement...

dim user as string
dim uClinic as string
dim uTester as boolean

IF reader.Read THEN
user = CType(reader("username"), string)
uClinic = CType(reader("UserClinic"), string)
uTester = CType(reader("UserTester"), boolean)
END IF

sorry in previous post I used [] in the reader ordinal call...[] is a C# thing|||No, no no. You're are correct to use OUTPUT params and not to use a result set. You are trying to get a few columns of data for a single row. OUTPUT params is exactly the correct thing to use. Your problem is that you've not written your proc correctly. Open up Query Analyser, write the proc and test the thing. Once its working then you can then worry about the .net code.|||I have tried this code but I get an error on "Inncorrect Syntax" I really cant figure this out..help please...

here is the source error
Source Error:

Line 86: cmd.Parameters.Add("@.LoginName", txtUser.Value)
Line 87: cmd.Parameters.Add("@.Password", txtPass.Value)
Line 88: reader = cmd.ExecuteReader <--is highlighted in red
Line 89: If reader.Read Then
Line 90: user = CType(reader("UserName"), String)

my VB.net Code.......

Private Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim con As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim user As String
Dim uclinic As String
Dim uTester As Boolean
Dim reader As SqlDataReader
Dim cmd As SqlCommand

con.Open()
cmd = New SqlCommand("Appt_Login", con)
cmd.Parameters.Add("@.LoginName", txtUser.Value)
cmd.Parameters.Add("@.Password", txtPass.Value)
reader = cmd.ExecuteReader
If reader.Read Then
user = CType(reader("UserName"), String)
uclinic = CType(reader("UserClinic"), String)
End If

End Sub|||view post 510022|||Thanks guys for all the help....I got it working

Filters for dataset from Parameter - How to ignore if the parameter is NULL ?

Hi,

I am starting to use reporting services and I created a report that takes 4 parameters for a Filter on the dataset.

The idea is the create snapshot nightly and then display the report filtered based on parameters to users.

I'd like that the filter be ignored for a parameter if the parameter is passed as NULL,

Right now I defined it this way :

Left =Fields!RegionCode.Value Operator = Right =IIF(IsNothing(Parameters!RegionCode.Value),Fields!RegionCode.Value,Parameters!RegionCode.Value)

I do this for the 4 parameters that are passed, if they are null, make then equals to the field.

I was wondering if there is a way to ignore the whole parameter all together, I would guess it'll be faster to execute.

Thanks

One way would be to use this in your stored procedure or query, with coalesce...

eg. AND COALESCE(@.RegionCode,[RegionCode]) = [RegionCode]

That way you're not bringing the data into the report and then filtering it out, you're filtering at the source.

|||

No I don't want to do that,

The idea is the get ALL the data in a snapshot nightly then apply filters on the snapshot when we display to the users.

The result is that the DB will not get hammered everytime a report is run but only once at night.

So the coalesce will not work.

|||

I guess you could do something then with the left side of the filter and an if statement?

If the parameter on the left side is null, then set it to 1, otherwise set it to the field. If the parameter on the right side is null, set it to 1, otherwise set it to the parameter. Then 1=1 and it will ignore the parameter.

Not sure if this will work but it sounds faster than comparing field to field in theory.

cheers,

Andrew

|||

Andrew - T4G wrote:

I guess you could do something then with the left side of the filter and an if statement?

If the parameter on the left side is null, then set it to 1, otherwise set it to the field. If the parameter on the right side is null, set it to 1, otherwise set it to the parameter. Then 1=1 and it will ignore the parameter.

Not sure if this will work but it sounds faster than comparing field to field in theory.

cheers,

Andrew

Thanks, I'll try something like that.