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
 Querying Question (eliminate blanks)

Author  Topic 

mattie
Starting Member

13 Posts

Posted - 2013-02-02 : 11:52:18
When using Case Statements:

Case When ( Turfs like '%Azalea%' or Turfs like '%Prichard%' or Turfs like '%Saraland%' ) then Tech_NAme End as SATGreer,
Case When ( Turfs like '%Airprt%'then Tech_NAme End as SATSchillinger,

Is there a way to change this to the "Desired Result" below?
Tech Name
Sally
Jane
Suzy
Mark
Judy

Desired Result:
Sally Mark
Jane Judy
Suzy

mattie
Starting Member

13 Posts

Posted - 2013-02-02 : 13:10:09
The first table should have posted like Sally, Jane and Suzy in the first column row 1,2,3. Mark Judy in Column 2 Row 4,5. Need column 2 to be in Row 1,2.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-02 : 15:52:53
I didn't completely follow your requirements or data, so there may be some other ways to do this more concisely and efficiently, but is something like this that you are looking for?
SELECT
SATGreer,SATSchillinger
FROM
(
SELECT tech_name AS SATGreer, ROW_NUMBER() OVER (ORDER BY tech_name) AS N
FROM Tbl WHERE
Turfs like '%Azalea%' or Turfs like '%Prichard%' or Turfs like '%Saraland%'
)a
FULL JOIN
(
SELECT tech_name AS SATSchillinger, ROW_NUMBER() OVER (ORDER BY tech_name) AS N
FROM Tbl WHERE
Turfs like '%Airprt%'
)b ON a.N = b.N
Go to Top of Page

mattie
Starting Member

13 Posts

Posted - 2013-02-03 : 09:04:13
That looks like it may work. Would it be possbile to incorporate this into the the full Query? Or could you show an example format?
The ultimate goal is to go ino columns on an Excel sheet, but with the numerous blanks in each column it's not feasible like it is. Thx!

Here is the full query:

Select MAX(Sched_Date) , datepart (weekday, SCHED_DATE) as Day_Week , TECH_NAME,


--Mobile Regular Saturday Schedule
Case When ( Turfs like '%Azalea%' or Turfs like '%Prichard%' or Turfs like '%Saraland%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =7 then Tech_NAme End as SATGreer,
Case When ( Turfs like '%Airprt%' or Turfs like '%Semmes%' or Turfs like '%Springhill%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =7 then Tech_NAme End as SATSchillinger,
Case When (Turfs Like'%Old_Shell%' or Turfs like '%Skyline%' or Turfs like '%Theodore%' or Turfs like '%bayfront%' or turfs like '%Bell_Fontaine%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =7 Then Tech_Name End as SatDemo,
Case When (Turfs Like'%Bay_Minette%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =7 Then Tech_Name End as SatBayMinette,
Case When (Turfs Like'%Brewton%' or Turfs like '%Flomaton%' or Turfs like '%Evergreen%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =7 Then Tech_Name End as SatBFE,
Case When (Turfs Like'%Fairhope%' or Turfs like '%Spanish_FT%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =7 Then Tech_Name End as SatDaphne,
Case When ( Turfs like '%Jackson_%' or Turfs like '%Thomasville%' ) and Turfs not like '%Jacksonville%' and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =7 then Tech_NAme End as SATJackson,
Case When ( Turfs like '%Mt_Vernon_%' or Turfs like '%Citronelle%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =7 then Tech_NAme End as SATMtVernon,



--Mobile Sunday Schedule


Case When ( Turfs like '%Azalea%' or Turfs like '%Prichard%' or Turfs like '%Saraland%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =1 then Tech_NAme End as SunGreer,
Case When ( Turfs like '%Airprt%' or Turfs like '%Semmes%' or Turfs like '%Springhill%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =1 then Tech_NAme End as SunSchillinger,
Case When (Turfs Like'%Old_Shell%' or Turfs like '%Skyline%' or Turfs like '%Theodore%' or Turfs like '%bayfront%' or turfs like '%Bell_Fontaine%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =1 Then Tech_Name End as SunDemo,
Case When (Turfs Like'%Bay_Minette%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =1 Then Tech_Name End as SunBayMinette,
Case When (Turfs Like'%Brewton%' or Turfs like '%Flomaton%' or Turfs like '%Evergreen%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =1 Then Tech_Name End as SunBFE,
Case When (Turfs Like'%Fairhope%' or Turfs like '%Spanish_FT%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =1 Then Tech_Name End as SunDaphne,
Case When ( Turfs like '%Jackson_%' or Turfs like '%Thomasville%' ) and Turfs not like '%Jacksonville%' and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =1 then Tech_NAme End as SunJackson,
Case When ( Turfs like '%Mt_Vernon_%' or Turfs like '%Citronelle%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =1 then Tech_NAme End as SunMtVernon,

Case When datepart (weekday, SCHED_DATE) = 1 Then 'Sunday' When datepart (weekday, SCHED_DATE) = 7 Then 'Saturday' End as Weekend

From TBL

WHERE SCHED_DATE >=DATEADD(day,(DATEDIFF(day,0,GETDATE())/7) * 7,5)
AND SCHED_DATE < DATEADD(day,(DATEDIFF(day,0,GETDATE())/7) * 7,7)


And Group_ID like 'AL%'

and turfs like '%PCA'
and SCH_1 not like 'on 00:00-00:02'
and (Sch_2 not like 'N%' and Sch_2 not like 'DP%' and Sch_2 not like 'I%' and Sch_2 not like 'HO%' and Sch_2 not like 'Vacation%' and SCH_2 not like 'off%' and sch_2 not like 'SO%' and sch_2 not like 'NH%' or sch_2 is null)


GROUP BY DATEPART(weekday,SCHED_DATE), TECH_NAME, Turfs, Sch_Profile


Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-03 : 18:37:37
If you have the flexibility to do so, add two columns to the table indicating which category each row belongs to (such as SunGreer, SunSchillinger etc.) and the ordering number. Then, you can do a simple select and PIVOT on the tech_name column. That will make the code simpler and more robust.

If that gives you what you want, you may want to consider additional enhancements, such as normalizing it to pull out all the category names to a separate table. Also, you can choose not to have a id column as in my example below, instead calculate it on the fly. Give that a try and post back if that does not seem to work right.
CREATE TABLE #tmp (id int, tech_name VARCHAR(32), category VARCHAR(32));

INSERT INTO #tmp VALUES
(1,'A','SunGreer'),
(2,'B','SunGreer'),
(3,'C','SunGreer'),
(1,'D','SunSchillinger'),
(2,'E','SunSchillinger');

SELECT SunGreer,SunSchillinger
FROM #tmp
PIVOT ( MAX(tech_name) FOR category IN ([SunGreer],[SunSchillinger]))P

DROP TABLE #tmp;
Go to Top of Page

mattie
Starting Member

13 Posts

Posted - 2013-02-04 : 12:23:59
I have view only access to the tables, if you have any other idea. Thx.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-04 : 19:14:28
mattie, what I am posting below is a skeleton. First change the Tbl table name to your actual table name and run as it is to see what it produces. If it seems like what you are looking for, then look at the comments I have in the code to see what you need to add so it will pick up all required categories and add appropriate filters via the where clause.
;WITH cte1 AS
(
SELECT Tech_name,
CASE
WHEN (
Turfs LIKE '%Azalea%'
OR Turfs LIKE '%Prichard%'
OR Turfs LIKE '%Saraland%'
) AND sch_profile NOT LIKE 'Call%' AND DATEPART(weekday, Sched_Date)
= 1 THEN 'SunGreer'
WHEN (
Turfs LIKE '%Airprt%'
OR Turfs LIKE '%Semmes%'
OR Turfs LIKE '%Springhill%'
) AND sch_profile NOT LIKE 'Call%' AND DATEPART(weekday, Sched_Date)
=
1 THEN 'SunSchillinger'
WHEN (
Turfs LIKE'%Old_Shell%'
OR Turfs LIKE '%Skyline%'
OR Turfs LIKE '%Theodore%'
OR Turfs LIKE '%bayfront%'
OR turfs LIKE '%Bell_Fontaine%'
) AND sch_profile NOT LIKE 'Call%' AND DATEPART(weekday, Sched_Date)
=
1 THEN 'SunDemo'
-- ADD ALL THE OTHER CATEGORIES HERE AS ADDITIONAL WHEN CLAUSES
END AS Category
FROM Tbl
WHERE SCHED_DATE >= DATEADD(DAY, (DATEDIFF(DAY, 0, GETDATE()) / 7) * 7, 5)
AND SCHED_DATE < DATEADD(DAY, (DATEDIFF(DAY, 0, GETDATE()) / 7) * 7, 7)
-- ADD ALL YOUR OTHER WHERE CLAUSES HERE
),
cte2 AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Tech_name) AS RN
FROM
cte1
)
SELECT
SunGreer,
SunSchillinger,
SunDemo
FROM
cte2
PIVOT (MAX(tech_name) FOR Category IN ([SunGreer],[SunSchillinger],[SunDemo]))P
Go to Top of Page

mattie
Starting Member

13 Posts

Posted - 2013-02-05 : 12:29:05
This works great. I love it! Thx for posting! Wish I knew the structure of how this works...
Go to Top of Page
   

- Advertisement -