November 10, 2008 at 7:35 am
I'm trying to get the data from the table
between two dates and for the List of Doctors
select * from tbl_test
where convert(char(10),createdate,121) between '2008-01-01'
and '2008-12-31' and id_doctor in (1,2,3,4,5)
I want to use two variables for the dates and one array for id_doctors
Pl Help :w00t:
November 10, 2008 at 10:26 pm
doss.tychicus (11/10/2008)
I'm trying to get the data from the tablebetween two dates and for the List of Doctors
select * from tbl_test
where convert(char(10),createdate,121) between '2008-01-01'
and '2008-12-31' and id_doctor in (1,2,3,4,5)
I want to use two variables for the dates and one array for id_doctors
Pl Help :w00t:
use temporary tables
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 10, 2008 at 10:49 pm
If you are using SQL 2008, you can pass table to stored procedure as a variable.
For more info see SQL 2008 BOL.
November 11, 2008 at 5:08 am
Check this article for lots of information on how to use arrays in SQL Server: http://www.sommarskog.se/arrays-in-sql-2005.html
November 11, 2008 at 5:48 pm
You have two options.
The first is to construct your query using dynamic SQL.
The second option is to convert the array to a table and then JOIN to that table.
The following article explains how to split an array into a table:
November 12, 2008 at 9:42 pm
An array is a 'type', something the archaic computer science of sql knows nothing about. You have to move to a 'real' relational system to find a list/array type. You'll find such adult computer science in Dataphor.
November 13, 2008 at 7:51 am
How are you getting the list of doctors? Is it passed in or is it in a table.
November 13, 2008 at 1:58 pm
If your list of doctors is being passed into the stored procedure as a delimited string then you can write a table-valued function to parse the string and return it as a table and use those results as sub-select for the 'IN' clause.
Here's an example:
This is the code for the split function:
CREATE FUNCTION [dbo].[tvf_Split]
(
@String VARCHAR(4000),
@Delimiter VARCHAR(5)
)
RETURNS @SplittedValues TABLE
(
OccurenceId SMALLINT IDENTITY(1,1),
SplitValue VARCHAR(200)
)
AS
BEGIN
DECLARE @SplitLength INT
WHILE LEN(@String) > 0
BEGIN
SELECT @SplitLength = (CASE CHARINDEX(@Delimiter,@String)
WHEN 0 THEN LEN(@String)
ELSE CHARINDEX(@Delimiter,@String) - 1
END)
INSERT INTO @SplittedValues
SELECT SUBSTRING(@String,1,@SplitLength)
SELECT @String = (CASE (LEN(@String) - @SplitLength)
WHEN 0 THEN ''
ELSE RIGHT(@String, LEN(@String) - @SplitLength - 1)
END)
END
RETURN
Here's how to use it:
declare @inValues varchar(255)
SET @inValues = '(1,2,3,4,5)'
select * from tbl_test
where convert(char(10),createdate,121) between '2008-01-01' and '2008-12-31'
and id_doctor in (select SplitValue FROM tvf_Split(@inValues, ','))
good luck!
-Mike
November 13, 2008 at 2:14 pm
Mike, a WHILE loop is not the best way to split a CSV.
Take a look at this article. It explains how a table of numbers replaces a loop.
November 13, 2008 at 5:07 pm
krayknot (11/10/2008)
doss.tychicus (11/10/2008)
I'm trying to get the data from the tablebetween two dates and for the List of Doctors
select * from tbl_test
where convert(char(10),createdate,121) between '2008-01-01'
and '2008-12-31' and id_doctor in (1,2,3,4,5)
I want to use two variables for the dates and one array for id_doctors
Pl Help :w00t:
use temporary tables
Sure! How? Got code? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2008 at 5:13 pm
doss.tychicus (11/10/2008)
I'm trying to get the data from the tablebetween two dates and for the List of Doctors
select * from tbl_test
where convert(char(10),createdate,121) between '2008-01-01'
and '2008-12-31' and id_doctor in (1,2,3,4,5)
I want to use two variables for the dates and one array for id_doctors
Pl Help :w00t:
Ggraber is spot on with the suggestion to use a Tally table. Assuming that you eventually want to turn the code into a stored procedure and assuming that you took Ggraber's advise and read the link he posted, the following untested code should be pretty close to right...
DECLARE @StartDate DATETIME,
@EndDate DATETIME,
@Doctor_IDs VARCHAR(8000)
SELECT @StartDate = '2008-01-01',
@EndDate = '2008-12-31',
@Doctor_IDs = '1,2,3,4,5'
SELECT tt.*
FROM tbl_Test tt
INNER JOIN
(SELECT SUBSTRING(','+@Doctor_IDs,N+1,CHARINDEX(',',@Doctor_IDs+',',N)-N) AS ID_Doctor
FROM dbo.Tally t
WHERE t.N <= LEN(','+@Doctor_IDs)
AND SUBSTRING(','+@Doctor_IDs,t.N,1) = ',') d
ON tt.ID_Doctor = d.ID_Doctor
WHERE tt.CreateDate >= DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)
AND tt.CreateDate < DATEADD(dd,DATEDIFF(dd,0,@EndDate)+1,0)
If you want actual tested code, then you'll need to provide CREATE TABLE statements and some data in a readily consummable format. See the link in my signature for a fairly easy way to do that.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2008 at 5:48 pm
Jeff Moden (11/13/2008)
Ggraber's advise and read the link he posted
:hehe: Not HE -- SHE :hehe:
November 13, 2008 at 5:58 pm
Oh dear... I blew it... :blush: I normally try to write in a gender-agnostic fashion especially when I don't really know someone. My appologies. Perhaps a first name introduction would be appropriate as a memory aid to this aging mind.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2008 at 6:03 pm
Jeff Moden (11/13/2008)
Oh dear... I blew it... :blush: I normally try to write in a gender-agnostic fashion especially when I don't really know someone. My appologies. Perhaps a first name introduction would be appropriate as a memory aid to this aging mind.
You don't have to apologize, Jeff. There was no way for you to know, and anyway I thought it was quite funny.
My name is Goldie Graber. 🙂
November 13, 2008 at 6:40 pm
Thanks, Goldie. Glad to "meet" you. I appreciate it. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply