Stuck in a rut- To Loop or Not, if not, how to query?

Question

Hi Guys,

I am trying to work out some logic here. No clue how to apply it in SQL.

I have a view With ID, Name, Worked_Date, Work_Type, Work_Category, Work_Location, Total_Worked_Dates_in_Table and Fees

I want to build summary Table with the Name, Worked_Dates and Total_Worked_Dates

Where If the Work_Type = 'ALPHA' ON Work_Location AND Worked_Dates Then remove all Names with Work_Type = 'BETA' on same Location and Dates If more than one Work_Type ='ALPHA' exists at Location and Date, Take the Name with Max(Total_Worked_Dates_in_Table) on it.

ELSE If Work_Type='BETA' ON Work_Location AND Worked_Date Display all Names with unique Work Category and highest Fees.

The final table should have Names and Worked Dates of people who's done Maximum Alpha work at a given time or location Or Maximum Beta work. There can be more than one category of Beta work done on same location and same time.

I am desperate to get a solution here! I am considering using While loops for each day and cycling through each event but I haven't got any idea how to do that yet.

Thanks Ajmal

---

Ajmal

Answer 1

Why not use a case statement?

CASE

WHEN [some condition] THEN [do this]

WHEN [some condition] THEN [do this]

ELSE [do this]

END

Answer 2

This is what I could come up as per the conditions you indicated. See if that works or provide us some sample data and expected outputs so that it can be further tuned (as needed).

;WITH GetAlphaDataTemp AS
(
	SELECT	ID, Name, Worked_Date, Work_Type, Work_Category, Work_Location, Total_Worked_Dates_in_Table, Fees,
			ROW_NUMBER() OVER(PARTITION BY Work_Location, Worked_Date ORDER BY Total_Worked_Dates_in_Table DESC) AS AlphaRanking
	FROM	YourView			-- replace with your actual view
	WHERE	Work_Type = 'Alpha'
),
GetBetaDataTemp AS
(
	SELECT	ID, Name, Worked_Date, Work_Type, Work_Category, Work_Location, Total_Worked_Dates_in_Table, Fees,
			ROW_NUMBER() OVER(PARTITION BY Work_Location, Worked_Date, Work_Category  ORDER BY Fees DESC) AS BetaRanking
	FROM	YourView			-- replace with your actual view
	WHERE	Work_Type = 'Beta'
)
SELECT	ID, Name, Worked_Date, Work_Type, Work_Category, Work_Location, Total_Worked_Dates_in_Table, Fees
FROM	GetAlphaDataTemp
WHERE	AlphaRanking = 1	-- To get the record with highest Total_Worked_Dates_in_Table value for respective Work_Location & Worked_Date combination
UNION ALL
SELECT	ID, Name, Worked_Date, Work_Type, Work_Category, Work_Location, Total_Worked_Dates_in_Table, Fees
FROM	GetBetaDataTemp Beta
WHERE	BetaRanking = 1		-- To get the record with highest fees for each Work_Category
AND		NOT EXISTS			-- To not include those Beta records when Alpha for the respective date and location already exists
		(
			SELECT	1
			FROM	GetAlphaDataTemp Alpha
			WHERE	Beta.Work_Location	= Alpha.Work_Location
			AND		Beta.Worked_Date	= Alpha.Worked_Date
		)

Thanks!

Answer 3

Frankly, I'm not sure I understand the description of your problem because your explenation blows. Perhaps a little harsh, but being able to describe your problem (in my experience) is paramount to being able to solve it. This is what I understand of your problem. There is a view (we'll call it "Foo") with columns:

ID
Name
Worked_Date
Work_Type
Work_Category
Work_Location
Total_Worked_Dates_in_Table

You want to create a summary table (we'll call it "Bar") with columns:

Name
Worked_Dates
Total_Worked_Dates

Some assumptions I'm making:

• By "remove all names", I assume you mean you want those records deleted.
• I'm assuming "ID" (from Foo) is a primary-key.
• I assume the valid values for "Work_Type" are "ALPHA" and "BETA".

You could use a CTE, temporary table, or table variable; anything to hold the interim results why you remove the records you don't want. I'd opt for a table variable unless you're dealing with a huge amount of data. You can use a permant table if you'll be selecting the results very fequently. For my samples, I'll use a temporary table (bucking my own advice). The flow I'll use is get the results then shape the results.

--Create your temporary table
INSERT INTO #Bar
SELECT Name, Worked_Dates, Total_Worked_Dates
FROM dbo.Foo;
--Delete the crud you don't want
DELETE FROM #Bar WHERE ...
--Bring back the data you want
SELECT ..., MAX(Total_Worked_Dates_in_Tab)
FROM #Bar

You can fill in the blanks for each statement based on the logic you have in mind. The flow will probably be the same. Your delete (probably the toughest of the statements) will look something like:

WITH work_alpha_multi (Work_Type, Work_Location, Worked_Date)
AS (
SELECT Work_Type, Work_Location, Worked_Date 
FROM #Bar
WHERE Work_Type = 'ALPHA'
GROUP BY Work_Type, Work_Location, Worked_Date
HAVING COUNT(*) > 1
)
DELETE FROM #Bar
WHERE ID IN (SELECT ID FROM #Bar INNER JOIN work_alpha_multi wam ON #Bar.Work_Location = wam.Work_Location AND #Bar.Worked_Date = wam.Worked_Date)
GO

Or, at least I presume it will.

Good Luck Dude,
CA-

Answer 4

Main Table

ID               Name             Worked_Date              Work_Type              Work Category            Work_Location           Total_Worked_Dates            Fees

1             Armin VD           12-12-2012                 ALPHA                       XYZ                                 DUBAI                                  2                               50

2             Armin VD           13-12-2012                 ALPHA                       XYZ                                 DUBAI                                  2                               50

3             Bonnie L            13-12-2012                   BETA                        AVG                                DUBAI                                  3                               70

4             Bonnie L            14-12-2012                   BETA                        AVG                                DUBAI                                  3                               70

5             Bonnie L            15-12-2012                   BETA                        AVG                                DUBAI                                  3                               70

6             Amen TT            15-12-2012                    BETA                       XYZ                                 DUBAI                                  2                               50

7             Amen TT            16-12-2012                    BETA                      XYZ                                 DUBAI                                  2                               50

8             Amen TT            17-12-2012                    BETA                      XYZ                                 DUBAI                                  2                               50

9             Downing            15-12-2012                    BETA                      XYZ                                 DUBAI                                  2                               40

10          Downing             16-12-2012                    BETA                      XYZ                                 DUBAI                                  2                               40

11          Ryver                  13-12-2012                    ALPHA                    XYZ                                 DUBAI                                  1                               40

Expected Result

1             Armin VD           12-12-2012                 ALPHA                       XYZ                                 DUBAI                                  2                               50

2             Armin VD           13-12-2012                 ALPHA                       XYZ                                 DUBAI                                  2                               50

4             Bonnie L            14-12-2012                   BETA                        AVG                                DUBAI                                  3                               70

5             Bonnie L            15-12-2012                   BETA                        AVG                                DUBAI                                  3                               70

6            Amen TT            15-12-2012                    BETA                       XYZ                                 DUBAI                                  2                               50

7             Amen TT            16-12-2012                    BETA                      XYZ                                 DUBAI                                  2                               50

8             Amen TT            17-12-2012                    BETA                      XYZ                                 DUBAI                                  2                               50

I hope that clears up any confusion! 

I think Deepak's answer solves it neater than what I had initially planned to do. 

Ajmal

---

Ajmal

Answer 5

Check out the table

---

Ajmal

Answer 6

Hi Deepak,

Thanks Bud. Your answer is the best so far and it works very well!

---

Ajmal

Answer 7

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

This is minimal polite behavior on SQL forums. How you do program from what you posted? We do not have table or column names!! 

 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL