Order question - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Order question

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-26 : 12:20:31

Hi

Say I use the following query:
SELECT '1 Copy'
UNION
SELECT '2-4 Copies'
UNION
SELECT '5-9 Copies'
UNION
SELECT '10-19 Copies'
UNION
SELECT '20-49 Copies'
UNION
SELECT '50-199 Copies'
UNION
SELECT '200+ Copies'

The output is
1 Copy
10-19 Copies
200+ Copies
20-49 Copies
2-4 Copies
50-199 Copies
5-9 Copies

How would I have to change the query so it is outputted in the correct order? As in:
1 Copy
2-4 Copies
5-9 Copies
10-19 Copies
20-49 Copies
50-199 Copies
200+ Copies

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-05-26 : 12:26:44

select YourColumnName from
(
SELECT 1 as orderNum, '1 Copy' as YourColumnName
UNION
SELECT 2, '2-4 Copies'
UNION
SELECT 3, '5-9 Copies'
UNION
SELECT 4, '10-19 Copies'
UNION
SELECT 5, '20-49 Copies'
UNION
SELECT 6, '50-199 Copies'
UNION
SELECT 7, '200+ Copies'
) t
order by orderNum

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-26 : 12:27:08

[code]SELECT t.Value,
CAST(LEFT(Value,CHARINDEX(' ',REPLACE(REPLACE(Value,'-',' '),'+',' '))-1) AS int) AS OrderVal
FROM
(
SELECT '1 Copy' AS Value
UNION
SELECT '2-4 Copies'
UNION
SELECT '5-9 Copies'
UNION
SELECT '10-19 Copies'
UNION
SELECT '20-49 Copies'
UNION
SELECT '50-199 Copies'
UNION
SELECT '200+ Copies'
)t
ORDER BY OrderVal[/code]

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-26 : 12:59:44

Hi

Thanks for that, but i'm a bit confused how I would apply that to the following query:

DECLARE @period DATETIME
SET @period = '2008-05-01'

--BEER
SELECT
'1' AS 'Amount'
,'BERR' AS Family
,COUNT(*) AS 'Value'
FROM dbo.OrderLines AS ol
LEFT JOIN dbo.OrderHeaders AS oh ON ol.[Sales Order Reference] = oh.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
LEFT JOIN dbo.DatePeriods AS dp ON oh.[Order Date] BETWEEN dp.BeginDate and dp.EndDate
WHERE sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'BERR')
AND ol.[Sales Order Reference] NOT IN (SELECT [Sales Order Reference] FROM dbo.CancelledOrderLines WHERE [Part Number] = ol.[Part Number])
AND [Qty of Stock Reserved] = 1
AND dp.ID=@Period

UNION

SELECT
'2 - 4' AS 'Amount'
,'BERR' AS Family
,COUNT(*) AS 'Value'
FROM dbo.OrderLines AS ol
LEFT JOIN dbo.OrderHeaders AS oh ON ol.[Sales Order Reference] = oh.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
LEFT JOIN dbo.DatePeriods AS dp ON oh.[Order Date] BETWEEN dp.BeginDate and dp.EndDate
WHERE sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'BERR')
AND ol.[Sales Order Reference] NOT IN (SELECT [Sales Order Reference] FROM dbo.CancelledOrderLines WHERE [Part Number] = ol.[Part Number])
AND [Qty of Stock Reserved] BETWEEN 2 AND 4
AND dp.ID=@Period

UNION

SELECT
'5-9' AS 'Amount'
,'BERR' AS Family
,COUNT(*) AS 'Value'
FROM dbo.OrderLines AS ol
LEFT JOIN dbo.OrderHeaders AS oh ON ol.[Sales Order Reference] = oh.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
LEFT JOIN dbo.DatePeriods AS dp ON oh.[Order Date] BETWEEN dp.BeginDate and dp.EndDate
WHERE sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'BERR')
AND ol.[Sales Order Reference] NOT IN (SELECT [Sales Order Reference] FROM dbo.CancelledOrderLines WHERE [Part Number] = ol.[Part Number])
AND [Qty of Stock Reserved] BETWEEN 5 AND 9
AND dp.ID=@Period

UNION

SELECT
'10-19' AS 'Amount'
,'BERR' AS Family
,COUNT(*) AS 'Value'
FROM dbo.OrderLines AS ol
LEFT JOIN dbo.OrderHeaders AS oh ON ol.[Sales Order Reference] = oh.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
LEFT JOIN dbo.DatePeriods AS dp ON oh.[Order Date] BETWEEN dp.BeginDate and dp.EndDate
WHERE sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'BERR')
AND ol.[Sales Order Reference] NOT IN (SELECT [Sales Order Reference] FROM dbo.CancelledOrderLines WHERE [Part Number] = ol.[Part Number])
AND [Qty of Stock Reserved] BETWEEN 10 AND 19
AND dp.ID=@Period

UNION

SELECT
'20-49' AS 'Amount'
,'BERR' AS Family
,COUNT(*) AS 'Value'
FROM dbo.OrderLines AS ol
LEFT JOIN dbo.OrderHeaders AS oh ON ol.[Sales Order Reference] = oh.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
LEFT JOIN dbo.DatePeriods AS dp ON oh.[Order Date] BETWEEN dp.BeginDate and dp.EndDate
WHERE sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'BERR')
AND ol.[Sales Order Reference] NOT IN (SELECT [Sales Order Reference] FROM dbo.CancelledOrderLines WHERE [Part Number] = ol.[Part Number])
AND [Qty of Stock Reserved] BETWEEN 20 AND 49
AND dp.ID=@Period

UNION

SELECT
'50-199' AS 'Amount'
,'BERR' AS Family
,COUNT(*) AS 'Value'
FROM dbo.OrderLines AS ol
LEFT JOIN dbo.OrderHeaders AS oh ON ol.[Sales Order Reference] = oh.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
LEFT JOIN dbo.DatePeriods AS dp ON oh.[Order Date] BETWEEN dp.BeginDate and dp.EndDate
WHERE sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'BERR')
AND ol.[Sales Order Reference] NOT IN (SELECT [Sales Order Reference] FROM dbo.CancelledOrderLines WHERE [Part Number] = ol.[Part Number])
AND [Qty of Stock Reserved] BETWEEN 50 AND 199
AND dp.ID=@Period

UNION

SELECT
'200+' AS 'Amount'
,'BERR' AS Family
,COUNT(*) AS 'Value'
FROM dbo.OrderLines AS ol
LEFT JOIN dbo.OrderHeaders AS oh ON ol.[Sales Order Reference] = oh.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
LEFT JOIN dbo.DatePeriods AS dp ON oh.[Order Date] BETWEEN dp.BeginDate and dp.EndDate
WHERE sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'BERR')
AND ol.[Sales Order Reference] NOT IN (SELECT [Sales Order Reference] FROM dbo.CancelledOrderLines WHERE [Part Number] = ol.[Part Number])
AND [Qty of Stock Reserved] >= 200
AND dp.ID=@Period

ORDER BY family

Any ideas???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-26 : 13:09:16

quote:

Originally posted by rcr69er

Hi

Thanks for that, but i'm a bit confused how I would apply that to the following query:

DECLARE @period DATETIME
SET @period = '2008-05-01'

--BEER
(SELECT 
	'1' AS 'Amount'
	,'BERR' AS Family
	,COUNT(*) AS 'Value',1 AS OrderValue
FROM  dbo.OrderLines AS ol
LEFT JOIN dbo.OrderHeaders AS oh ON ol.[Sales Order Reference] = oh.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
LEFT JOIN  dbo.DatePeriods AS dp ON oh.[Order Date] BETWEEN dp.BeginDate and dp.EndDate
WHERE sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'BERR')
AND ol.[Sales Order Reference] NOT IN (SELECT [Sales Order Reference] FROM dbo.CancelledOrderLines WHERE [Part Number] = ol.[Part Number])
AND [Qty of Stock Reserved] = 1
AND dp.ID=@Period

UNION

SELECT 
	'2 - 4' AS 'Amount'
	,'BERR' AS Family
	,COUNT(*) AS 'Value',2
FROM  dbo.OrderLines AS ol
LEFT JOIN dbo.OrderHeaders AS oh ON ol.[Sales Order Reference] = oh.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
LEFT JOIN  dbo.DatePeriods AS dp ON oh.[Order Date] BETWEEN dp.BeginDate and dp.EndDate
WHERE sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'BERR')
AND ol.[Sales Order Reference] NOT IN (SELECT [Sales Order Reference] FROM dbo.CancelledOrderLines WHERE [Part Number] = ol.[Part Number])
AND [Qty of Stock Reserved] BETWEEN 2 AND 4
AND dp.ID=@Period

UNION

SELECT 
	'5-9' AS 'Amount'
	,'BERR' AS Family
	,COUNT(*) AS 'Value',3
FROM  dbo.OrderLines AS ol
LEFT JOIN dbo.OrderHeaders AS oh ON ol.[Sales Order Reference] = oh.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
LEFT JOIN  dbo.DatePeriods AS dp ON oh.[Order Date] BETWEEN dp.BeginDate and dp.EndDate
WHERE sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'BERR')
AND ol.[Sales Order Reference] NOT IN (SELECT [Sales Order Reference] FROM dbo.CancelledOrderLines WHERE [Part Number] = ol.[Part Number])
AND [Qty of Stock Reserved] BETWEEN 5 AND 9
AND dp.ID=@Period

UNION 

SELECT 
	'10-19' AS 'Amount'
	,'BERR' AS Family
	,COUNT(*) AS 'Value',4
FROM  dbo.OrderLines AS ol
LEFT JOIN dbo.OrderHeaders AS oh ON ol.[Sales Order Reference] = oh.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
LEFT JOIN  dbo.DatePeriods AS dp ON oh.[Order Date] BETWEEN dp.BeginDate and dp.EndDate
WHERE sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'BERR')
AND ol.[Sales Order Reference] NOT IN (SELECT [Sales Order Reference] FROM dbo.CancelledOrderLines WHERE [Part Number] = ol.[Part Number])
AND [Qty of Stock Reserved] BETWEEN 10 AND 19
AND dp.ID=@Period

UNION

SELECT 
	'20-49' AS 'Amount'
	,'BERR' AS Family
	,COUNT(*) AS 'Value',5
FROM  dbo.OrderLines AS ol
LEFT JOIN dbo.OrderHeaders AS oh ON ol.[Sales Order Reference] = oh.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
LEFT JOIN  dbo.DatePeriods AS dp ON oh.[Order Date] BETWEEN dp.BeginDate and dp.EndDate
WHERE sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'BERR')
AND ol.[Sales Order Reference] NOT IN (SELECT [Sales Order Reference] FROM dbo.CancelledOrderLines WHERE [Part Number] = ol.[Part Number])
AND [Qty of Stock Reserved] BETWEEN 20 AND 49
AND dp.ID=@Period

UNION

SELECT 
	'50-199' AS 'Amount'
	,'BERR' AS Family
	,COUNT(*) AS 'Value',6
FROM  dbo.OrderLines AS ol
LEFT JOIN dbo.OrderHeaders AS oh ON ol.[Sales Order Reference] = oh.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
LEFT JOIN  dbo.DatePeriods AS dp ON oh.[Order Date] BETWEEN dp.BeginDate and dp.EndDate
WHERE sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'BERR')
AND ol.[Sales Order Reference] NOT IN (SELECT [Sales Order Reference] FROM dbo.CancelledOrderLines WHERE [Part Number] = ol.[Part Number])
AND [Qty of Stock Reserved] BETWEEN 50 AND 199
AND dp.ID=@Period

UNION

SELECT 
	'200+' AS 'Amount'
	,'BERR' AS Family
	,COUNT(*) AS 'Value',7
FROM  dbo.OrderLines AS ol
LEFT JOIN dbo.OrderHeaders AS oh ON ol.[Sales Order Reference] = oh.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
LEFT JOIN  dbo.DatePeriods AS dp ON oh.[Order Date] BETWEEN dp.BeginDate and dp.EndDate
WHERE sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'BERR')
AND ol.[Sales Order Reference] NOT IN (SELECT [Sales Order Reference] FROM dbo.CancelledOrderLines WHERE [Part Number] = ol.[Part Number])
AND [Qty of Stock Reserved] >= 200
AND dp.ID=@Period)t

ORDER BY t.OrderValue,t.family

Any ideas???

Change like this

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-26 : 13:14:47

Something similar to this. It might not be correct since you haven't prefixed all columns.

SELECT		c.msgText,
		'BERR' AS Family,
		COUNT(dp.BeginDate) AS [Value]
FROM		(
			SELECT '1 Copy' AS msgText, 1 AS msgLower, 1 AS msgUpper UNION ALL
			SELECT '2-4 Copies', 2, 4 UNION ALL
			SELECT '5-9 Copies', 5, 9 UNION ALL
			SELECT '10-19 Copies', 10, 19 UNION ALL
			SELECT '20-49 Copies', 20, 49 UNION ALL
			SELECT '50-199 Copies', 50, 199 UNION  ALL
			SELECT '200+ Copies', 200, 2147483647
		) AS c
LEFT JOIN	dbo.OrderLines AS ol ON ol.[Qty of Stock Reserved] BETWEEN c.msgLower AND c.msgUpper
			AND ol.[Sales Order Reference] NOT IN (SELECT [Sales Order Reference] FROM dbo.CancelledOrderLines WHERE [Part Number] = ol.[Part Number])
LEFT JOIN	dbo.StockHeaders AS sh ON sh.[Part Number] = ol.[Part Number]
LEFT JOIN	dbo.StockCategories AS sc ON sc.[Stock Category] = sh.[Stock Category]
			AND EXISTS (SELECT * FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'BERR' AND sg.[Group] = sc.[Stock Group])
LEFT JOIN	dbo.OrderHeaders AS oh ON oh.[Sales Order Reference] = ol.[Sales Order Reference]
LEFT JOIN	dbo.DatePeriods AS dp ON dp.BeginDate <= oh.[Order Date] AND dp.EndDate >= oh.[Order Date]
			AND dp.ID = @Period
GROUP BY	c.msgText,
		c.msgLower
ORDER BY	c.msgLower

E 12°55'05.25"
N 56°04'39.16"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-26 : 13:21:58

Hi

visakh16 your query isnt working

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-26 : 13:30:30

quote:
Originally posted by rcr69er

Hi

visakh16 your query isnt working

What's the error you're getting?

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-05-27 : 08:42:35

Dear friend...,

chk this...,
i think it would useful to you...,

(SELECT 1 as id,'1 Copy' as [No.s] into #temp
UNION
SELECT 2 as id,'2-4 Copies'as [No.s]
UNION
SELECT 3 as id,'5-9 Copies'as [No.s]
UNION
SELECT 4 as id,'10-19 Copies'as [No.s]
UNION
SELECT 5 as id,'20-49 Copies'as [No.s]
UNION
SELECT 6 as id,'50-199 Copies'as [No.s]
UNION
SELECT 7 as id,'200+ Copies'as [No.s])
order by id

select [No.s] from #temp

cool...,

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-27 : 15:05:37

Hey

Thanks guys for your help!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources