[KCross]

I think it's fairly obvious what I'm doing here. I'm using different queries depending on whether certain parameters are null. This is an absolute mess, but I'm a SQL noob. I just started learning, and it took me hours to even get this working. Since it works, I left it alone. But it's time to revisit and clean it up.

How should I approach this? Case statements? Something else?

ALTER PROCEDURE [dbo].[FilterReportByStatusandDate]
	-- Add the parameters for the stored procedure here
	@filterby varchar(50),
	@startdate date,
	@enddate date
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF @filterby = '' SET @filterby = null
    IF @startdate = '' SET @startdate = null
    IF @enddate = '' SET @enddate = null
    
	IF @filterby is null and
	@enddate is null and
	@startdate is null
	BEGIN
	select d.type, m.model, p.phonenumber, prov.provider, b.pin, l.laptop_id, s.description, x.fname, x.lname, u.checkoutdate, u.checkindate
FROM Usage u INNER JOIN Device d
ON u.device_id = d.device_id
INNER JOIN PhoneNumber p
ON d.device_id = p.device_id
INNER JOIN BlackBerry b
ON d.device_id = b.device_id 
INNER JOIN Status s
ON u.status_id = s.status_id 
LEFT OUTER JOIN Users x
ON x.user_id = u.user_id 
INNER JOIN Model m
ON m.device_id = d.device_id
INNER JOIN Provider prov
ON prov.device_id = d.device_id
LEFT OUTER JOIN Laptop l
ON l.device_id = d.device_id
	END
	
IF @enddate is null and
	@startdate is null
	BEGIN
	select d.type, m.model, p.phonenumber, prov.provider, b.pin, l.laptop_id, s.description, x.fname, x.lname, u.checkoutdate, u.checkindate
FROM Usage u INNER JOIN Device d
ON u.device_id = d.device_id
INNER JOIN PhoneNumber p
ON d.device_id = p.device_id
INNER JOIN BlackBerry b
ON d.device_id = b.device_id 
INNER JOIN Status s
ON u.status_id = s.status_id 
LEFT OUTER JOIN Users x
ON x.user_id = u.user_id 
INNER JOIN Model m
ON m.device_id = d.device_id
INNER JOIN Provider prov
ON prov.device_id = d.device_id
LEFT OUTER JOIN Laptop l
ON l.device_id = d.device_id
WHERE s.description = @filterby
END
	
IF @startdate is null and
@filterby is null
BEGIN
select d.type, m.model, p.phonenumber, prov.provider, b.pin, l.laptop_id, s.description, x.fname, x.lname, u.checkoutdate, u.checkindate
FROM Usage u INNER JOIN Device d
ON u.device_id = d.device_id
INNER JOIN PhoneNumber p
ON d.device_id = p.device_id
INNER JOIN BlackBerry b
ON d.device_id = b.device_id 
INNER JOIN Status s
ON u.status_id = s.status_id 
LEFT OUTER JOIN Users x
ON x.user_id = u.user_id 
INNER JOIN Model m
ON m.device_id = d.device_id
INNER JOIN Provider prov
ON prov.device_id = d.device_id
LEFT OUTER JOIN Laptop l
ON l.device_id = d.device_id
WHERE u.checkindate <= @enddate
	END
	
	IF @enddate is null and
	@filterby is null
	BEGIN
	select d.type, m.model, p.phonenumber, prov.provider, b.pin, l.laptop_id, s.description, x.fname, x.lname, u.checkoutdate, u.checkindate
FROM Usage u INNER JOIN Device d
ON u.device_id = d.device_id
INNER JOIN PhoneNumber p
ON d.device_id = p.device_id
INNER JOIN BlackBerry b
ON d.device_id = b.device_id 
INNER JOIN Status s
ON u.status_id = s.status_id 
LEFT OUTER JOIN Users x
ON x.user_id = u.user_id 
INNER JOIN Model m
ON m.device_id = d.device_id
INNER JOIN Provider prov
ON prov.device_id = d.device_id
LEFT OUTER JOIN Laptop l
ON l.device_id = d.device_id
WHERE u.checkoutdate >= @startdate
	END
	
	IF @filterby is null
	BEGIN
	select d.type, m.model, p.phonenumber, prov.provider, b.pin, l.laptop_id, s.description, x.fname, x.lname, u.checkoutdate, u.checkindate
FROM Usage u INNER JOIN Device d
ON u.device_id = d.device_id
INNER JOIN PhoneNumber p
ON d.device_id = p.device_id
INNER JOIN BlackBerry b
ON d.device_id = b.device_id 
INNER JOIN Status s
ON u.status_id = s.status_id 
LEFT OUTER JOIN Users x
ON x.user_id = u.user_id 
INNER JOIN Model m
ON m.device_id = d.device_id
INNER JOIN Provider prov
ON prov.device_id = d.device_id
LEFT OUTER JOIN Laptop l
ON l.device_id = d.device_id
WHERE u.checkoutdate >= @startdate and
u.checkindate <= @enddate
	END
	
	IF @enddate is null 
	BEGIN
	select d.type, m.model, p.phonenumber, prov.provider, b.pin, l.laptop_id, s.description, x.fname, x.lname, u.checkoutdate, u.checkindate
FROM Usage u INNER JOIN Device d
ON u.device_id = d.device_id
INNER JOIN PhoneNumber p
ON d.device_id = p.device_id
INNER JOIN BlackBerry b
ON d.device_id = b.device_id 
INNER JOIN Status s
ON u.status_id = s.status_id 
LEFT OUTER JOIN Users x
ON x.user_id = u.user_id 
INNER JOIN Model m
ON m.device_id = d.device_id
INNER JOIN Provider prov
ON prov.device_id = d.device_id
LEFT OUTER JOIN Laptop l
ON l.device_id = d.device_id
WHERE s.description = @filterby and
u.checkoutdate >= @startdate
	END
	
	IF @startdate is null
	BEGIN
	select d.type, m.model, p.phonenumber, prov.provider, b.pin, l.laptop_id, s.description, x.fname, x.lname, u.checkoutdate, u.checkindate
FROM Usage u INNER JOIN Device d
ON u.device_id = d.device_id
INNER JOIN PhoneNumber p
ON d.device_id = p.device_id
INNER JOIN BlackBerry b
ON d.device_id = b.device_id 
INNER JOIN Status s
ON u.status_id = s.status_id 
LEFT OUTER JOIN Users x
ON x.user_id = u.user_id 
INNER JOIN Model m
ON m.device_id = d.device_id
INNER JOIN Provider prov
ON prov.device_id = d.device_id
LEFT OUTER JOIN Laptop l
ON l.device_id = d.device_id
WHERE s.description = @filterby and
u.checkindate <= @enddate 
	END
	
END

[Vomster]

You could do the following.

Add a few parameter and set them to null. When they are null pull everything basically.

ALTER PROCEDURE [dbo].[FilterReportByStatusandDate] 
        -- Add the parameters for the stored procedure here 
        @filterby varchar(50) = null, 
        @CheckInStartDate datetime = null, 
        @CheckInEndDate datetime = null, 
        @CheckOutStartDate datetime = null, 
        @CheckOutEndDate datetime = null
AS 

select 
	d.type, 
	m.model, 
	p.phonenumber, 
	prov.provider, 
	b.pin, 
	l.laptop_id, 
	s.description, 
	x.fname, 
	x.lname, 
	u.checkoutdate, 
	u.checkindate 
FROM Usage u INNER JOIN Device d ON u.device_id = d.device_id 
INNER JOIN PhoneNumber p ON d.device_id = p.device_id 
INNER JOIN BlackBerry b ON d.device_id = b.device_id  
INNER JOIN Status s ON u.status_id = s.status_id  
LEFT OUTER JOIN Users x ON x.user_id = u.user_id  
INNER JOIN Model m ON m.device_id = d.device_id 
INNER JOIN Provider prov ON prov.device_id = d.device_id 
LEFT OUTER JOIN Laptop l ON l.device_id = d.device_id 
WHERE
isnull(s.description,'') = isnull(@filterby,s.description) AND 
isnull(u.checkindate,'') BETWEEN ISNULL(@CheckInStartDate,'1900-1-1') AND ISNULL(@CheckInEndDate,'2099-1-1') AND
isnull(u.checkoutdate,'') BETWEEN ISNULL(@CheckOutStartDate,'1900-1-1') AND ISNULL(@CheckOutEndDate,'2099-1-1') 

[KCross]

When real parameters are passed in, they'll override the null assignments, right?

[Vomster]

Correct.

Basically you are setting those parameters to null by default unless something is passed.

[KCross]

I don't understand why you've added the new parameters. Do you mind explaining?

[Vomster]

I added the two extra date parameters because you would be limiting yourself to people who checked in and out in the same date range by using the BETWEEN in there. If that is ok, which I was guessing is not, then use one set of date parameters.

Make sense?

[KCross]

I'm trying to make sense of this...

Now... That report will ALWAYS throw back a checkoutdate. That is, it shows current and past history. So it will only show device that are checked out, or were checked out and checked back in.

In my original query, I don't believe I needed any "if @startdate is null" statements... But (as you can see) I was very confused writing the original query. I'm amazed it worked.

The "startdate" and "enddate" (or "checkoutdate" and "checkindate") will always be one date. That is, a "checkoutstartdate" and "checkoutenddate" will always be the same. The process of checking a device in or out is only 1 action.

I think I understand why you've done this though.

You're saying if I don't use variables for the BETWEEN statement, I'll have to code it with static dates (1900-1-1 and 2099-1-1). Is there a reason that would be inefficient or not ideal? My concern is that having Start and End versions of the dates will make me have to pass extra parameters that are simply duplicates of what I originally used.

Am I not understanding this at all? I'm still really out of my element with SQL.

[KCross]

I remember why I had the "if @startdate is null."

I wanted to give people the ability to filter the report by Status (@filterby), CheckOut Date (@startdate) and CheckIn Date (@enddate) independently. So it would be likely someone would make a request that wouldn't pass in a @startdate parameters. They don't care when the startdate was - only the enddate (or status, or combination of status & enddate).

I'm trying to apply this, but so far I've only managed to break my filtering. They no longer work properly.

I've only made a small adjustment...

USE [LNRINV]
GO
/****** Object:  StoredProcedure [dbo].[FilterReportByStatusandDate]    Script Date: 03/29/2010 11:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[FilterReportByStatusandDate] 
        -- Add the parameters for the stored procedure here 
        @filterby varchar(50) = null, 
        @startdate datetime = null, 
        @enddate datetime = null

AS 

select 
	d.type, 
	m.model, 
	p.phonenumber, 
	prov.provider, 
	b.pin, 
	l.laptop_id, 
	s.description, 
	x.fname, 
	x.lname, 
	u.checkoutdate, 
	u.checkindate 
FROM Usage u INNER JOIN Device d ON u.device_id = d.device_id 
INNER JOIN PhoneNumber p ON d.device_id = p.device_id 
INNER JOIN BlackBerry b ON d.device_id = b.device_id  
INNER JOIN Status s ON u.status_id = s.status_id  
LEFT OUTER JOIN Users x ON x.user_id = u.user_id  
INNER JOIN Model m ON m.device_id = d.device_id 
INNER JOIN Provider prov ON prov.device_id = d.device_id 
LEFT OUTER JOIN Laptop l ON l.device_id = d.device_id 
WHERE
isnull(s.description,'') = isnull(@filterby,s.description) AND 
isnull(u.checkindate,'') BETWEEN ISNULL(@startdate,'1900-1-1') AND ISNULL(@enddate,'2099-1-1') AND
isnull(u.checkoutdate,'') BETWEEN ISNULL(@startdate,'1900-1-1') AND ISNULL(@enddate,'2099-1-1') 

[Vomster]

By using the same parameter (@startdate and @enddate) for both check in and check out you will only be getting people who check in and out in within the same date range. Unless you put an OR where the AND is below.

isnull(u.checkindate,'') BETWEEN ISNULL(@startdate,'1900-1-1') AND ISNULL(@enddate,'2099-1-1') AND 
isnull(u.checkoutdate,'') BETWEEN ISNULL(@startdate,'1900-1-1') AND ISNULL(@enddate,'2099-1-1')

Does that help?

Did you play around with using the four parameters dates instead of two?

This post has been edited by Vomster: 29 March 2010 - 10:25 AM

[KCross]

I honestly couldn't wrap my head around how I'd pass those parameters into the stored procedure - that is, where'd they'd come from besides abritrarily making them up, or duplicating the @startdate and @enddate parameters.

I tried the OR. The filters just don't filter the data properly. It seems like the Check Out dropdown now filters the @enddate parameters, and the Check In doesn't seem to do anything at all. And my status dropdown also seems to not do anything.

I just don't think I have a good enough understanding to apply what you're suggesting. Maybe I'll revisit this in another month.

As an aside - Thank you very much for trying to help! I really do appreciate it. And I like your d20. Did you go to PAX this weekend?

[Vomster]

It took me a while to get accustomed to it. Are you using SQL Reporting Services? Because, if you didn't know, when you change the parameters of a stored procedure I always refresh the fields in the dataset and go into the parameters to make sure new ones get setup or add them myself.

You're quite welcome. Sorry it didn't work out.

Thanks, I did not get a chance to go but I'd like to in the future.

Good luck!

[KCross]

Yes, I'm using SSRS. That's part of the reason I'm trying to keep the original 3 parameters. SSRS gives me a headache. I didn't want to have to goof with it.

[Vomster]

50% of my job is creating reports through that. It took me a bit to catch on but it really isn't too difficult. I created a test template and played around adding stored procedures/parameters and taking them awawy and stuff like that. I just kept changing stuff in it to see what would happen before I dorked with any production stuff.

[KCross]

!!!!!!!!

I got it!!!

Thank you so much for your help!!

USE [LNRINV]
GO
/****** Object:  StoredProcedure [dbo].[FilterReportByStatusandDate]    Script Date: 03/29/2010 11:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[FilterReportByStatusandDate] 
        -- Add the parameters for the stored procedure here 
        @filterby varchar(50) = null, 
        @startdate datetime = null, 
        @enddate datetime = null
        

AS 

select 
	d.type, 
	m.model, 
	p.phonenumber, 
	prov.provider, 
	b.pin, 
	l.laptop_id, 
	s.description, 
	x.fname, 
	x.lname, 
	u.checkoutdate, 
	u.checkindate 
FROM Usage u INNER JOIN Device d ON u.device_id = d.device_id 
INNER JOIN PhoneNumber p ON d.device_id = p.device_id 
INNER JOIN BlackBerry b ON d.device_id = b.device_id  
INNER JOIN Status s ON u.status_id = s.status_id  
LEFT OUTER JOIN Users x ON x.user_id = u.user_id  
INNER JOIN Model m ON m.device_id = d.device_id 
INNER JOIN Provider prov ON prov.device_id = d.device_id 
LEFT OUTER JOIN Laptop l ON l.device_id = d.device_id 
WHERE
isnull(s.description,'') = isnull(@filterby,s.description) AND 
isnull(u.checkindate,'') BETWEEN ISNULL(@enddate,'1900-1-1') AND '2099-1-1' AND
isnull(u.checkoutdate,'') BETWEEN ISNULL(@startdate,'1900-1-1') AND '2099-1-1'

[baavgai]

I actually have a pretty good trick for this. You're going to like it:

select d.type, m.model, p.phonenumber, prov.provider, b.pin, l.laptop_id, s.description, x.fname, x.lname, u.checkoutdate, u.checkindate
	FROM Usage u 
		INNER JOIN Device d
			ON u.device_id = d.device_id
		INNER JOIN PhoneNumber p
			ON d.device_id = p.device_id
		INNER JOIN BlackBerry b
			ON d.device_id = b.device_id 
		INNER JOIN Status s
			ON u.status_id = s.status_id 
				AND (@filterby is null OR s.description = @filterby)
		LEFT OUTER JOIN Users x
			ON x.user_id = u.user_id 
		INNER JOIN Model m
			ON m.device_id = d.device_id
		INNER JOIN Provider prov
			ON prov.device_id = d.device_id
		LEFT OUTER JOIN Laptop l
			ON l.device_id = d.device_id
	WHERE (@enddate is null OR u.checkindate <= @enddate)
		AND (@startdate is null OR u.checkoutdate >= @startdate)

Edit: bah, beat me to it.

This post has been edited by baavgai: 29 March 2010 - 12:52 PM