How to split results from one column (i.e. results of time-periods) into more columns with different names

Hi Experts, I am desperate :(
I am looking for a postgres statetement, to show 2 Queries within 1 table.
I meen, I want to split  1 period from 2017 - 2018into two periods 2017 in one column and 2018 in a second column.

My following statement does not work

SELECT
  public."RechDat"."RECHDATUM",
  public."RechPos"."ARTNR",
  public."RechPos"."ID",
  "RechPos1"."ARTNR",
  "RechPos1"."ID",
  SUM("RechPos1"."NettoGP") AS field_1,
  SUM(public."RechPos"."NettoGP") AS field_2,
  "RechDat1"."RECHDATUM"
FROM
  public."RechDat" "RechDat1"
  INNER JOIN public."RechPos" "RechPos1" ON ("RechDat1"."RECHNR" = "RechPos1"."RECHNR")
  INNER JOIN public."RechDat" ON ("RechDat1"."RECHNR" = public."RechDat"."RECHNR")
  INNER JOIN public."RechPos" ON (public."RechDat"."RECHNR" = public."RechPos"."RECHNR")
WHERE
  public."RechDat"."RECHDATUM" BETWEEN '2017-02-01' AND '2017-02-20' OR
  "RechDat1"."RECHDATUM" BETWEEN '2018-02-01' AND '2018-02-20'
GROUP BY
  public."RechDat"."RECHDATUM",
  public."RechPos"."ARTNR",
  public."RechPos"."ID",
  "RechPos1"."ARTNR",
  "RechPos1"."ID",
  "RechDat1"."RECHDATUM"


Where is my mistake?
Thanks in advance,
TomBild-076.png
Brainwashed2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
You have to group by date(year)

Once you have that,  a secondary query would group by everything other than date, but you gave a conditional, if date_year=2017 then value. End as 2017, if date_year=2018 then value end as 2018

This will pull the 2017,2018 values from rows into columns.
0
arnoldCommented:
You need to get your first query to sum the yearly, data
Year,item1id,sum()
2017,1,324
2018,1,245




Then this can be converted to
Item1id,2017_sum,2018_sum
1,324,245
0
Brainwashed2Author Commented:
Thanks a lot but maybe I am to stupid..

I've tried group by Year, but stuck. I got two columns for 'jahr1' and 'jahr2' but need a split for all other columns (ID1|ID2, ARTNR1|ARTNR2 etc. ... and Period1|Period2 (day of year),  correct cummulativ1|cummulative2) too. Not at the end but at the right side of 2017 results.
img_283.jpg
Can you take a look at my following statement and perhaps tell, where to  correct it?

SELECT
CASE WHEN extract(YEAR FROM "RECHDATUM") = '2017'  THEN '2017'  END AS Jahr1,  
CASE WHEN extract(YEAR FROM "RECHDATUM") = '2018'  THEN '2018'  END AS Jahr2,
  "RECHDATUM",
  "RechPos"."ID",
  "RechPos"."ARTNR",

 extract(days FROM (TIMESTAMP '2017-01-01'  - "RECHDATUM") / -1) + 1 AS Period,   SUM("RechPos"."NettoGP"),
 sum("RechPos"."NettoGP") OVER (ORDER  BY "RECHDATUM") AS cummulativ,
 sum("RechPos"."NettoGP") OVER (ORDER  BY "RECHDATUM") / (extract(days FROM (TIMESTAMP '2017-01-01'  - "RECHDATUM") / -1) + 1) AS Faktor
   

FROM
  "RechPos"
  INNER JOIN "RechDat" ON ("RechPos"."RECHNR" = "RechDat"."RECHNR")


GROUP BY
 Jahr1, Jahr2,
 "RECHDATUM",
 "RechPos"."ID",
 "RechPos"."NettoGP",
 "RechPos"."ARTNR"
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

arnoldCommented:
First you run

Using the reference from https://stackoverflow.com/questions/13437362/how-to-get-the-first-and-last-date-of-the-current-year to get the first day of the year.

SELECT
CASE WHEN extract(YEAR FROM "RECHDATUM") = '2017'  THEN '2017'  END AS Jahr1,  
CASE WHEN extract(YEAR FROM "RECHDATUM") = '2018'  THEN '2018'  END AS Jahr2, 
 "RECHDATUM",

  "RechPos"."ID",
  "RechPos"."ARTNR",

 datediff (day, (DATEADD(yy, DATEDIFF(yy, 0, RECHDATUM), 0)) ,"RECHDATUM")  AS Period,   SUM("RechPos"."NettoGP") as 'sum of money',
 sum("RechPos"."NettoGP") OVER (ORDER  BY "RECHDATUM") AS cummulativ,
 sum("RechPos"."NettoGP") OVER (ORDER  BY "RECHDATUM") / (datediff (day, (DATEADD(yy, DATEDIFF(yy, 0, RECHDATUM), 0)) ,"RECHDATUM")) AS Faktor
   

FROM
  "RechPos"
  INNER JOIN "RechDat" ON ("RechPos"."RECHNR" = "RechDat"."RECHNR")


GROUP BY
 "RECHDATUM"
 "RechPos"."ID",
 "RechPos"."NettoGP",
 "RechPos"."ARTNR" 

Open in new window


the above should give you what you are looking for
0
arnoldCommented:
Without the sample data and the resulting output it is difficult for me to know where the issue messes up. I adjusted your calculation to adjust based on the begining of the year based on the RECHDATUM


I am uncertain what you are tryiing to do when you group by the datepart year and the rechdatum as they seem to be negating each other. Do you want a daily or a yearly.
you would either do based on the RECHDATUM stripped of time for a daily,dayofyear, or use the datepart year to tabulate yearly total.

please clarify your tabulation
0
Brainwashed2Author Commented:
Thanks again and sorry for my ambiguous explanation.
Give me one more try:

I have two Rech Tables for Invoices, "RechDat" and "RechPos".
RechDat is containing Number of Invoice ("RECHNR") and Date of Invoice ( "RECHDATUM").
RechPos is containing the corresponding Positions to  Number of Invoice ("RechPos"."RECHNR" = "RechDat"."RECHNR")
The Positions are containing  "RechPos"."ID",  "RechPos"."ARTNR" and the amount of any corresponding Position ==> Pieces * Price  ("RechPos"."NettoGP").

My statement results in correct values, but only in one column for each field but I need two different colmuns for  corresponding entries in year1 and  year2, to use it with a comparing chart to show comparative values for every day (or weekly differences) . I think it is difficult, but not impossible.
0
Brainwashed2Author Commented:
...and let me complete - my human query would sound like that:

SELECT
  "RechDat"."RECHDATUM"                                    AS Datum_Year1,
  "RechPos"."ID"                                                        AS ID-Year1,
  "RechPos"."ARTNR"                                               AS ARTNR_Year1,

  Age of "RechDat"."RECHDATUM" since  01.01.Year1             AS Period_Year1
  Cummulative SUM of all Positions since  01.01.Year1           AS CummSinceJan_Year1
  Value of CummSinceJan_Year1 divided by Period_Year1      AS FAKTOR_Year1
 
 
  WHERE "RechDat"."RECHDATUM" BETWEEN 01.01.Year1 AND 31.12.Year1
 
 
 AND ADDITIONALLY SELECT
 
  "RechDat"."RECHDATUM"                                    AS Datum_Year2,
  "RechPos"."ID"                                                       AS ID-Year2,
  "RechPos"."ARTNR"                                              AS ARTNR_Year2,

  Age of "RechDat"."RECHDATUM" since  01.01.Year2          AS Period_Year2
  Cummulative SUM of all Positions since 01.01.Year2            AS CummSinceJan_Year2
  Value of CummSinceJan_Year2 divided by Period_Year2      AS FAKTOR_Year2
 
 
  WHERE "RechDat"."RECHDATUM" BETWEEN 01.01.Year2 AND 31.12.Year2
 
 
  AND THEN SHOW ME ALL IN SEPERATE COLUMNS SORTED BY ID-Year1, ID-Year2
0
arnoldCommented:
Having the information you do, please post what you expect to see

SELECT
CASE WHEN extract(YEAR FROM "RECHDATUM") = '2017'  THEN '2017'  END AS Jahr1,  
CASE WHEN extract(YEAR FROM "RECHDATUM") = '2018'  THEN '2018'  END AS Jahr2, 
 

  "RechPos"."ID",
  "RechPos"."ARTNR",

 datediff (day, (DATEADD(yy, DATEDIFF(yy, 0, RECHDATUM), 0)) ,"RECHDATUM")  AS Period,   SUM("RechPos"."NettoGP") as sum_of_money,
 sum("RechPos"."NettoGP") OVER (ORDER  BY "RECHDATUM") AS cummulativ,
 sum("RechPos"."NettoGP") OVER (ORDER  BY "RECHDATUM") / (datediff (day, (DATEADD(yy, DATEDIFF(yy, 0, RECHDATUM), 0)) ,"RECHDATUM")) AS Faktor
   

FROM
  "RechPos"
  INNER JOIN "RechDat" ON ("RechPos"."RECHNR" = "RechDat"."RECHNR")


GROUP BY
 Datepart(year,"RECHDATUM"),
 "RechPos"."ID",
 "RechPos"."NettoGP",
 "RechPos"."ARTNR" 

Open in new window


What do you get from the above query?
0
Brainwashed2Author Commented:
Thanks for your help, but meanwhile I dit it myself by better clause when.. conditions in subquery.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brainwashed2Author Commented:
I was alone for days and had much time to use my own brain until an useful result. But from arnold there was a little hint, that speeded my thoughts ;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.