Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 totally confused.

Author  Topic 

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-10-02 : 14:16:33
I have a temp table (#aggressorid) which has a resultset like:


CFSID AggressorIdentifier ctsid CTSDESCRIPTION SDDESCRIPTION OrderCol
45022 317 01 Physical Evidence Physical Evidence 1
45022 318 02 Testimonial Evidence Testimonial Evidence 2
45684 317 01 Physical Evidence Physical Evidence 1
45684 318 02 Testimonial Evidence Testimonial Evidence 2
45812 318 02 Testimonial Evidence Testimonial Evidence 1
45967 317 01 Physical Evidence Physical Evidence 1


In any case where the ordercol value is >= 2 that data needs to be put into a new table (Offense.dbo.off_nar) so that it would be like this:


Insert into offense.dbo.off_nar (offenseno, Narrative, Uniquekey)
Values ('DCSO99OFF045022','Additional AggressorIDs in this case are: Physical Evidence',SCADMIN.dbo.fnsc_GetUniqueID(newid()))


The first value is the CFSID is preceded by 'DCSO99OFF' where the len of the CFSID is equal to 6. If the len of the CFSID is = 5 (the only other option, the OFFENSENO will be preceded by 'DCSO99OFF0'. The Offenseno must always be 15 chars long.

HOWEVER....I have another temp table, #AbuseType, which has a resultset like:


CFSID AbuseTypedesc ctsid CTSDESCRIPTION SDDESCRIPTION OrderCol
45022 1929 06 Superficial injuries Superficial injuries 1
45022 1931 08 Threats Threats 2
45684 1934 11 Other Other 1
45812 1931 08 Threats Threats 1
45812 1932 09 Abusive Language Abusive Language 2
45967 1929 06 Superficial injuries Superficial injuries 1
45967 1930 7 Property Damage/Theft Property Damage/Theft 2
45967 1934 11 Other Other 3
46003 1929 06 Superficial injuries Superficial injuries 1
46003 1930 7 Property Damage/Theft Property Damage/Theft 2
46003 1931 08 Threats Threats 3
46144 1930 7 Property Damage/Theft Property Damage/Theft 1
46144 1934 11 Other Other 2


The same rule applies, but in this case, where the ordercol value is greater than 3, it would insert into offense.dbo.off_nar the same way the first example did.

HOWEVER, the way "they" want it done is that, the results in #aggressorID which have an ordercol value >=2 need to have their CTSDESCRIPTION combined with the rows in #abusetype which which have their ordercol value greater than 3 based on a common CFSID, which is converted to the offenseno for the off_nar table (the DCSO99OFF prefix).

So if, between the two tables, there was CFSID #434567, and in the #aggressorid table there was an ordercol value greater than 2 and the CTSDescription column said "Physical Evidence" and in the #abusetype table, that same CFSID was there with an ordercol value greater than 3, and the CTSDESCRIPTION value was 'Property Damage/Theft', the actual insert into off_nar, for the offenseno would be 'DCSO99OFF434567', and, for the NARRATIVE column, for that Offenseno/CFSID would be "Additional aggressorid values in this case are Physical Evidence, Additional AbuseTypes in this case are Property Damage/Theft".

This is so confusing it's making my head spin. Any advice?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-02 : 14:53:24
something like

Insert into offense.dbo.off_nar (offenseno, Narrative, Uniquekey)
select 'DCSO99OFF' + RIGHT('000000' + CAST(CFSID AS varchar(6)),6),
'Additional aggressorid values in this case are: ' + STUFF((SELECT ',' + CTSDESCRIPTION
FROM #Aggressorid
WHERE OrderCol>=2
AND CFSID = a.CFSID
FOR XML PATH('')),1,1,'') + ', Additional AbuseTypes in this case are: ' +
STUFF((SELECT ',' + CTSDESCRIPTION
FROm #AbuseType
WHERE OrderCol>3
AND CFSID = a.CFSID
FOR XML PATH('')),1,1,''),
SCADMIN.dbo.fnsc_GetUniqueID(newid()))
FROM (SELECT DISTINCT CFSID FROM #AggressorID WHERE OrderCol >=2 )a




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-10-02 : 15:11:02
close close close. we're REAL close. That works, but what it's doing is inserting TWO rows into off_nar. They have the same Offenseno, but one has all null values in the other columns (except for uniquekey) and the other has the correct values in all places. it's also inserting ALOT of other rows with Null values in the narrative column. Where are the rows with null values coming from??
Go to Top of Page

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-10-02 : 15:14:39
it needs something wrapped in an isnull, doesn't it?
Go to Top of Page

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-10-02 : 15:31:27
I've wrapped CTSDESCRIPTION in an Isnull and it still produces a large number of rows with null Narrative values.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-02 : 15:32:26
quote:
Originally posted by WJHamel

it needs something wrapped in an isnull, doesn't it?


you can put an ISNULL around STUFF and see

ie like



Insert into offense.dbo.off_nar (offenseno, Narrative, Uniquekey)
select 'DCSO99OFF' + RIGHT('000000' + CAST(CFSID AS varchar(6)),6),
'Additional aggressorid values in this case are: ' + STUFF((SELECT ',' + CTSDESCRIPTION
FROM #Aggressorid
WHERE OrderCol>=2
AND CFSID = a.CFSID
FOR XML PATH('')),1,1,'') +
ISNULL(', Additional AbuseTypes in this case are: ' +
STUFF((SELECT ',' + CTSDESCRIPTION
FROm #AbuseType
WHERE OrderCol>3
AND CFSID = a.CFSID
FOR XML PATH('')),1,1,''),''),
SCADMIN.dbo.fnsc_GetUniqueID(newid()))
FROM (SELECT DISTINCT CFSID FROM #AggressorID WHERE OrderCol >=2 )a


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-10-02 : 15:38:33
Bingo. Good God man, you are my hero. Thank you.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-02 : 15:41:01
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-10-02 : 16:17:47
Last question on this. I swear. So in adding additional columns to the insert, (Date, Time, Type), i'm doing so like this:



Insert into offense.dbo.off_nar (offenseno, Narrative, Uniquekey, [Date], [Time], [Type])
select 'DCSO99OFF' + RIGHT('000000' + CAST(CFSID AS varchar(6)),6),
'Additional aggressorid values in this case are: ' + STUFF((SELECT ',' + Isnull(CTSDESCRIPTION,'')
FROM #Aggressorid
WHERE OrderCol>=2
AND CFSID = a.CFSID
FOR XML PATH('')),1,1,'') +
ISNULL(', Additional AbuseTypes in this case are: ' +
STUFF((SELECT ',' + CTSDESCRIPTION
FROm #AbuseType
WHERE OrderCol>3
AND CFSID = a.CFSID
FOR XML PATH('')),1,1,''),''),
SCADMIN.dbo.fnsc_GetUniqueID(newid()),
GETDATE(),
convert(varchar(5), [DATE], 114),
'S'
FROM (SELECT DISTINCT CFSID FROM #AggressorID WHERE OrderCol >=2 )a



but it's failing. The error says Invalid Column Name 'Date'. Date column DOES exist in off_nar. I also need the date to be JUST the current date (no time attached) and the Time to be the current TIME, with NO date attached. Is my insert addressing that correctly?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-02 : 16:26:32
[code]
Insert into offense.dbo.off_nar (offenseno, Narrative, Uniquekey, [Date], [Time], [Type])
select 'DCSO99OFF' + RIGHT('000000' + CAST(CFSID AS varchar(6)),6),
'Additional aggressorid values in this case are: ' + STUFF((SELECT ',' + Isnull(CTSDESCRIPTION,'')
FROM #Aggressorid
WHERE OrderCol>=2
AND CFSID = a.CFSID
FOR XML PATH('')),1,1,'') +
ISNULL(', Additional AbuseTypes in this case are: ' +
STUFF((SELECT ',' + CTSDESCRIPTION
FROm #AbuseType
WHERE OrderCol>3
AND CFSID = a.CFSID
FOR XML PATH('')),1,1,''),''),
SCADMIN.dbo.fnsc_GetUniqueID(newid()),
DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0),
convert(varchar(8), DATEADD(dd,-1*DATEDIFF(dd,0,GETDATE()),0), 108),
'S'
FROM (SELECT DISTINCT CFSID FROM #AggressorID WHERE OrderCol >=2 )a
[/code]

see below for explanation

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-10-03 : 09:11:57
Sorry i didn't post earlier. I found the same solution to split the date and time. Worked perfectly. Thanks again.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-03 : 10:21:27
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -