January 8, 2009 at 5:29 am
Hello,
I have the following problem:
some prices are set in a table based on some codes witch give us 3 rows for every article (3 different types of prices)
But now for reporting issues i have to place those 3 different prices on 1 single row
take for example the following:
DECLARE @tt TABLE (pk INT IDENTITY(1,1), tsd_id INT, Code INT, verkoop DECIMAL(12,4))
INSERT INTO @tt ([tsd_id],[Code],[verkoop]) VALUES (1,101,12.05)
INSERT INTO @tt ([tsd_id],[Code],[verkoop]) VALUES (1,102,13.05)
INSERT INTO @tt ([tsd_id],[Code],[verkoop]) VALUES (1,103,14.05)
INSERT INTO @tt ([tsd_id],[Code],[verkoop]) VALUES (2,101,12.04)
INSERT INTO @tt ([tsd_id],[Code],[verkoop]) VALUES (2,102,13.04)
INSERT INTO @tt ([tsd_id],[Code],[verkoop]) VALUES (2,103,14.04)
SELECT * FROM @tt
This is giving us 6 rows for witch the tsd_id is the same for every 3 rows meaning those 6 results reflect 3 different prices for an article
what i should have as result would be the following output
TSD Price1 Price2 Price3
1 12.05 13.05 14.05
2 12.04 13.04 14.04
i have tried some stuff with the CASE statement but it does not what i want.
Is it possible to do this in a set based manner or should i rather think in a tally table method while looping through the first table
Tnx for any advice
wkr,
Eddy
January 8, 2009 at 5:39 am
Try this, eddy:SELECT tsd_id,
SUM(CASE WHEN Code = 101 THEN verkoop ELSE 0 END) AS Price1,
SUM(CASE WHEN Code = 102 THEN verkoop ELSE 0 END) AS Price2,
SUM(CASE WHEN Code = 103 THEN verkoop ELSE 0 END) AS Price3
FROM @tt
GROUP BY tsd_idCheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2009 at 5:49 am
Hi chrism,
Tnx this seems to work for the example,
Now i going to try to implement this with an update statement
my original temptable has this structure and i foresee some problems while using the group by statement.
DECLARE @mytable TABLE ( pk INT IDENTITY (1,1) PRIMARY KEY,
LEV_ID INT, ADR_NAAM VARCHAR(50), ADR_TEL VARCHAR(20),FirmaNaam VARCHAR(50), ADR_ID INT, ADS_ID INT, TSD_ARTNR_LEV VARCHAR(60), PAR_ID_TYPE INT,
PAR_NR INT, TSD_ID INT, TSP_TOESLAG DECIMAL(12,4),TSD_VANDATUM DATETIME, TSD_TOTDATUM DATETIME, TSP_ID INT, LLA_AANKOOPNR VARCHAR(25), TSP_VPK_BEDRAG DECIMAL(12,4), ART_ID INT,
ART_OMS_NED VARCHAR(80), MUNT VARCHAR(50), EENHEID VARCHAR(50), LAADLOSNAAM VARCHAR(50), LAADLOSSTRAAT VARCHAR(80),LAADLOSINDUSTRIE VARCHAR(50),LAADLOSTEL VARCHAR(20), LAADLOSLEVID INT
)
Where the Code from the example would be : PAR_NR
And the prices coming from : TSP_TOESLAG
wkr,
Eddy
January 8, 2009 at 6:14 am
eddy (1/8/2009)
Hi chrism,Tnx this seems to work for the example,
Now i going to try to implement this with an update statement
my original temptable has this structure and i foresee some problems while using the group by statement.
DECLARE @mytable TABLE ( pk INT IDENTITY (1,1) PRIMARY KEY,
LEV_ID INT, ADR_NAAM VARCHAR(50), ADR_TEL VARCHAR(20),FirmaNaam VARCHAR(50), ADR_ID INT, ADS_ID INT, TSD_ARTNR_LEV VARCHAR(60), PAR_ID_TYPE INT,
PAR_NR INT, TSD_ID INT, TSP_TOESLAG DECIMAL(12,4),TSD_VANDATUM DATETIME, TSD_TOTDATUM DATETIME, TSP_ID INT, LLA_AANKOOPNR VARCHAR(25), TSP_VPK_BEDRAG DECIMAL(12,4), ART_ID INT,
ART_OMS_NED VARCHAR(80), MUNT VARCHAR(50), EENHEID VARCHAR(50), LAADLOSNAAM VARCHAR(50), LAADLOSSTRAAT VARCHAR(80),LAADLOSINDUSTRIE VARCHAR(50),LAADLOSTEL VARCHAR(20), LAADLOSLEVID INT
)
Where the Code from the example would be : PAR_NR
And the prices coming from : TSP_TOESLAG
wkr,
Eddy
t would be a whole lot easier if you simply got to the point... what do you mean by "i foresee some problems while using the group by statement"?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 8, 2009 at 6:20 am
Hi Jef,
Well what i mean is that when i'm off starting to include more "columns" (edited from rows) its more likely that those rows wont be unique (distinct) anymore witch means that this same example would not give the same result as wanted:
Take the following:
DECLARE @tt TABLE (pk INT IDENTITY(1,1), tsd_id INT, Code INT, verkoop DECIMAL(12,4), datum DATETIME)
INSERT INTO @tt ([tsd_id],[Code],[verkoop], datum) VALUES (1,101,12.05, GETDATE())
INSERT INTO @tt ([tsd_id],[Code],[verkoop], datum) VALUES (1,102,13.05, GETDATE())
INSERT INTO @tt ([tsd_id],[Code],[verkoop], datum) VALUES (1,103,14.05, GETDATE())
INSERT INTO @tt ([tsd_id],[Code],[verkoop], datum) VALUES (2,101,12.05, GETDATE())
INSERT INTO @tt ([tsd_id],[Code],[verkoop], datum) VALUES (2,102,13.05, GETDATE())
INSERT INTO @tt ([tsd_id],[Code],[verkoop], datum) VALUES (2,103,14.05, '2008-12-12')
SELECT tsd_id,
SUM(CASE WHEN Code = 101 THEN verkoop ELSE 0 END) AS Price1,
SUM(CASE WHEN Code = 102 THEN verkoop ELSE 0 END) AS Price2,
SUM(CASE WHEN Code = 103 THEN verkoop ELSE 0 END) AS Price3,
[datum]
FROM @tt
GROUP BY tsd_id, datum
if i try to run it now im not getting 2 rows with 3 prices each but i get 3 rows because the date filed is not the same so the group by is not only on the TSD_ID anymore
Wkr,
Eddy
January 8, 2009 at 6:27 am
eddy (1/8/2009)
SELECT tsd_id,SUM(CASE WHEN Code = 101 THEN verkoop ELSE 0 END) AS Price1,
SUM(CASE WHEN Code = 102 THEN verkoop ELSE 0 END) AS Price2,
SUM(CASE WHEN Code = 103 THEN verkoop ELSE 0 END) AS Price3,
[datum]
FROM @tt
GROUP BY tsd_id, datum
if i try to run it now im not getting 2 rows with 3 prices each but i get 3 rows because the date filed is not the same so the group by is not only on the TSD_ID anymoreEddy
eddy, check out the aggregate functions MIN and MAX. You may be able to include more columns.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2009 at 6:36 am
eddy (1/8/2009)
if i try to run it now im not getting 2 rows with 3 prices each but i get 3 rows because the date filed is not the same so the group by is not only on the TSD_ID anymore
Hi Edy... 😉
So... what do you want to do in that case? Most of this is pretty simply but the requirements must be known. Only you can state what the requirements are.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 8, 2009 at 8:32 am
Hi Jef,
In fact it is really simple,
I select a lot of columns from different tables (lets say 10)
I know for sure that i will get 3 rows back for each article in witch every row include a different price for this product
as an end result i want 1 single row with 12 columns (10 + 2 extra prices) for each product found
The other 9 columns may be filled with the column data for the first row (from the 3 rows) found for each product
i have managed to make it work with the following code although i'm not verry pleased with it cause i really think this could be done in 1 statement
what i do hereunder is : leave the section out that causes 3 rows for each article
provide 3 empty columns to hold the prices afterward
perform an update on this temp table while fetching the desired prices from the underlying table
CREATE TABLE #mytable( pk INT IDENTITY (1,1) PRIMARY KEY,
LEV_ID INT, ADR_NAAM VARCHAR(50), ADR_TEL VARCHAR(20),FirmaNaam VARCHAR(50), ADR_ID INT, ADS_ID INT, TSD_ARTNR_LEV VARCHAR(60),
TSD_ID INT,TSD_VANDATUM DATETIME, TSD_TOTDATUM DATETIME, LLA_AANKOOPNR VARCHAR(25), ART_ID INT,
ART_OMS_NED VARCHAR(80), MUNT VARCHAR(50), EENHEID VARCHAR(50), LAADLOSNAAM VARCHAR(50), LAADLOSSTRAAT VARCHAR(80),LAADLOSINDUSTRIE VARCHAR(50),LAADLOSTEL VARCHAR(20), LAADLOSLEVID INT
, KPrijs DECIMAL(12,4), Minprijs2 DECIMAL(12,4), Stdprijs3 DECIMAL(12,4)
)
-- resultset in tijdelijke table plaatsen
-- dit geeft wel 3 rijen terug per prijs
INSERT INTO #mytable SELECT DISTINCT
view_leveranciers.LEV_ID, view_leveranciers.ADR_NAAM, view_leveranciers.ADR_TEL, view_leveranciers.PAR_NED AS FirmaNaam,
view_leveranciers.ADR_ID, view_leveranciers.ADS_ID, TOEDETAIL.TSD_ARTNR_LEV, TOEDETAIL.TSD_ID,
TOEDETAIL.TSD_VANDATUM, TOEDETAIL.TSD_TOTDATUM, LEVLADAAN.LLA_AANKOOPNR,
TOEDETAIL.ART_ID, ARTIKEL.ART_OMS_NED, PARAM_1.PAR_NED AS Munt, PARAM_2.PAR_NED AS Eenheid,
view_adressen.ADR_NAAM AS LAADLOSNAAM, view_adressen.ADR_STRAAT AS LAADLOSSTRAAT, view_adressen.ADR_INDUSTRIE AS LAADLOSINDUSTRIE, view_adressen.ADR_TEL AS LAADLOSTEL,
LEV.LEV_ID AS LAADLOSLEVID,0,0,0
FROM view_adressen INNER JOIN
LEVLAD INNER JOIN
LEVLADAAN ON LEVLAD.LLD_ID = LEVLADAAN.LLD_ID INNER JOIN
TOESLAG ON LEVLADAAN.LLA_ID = TOESLAG.LLA_ID INNER JOIN
TOEDETAIL ON TOESLAG.TSL_ID = TOEDETAIL.TSL_ID INNER JOIN
TOEPRIJS ON TOEDETAIL.TSD_ID = TOEPRIJS.TSD_ID INNER JOIN
PARAM ON TOEPRIJS.PAR_ID_TYPE = PARAM.PAR_ID INNER JOIN
ARTIKEL ON TOEDETAIL.ART_ID = ARTIKEL.ART_ID INNER JOIN
PARAM AS PARAM_1 ON TOEDETAIL.PAR_ID_MUNT = PARAM_1.PAR_ID INNER JOIN
PARAM AS PARAM_2 ON TOEDETAIL.PAR_ID_EENHEID = PARAM_2.PAR_ID ON view_adressen.ADS_ID = LEVLAD.ADS_ID INNER JOIN
LEV ON LEVLAD.LEV_ID = LEV.LEV_ID INNER JOIN
view_leveranciers ON LEV.LEV_ID = view_leveranciers.LEV_ID
WHERE (view_leveranciers.PAR_ID = 175) AND (view_leveranciers.LEV_TYPE = 0) AND (view_leveranciers.LEV_REMOVED = 0) AND (PARAM.PAR_NR IN (401,
201, 202)) AND(TOEDETAIL.TSD_VANDATUM = '2008-01-01') AND (LEVLADAAN.LLA_REMOVED = 0)
AND (TOEPRIJS.TSP_VKP_BEDRAG > 0)
ORDER BY [ART_ID]
SELECT * FROM #mytable
UPDATE #mytable SET [KPrijs] = (SELECT tp.[TSP_VKP_BEDRAG] FROM TOEPRIJS tp INNER JOIN
PARAM ON tp.PAR_ID_TYPE = PARAM.PAR_ID WHERE PARAM.PAR_NR = 401 AND tp.TSD_ID = #mytable.[TSD_ID] )
,[Minprijs2] = (SELECT tp.[TSP_VKP_BEDRAG] FROM TOEPRIJS tp INNER JOIN
PARAM ON tp.PAR_ID_TYPE = PARAM.PAR_ID WHERE PARAM.PAR_NR = 201 AND tp.TSD_ID = #mytable.[TSD_ID] )
,[Stdprijs3] = (SELECT tp.[TSP_VKP_BEDRAG] FROM TOEPRIJS tp INNER JOIN
PARAM ON tp.PAR_ID_TYPE = PARAM.PAR_ID WHERE PARAM.PAR_NR = 202 AND tp.TSD_ID = #mytable.[TSD_ID] )
SELECT * FROM [#mytable] AS m
DROP TABLE [#mytable]
Is it possible to handle all in one statement?
Wkr
Eddy
January 8, 2009 at 8:54 am
Integrate it into the body of your query, something like this:[font="Courier New"]SELECT DISTINCT
view_leveranciers.LEV_ID,
view_leveranciers.ADR_NAAM,
view_leveranciers.ADR_TEL,
view_leveranciers.PAR_NED AS FirmaNaam,
view_leveranciers.ADR_ID,
view_leveranciers.ADS_ID,
TOEDETAIL.TSD_ARTNR_LEV,
TOEDETAIL.TSD_ID,
TOEDETAIL.TSD_VANDATUM,
TOEDETAIL.TSD_TOTDATUM,
LEVLADAAN.LLA_AANKOOPNR,
TOEDETAIL.ART_ID,
ARTIKEL.ART_OMS_NED,
PARAM_1.PAR_NED AS Munt,
PARAM_2.PAR_NED AS Eenheid,
view_adressen.ADR_NAAM AS LAADLOSNAAM,
view_adressen.ADR_STRAAT AS LAADLOSSTRAAT,
view_adressen.ADR_INDUSTRIE AS LAADLOSINDUSTRIE,
view_adressen.ADR_TEL AS LAADLOSTEL,
LEV.LEV_ID AS LAADLOSLEVID,
d.[KPrijs],
d.[Minprijs2],
d.[Stdprijs3]
FROM view_adressen
INNER JOIN LEVLAD ON view_adressen.ADS_ID = LEVLAD.ADS_ID
INNER JOIN LEVLADAAN ON LEVLAD.LLD_ID = LEVLADAAN.LLD_ID
INNER JOIN TOESLAG ON LEVLADAAN.LLA_ID = TOESLAG.LLA_ID
INNER JOIN TOEDETAIL ON TOESLAG.TSL_ID = TOEDETAIL.TSL_ID
INNER JOIN TOEPRIJS ON TOEDETAIL.TSD_ID = TOEPRIJS.TSD_ID
INNER JOIN PARAM ON TOEPRIJS.PAR_ID_TYPE = PARAM.PAR_ID
INNER JOIN ARTIKEL ON TOEDETAIL.ART_ID = ARTIKEL.ART_ID
INNER JOIN PARAM AS PARAM_1 ON TOEDETAIL.PAR_ID_MUNT = PARAM_1.PAR_ID
INNER JOIN PARAM AS PARAM_2 ON TOEDETAIL.PAR_ID_EENHEID = PARAM_2.PAR_ID
INNER JOIN LEV ON LEVLAD.LEV_ID = LEV.LEV_ID
INNER JOIN view_leveranciers ON LEV.LEV_ID = view_leveranciers.LEV_ID
LEFT JOIN (SELECT tp.TSD_ID,
SUM(CASE WHEN PARAM.PAR_NR = 401 THEN tp.[TSP_VKP_BEDRAG] ELSE 0 END) AS [KPrijs],
SUM(CASE WHEN PARAM.PAR_NR = 201 THEN tp.[TSP_VKP_BEDRAG] ELSE 0 END) AS [Minprijs2],
SUM(CASE WHEN PARAM.PAR_NR = 402 THEN tp.[TSP_VKP_BEDRAG] ELSE 0 END) AS [Stdprijs3]
FROM TOEPRIJS tp
INNER JOIN PARAM ON tp.PAR_ID_TYPE = PARAM.PAR_ID
GROUP BY tp.TSD_ID) d ON d.TSD_ID = TOEPRIJS.TSD_ID
WHERE (view_leveranciers.PAR_ID = 175)
AND (view_leveranciers.LEV_TYPE = 0)
AND (view_leveranciers.LEV_REMOVED = 0)
AND (PARAM.PAR_NR IN (401, 201, 202))
AND (TOEDETAIL.TSD_VANDATUM = '2008-01-01')
AND (LEVLADAAN.LLA_REMOVED = 0)
AND (TOEPRIJS.TSP_VKP_BEDRAG > 0)
ORDER BY [ART_ID]
[/font]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2009 at 11:31 pm
Hi ChrisM,
Tnx a lot for your assistance,
It works not only like a charm, It gives me the results that i wanted so thats verry nice, but it also execute faster than the solution i came up with.
I i run them al together, mine solution and yours in 1 batch then yours is taking up 26% of the total batch,
what i did notice however is that in mine the sqlengine uses more index seeks while in yours it mostly running index scans.,
So this confuses me a bit cause i thought there was nothing faster than index seeks .?
Ps: Jeff, i'm sorry for mispelling your name, did just notice that i managed to do it twice,, stupid me :hehe:
ChrisM, Tnx a lot for the help in this one, thats why i love this forum, not only is it filled with a lot and a lot of verry usefull educational stuff, its also filled with a lot of guy's like you with the hart @ the right place that are willing to help junior dba's to learn more about whats possible..
January 9, 2009 at 2:38 am
Hi Eddy, thanks for your kind words.
As for there was nothing faster than index seeks - in this case it's difficult to compare the original query with the new query because the original had three updates, each touching the source table for the prices, whereas the new query touches the source table only once.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 9, 2009 at 11:32 am
eddy (1/8/2009)
Ps: Jeff, i'm sorry for mispelling your name, did just notice that i managed to do it twice,, stupid me :hehe:
Heh... not really a problem, Eddy... just had to bust your chops about it once. Thanks for the good natured come-back.
As for Chris' solution, I've got nothing to add to it. He's one of those good folks that are damned hard to beat for performance in code. He's a faster typist than me, too! He beats me to the punch all the time. 😀
Nice job, Chris.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply