July 9, 2007 at 12:14 pm
I have the following Code:
CREATE
TABLE #log (RecordType varchar(100), RecordsUpdated int, FirstEncDt varchar(10), LastEncDt Varchar(10))
Go
CREATE
TABLE #SPs (SampleUnit_ID int, SamplePop_ID int, Enc_ID int, DaysFromFirst int,
ReportDate
DateTime, BigTableName varchar(100), SentMail_id int, FirstEncDt varchar(10),
LastEncDt Varchar
(10))
--Get all enc_id with SampleUnit.bitHCAHPS = 1
INSERT
#SPs (SampleUnit_ID, SamplePop_ID, Enc_ID, ReportDate, FirstEncDt,LastEncDt)
SELECT su.SampleUnit_ID, sp.SamplePop_ID, ss.Enc_ID, ss.ReportDate,MIN(ss.SampleEncounterDate),
Max(ss.SampleEncounterDate)
FROM SampleUnit su, SelectedSample ss, SamplePop sp
WHERE su.SampleUnit_id = ss.SampleUnit_id and
ss
.Sampleset_id = sp.Sampleset_id and
ss
.Pop_id = sp.Pop_id and
su
.bitHCAHPS = 1 and
ss
.Study_id = @Study_id and
ss
.Enc_id in (SELECT Enc_id FROM #Work)
How Can I take FirstEncDt,LastEncDt from the #SPs table and pass them into another temp table:
SELECT
TOP 1 SampleUnit_ID FROM #Sps
IF
@@ROWCOUNT = 0
BEGIN
-- Update #Log
INSERT INTO #Log (RecordType, RecordsUpdated,FirstEncDt,LastEncDt)
Select 'No matching records were sampled for an HCAHPS unit.', LTRIM(STR(@@ROWCOUNT)),NULL,NULL
END
The NULL need to be the contents of FirstEncDt,LastEncDt
Thank you
Art
July 9, 2007 at 12:19 pm
If I read your code correctly, you only insert into the #Log table if the #Sps table is empty. If so, you don't have any values in the FirstEncDt or LastEncDt columns. Am I missing something?
Brian
Tell Steve hi from me.
July 9, 2007 at 12:24 pm
Sorry, it should have read:
IF @@ROWCOUNT > 0
BEGIN
-- Update #Log
INSERT INTO #Log (RecordType, RecordsUpdated,FirstEncDt,LastEncDt)
Select 'No matching records were sampled for an HCAHPS unit.', LTRIM(STR(@@ROWCOUNT)),NULL,NULL
END
The NULL need to be the contents of FirstEncDt,LastEncDt
July 9, 2007 at 12:30 pm
You are doing the query for multiple encounters, so won't you have multiple records in the #Sps table? Do you just want the first record? Min/Max?
You will need to set the rowcount value to a variable if you then want to insert it into a table. Your IF statement will set it to 1, so all of your inserts will be 1.
SELECT @Cnt=@@ROWCOUNT
SELECT TOP 1 'No matching records...',LTRIM(STR(@Cnt)),FirstEncDt,LastEncDt
Brian
July 9, 2007 at 12:40 pm
Yes, there will be multiple records in #SPs and from that I need the min and max encounter date which will be returned to the #log table.
This code just checks to see if there is a record in #SPs and inserts a record into the #Log table:
SELECT
TOP 1 SampleUnit_ID FROM #Sps
IF
@@ROWCOUNT = 0
BEGIN
-- Update #Log
INSERT INTO #Log (RecordType, RecordsUpdated,FirstEncDt,LastEncDt)
Select 'No matching records were sampled for an HCAHPS unit.', LTRIM(STR(@@ROWCOUNT)),NULL,NULL
END
Towards the end of the Proc I need to insert the min/max encounter dates into #Log.
July 10, 2007 at 2:20 am
First, I would recommend changing this:
SELECT TOP 1 SampleUnit_ID FROM #Sps
IF @@ROWCOUNT...
to:
IF EXISTS (SELECT * FROM #Sps) /or NOT EXISTS - depending on what you are checking/
And the insert - if I understand correctly, of which I'm not sure - could be something like that:
INSERT INTO #Log (RecordType, RecordsUpdated,FirstEncDt,LastEncDt)
SELECT 'Some text', COUNT(*), MAX(FirstEncDt), MAX(LastEncDt)
FROM #Sps
I don't know what you mean by min and max, if you already have filled the columns FirstEncDt,LastEncDt with MIN and MAX values from the SelectedSample table, so you may need different values there, but this should give you an idea how to do it. If you need more advice, please decribe in more detail what you need and what is the problem.
BTW, it is not a good idea to store dates as VARCHAR. Better make the two columns DATETIME, then you'll have no problems handling them correctly (e.g. for ordering, min, max, finding difference and so on).
July 10, 2007 at 2:23 am
Oh and one more thing... maybe you don't need to branch (IF something...) at all :
INSERT INTO #Log (RecordType, RecordsUpdated,FirstEncDt,LastEncDt)
SELECT CASE WHEN COUNT(*)=0 THEN 'No records updated' ELSE 'Some other text' END,
COUNT(*), MAX(FirstEncDt), MAX(LastEncDt)
FROM #Sps
If there are no rows in table #SPs, it will insert this:
No records updated 0 NULL NULL
which I think is what you wanted.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply