Solved

Advanced T-SQL Query Skills Needed

Posted on 2004-08-02
16
278 Views
Last Modified: 2006-11-17
Hey DB Experts,

This problem is giving me a lot of trouble.  My T-SQL skills are not advanced.
So, sorry I may need some hand-holding.

I have these tables:
Table: Samples
SAMP_PIDS_SEQ
SAMP_LOC_SEQ
SAMP_MATRIX_SEQ
SAMP_TYPE_SEQ
SAMP_SAMPLER_SEQ
SAMP_BANKER_SEQ

Table: Property_ids
PIDS_SEQ
PIDS_ADR_SEQ

Table: RT_SAMLOC
SAMLOC_SEQ

Table: RT_MatrixType
MATRIXTYPE_SEQ

Table: RT_SampType
SAMPTYPE_SEQ

Table: Results
RES_ANALYTE_SEQ
RES_UNITS_SEQ
RES_IDL_UNITS_SEQ

Table: RT_ANALYTE
ANALYTE_SEQ

Table: RT_Units
UNITS_SEQ
UNITS_SEQ


Our db developer created a couple of functions that are used in several stored procedures.

ALTER       function fx_Sum_MasterLogs
() RETURNS table
as
RETURN
(select TOP 100 PERCENT SAMPLES.*, PIDS_SEQ, StreetAddress, MatrixType_Desc,
      SAMLOC_ID+': '+SAMLOC_DESC+' '+ convert(varchar(5),SAMLOC_UPPER_DEPTH)+'-'+convert(varchar(5),SAMLOC_LOWER_DEPTH)+' '+SAMLOC_DEPTH_UNITS AS SAMPLELOCATION,
      samptype_name,
      s1.staff_initials+': '+s1.staff_lname+' '+ s1.staff_fname as sampler,
      s2.staff_initials+': '+s2.staff_lname+' '+s2.staff_fname as banker,
      samloc_desc
      from       SAMPLES, PROPERTY_IDS, fx_sum_addresses(), RT_MATRIXTYPE,
            RT_SAMLOC, RT_SAMPTYPE, RT_STAFF S1, RT_STAFF S2
      where       SAMP_PIDS_SEQ = PIDS_SEQ AND
            PIDS_ADR_SEQ = ADR_SEQ AND
            SAMP_LOC_SEQ = SAMLOC_SEQ AND
            SAMP_MATRIX_SEQ = MATRIXTYPE_SEQ AND
            SAMPTYPE_SEQ = SAMP_TYPE_SEQ AND
            SAMP_SAMPLER_SEQ = S1.STAFF_SEQ AND
            SAMP_BANKER_SEQ = S2.STAFF_SEQ
            Order by SAMP_SEQ
)

And

ALTER     function fx_Sum_Results
() RETURNS table
as
RETURN
(select TOP 100 PERCENT RESULTS.*, ANALYTE_desc,
U1.UNITS_ABBR AS RES_UNITS, U2.UNITS_ABBR AS IDL_UNITS
from RESULTS, RT_ANALYTE, RT_UNITS U1, RT_UNITS U2
WHERE
RES_ANALYTE_SEQ = ANALYTE_SEQ AND
U1.UNITS_SEQ = RES_UNITS_SEQ AND
U2.UNITS_SEQ = RES_IDL_UNITS_SEQ
Order by RES_SEQ
)

[These call some additional tables that I didn't list because they have nothing to do with the query I'm looking for.]

I have used the two functions above in this way:

declare @PIDSSEQ int
select @PIDSSEQ = 50263

select left(samloc_desc,(patindex('%-%',samloc_desc)-1)) as [Sample Location],
SUBSTRING(samplelocation,4,1) as Depth,
analyte_desc as Analyte, res_units as Units, res_result,res_comment

FROM fx_Sum_MasterLogs(), fx_Sum_Results()

where
    SAMP_PIDS_SEQ = @PIDSSEQ
AND RES_SAMP_SEQ = SAMP_SEQ

order by samloc_desc

That query gives the following RESULTS

DriveWay     A     ARSENIC     MG/KG     24.5    
DriveWay     A     LEAD     MG/KG     1620    
DriveWay     B     ARSENIC     MG/KG     41.5    
DriveWay     B     LEAD     MG/KG     5010    


But WHAT I NEED is this

LOCATION       RESULTS DEPTH A     RESULTS DEPTH B    
DriveWay     ARSENIC     MG/KG     24.5     ARSENIC     MG/KG     41.5    
DriveWay     LEAD     MG/KG     1620     LEAD     MG/KG     5010    


TIA,
Malika
0
Comment
Question by:altarEgo
  • 10
  • 3
16 Comments
 
LVL 1

Author Comment

by:altarEgo
ID: 11696118
I need a working example.

I've tried a couple of variations on the following:

declare @PIDSSEQ int
set @PIDSSEQ = 50263

select A.[Sample Location], A.Depth, A.Analyte, A.Units, A.res_result, A.res_comment
FROM (
select left(samloc_desc,(patindex('%-%',samloc_desc)-1)) as [Sample Location],
SUBSTRING(samplelocation,4,1) as Depth,
analyte_desc as Analyte, res_units as Units, res_result,res_comment
FROM fx_Sum_MasterLogs(), fx_Sum_Results()
where
    SAMP_PIDS_SEQ = @PIDSSEQ
AND RES_SAMP_SEQ = SAMP_SEQ
AND left(samloc_desc,(patindex('%-%',samloc_desc)-1))  = "A" ) AS A

RIGHT OUTER JOIN(
select left(samloc_desc,(patindex('%-%',samloc_desc)-1)) as [Sample Location],
SUBSTRING(samplelocation,4,1) as Depth,
analyte_desc as Analyte, res_units as Units, res_result,res_comment
FROM fx_Sum_MasterLogs(), fx_Sum_Results()
where
    SAMP_PIDS_SEQ = @PIDSSEQ
AND RES_SAMP_SEQ = SAMP_SEQ
AND left(samloc_desc,(patindex('%-%',samloc_desc)-1))  = "B" ) AS B

ON A.[Sample Location] = B.[Sample Location]
AND A.Analyte = B.Analyte

But I get errors on the "AS A", "AS B" assignment.  
0
 
LVL 1

Author Comment

by:altarEgo
ID: 11696446
Also, I failed to mention that there are depths A through G.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 11696595
use a temporary table and try this...

select left(samloc_desc,(patindex('%-%',samloc_desc)-1)) as Location,
SUBSTRING(samplelocation,4,1) as Depth,
analyte_desc as Analyte, res_units as Units, res_result,res_comment
into @temp
FROM fx_Sum_MasterLogs(), fx_Sum_Results()
where SAMP_PIDS_SEQ = @PIDSSEQ
  AND RES_SAMP_SEQ = SAMP_SEQ


select location
  Max(Case Depth when 'A'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result A]
  Max(Case Depth when 'B'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result A]
  Max(Case Depth when 'C'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result A]
  Max(Case Depth when 'D'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result A]
  Max(Case Depth when 'E'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result A]
  Max(Case Depth when 'F'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result A]
  Max(Case Depth when 'G'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result A]
  From @temp
  Group by [Location]
  order by [Location]
0
 
LVL 1

Author Comment

by:altarEgo
ID: 11696763
Hey, I like the idea.  The query gives these errors

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '@temp'.
Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'Case'.


0
 
LVL 1

Author Comment

by:altarEgo
ID: 11698125
the error on '@temp' was just that it should be '#temp'.
I still don't know see where the syntax error is with the case statement



declare @PIDSSEQ int
set @PIDSSEQ = 50263

select left(samloc_desc,(patindex('%-%',samloc_desc)-1)) as Location,
SUBSTRING(samplelocation,4,1) as Depth,
analyte_desc as Analyte, res_units as Units, res_result,res_comment
into #temp
FROM fx_Sum_MasterLogs(), fx_Sum_Results()
where SAMP_PIDS_SEQ = @PIDSSEQ
  AND RES_SAMP_SEQ = SAMP_SEQ

select location
  Max(Case Depth when 'A'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result A]
  Max(Case Depth when 'B'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result B]
  Max(Case Depth when 'C'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result C]
  Max(Case Depth when 'D'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result D]
  Max(Case Depth when 'E'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result E]
  Max(Case Depth when 'F'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result F]
  Max(Case Depth when 'G'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result G]
  From #temp
  Group by [Location]
  order by [Location]
0
 
LVL 1

Author Comment

by:altarEgo
ID: 11698488
sorry, I should have noticed this earlier.

The Header is something like

                  Depth A                         Depth B                                                Depth G
Location Analyte Units Results     Analyte Units Results       and so on to      Analyte Units Results
 
I'm not worried about it at all.  My main goal is get the results for ARSENIC at Depth A on the same line as the results for ARSENIC at Depth B in the output.

LOCATION       RESULTS DEPTH A                         RESULTS DEPTH B    -- just the column header
DriveWay        ARSENIC     MG/KG     24.5              ARSENIC     MG/KG     41.5    
DriveWay        LEAD          MG/KG     1620              LEAD          MG/KG     5010    
Dripline           ARSENIC     MG/KG     .009              ARSENIC     MG/KG    .0001

units, analyte and results would be separate columns.  I hope that makes sense.  Sorry if it doesn't.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 11706979
sorry the "case" error is due to missing commas

select location
,  Max(Case Depth when 'A'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result A]
,  Max(Case Depth when 'B'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result B]
,  Max(Case Depth when 'C'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result C]
 , Max(Case Depth when 'D'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result D]
  ,Max(Case Depth when 'E'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result E]
  ,Max(Case Depth when 'F'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result F]
 , Max(Case Depth when 'G'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' + res_comment
                 else ''
                 end) as [Result G]
  From #temp
  Group by [Location]
  order by [Location]


and I'm not aware of any method available with Straight SQL to get the Column name to format to 2 kines unfortunately...
thats normally considered to be a client side responsibilty...

hth  
0
 
LVL 1

Author Comment

by:altarEgo
ID: 11707294
When i run it it drops out the arsenic analyte and all i get back is lead.  
here is the data for that pids_seq

Sample Location      Depth      Analyte      Units      res_result      res_comment
DriveWay      A      ARSENIC      MG/KG      24.5      
DriveWay      A      LEAD      MG/KG      1620      
DriveWay      B      ARSENIC      MG/KG      41.5      
DriveWay      B      LEAD      MG/KG      5010      
DriveWay      C      ARSENIC      MG/KG      49.4      
DriveWay      C      LEAD      MG/KG      5600      
DriveWay      D      ARSENIC      MG/KG      56.0      
DriveWay      D      LEAD      MG/KG      5630      
DriveWay      D      ARSENIC      MG/KG      54.1      
DriveWay      D      LEAD      MG/KG      5390      
DriveWay      D      ARSENIC      MG/KG      59.7      
DriveWay      D      LEAD      MG/KG      5470      


0
 
LVL 1

Author Comment

by:altarEgo
ID: 11707307
And here is what the formatted query returns

DriveWay
LEAD MG/KG 1620       LEAD MG/KG 5010       LEAD MG/KG 5600       LEAD MG/KG 5630       


Getting really close :o)
0
 
LVL 1

Author Comment

by:altarEgo
ID: 11707375
It looks like some of the lead values aren't getting picked up either.
0
 
LVL 1

Author Comment

by:altarEgo
ID: 11710365
this is where i am now....my client  wants this today so time
is becoming a factor...I'm quite willing to increase the points given
the addition of time as a factor.

declare @PIDSSEQ int
set @PIDSSEQ = 50263

select left(samloc_desc,(patindex('%-%',samloc_desc)-1)) as Location,
SUBSTRING(samplelocation,4,1) as Depth,
analyte_desc as Analyte, res_units as Units, res_result,res_comment
into #temp_rtc
FROM fx_Sum_MasterLogs(), fx_Sum_Results()
where SAMP_PIDS_SEQ = @PIDSSEQ
  AND RES_SAMP_SEQ = SAMP_SEQ

select location,UNITS,ANALYTE,DEPTH,
  (Case Depth when 'A' Then
      (case ANALYTE when 'lead' then cast(res_result as float) end)
      end),
  (Case Depth when 'A' Then
      (case ANALYTE when 'arsenic' then cast(res_result as float)  end)
      end),
  (Case Depth when 'A' Then
      (case ANALYTE when 'zinc' then cast(res_result as float)  end)
      end),
  (Case Depth when 'A' Then
      (case ANALYTE when 'cadmium' then cast(res_result as float)  end)
      end),
  (Case Depth when 'A' Then
      (case ANALYTE when 'manganese' then cast(res_result as float)  end)
      end),
  (Case Depth when 'A' Then
      (case ANALYTE when 'iron' then cast(res_result as float)  end)
      end),
  (Case Depth when 'B' Then
      (case ANALYTE when 'lead' then cast(res_result as float)  end)
      end),
  (Case Depth when 'B' Then
      (case ANALYTE when 'arsenic' then cast(res_result as float)  end)
      end),
  (Case Depth when 'B' Then
      (case ANALYTE when 'zinc' then cast(res_result as float)  end)
      end),
  (Case Depth when 'B' Then
      (case ANALYTE when 'cadmium' then cast(res_result as float)  end)
      end),
  (Case Depth when 'B' Then
      (case ANALYTE when 'manganese' then cast(res_result as float)  end)
      end),
  (Case Depth when 'B' Then
      (case ANALYTE when 'iron' then cast(res_result as float)  end)
      end),
  (Case Depth when 'C' Then
      (case ANALYTE when 'lead' then cast(res_result as float)  end)
      end),
  (Case Depth when 'C' Then
      (case ANALYTE when 'arsenic' then cast(res_result as float)  end)
      end),
  (Case Depth when 'C' Then
      (case ANALYTE when 'zinc' then cast(res_result as float)  end)
      end),
  (Case Depth when 'C' Then
      (case ANALYTE when 'cadmium' then cast(res_result as float)  end)
      end),
  (Case Depth when 'C' Then
      (case ANALYTE when 'manganese' then cast(res_result as float)  end)
      end),
  (Case Depth when 'C' Then
      (case ANALYTE when 'iron' then cast(res_result as float)  end)
      end),
  (Case Depth when 'D' Then
      (case ANALYTE when 'lead' then cast(res_result as float)  end)
      end),
  (Case Depth when 'D' Then
      (case ANALYTE when 'arsenic' then cast(res_result as float)  end)
      end),
  (Case Depth when 'D' Then
      (case ANALYTE when 'zinc' then cast(res_result as float)  end)
      end),
  (Case Depth when 'D' Then
      (case ANALYTE when 'cadmium' then cast(res_result as float)  end)
      end),
  (Case Depth when 'D' Then
      (case ANALYTE when 'manganese' then cast(res_result as float)  end)
      end),
  (Case Depth when 'D' Then
      (case ANALYTE when 'iron' then cast(res_result as float)  end)
      end),
  (Case Depth when 'E' Then
      (case ANALYTE when 'lead' then cast(res_result as float)  end)
      end),
  (Case Depth when 'E' Then
      (case ANALYTE when 'arsenic' then cast(res_result as float)  end)
      end),
  (Case Depth when 'E' Then
      (case ANALYTE when 'zinc' then cast(res_result as float)  end)
      end),
  (Case Depth when 'E' Then
      (case ANALYTE when 'cadmium' then cast(res_result as float)  end)
      end),
  (Case Depth when 'E' Then
      (case ANALYTE when 'manganese' then cast(res_result as float)  end)
      end),
  (Case Depth when 'E' Then
      (case ANALYTE when 'iron' then cast(res_result as float)  end)
      end)

  From #temp_rtc
  Group by [Location],res_result,UNITS,ANALYTE,DEPTH
  order by [Location],res_result,UNITS,ANALYTE,DEPTH

drop table #temp_rtc
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 250 total points
ID: 11713457
try this

select location
,  Max(Case Depth when 'A'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' +coalesce(res_comment,'')
                 else ''
                 end) as [Result A]
,  Max(Case Depth when 'B'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' +coalesce(res_comment,'')
                 else ''
                 end) as [Result B]
,  Max(Case Depth when 'C'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' +coalesce(res_comment,'')
                 else ''
                 end) as [Result C]
 , Max(Case Depth when 'D'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' +coalesce(res_comment,'')
                 else ''
                 end) as [Result D]
  ,Max(Case Depth when 'E'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' +coalesce(res_comment,'')
                 else ''
                 end) as [Result E]
  ,Max(Case Depth when 'F'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' +coalesce(res_comment,'')
                 else ''
                 end) as [Result F]
 , Max(Case Depth when 'G'
                 Then Analyte + ' ' + Units + ' ' + res_result + ' ' +coalesce(res_comment,'')
                 else ''
                 end) as [Result G]
  From #temp
  Group by [Location]
  order by [Location]
0
 
LVL 1

Author Comment

by:altarEgo
ID: 11716853
Lowfat, the example above still drops out all of the other analytes.

Last night I found this solution

declare @PIDSSEQ int
set @PIDSSEQ = 50268


select left(samloc_desc,(patindex('%-%',samloc_desc)-1)) as Location,
SUBSTRING(samplelocation,4,1) as Depth,
 (left(samloc_desc,(patindex('%-%',samloc_desc)-1)) + ' ' + analyte_desc + ' ' + cast(samp_is_dup as varchar) + cast(samp_is_split as varchar) + cast(samp_is_rsp as varchar) ) as AnLoc,
analyte_desc as Analyte, res_units as Units, res_result,res_comment, res_valid_qual
into #temp_rtc
FROM fx_Sum_MasterLogs(), fx_Sum_Results()
where SAMP_PIDS_SEQ = @PIDSSEQ
  AND RES_SAMP_SEQ = SAMP_SEQ and
 res_qccode is null

select * from #temp_rtc

SELECT t.anLoc ,
       MIN( CASE t.ts_cnt WHEN 1 THEN t.res_result END ) AS [0-1"],MIN( CASE t.ts_cnt WHEN 1 THEN t.valQual END ) AS [validqualA],
       MIN( CASE t.ts_cnt WHEN 2 THEN t.res_result END ) AS [1-6"],MIN( CASE t.ts_cnt WHEN 2 THEN t.valQual END ) AS [validqualB],
       MIN( CASE t.ts_cnt WHEN 3 THEN t.res_result END ) AS [6-12"],MIN( CASE t.ts_cnt WHEN 3 THEN t.valQual END ) AS [validqualC],
       MIN( CASE t.ts_cnt WHEN 4 THEN t.res_result END ) AS [12-18"],MIN( CASE t.ts_cnt WHEN 4 THEN t.valQual END ) AS [validqualD],
       MIN( CASE t.ts_cnt WHEN 5 THEN t.res_result END ) AS [18-24"],MIN( CASE t.ts_cnt WHEN 5 THEN t.valQual END ) AS [validqualE]
FROM (
SELECT anLoc , res_result , res_valid_qual as valQual,
       -- Get count of each timestamp value within a anLoc
       -- This is used to generate the columns later
       ( SELECT COUNT( * ) FROM #temp_rtc AS t2
         WHERE t2.anLoc = t1.anLoc And
               t2.res_result <= t1.res_result ) AS ts_cnt
FROM #temp_rtc AS t1
) AS t
GROUP BY t.anLoc
ORDER BY t.anLoc

drop table #temp_rtc


How about splitting the points?

Tia,
Malika
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now