[SQL] Verschillende selects in stored procedure terugsturen

[Sven_Vdb]

Ik heb dus een stored procedure. Om enkele berekeningen uit te voeren op een basistabel "MonthlyUnits"
Door verschillende where clausules kan dit niet in 1 select. Maar ik krijg alles niet 1 een keer terug verstuurd.

De eerste select die gaat een som nemen van de kolom "SumNAE" . Deze moet de som nemen voor
de productiemaandid's van het begin van zijn carriere ( maand 1 ) tot de voorbije productiemaand. Hierbij moet hij ook het contractid meenemen.

De tweede select gaat een som van de kolom "SumNAE" en "SumNLV" nemen voor een periode. Dit loopt dan tussen verschillende productiemaanden. Dit kan dus van productiemaand 5 tot 9 zijn. Hier moet ook weer een contractid meegegeven worden. Hetgene ik nu wil terugsturen is ProductieMaandId, ContractId, TotalNAE, NAEPeriod , NLVPeriod

Ik heb al geprobeerd om via een Return de gegevens terug te geven. Maar dit mag blijkbaar niet.In een stored procedure met maar 1 select is de return niet nodig dan gaat de select automatisch terug. Hoe kan ik dit oplossen? Ik zie het niet zo meteen.

SQL:

CREATE PROCEDURE GetEarnedUnitsConsultants
    @ProductionMonth        as bigInt,
    @FromProductionMonth    as bigInt,
    @ToProductionMonth      as bigInt
AS BEGIN
    DECLARE @NAETotal       as decimal

SELECT contractid,sum(SumNAE)as TotalNAE FROM MonthlyUnits
WHERE ProductionMonthId between 1 and (@ProductionMonth -1)
GROUP BY ContractId

SELECT Contractid,sum(SumNAE) as NAEPeriod,SUM(SumNLV) as NLVPeriod FROm MonthlyUnits
WHERE ProductionMonthId between @FromProductionMonth and @ToProductionMonth
GROUP BY ContractId

[Skinny]

Je wilt dus op basis van dezelfde GROUP BY meerdere gesommeerde waarden teruggeven. Volgens mij kun je dan prima iets doen met een CASE WHEN statement i.c.m. een SUM :

SQL:

SELECT  ContractID
        ,SUM (  CASE WHEN   ProductionMonthId 
                            BETWEEN 1 and (@ProductionMonth -1) 
                THEN SumNAE 
                ELSE 0 
                END  ) AS TotalNAE
        ,SUM (  CASE WHEN   ProductionMonthId 
                            BETWEEN @FromProductionMonth and @ToProductionMonth  
                THEN SumNAE 
                ELSE 0 
                END  ) AS PeriodNAE
FROM MonthlyUnits
GROUP BY ContractID

Op basis van de voorwaarde die je nu over twee queries verdeeld hebt tel je de waarden wel of niet op. Als aan de voorwaarde voldaan is neem je de waarde (SumNAE) en anders 0

(niet getest, zo even ingetypt)

De NLVPeriod laat ik als 'oefening' over

[ Voor 7% gewijzigd door Skinny op 12-07-2010 21:25 ]

[Sven_Vdb]

@Skinny: Dit ziet er op het eerste zicht inderdaad uit wat ik wil bereiken. Ik zou er zelf zo niet opgekomen zijn.

[Sven_Vdb]

Het probleem zoals het hiervoor voorgesteld is, werkte perfect maar de query is wat uitgebreid en ik loop weer op een klein probleempje. Maar doordat de Id's van de productiemaanden niet op elkaar volgen kan ik niet via de id's gaan controleren.

De MonthlyUnits tabel bestaat uit:
Id | ProductionMonthId | ContractId | SumNAE | SumNLV

De ProductionMonth tabel bestaat uit:
Id | BeginDate | CalculationDate | EndDate | DisplayName | ProductionMonthStatusId

Daarom dat ik wil gaan controleren op de datum. Van die productiemaanden.
Ik krijg via mijn stored procedure een productiemaand door, nu gaat die nog gewoon controleren op het Id.
Ik maak in mijn select 3 Sommen
- Gaat de som van begin carriere tot een gekozen productiemaand( om juist de zijn tot die productiemaand vandaar de -1 )
- Gaat de som van SumNAE ophalen voor een welbepaald semester. Semester geef je mee via stored procedure.
- Gaat de som van SumNLV ophalen voor een welbepaald semester. Semester geef je mee via stored procedure.

Maar omdat in de database de productiemaanden niet altijd mekaar opvolgen,moet ik gaan controleren op de begin en end date
van de productiemaand.
Maar het lukt mij niet zo best.

Ik heb eerst geprobeerd via een where clausule,maar omdat ik bij mijn sommen verschillende periode's nodig heb kan dit dus ook niet.
Hetgene ik in de where clausule plaatste was het volgende

SQL:

AND (@FromProductionMonthCurrentSemester IS NULL OR ipoBookingsMonth.BeginDate >= (SELECT paramBookingsMonthFrom.BeginDate FROM ProductionMonths paramBookingsMonthFrom WHERE paramBookingsMonthFrom.Id = @FromProductionMonthCurrentSemester))
AND (@ToProductionMonthCurrentSemester IS NULL OR ipoBookingsMonth.BeginDate < (SELECT paramBookingsMonthTo.EndDate FROM ProductionMonths paramBookingsMonthTo WHERE paramBookingsMonthTo.Id = @ToProductionMonthCurrentSemester))

De stukjes over ipoBookingsMonth zijn van joins die het id gaan bekijken. Iemand die misschien nog kan helpen? Om toch de juiste waarden uit de database te halen?

SQL:

CREATE PROCEDURE GetEarnedUnitsConsultants
    @ProductionMonth                    as bigInt,
    @FromProductionMonthCurrentSemester as bigInt,
    @ToProductionMonthCurrentSemester   as bigInt,
    @LanguageCode                       as nvarchar(5)
AS BEGIN
    
SELECT      MonthlyUnits.ContractId as ContractId, 
            People.FirstName as FirstName,  
            People.LastName as LastName, 
            Companies.CompanyName as CompanyName,
            BTCLeader.FirstName as BTCLeaderFirstName,
            BTCLeader.LastName as BTCLeaderLastName,
            BTCLeader.CompanyName as BTCLeaderCompanyName,
            ISNULL(ContractVersions.CommissionPerUnit,0) as CommissionPerUnit,
            TranslationElements.[Description] as Title,
            Strukki.Id as StrukkiId,
            ISNULL(SalesTitles.RequiredUnits,0) as RequiredUnits,
            ISNULL(SalesTitles.PercentageClause,0) as PercentageClause,
            strukki.FirstName as StrukkiFirstName,
            Strukki.LastName as StrukkiLastName,
            Strukki.CompanyName as StrukkiCompanyName,
            Strukki.CommissionPerUnit as StrukkiCommissionPerUnit,
            Strukki.[Description] as StrukkiTitle,
            SUM(CASE WHEN ProductionMonthId BETWEEN 1 AND (@ProductionMonth - 1)  THEN SumNAE ELSE 0 END) AS TotalNAE, 
            SUM(CASE WHEN ProductionMonthId BETWEEN @FromProductionMonthCurrentSemester AND @ToProductionMonthCurrentSemester THEN SumNAE ELSE 0 END) AS PeriodNAE, 
            SUM(CASE WHEN ProductionMonthId BETWEEN @FromProductionMonthCurrentSemester AND @ToProductionMonthCurrentSemester THEN SumNLV ELSE 0 END) AS PeriodNLV,
            dbo.GetBiggestSubOrdinateConsultant(MonthlyUnits.ContractId,@FromProductionMonthCurrentSemester,@ToProductionMonthCurrentSemester) as PeriodSubordinateNAENLV
FROM dbo.MonthlyUnits as MonthlyUnits
    LEFT JOIN   dbo.Contracts as Contracts ON Contracts.Id = MonthlyUnits.ContractId 
    LEFT JOIN   dbo.ContractVersions as ContractVersions ON Contracts.Id = ContractVersions.ContractId
    LEFT JOIN   dbo.BusinessAndTrainingCenters as BusinessAndTrainingCenters ON BusinessAndTrainingCenters.Id = ContractVersions.AffiliationBtcId
    LEFT JOIN   dbo.LegalEntities as LegalEntities ON Contracts.LegalEntityId = LegalEntities.Id 
    LEFT JOIN   dbo.LegalEntityVersions as LegalEntityVersions ON LegalEntities.Id = LegalEntityVersions.LegalEntityId 
    LEFT JOIN   dbo.Companies as Companies ON LegalEntityVersions.CompanyId = Companies.Id 
    LEFT JOIN   dbo.People as People ON LegalEntityVersions.IdentityId = People.Id
    LEFT JOIN   dbo.SalesTitles as SalesTitles ON ContractVersions.SalesTitleId = SalesTitles.Id 
    LEFT JOIN   dbo.TranslationElements as TranslationElements ON SalesTitles.NameId = TranslationElements.TranslationId AND TranslationElements.LanguageCode = @LanguageCode
    OUTER APPLY GetStrukkiInfo(ContractVersions.MainStrukkiContractId,@LanguageCode) as Strukki
    OUTER APPLY GetIntermediaryInfo(BusinessAndTrainingCenters.Manager) as BTCLeader
GROUP BY MonthlyUnits.ContractId, People.FirstName, People.LastName, Companies.CompanyName, ContractVersions.CommissionPerUnit,
Strukki.Id,strukki.FirstName,Strukki.LastName,Strukki.CompanyName,Strukki.CommissionPerUnit,Strukki.[Description],TranslationElements.[Description],SalesTitles.RequiredUnits,
SalesTitles.PercentageClause,BTCLeader.FirstName,BTCLeader.LastName,BTCLeader.CompanyName
END

[Skinny]

Ik wil je best nog eens helpen , maar...

kun je die query even ontdoen van alle overbodige tabellen en velden, zodat het probleem alleen overblijft en 2, geef eens een paar voorbeeld records waaruit blijkt hoe die maanden/datums in de tabellen aanwezig zijn.

Ik vermoed dat je namelijk met de door mij eerder voorgestelde oplossing prima uit de voeten kunt, maar dan door met datum-ranges in dat SUM statement te gaan werken ipv de ID's..

[edit] typo's

[Sven_Vdb]

Ik zal maandag het een en ander nog uitschrijven.
We vertrekken zo dadelijk naar TomorrowLand 2010 voor het hele weekend.

Alvast bedankt om me verder te willen helpen.

[Sven_Vdb]

Zo een verkorte versie van mijn procedure. Deze gaat alles per contractid per periode ophalen.
De rest is gewoon randinformatie die weergegeven moet worden.

SQL:

CREATE PROCEDURE GetEarnedUnitsConsultants 
    @ProductionMonth                    as bigInt, 
    @FromProductionMonthCurrentSemester    as bigInt, 
    @ToProductionMonthCurrentSemester    as bigInt, 
    @LanguageCode                        as nvarchar(5) 
AS BEGIN 
     
SELECT      MonthlyUnits.ContractId as ContractId,  
            SUM(CASE WHEN ProductionMonthId BETWEEN 1 AND (@ProductionMonth - 1)  THEN SumNAE ELSE 0 END) AS TotalNAE,  
            SUM(CASE WHEN ProductionMonthId BETWEEN @FromProductionMonthCurrentSemester AND @ToProductionMonthCurrentSemester THEN SumNAE ELSE 0 END) AS PeriodNAE,  
            SUM(CASE WHEN ProductionMonthId BETWEEN @FromProductionMonthCurrentSemester AND @ToProductionMonthCurrentSemester THEN SumNLV ELSE 0 END) AS PeriodNLV
FROM dbo.MonthlyUnits as MonthlyUnits 
GROUP BY MonthlyUnits.ContractId
END

In de tabel MonthlyUnits kunnen volgende gegevens staan

Id	ProductionMonthId	ContractId	SumNAE	SumNLV
1	1	73100010	2000	1000
2	2	73100010	1500	750
3	1	73100020	3000	2000
4	2	73100020	3000	2000
5	3	73100010	1000	500
6	4	73100010	2500	1125

In de ProductionMonth tabel kunnen zich volgende gegevens bevinden

Id	BeginDate	CalculationDate	EndDate	DisplayName
1	01/01/2010	01/02/2010	31/01/2010	01.2010
2	01/02/2010	01/03/2010	28/02/2010	02.2010
3	01/03/2010	01/04/2010	31/03/2010	03.2010
4	01/04/2010	01/05/2010	30/04/2010	04.2010

Zoals je kan zien moeten er 2 verschillende sommen uitkomen. Je krijgt de gekozen productiemaand door waarin we ons nu bevinden.
En je moet ook een periode(semester meegeven). Dus je geeft als afrekeningsmaandid 4 mee. En als periode 2 tot 4.

Dan krijg je dus in je query hetvolgende. Hieronder staat het dus nog met Id's , zo zou het dus nie mogen omdat Id's niet opeenvolgend kunnen zijn.

SQL:

SUM(CASE WHEN ProductionMonthId BETWEEN 1  AND  4 - 1 )  THEN SumNAE ELSE 0 END) AS TotalNAE,  
SUM(CASE WHEN ProductionMonthId BETWEEN 2 AND 4 THEN SumNAE ELSE 0 END) AS PeriodNAE,  
SUM(CASE WHEN ProductionMonthId BETWEEN 2 AND 4 THEN SumNLV ELSE 0 END) AS PeriodNLV

TotalNAE is dus de som waarvan hij van begin carriere moet gaan selecteren tot de gekozen productiemaand -1
PeriodNAE en PeriodNLV is de som waarbij je de semester moet gaan kiezen, de periode dus.

[SiErRa]

Meestal kan je meerdere resultsets teruggeven in een stored procedure. Van SQLServer weet ik zeker dat het kan. Je hoeft geen return te doen, maar gewoon 2 selects doen in je STP.

[Skinny]

Ik ga er even vanuit dat ProductionMonthStatusId in je tweede tabel een link is naar ProductionMonthId in je eerste tabel. Je kan dan de twee tabellen joinen op dat veld en
vervolgens de SUM regels aanpassen om te werken met de Begin+EndDate ipv de Begin(Month)ID en de huidige (Month)Id ?
Eventueel kun je met een query voor je 'grote' select op basis van de @ProductionMonth parameter de juiste datum range uit de tabel halen en vervolgens in je hoofd query gebruiken.

Probeer hier eens mee te experimenteren en dan kan ik je altijd nog een kant-en-klare oplossing geven

[edit] @hierboven, dat kan ook maar volgens mij kan het vrij simpel door wat kleine aanpassingen aan die SUM statement opgelost worden

[ Voor 10% gewijzigd door Skinny op 26-07-2010 19:03 ]

[Sven_Vdb]

Neen die statusid heeft niets te maken met de andere tabel. Dit is nog een andere tabel.
Maar daarop moet ik niet controleren. Daarom nu even dit uit de tabel verwijderd. Het is dus uit de eerste tabel dat de resultaten moeten komen.

Ja ik weet dat ik via mijn begin en end datum moet gaan werken. Maar ik kom er niet uit hoe je dan kan gaan selecteren van tot die datum.

Eerst via een join de geselecteerde maanden gaan selecteren en deze dan gebruiken in de som om te gaan selecteren between de 2 datums ?

[Skinny]

Sven_Vdb schreef op maandag 26 juli 2010 @ 22:35:
Eerst via een join de geselecteerde maanden gaan selecteren en deze dan gebruiken in de som om te gaan selecteren between de 2 datums ?

Bijvoorbeeld ja. Hieronder weer een uitleg + aanzet om je verder te helpen. En nee, naar goed gebruik van /14 geef ik je een stok, visdraad en aas. De hengel mag je zelf maken maar ik help je graag met de bouwtekening

Begin eens met het uitvoeren van onderstaande query :

SQL:

-- Snippet 1
SELECT      *
FROM dbo.MonthlyUnits AS MU
    INNER JOIN dbo.ProductionMonth PM ON (MU.ProductionMonthId = PM.ID)
WHERE MU.ContractId = 73100010

Dit geeft als resultaat alle gegevens uit de MonthlyUnits (MU) tabel voor een bepaald contract. Die WHERE vervang je straks door een GROUP BY, maar ik begin altijd liever klein zodat je het overzichtelijk houdt voor jezelf. Ook krijg je van elke entry in de MU tabel de bijbehorende datums uit de PM tabel.

Nu pakken we een van je gevraagde SUM's :

SQL:

-- Snippet 2
 SUM(CASE WHEN ProductionMonthId BETWEEN 1 AND (@ProductionMonth - 1)  THEN SumNAE ELSE 0 END) AS TotalNAE,   

Hier moet dus de som van SumNAE komen sinds het 'begin der tijden' tot het begin van de @ProductionMonth. Je moet dus van @ProductionMonth de BeginDate weten om de resultaten van snippet 1 verder te filteren. Zoals ik al eerder voorstelde kun je beginnen om twee datum variabelen te declareren en deze te vullen met de Begin/End Date van de gevraagde @ProductionMonth :

SQL:

-- Snippet 3

DECLARE @BeginDateProductionMonth datetime
DECLARE @EndDateProductionMonth datetime

SELECT @BeginDateProductionMonth = BeginDate, @EndDateProductionMonth = EndDate
FROM dbo.ProductionMonth
WHERE Id = @ProductionMonth

PRINT 'Begin periode :'
PRINT @BeginDateProductionMonth
PRINT 'Eind Periode :'
PRINT @EndDateProductionMonth

Voer snippet 3 maar eens uit en kijk goed wat er opgevraagd wordt, waarom en wat je als resultaat krijgt. Probeer de twee variabelen @BeginDateProductionMonth en @EndDateProductionMonth nu eens in de volgende query te verwerken (deze is gebaseerd op Snippet 1)

SQL:

-- Snippet 3
SELECT      MU.ContractID, 
    ........ AS TotalNAE
FROM dbo.MonthlyUnits AS MU
    INNER JOIN dbo.ProductionMonth PM ON (MU.ProductionMonthId = PM.ID)
WHERE MU.ContractId = 73100010
GROUP BY MU.ContractID

(hint : kijk goed naar snippet 2 en wat je nu meer weet dan toen je begon)

Grofweg bestaat je stored procedure dus zometeen uit twee gedeeltes :

1) Haal datumrange voor @ProductionMonth op
2) Gebruik deze datumrange om de gevraagde gegevens te selecteren


Als je de puntjes in snippet 3 hebt weten in te vullen moet je 1 regel resultaat krijgen met twee kolommen

code:

ContractId       TotalNAE
73100010         xxxxx

De overige twee sUM's kun je op soortgelijke wijze invullen. Tevens kun je daarna het filter op ContractId eruit halen om te controleren of je query ook voor de overige contracten klopt.

HTH & Succes!


disclaimer: uit het hoofd getyped, dus syntax errors voorbehouden

[Sven_Vdb]

Ik heb het uiteindelijk toch nog via een functie gedaan waarin ik dan met de where clausule werkte die ik hiervoor ook al geplaatst had. En dan gewoon in die functie de maanden meegeven die nodig zijn.
Kwam er niet direct uit hoe op te lossen volgens jouw manier Zal wel aan mij liggen
Toch bedankt voor de hulp.

SQL:

AND (@FromProductionMonthCurrentSemester IS NULL OR ipoBookingsMonth.BeginDate >= (SELECT paramBookingsMonthFrom.BeginDate FROM ProductionMonths paramBookingsMonthFrom WHERE paramBookingsMonthFrom.Id = @FromProductionMonthCurrentSemester))
AND (@ToProductionMonthCurrentSemester IS NULL OR ipoBookingsMonth.BeginDate < (SELECT paramBookingsMonthTo.EndDate FROM ProductionMonths paramBookingsMonthTo WHERE paramBookingsMonthTo.Id = @ToProductionMonthCurrentSemester))