SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 totally confused.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WJHamel
Aged Yak Warrior

USA
646 Posts

Posted - 10/02/2012 :  14:16:33  Show Profile  Reply with Quote
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?

Edited by - WJHamel on 10/02/2012 14:28:40

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/02/2012 :  14:53:24  Show Profile  Reply with Quote
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/


Edited by - visakh16 on 10/02/2012 14:54:10
Go to Top of Page

WJHamel
Aged Yak Warrior

USA
646 Posts

Posted - 10/02/2012 :  15:11:02  Show Profile  Reply with Quote
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

USA
646 Posts

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

WJHamel
Aged Yak Warrior

USA
646 Posts

Posted - 10/02/2012 :  15:31:27  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 10/02/2012 :  15:32:26  Show Profile  Reply with Quote
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

USA
646 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/02/2012 :  15:41:01  Show Profile  Reply with Quote
welcome

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

Go to Top of Page

WJHamel
Aged Yak Warrior

USA
646 Posts

Posted - 10/02/2012 :  16:17:47  Show Profile  Reply with Quote
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?

Edited by - WJHamel on 10/02/2012 16:19:53
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/02/2012 :  16:26:32  Show Profile  Reply with Quote

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 


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

USA
646 Posts

Posted - 10/03/2012 :  09:11:57  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 10/03/2012 :  10:21:27  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000