July 24, 2017 at 6:07 am
Hello everyone,
I'am new here and also a newbie in Trans_Sql
I searching for create a query with 2 kinds of sorting.
Afeter many many hours searching and trying, i decided to ask here for help.
this is mi query
USE [Eurosort]
GO
/****** Object: StoredProcedure [dbo].[S_GetSorteerAllstockperVarieteit] Script Date: 24/07/2017 7:39:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[S_GetSorteerAllstockperVarieteit]
@id as int = 0
AS
BEGIN
SELECT
sub.varieteit
,sub.stockeerder
,sub.referentie
,sub.klasse
,sub.idmaat
,sub.maat
,aantal
,sub.verpak
,sub.eenheid
,sub.totaal
,sub.pakblad
,sub.stikker
,sub.kaart
,sub.etiket
,sub.diversen
,sub.pallet
,sub.kistpal
,sub.stapels
FROM
(
Select Distinct SPL.idsorteeritem,
SPL.idpallet,
SPL.Aantal,
SO.referentie,
SPL.eenheid,
PS.naam as stockeerder,
VA.naam as varieteit,
SK.naam as klasse,
SM.idmaat,
SM.naam as maat,
LE.naam as verpak,
CAST(SPL.aantal as int ) * CAST(SPL.eenheid as numeric) as totaal,
LEP.naam as pakblad,
LES.naam as stikker,
LEK.naam as kaart,
LEE.naam as etiket,
LED.naam as diversen,
LEPL.naam as pallet,
SI.kistpal,
SK.idklasse,
(SELECT COUNT(*) AS "aantal_palletten" FROM SorteerPalletlabel WHERE idsorteeritem = SI.id and Aantal = SPL.Aantal and idpallet = SPL.idpallet) AS stapels,
Orderby=( case @id when 1 then 'varieteit' else 'stockeerder' end )
FROM SorteerPalletlabel SPL INNER JOIN Sorteeritem AS SI ON SI.id = SPL.idsorteeritem
LEFT JOIN SorteerKlasse AS SK ON SI.idsorteerklas = SK.id
LEFT JOIN SorteerMaat AS SM ON SI.idsorteermaat = SM.id
LEFT JOIN Persoon AS PS ON SI.idstockeerder = PS.id
LEFT JOIN Leeggoed AS LE ON LE.id = SI.idverpak
LEFT JOIN Leeggoed AS LEB2 ON LEB2.id = SI.idbodem2
LEFT JOIN Leeggoed AS LEP ON LEP.id = SI.idpakblad
LEFT JOIN Leeggoed AS LES ON LES.id= SI.idstikker
LEFT JOIN Leeggoed AS LEK ON LEK.id = SI.idkaart
LEFT JOIN Leeggoed AS LEE ON LEE.id = SI.idetiket
LEFT JOIN Leeggoed AS LEPL ON LEPL.id = SI.idpallet
LEFT JOIN Leeggoed AS LED ON LED.id = SI.iddiversen
LEFT JOIN Sorteer AS SO ON SO.id = SPL.idsorteer
LEFT JOIN Varieteiten as VA on VA.Id = SO.idvarieteit
Where SPL.sorteerverkoop IS NULL
) sub
ORDER BY sub.Orderby DESC
END
Ken er nog niet veel van, maar leer het wel
Do not know much about it yet, but learn
July 24, 2017 at 6:13 am
What do you want help with?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2017 at 6:16 am
If I understand your problem correctly, you just need to remove the quotes.
Orderby=( case @id when 1 then varieteit else stockeerder end )
Your code has some things that might need further review, but I don't have enough information to be sure if they're really a problem. (use of distinct and COUNT() in a subquery).
July 24, 2017 at 7:44 am
I think Luis is on the right track, but instead of making the sort as a column called Orderby in SUB, I'd just remove that line completely and in your main query make the ORDER BY like this:ORDER BY CASE @id WHEN 1 THEN varieteit ELSE stockeerder END DESC
July 24, 2017 at 7:47 am
Sorry for my bad first question, i hope that i follows the rules now😉
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Multisorting','U') IS NOT NULL
DROP TABLE #Multisorting
--===== Create the test table with
GO
/****** Object: Table [dbo].[Multisorting] Script Date: 24/07/2017 15:36:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Multisorting](
[id] [int] IDENTITY(1,1) NOT NULL,
[varieteit] [nvarchar](20) NULL,
[referentie] [nvarchar](20) NULL,
[stockeerder] [nvarchar](50) NULL,
[verpak] [nvarchar](10) NULL,
[pallet] [nvarchar](10) NULL,
[eenheid] [numeric](6, 2) NULL,
[aantal] [int] NULL,
[sorteerverkoop] [nvarchar](1) NULL,
[klasse] [nvarchar](10) NULL,
[maat] [nvarchar](10) NULL,
[kistpal] [int] NULL,
CONSTRAINT [PK_Sorteerpalet] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Multisorting] ON
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (1, N'CONFERENCE', N'B.F.V.00001', N'BELCOOL', N'PXPLA', N'', CAST(450.00 AS Numeric(6, 2)), 1, NULL, N'3', N'', 1)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (2, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'1', N'65/75', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (3, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'1', N'70/80', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (4, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'1', N'70/80', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (5, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'1', N'70/80', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (6, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'', CAST(12.00 AS Numeric(6, 2)), 32, NULL, N'1', N'70/80', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (7, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'1', N'60/70', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (8, N'DURANDEAU', N'HEALT00002', N'BOUSSIER', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'1', N'55/65', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (9, N'DURANDEAU', N'HEALT00002', N'COENEN', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'1', N'65/75', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (10, N'DURANDEAU', N'HEALT00002', N'BOUSSIER', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'1', N'70/80', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (11, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'2', N'60/70', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (12, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'2', N'65/75', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (13, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'2', N'55/65', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (14, N'CONFERENCE', N'B.F.V.00001', N'BELCOOL', N'PXPLA', N'', CAST(450.00 AS Numeric(6, 2)), 1, NULL, N'3', N'', 1)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (15, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'2', N'60/70', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (16, N'CONFERENCE', N'B.F.V.00001', N'BELCOOL', N'PXPLA', N'', CAST(450.00 AS Numeric(6, 2)), 1, NULL, N'3', N'', 1)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (19, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'', CAST(12.00 AS Numeric(6, 2)), 14, NULL, N'1', N'70/80', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (20, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'BELDOOS', N'BLOK', CAST(7.00 AS Numeric(6, 2)), 72, NULL, N'A2++', N'75/85', 72)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (21, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'0', CAST(12.00 AS Numeric(6, 2)), 14, NULL, N'1', N'70/80', 80)
SET IDENTITY_INSERT [dbo].[Multisorting] OFF
/****** Object: StoredProcedure [dbo].[S_Multisortering] Script Date: 24/07/2017 15:36:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[S_Multisortering]
@id as int = 0
AS
BEGIN
SELECT
sub.varieteit
,sub.stockeerder
,sub.referentie
,sub.klasse
,sub.maat
,aantal
,sub.verpak
,sub.eenheid
,sub.totaal
,sub.pallet
,sub.kistpal
,sub.stapels
FROM
(
Select Distinct
SPL.pallet,
SPL.Aantal,
SPL.referentie,
SPL.eenheid,
SPL.stockeerder,
SPL.varieteit,
SPL.klasse,
SPL.maat,
SPL.verpak,
CAST(SPL.aantal as int ) * CAST(SPL.eenheid as numeric) as totaal,
SPL.kistpal,
(SELECT COUNT(*) AS "aantal_palletten" FROM Multisorting WHERE Aantal = SPL.Aantal and pallet = SPL.pallet) AS stapels,
-- With this testtable the SELECT COUNT Work not perfect, but this is not the problem
Orderby= case @id when 1 then 'varieteit' else 'stockeerder' end
FROM Multisorting SPL
Where SPL.sorteerverkoop IS NULL
) sub
ORDER BY sub.Orderby DESC
END
GO
Ken er nog niet veel van, maar leer het wel
Do not know much about it yet, but learn
July 24, 2017 at 9:18 am
Can you please state what your desired output is for different inputs?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 24, 2017 at 11:52 am
Thx Cris for this good and quickly reaction
ORDER BY CASE @id WHEN 1 THEN varieteit ELSE stockeerder END DESC
I hav done what you proposed ans it works very fine.
Thx also Luis and TheSQLGuru
i am very happy that mi first question in this forum
is so quickly solved
Thanx everybody 😛
Ken er nog niet veel van, maar leer het wel
Do not know much about it yet, but learn
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply