December 10, 2008 at 2:42 pm
Code below works great and pulls data in query Analyzer but when I insert into a transform data task between the sql connetion(source) and a excel or text file connection(destination) I get an error when I run or even try to preview:
No rowset was returned from sql statement.
Is there a way to get this to work, I am going about it all wrong? Thanks,
DECLARE @Today DateTime, @Yesterday DateTime, @RunDate char(25), @ForDate char(12)
SET @Today = dateadd(dd,0, datediff(dd,0,getdate()))
SET @Yesterday = dateadd(day, -1, @Today)
SELECT @RunDate = convert(char(19), getdate())
SELECT @ForDate = convert(char, @Yesterday)
/**************************************************
print 'Report run on ' + rtrim(@RunDate) + ' for ' + rtrim(@ForDate)
print ' '
print ' '
print ' '
*******************************************/
USE docnet
SELECT rtrim(de.FirstName) + ' ' + rtrim(de.LastName) AS 'Patient Full Name',
rtrim(eu.Value) AS 'AlternatePtID',
en.DischargeDate AS 'Discharge Date',
rtrim(wt.ID) AS 'WorktypeID',
rtrim(ep.Value) AS 'PT Class',
do.EndDictTime AS 'Dictated Date-Time',
do.CreateTime AS 'Transcribed Date-Time',
do.LastModifiedTime AS 'Last Modified Time',
rtrim(p.FirstName) + ' ' + rtrim(p.LastName) AS 'Dictator Name',
do.InternalID AS 'DocumentID' FROM document do
JOIN demographic de ON de.InternalID = do.PatientInternalID
JOIN encounter en ON (en.InternalID = do.EncounterInternalID AND en.Location = 'SVH')
JOIN worktype wt ON (wt.InternalID = do.WorktypeInternalID AND wt.ID in (1,2,3,4,5,6,7,9,11,12,17))
JOIN encuserdefineddata eu ON (eu.ObjectInternalID = en.InternalID AND eu.UserDefinedFieldInternalID = 91)
JOIN encuserdefineddata ep ON (ep.ObjectInternalID = en.InternalID AND ep.UserDefinedFieldInternalID = 102)
JOIN provider p ON p.InternalID = do.DictatorInternalID
WHERE do.LastModifiedTime BETWEEN @Yesterday AND @Today AND en.DischargeDate <> ' ' AND (
CASE WHEN (wt.ID = 3 AND ep.Value IN ('Ambulatory Surgery', 'Same Day Surgery')) THEN 1
ELSE 0
END) = 0
December 10, 2008 at 3:18 pm
Try moving that USE docnet to the first line of your query, it's just good practice.
Now
[font="Courier New"]
WHERE do.LastModifiedTime BETWEEN @Yesterday AND @Today
AND en.DischargeDate <> ' '
AND (CASE WHEN (wt.ID = 3 AND ep.Value IN ('Ambulatory Surgery', 'Same Day Surgery')) THEN 1
ELSE 0
END) = 0
[/font]
The way I read this... you only want rows that DO NOT meet the conditions you are specifying in your case expression. If they are both met, then you set your case value to 1. But you then compare that value to 0. Is that your intention, or is it a typo?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 10, 2008 at 3:50 pm
Actually everything below the "use" is what you would put into the DTS task.
Put the definitions for the variables inline in the code in place of the variables.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 10, 2008 at 4:26 pm
If he moves the USE, won't the variables be okay? Looks like all he's doing is getting values for today and yesterday.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply