SQL Converting Columns to Rows based on specific column value
-
Hallo,
I have a problem to fill a new column from a previous column where the values are calculate.
Current output:
Required output:
As you can see when there is a accordance beetween previous year/month in column1 (weample 0, 1 etc.) it shold dispay in the row in column2.
When we have in current year less days than in previous year (like A and B), the highest day from previous year in column1 (A) should have the highest number from column1 (B) in column2 (A).
When we have in current year more days than in pervious year (like B and C), the highest day from pervious year in column1 (B) would have the highest number form column1 (C) in column2 (A).
I tried to solve this issue with a substring and case syntax without a good result.
Thanks for your feedback!
-
We will need your help in order to be able to help you, so please help us!
😎
It would be greatly appreciated if you could provide the DDL (create table) script, sample data as an insert statement, the desired output from the sample data, and what you have tried so far.
-
Hallo,
that so far my DDL. I am counting with the column ZTF SUMMER.ARBEITSTAG the working day within in a month.
But I also need the date/working day from the previous year compared to the current year.
I know that the working day from every month can be less or more, therefore if it is in accordance between the working day it should display in column2 if not than the highest ring day from current year should be displayed.
My attempts so far:
SELECT ZKV1_UP.Jahr, ZKV1_UP.CalendarDate, ZKV1_UP.YearMonth, ZKV1_UP.ARBEITSTAG, ZKV1_UP.DatumVorjahr, (ZKV1_UP.YearMonth_Int - 100) as VorjahrMonth, x.ARBEITSTAG from ZICKALENDER_V1 ZKV1_UP INNER JOIN (SELECT ZKV1_SUB.ARBEITSTAG, ZKV1_SUB.CalendarDate from ZICKALENDER_V1 ZKV1_SUB) x on ZKV1_UP.DatumVorjahr = x.CalendarDate
Sample Data: The first column (ABC) dont count!
Current output:
| | date | column1 |
| ----- | ------------- | ---------- |
|A | 01.01.2022 | 0 |
|A | 02.01.2022 | 1 |
|A | 03.01.2022 | 2 |
|A | 04.01.2022 | 3 |
|A | 05.01.2022 | 4 |
|B | 01.01.2023 | 0 |
|B | 02.01.2023 | 1 |
|B | 03.01.2023 | 2 |
|B | 04.01.2023 | 3 |
|C | 01.01.2024 | 0 |
|C | 02.01.2024 | 1 |
|C | 03.01.2024 | 2 |
|C | 04.01.2024 | 3 |
|C | 05.01.2024 | 4 |
Required output:
| | date | column1 | column2 |
| ----- | ------------- | ---------- | ---------- |
|A | 01.01.2022 | 0 | 0 |
|A | 02.01.2022 | 1 | 1 |
|A | 03.01.2022 | 2 | 2 |
|A | 04.01.2022 | 3 | 3 |
|A | 05.01.2022 | 4 | 3 |
|B | 01.01.2023 | 0 | 0 |
|B | 02.01.2023 | 1 | 1 |
|B | 03.01.2023 | 2 | 2 |
|B | 04.01.2023 | 3 | 4 |
|C | 01.01.2024 | 0 | etc |
|C | 02.01.2024 | 1 | etc |
|C | 03.01.2024 | 2 | etc |
|C | 04.01.2024 | 3 | etc |
|C | 05.01.2024 | 4 | etc |
The DDL:
@AbapCatalog.sqlViewName: 'ZICKALENDER_V1'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Composite View Zeelandia SAP Kalender V1'
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #COMPOSITE
@Analytics.dataCategory: #CUBE
define view ZIC_KALENDER_V1 as select from I_CalendarDate
left outer join ZIB_T246 //Basic View Kalendertage-Texte
on I_CalendarDate.WeekDay = ZIB_T246.Wotnr
left outer join ZIB_T247 //Basic View Kalendermonat-Texte
on I_CalendarDate.CalendarMonth = ZIB_T247.Mnr
left outer join ZTF_SUMWDM //Table Function Akkumulation Arbeitstage im Monat
on I_CalendarDate.CalendarYear = ZTF_SUMWDM.JAHR
and I_CalendarDate.CalendarMonth = ZTF_SUMWDM.MONATNR
and I_CalendarDate.CalendarDay = ZTF_SUMWDM.TAGNR
left outer join ZTF_AFKO //Table Function MIN-MAX Produktionszeit
on I_CalendarDate.CalendarDate = ZTF_AFKO.GSTRP
left outer join ZIB_ZZEE_HOLIDAYCAL //Basic View Tabelle für Ferien und Feiertage00
on I_CalendarDate.CalendarDate = ZIB_ZZEE_HOLIDAYCAL.Dats
and ZTF_SUMWDM.Ident = ZIB_ZZEE_HOLIDAYCAL.RegionNr
{
key I_CalendarDate.CalendarDate,
// key ZIB_T246.Sprsl,
key ZIB_T246.Wotnr,
// key ZIB_T247.Spras,
key ZIB_T247.Mnr,
// key ZTF_SUMWDM.Ident,
key ZTF_SUMWDM.JAHR,
//////////////////////////////////////////////////
//-----------SAP interne Kalendertabelle--------//
//////////////////////////////////////////////////
I_CalendarDate.CalendarQuarter,
I_CalendarDate.CalendarMonth,
I_CalendarDate.CalendarWeek,
I_CalendarDate.CalendarDay,
I_CalendarDate.YearMonth,
I_CalendarDate.YearQuarter,
I_CalendarDate.YearWeek,
I_CalendarDate.WeekDay,
I_CalendarDate.FirstDayOfWeekDate,
I_CalendarDate.FirstDayOfMonthDate,
I_CalendarDate.CalendarDayOfYear,
I_CalendarDate.YearDay,
//////////////////////////////////////////////////
//-------------Texte zu Kalendertagen-----------//
//////////////////////////////////////////////////
ZIB_T246.Kurzt,
ZIB_T246.Langt,
//////////////////////////////////////////////////
//------------Texte zu Kalendermonaten----------//
//////////////////////////////////////////////////
ZIB_T247.Ktx,
ZIB_T247.Ltx,
//////////////////////////////////////////////////
//-------Akkumulierte Arbeitstage im Monat------//
//////////////////////////////////////////////////
ZTF_SUMWDM.TAG,
//In dieser Anweisung geben wir in Textform aus ob an diesem Tag es frei ist oder nicht.
case when ZTF_SUMWDM.TAG = 0
then 'Frei'
else 'Nicht_Frei'
end as Arbeit_Status,
ZTF_SUMWDM.ARBEITSTAG,
//////////////////////////////////////////////////
//----Table Function MIN-MAX Produktionszeit----//
//////////////////////////////////////////////////
//Hier wird geprüft von wann bis Produziert wird. Entsprechend gibt es einen Vermerk als Text.
case when ZTF_AFKO.GSTRP > '0000-00-00'
or ZTF_AFKO.GLTRP > '0000-00-00'
then 'Produktion'
else 'Keine_Produktion'
end as Prokuktion_Status,
//Hier wird geprüft von wann bis Produziert wird. Entsprechend gibt es einen Vermerk als Nummer.
case when ZTF_AFKO.GSTRP > '0000-00-00'
or ZTF_AFKO.GLTRP > '0000-00-00'
then 1
else 0
end as Prokuktion,
ZTF_AFKO.GSTRP as ProduktionPlanStarttag,
ZTF_AFKO.GSUZP as ProduktionPlanStartZeit,
ZTF_AFKO.GLTRP as ProduktionPlanEndtag,
ZTF_AFKO.GLUZP as ProduktionPlanEndZeit,
//////////////////////////////////////////////////
//--Basic View Tabelle für Ferien und Feiertage-//
//////////////////////////////////////////////////
ZIB_ZZEE_HOLIDAYCAL.Holiday,
ZIB_ZZEE_HOLIDAYCAL.HolidayText,
ZIB_ZZEE_HOLIDAYCAL.Vacation,
ZIB_ZZEE_HOLIDAYCAL.VacationText,
/* Associations */
I_CalendarDate._CalendarMonth,
I_CalendarDate._CalendarQuarter,
I_CalendarDate._WeekDay,
I_CalendarDate._YearMonth,
//Notwendiger Zähler für spätere Anzeig ein SAC
@DefaultAggregation: #SUM
ZIB_ZZEE_HOLIDAYCAL.anzahl
}
//Eingrenzen auf deutsche Sprache
where ZIB_T246.Sprsl = 'D'
and ZIB_T247.Spras = 'D'
//Eingrenzen das Bundesland Hessen
and ZTF_SUMWDM.Ident = '7'
group by
I_CalendarDate.CalendarDate,
ZIB_T246.Sprsl,
ZIB_T246.Wotnr,
ZIB_T247.Spras,
ZIB_T247.Mnr,
ZTF_SUMWDM.Ident,
ZTF_SUMWDM.JAHR,
//////////////////////////////////////////////////
//-----------SAP interne Kalendertabelle--------//
//////////////////////////////////////////////////
I_CalendarDate.CalendarYear,
I_CalendarDate.CalendarQuarter,
I_CalendarDate.CalendarMonth,
I_CalendarDate.CalendarWeek,
I_CalendarDate.CalendarDay,
I_CalendarDate.YearMonth,
I_CalendarDate.YearQuarter,
I_CalendarDate.YearWeek,
I_CalendarDate.WeekDay,
I_CalendarDate.FirstDayOfWeekDate,
I_CalendarDate.FirstDayOfMonthDate,
I_CalendarDate.CalendarDayOfYear,
I_CalendarDate.YearDay,
//////////////////////////////////////////////////
//-------------Texte zu Kalendertagen-----------//
//////////////////////////////////////////////////
ZIB_T246.Kurzt,
ZIB_T246.Langt,
//////////////////////////////////////////////////
//------------Texte zu Kalendermonaten----------//
//////////////////////////////////////////////////
ZIB_T247.Ktx,
ZIB_T247.Ltx,
//////////////////////////////////////////////////
//-------Akkumulierte Arbeitstage im Monat------//
//////////////////////////////////////////////////
ZTF_SUMWDM.TAG,
ZTF_SUMWDM.ARBEITSTAG,
//////////////////////////////////////////////////
//----Table Function MIN-MAX Produktionszeit----//
//////////////////////////////////////////////////
ZTF_AFKO.GSTRP,
ZTF_AFKO.GSUZP,
ZTF_AFKO.GLTRP,
ZTF_AFKO.GLUZP,
//////////////////////////////////////////////////
//--Basic View Tabelle für Ferien und Feiertage-//
//////////////////////////////////////////////////
ZIB_ZZEE_HOLIDAYCAL.Holiday,
ZIB_ZZEE_HOLIDAYCAL.HolidayText,
ZIB_ZZEE_HOLIDAYCAL.Vacation,
ZIB_ZZEE_HOLIDAYCAL.VacationText,
//Notwendiger Zähler für spätere Anzeig ein SAC
ZIB_ZZEE_HOLIDAYCAL.anzahl
-
DDL = Data Definition Language (CREATE TABLE scripts, [INSERT] scripts, so we have data to work with).
you can see your data, but we can't. So we need some sample data to work from. You know, like CREATE TABLE and INSERT scripts, so we have populated tables.=)
-
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply