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
 Querying Question (eliminate blanks)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mattie
Starting Member

13 Posts

Posted - 02/02/2013 :  11:52:18  Show Profile  Reply with Quote
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 - 02/02/2013 :  13:10:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 02/02/2013 :  15:52:53  Show Profile  Reply with Quote
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 - 02/03/2013 :  09:04:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 02/03/2013 :  18:37:37  Show Profile  Reply with Quote
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 - 02/04/2013 :  12:23:59  Show Profile  Reply with Quote
I have view only access to the tables, if you have any other idea. Thx.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 02/04/2013 :  19:14:28  Show Profile  Reply with Quote
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 - 02/05/2013 :  12:29:05  Show Profile  Reply with Quote
This works great. I love it! Thx for posting! Wish I knew the structure of how this works...
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.09 seconds. Powered By: Snitz Forums 2000