Hello there,
I'm developing a stored procedure for a Crystal report and starting out with some code that has already been developed. I'm fairly new to this and this is just over my head. Can you help?
I have 8 types of fields that need to be sorted, according to the values contained in each. Each type is listed on the same row, with two other fields that go along with it, but we can leave those out for now. Sometimes the values in each of the columns will be the same, in which case I want them next to each other in the display.
What I'm starting with is sort of like this:
TypeA, TypeB, TypeC, TypeD etc.
Val3 Val2 Val3 Val1
When I sort them, I want them to sort by Val1,Val2,Val3. I was thinking I could use a Case statement to identify each sort value, but since there can be multiples of the same sort value, I'm just boggled.
What I want to see at the end is this:
TypeD, TypeB, TypeA, TypeC
Val1 Val2 Val3 Val3
How would I go about doing this?
Following is the code I've got so far. I know there are only 6 insert sections, but 8 values. Two of them aren't identified yet and will be added later:
I'm developing a stored procedure for a Crystal report and starting out with some code that has already been developed. I'm fairly new to this and this is just over my head. Can you help?
I have 8 types of fields that need to be sorted, according to the values contained in each. Each type is listed on the same row, with two other fields that go along with it, but we can leave those out for now. Sometimes the values in each of the columns will be the same, in which case I want them next to each other in the display.
What I'm starting with is sort of like this:
TypeA, TypeB, TypeC, TypeD etc.
Val3 Val2 Val3 Val1
When I sort them, I want them to sort by Val1,Val2,Val3. I was thinking I could use a Case statement to identify each sort value, but since there can be multiples of the same sort value, I'm just boggled.
What I want to see at the end is this:
TypeD, TypeB, TypeA, TypeC
Val1 Val2 Val3 Val3
How would I go about doing this?
Following is the code I've got so far. I know there are only 6 insert sections, but 8 values. Two of them aren't identified yet and will be added later:
Code:
DECLARE
@SORT1 CHAR(1)
@SORT2 CHAR(1)
@SORT3 CHAR(1)
@SORT4 CHAR(1)
@SORT5 CHAR(1)
@SORT6 CHAR(1)
@SORT7 CHAR(1)
@SORT8 CHAR(1)
Select
CA.TFSClientNumber
,RTRIM(FS.NameCalcInit) AS ClientName
,CA.DateOfAssmt AS AssessmentDate
,CA.AxisI_Code1 AS Axis1_Code1
,CA.AxisI_Code2 AS Axis1_Code2
,CA.AxisI_Code3 AS Axis1_Code3
,CA.AxisI_Code4 AS Axis1_Code4
,CA.AxisI_Code5 AS Axis1_Code5
,CA.AxisI_Code6 AS Axis1_Code6
,CA.AxisI_Code7 AS Axis1_Code7
,CA.AxisI_Code8 AS Axis1_Code8
,CA.Axis1_desc1 AS Axis1_Desc1
,CA.Axis1_desc2 AS Axis1_Desc2
,CA.Axis1_desc3 AS Axis1_Desc3
,CA.Axis1_desc4 AS Axis1_Desc4
,CA.Axis1_desc5 AS Axis1_Desc5
,CA.Axis1_desc6 AS Axis1_Desc6
,CA.Axis1_desc7 AS Axis1_Desc7
,CA.Axis1_desc8 AS Axis1_Desc8
,CA.A1Type1 AS Axis1_Type1
,CA.A1Type2 AS Axis1_Type2
,CA.A1Type3 AS Axis1_Type3
,CA.A1Type4 AS Axis1_Type4
,CA.A1Type5 AS Axis1_Type5
,CA.A1Type6 AS Axis1_Type6
,CA.A1Type7 AS Axis1_Type7
,CA.A1Type8 AS Axis1_Type8
into #Axis1Temp
FROM FD__CLINICAL_ASSESSMENT CA
INNER JOIN FD__TFS_FACE_SHEET FS ON CA.TFSCLientNumber = FS.TFSClientNumber
WHERE -- @DocID is null or
CA.OP__DOCID = '33064' AND
(
CA.A1Type1 = 'Primary'
OR
CA.A1Type2 = 'Primary'
OR
CA.A1Type3 = 'Primary'
OR
CA.A1Type4 = 'Primary'
OR
CA.A1Type5= 'Primary'
OR
CA.A1Type6 = 'Primary'
OR
CA.A1Type7 = 'Primary'
OR
CA.A1Type8= 'Primary'
)
union all
Select
CA.TFSClientNumber
,RTRIM(FS.NameCalcInit) AS ClientName
,CA.DateOfAssmt AS AssessmentDate
,CA.AxisI_Code1 AS Axis1_Code1
,CA.AxisI_Code2 AS Axis1_Code2
,CA.AxisI_Code3 AS Axis1_Code3
,CA.AxisI_Code4 AS Axis1_Code4
,CA.AxisI_Code5 AS Axis1_Code5
,CA.AxisI_Code6 AS Axis1_Code6
,CA.AxisI_Code7 AS Axis1_Code7
,CA.AxisI_Code8 AS Axis1_Code8
,CA.Axis1_desc1 AS Axis1_Desc1
,CA.Axis1_desc2 AS Axis1_Desc2
,CA.Axis1_desc3 AS Axis1_Desc3
,CA.Axis1_desc4 AS Axis1_Desc4
,CA.Axis1_desc5 AS Axis1_Desc5
,CA.Axis1_desc6 AS Axis1_Desc6
,CA.Axis1_desc7 AS Axis1_Desc7
,CA.Axis1_desc8 AS Axis1_Desc8
,CA.A1Type1 AS Axis1_Type1
,CA.A1Type2 AS Axis1_Type2
,CA.A1Type3 AS Axis1_Type3
,CA.A1Type4 AS Axis1_Type4
,CA.A1Type5 AS Axis1_Type5
,CA.A1Type6 AS Axis1_Type6
,CA.A1Type7 AS Axis1_Type7
,CA.A1Type8 AS Axis1_Type8
FROM FD__CLINICAL_ASSESSMENT CA
INNER JOIN FD__TFS_FACE_SHEET FS ON CA.TFSCLientNumber = FS.TFSClientNumber
WHERE -- @DocID is null or
CA.OP__DOCID = '33064' AND
(
CA.A1Type1 = 'Secondary'
OR
CA.A1Type2 = 'Secondary'
OR
CA.A1Type3 = 'Secondary'
OR
CA.A1Type4 = 'Secondary'
OR
CA.A1Type5= 'Secondary'
OR
CA.A1Type6 = 'Secondary'
OR
CA.A1Type7 = 'Secondary'
OR
CA.A1Type8= 'Secondary'
)
union all
Select
CA.TFSClientNumber
,RTRIM(FS.NameCalcInit) AS ClientName
,CA.DateOfAssmt AS AssessmentDate
,CA.AxisI_Code1 AS Axis1_Code1
,CA.AxisI_Code2 AS Axis1_Code2
,CA.AxisI_Code3 AS Axis1_Code3
,CA.AxisI_Code4 AS Axis1_Code4
,CA.AxisI_Code5 AS Axis1_Code5
,CA.AxisI_Code6 AS Axis1_Code6
,CA.AxisI_Code7 AS Axis1_Code7
,CA.AxisI_Code8 AS Axis1_Code8
,CA.Axis1_desc1 AS Axis1_Desc1
,CA.Axis1_desc2 AS Axis1_Desc2
,CA.Axis1_desc3 AS Axis1_Desc3
,CA.Axis1_desc4 AS Axis1_Desc4
,CA.Axis1_desc5 AS Axis1_Desc5
,CA.Axis1_desc6 AS Axis1_Desc6
,CA.Axis1_desc7 AS Axis1_Desc7
,CA.Axis1_desc8 AS Axis1_Desc8
,CA.A1Type1 AS Axis1_Type1
,CA.A1Type2 AS Axis1_Type2
,CA.A1Type3 AS Axis1_Type3
,CA.A1Type4 AS Axis1_Type4
,CA.A1Type5 AS Axis1_Type5
,CA.A1Type6 AS Axis1_Type6
,CA.A1Type7 AS Axis1_Type7
,CA.A1Type8 AS Axis1_Type8
FROM FD__CLINICAL_ASSESSMENT CA
INNER JOIN FD__TFS_FACE_SHEET FS ON CA.TFSCLientNumber = FS.TFSClientNumber
WHERE -- @DocID is null or
CA.OP__DOCID = '33064' AND
(
CA.A1Type1 = 'Provisional'
OR
CA.A1Type2 = 'Provisional'
OR
CA.A1Type3 = 'Provisional'
OR
CA.A1Type4 = 'Provisional'
OR
CA.A1Type5= 'Provisional'
OR
CA.A1Type6 = 'Provisional'
OR
CA.A1Type7 = 'Provisional'
OR
CA.A1Type8= 'Provisional'
)
Union All
Select
CA.TFSClientNumber
,RTRIM(FS.NameCalcInit) AS ClientName
,CA.DateOfAssmt AS AssessmentDate
,CA.AxisI_Code1 AS Axis1_Code1
,CA.AxisI_Code2 AS Axis1_Code2
,CA.AxisI_Code3 AS Axis1_Code3
,CA.AxisI_Code4 AS Axis1_Code4
,CA.AxisI_Code5 AS Axis1_Code5
,CA.AxisI_Code6 AS Axis1_Code6
,CA.AxisI_Code7 AS Axis1_Code7
,CA.AxisI_Code8 AS Axis1_Code8
,CA.Axis1_desc1 AS Axis1_Desc1
,CA.Axis1_desc2 AS Axis1_Desc2
,CA.Axis1_desc3 AS Axis1_Desc3
,CA.Axis1_desc4 AS Axis1_Desc4
,CA.Axis1_desc5 AS Axis1_Desc5
,CA.Axis1_desc6 AS Axis1_Desc6
,CA.Axis1_desc7 AS Axis1_Desc7
,CA.Axis1_desc8 AS Axis1_Desc8
,CA.A1Type1 AS Axis1_Type1
,CA.A1Type2 AS Axis1_Type2
,CA.A1Type3 AS Axis1_Type3
,CA.A1Type4 AS Axis1_Type4
,CA.A1Type5 AS Axis1_Type5
,CA.A1Type6 AS Axis1_Type6
,CA.A1Type7 AS Axis1_Type7
,CA.A1Type8 AS Axis1_Type8
FROM FD__CLINICAL_ASSESSMENT CA
INNER JOIN FD__TFS_FACE_SHEET FS ON CA.TFSCLientNumber = FS.TFSClientNumber
WHERE -- @DocID is null or
CA.OP__DOCID = '33064' AND
(
CA.A1Type1 = 'By History'
OR
CA.A1Type2 = 'By History'
OR
CA.A1Type3 = 'By History'
OR
CA.A1Type4 = 'By History'
OR
CA.A1Type5= 'By History'
OR
CA.A1Type6 = 'By History'
OR
CA.A1Type7 = 'By History'
OR
CA.A1Type8= 'By History'
)
Union All
Select
CA.TFSClientNumber
,RTRIM(FS.NameCalcInit) AS ClientName
,CA.DateOfAssmt AS AssessmentDate
,CA.AxisI_Code1 AS Axis1_Code1
,CA.AxisI_Code2 AS Axis1_Code2
,CA.AxisI_Code3 AS Axis1_Code3
,CA.AxisI_Code4 AS Axis1_Code4
,CA.AxisI_Code5 AS Axis1_Code5
,CA.AxisI_Code6 AS Axis1_Code6
,CA.AxisI_Code7 AS Axis1_Code7
,CA.AxisI_Code8 AS Axis1_Code8
,CA.Axis1_desc1 AS Axis1_Desc1
,CA.Axis1_desc2 AS Axis1_Desc2
,CA.Axis1_desc3 AS Axis1_Desc3
,CA.Axis1_desc4 AS Axis1_Desc4
,CA.Axis1_desc5 AS Axis1_Desc5
,CA.Axis1_desc6 AS Axis1_Desc6
,CA.Axis1_desc7 AS Axis1_Desc7
,CA.Axis1_desc8 AS Axis1_Desc8
,CA.A1Type1 AS Axis1_Type1
,CA.A1Type2 AS Axis1_Type2
,CA.A1Type3 AS Axis1_Type3
,CA.A1Type4 AS Axis1_Type4
,CA.A1Type5 AS Axis1_Type5
,CA.A1Type6 AS Axis1_Type6
,CA.A1Type7 AS Axis1_Type7
,CA.A1Type8 AS Axis1_Type8
FROM FD__CLINICAL_ASSESSMENT CA
INNER JOIN FD__TFS_FACE_SHEET FS ON CA.TFSCLientNumber = FS.TFSClientNumber
WHERE -- @DocID is null or
CA.OP__DOCID = '33064' AND
(
CA.A1Type1 = 'Rule Out'
OR
CA.A1Type2 = 'Rule Out'
OR
CA.A1Type3 = 'Rule Out'
OR
CA.A1Type4 = 'Rule Out'
OR
CA.A1Type5= 'Rule Out'
OR
CA.A1Type6 = 'Rule Out'
OR
CA.A1Type7 = 'Rule Out'
OR
CA.A1Type8= 'Rule Out'
)
Union All
Select
CA.TFSClientNumber
,RTRIM(FS.NameCalcInit) AS ClientName
,CA.DateOfAssmt AS AssessmentDate
,CA.AxisI_Code1 AS Axis1_Code1
,CA.AxisI_Code2 AS Axis1_Code2
,CA.AxisI_Code3 AS Axis1_Code3
,CA.AxisI_Code4 AS Axis1_Code4
,CA.AxisI_Code5 AS Axis1_Code5
,CA.AxisI_Code6 AS Axis1_Code6
,CA.AxisI_Code7 AS Axis1_Code7
,CA.AxisI_Code8 AS Axis1_Code8
,CA.Axis1_desc1 AS Axis1_Desc1
,CA.Axis1_desc2 AS Axis1_Desc2
,CA.Axis1_desc3 AS Axis1_Desc3
,CA.Axis1_desc4 AS Axis1_Desc4
,CA.Axis1_desc5 AS Axis1_Desc5
,CA.Axis1_desc6 AS Axis1_Desc6
,CA.Axis1_desc7 AS Axis1_Desc7
,CA.Axis1_desc8 AS Axis1_Desc8
,CA.A1Type1 AS Axis1_Type1
,CA.A1Type2 AS Axis1_Type2
,CA.A1Type3 AS Axis1_Type3
,CA.A1Type4 AS Axis1_Type4
,CA.A1Type5 AS Axis1_Type5
,CA.A1Type6 AS Axis1_Type6
,CA.A1Type7 AS Axis1_Type7
,CA.A1Type8 AS Axis1_Type8
FROM FD__CLINICAL_ASSESSMENT CA
INNER JOIN FD__TFS_FACE_SHEET FS ON CA.TFSCLientNumber = FS.TFSClientNumber
WHERE -- @DocID is null or
CA.OP__DOCID = '33064' AND
(
CA.A1Type1 = 'In Remission'
OR
CA.A1Type2 = 'In Remission'
OR
CA.A1Type3 = 'In Remission'
OR
CA.A1Type4 = 'In Remission'
OR
CA.A1Type5= 'In Remission'
OR
CA.A1Type6 = 'In Remission'
OR
CA.A1Type7 = 'In Remission'
OR
CA.A1Type8= 'In Remission'
)
select * from #Axis1Temp
drop table #axis1temp