CODEWITHSUNDEEP
sqlt-sql
user1672932
user1672932

Reputation: 129

Tsql - Is there an efficient way to query this?

Is there an efficient or simple way to query this? for example to minimize server load. Thanks!

db table

qId     WKDT    WK_ENDT                  SUM_LVL_Sort  ADOCD  ocd  OfficeName     WKLY_AVG  WeekCode
------  ------  -----------------------  ------------  -----  ---  -------------  --------  --------
201112  201110  2011-10-28 00:00:00.000  19.00         NULL   NAT  NATION         2.23      n
201112  201110  2011-10-28 00:00:00.000  13.00         NULL   F09  SAN FRANCISCO  2.20      n
201112  201111  2011-11-25 00:00:00.000  19.00         NULL   NAT  NATION         2.39      n
201112  201111  2011-11-25 00:00:00.000  13.00         NULL   F09  SAN FRANCISCO  2.14      n
201112  201112  2011-12-30 00:00:00.000  19.00         NULL   NAT  NATION         2.37      n
201112  201112  2011-12-30 00:00:00.000  13.00         NULL   F09  SAN FRANCISCO  2.18      n

This is the layout I need

WK_ENDT                  adocd  ocd  OfficeName     SUM_LVL_Sort  10    11    12
-----------------------  -----  ---  -------------  ------------  ----  ----  ----
2011-10-07 00:00:00.000  NULL   F09  SAN FRANCISCO  13.00         2.2   2.14  2.18
2011-10-07 00:00:00.000  NULL   NAT  NATION         19.00         2.23  2.39  2.37

tql query

 select TOP 1 (WK_ENDT),
     ,SUM_LVL_Sort
     ,ADOCD     as adocd
     ,Alias   as ocd
     ,OfficeName
     ,(select WKLY_AVG from kpi.dbo.tb_ssi_rzli where SUM_LVL_Sort = 19.00 and RIGHT(wkdt,2) = '10' and weekcode = 'n'  ) as [10]
     ,(select WKLY_AVG from kpi.dbo.tb_ssi_rzli where SUM_LVL_Sort = 19.00 and RIGHT(wkdt,2) = '11' and weekcode = 'n'  ) as [11]
     ,(select WKLY_AVG from kpi.dbo.tb_ssi_rzli where SUM_LVL_Sort = 19.00 and RIGHT(wkdt,2) = '12' and weekcode = 'n'  ) as [12]
from kpi.dbo.tb_ssi_rzli
where SUM_LVL_Sort = 19.00 and right(qId,2) = '12' and WeekCode = 'n'
order by WK_ENDT desc

bluefeet's query result

WK_ENDT                  SUM_LVL_Sort  adocd  ocd  OfficeName  10    11    12
-----------------------  ------------  -----  ---  ----------  ----  ----  ----
2011-12-30 00:00:00.000  19.00         NULL   NAT  NATION      NULL  NULL  2.37
2011-11-25 00:00:00.000  19.00         NULL   NAT  NATION      NULL  2.39  NULL
2011-10-28 00:00:00.000  19.00         NULL   NAT  NATION      2.23  NULL  NULL

Upvotes: 1

Views: 107

Answers (1)

Taryn
Taryn

Reputation: 247720

Did you try this:

select  (WK_ENDT),
     ,SUM_LVL_Sort
     ,ADOCD   as adocd
     ,Alias   as ocd
     ,OfficeName
     , CASE WHEN RIGHT(wkdt,2) = '10' THEN WKLY_AVG END As [10]
     , CASE WHEN RIGHT(wkdt,2) = '11' THEN WKLY_AVG END As [11]
     , CASE WHEN RIGHT(wkdt,2) = '12' THEN WKLY_AVG END As [12]
from kpi.dbo.tb_ssi_rzli
where SUM_LVL_Sort = 19.00 
    and right(qId,2) = '12'
    --and RIGHT(wkdt,2) IN ('10', '11', '12')
    and WeekCode = 'n'
order by WK_ENDT desc

Edit try this, it looks like you want to get values based on the month:

select TOP 1 (WK_ENDT), ,SUM_LVL_Sort ,ADOCD as adocd ,Alias as ocd ,OfficeName , CASE WHEN month(wkdt) = '10' THEN WKLY_AVG END As [10] , CASE WHEN month(wkdt) = '11' THEN WKLY_AVG END As [11] , CASE WHEN month(wkdt) = '12' THEN WKLY_AVG END As [12] from kpi.dbo.tb_ssi_rzli where SUM_LVL_Sort = 19.00 and right(qId,2) = '12' and WeekCode = 'n' order by WK_ENDT desc

Upvotes: 1

Related Questions