Question: I'm trying to create a query that has 3 columns: 1 column represents the list Job numbers (not nulls values), the 2° and 3rd column represents a sum of values respectively before and after a specific date

SQL Server gives an error and I really don't know how to work it around (except than create 2 queries..)

```
SELECT 
    (SELECT
         PiIndex
     FROM 
         EleGcImpianti
     WHERE 
         PiIndex IS NOT NULL) AS Commessa,
    (SELECT
         SUM(PrImportoDaFatturare)
     FROM 
         EleGcPianificazioneRate
     RIGHT OUTER JOIN 
         EleGcImpianti ON EleGcPianificazioneRate.PrIdImpianto = EleGcImpianti.PiId
     WHERE 
         (EleGcPianificazioneRate.PrDataFattura <= CONVERT(datetime, '2014-01-01', 102))) AS [Somma Rate],
    (SELECT
         SUM(PrPercentualeDaFatturare)
     FROM 
         EleGcPianificazioneRate
     RIGHT OUTER JOIN 
         EleGcImpianti ON EleGcPianificazioneRate.PrIdImpianto = EleGcImpianti.PiId
     WHERE 
         (PrDataFattura > CONVERT(datetime, '2014-01-01', 102))) AS [Somma Percentuale Rate]

```

Answer: 
It seems you simply want to select records from EleGcImpianti and want related sums from EleGcPianificazioneRate:

```
SELECT 
  i.PiIndex,
  COALESCE(pr.sum_after, 0) AS sum_after_date, 
  COALESCE(pr.sum_before, 0) AS sum_before_date
FROM EleGcImpianti i
LEFT JOIN
(
  SELECT
    PrIdImpianto,
    SUM(CASE WHEN PrDataFattura <= '20140101' THEN PrImportoDaFatturare END) AS sum_after,
    SUM(CASE WHEN PrDataFattura > '20140101' THEN PrImportoDaFatturare END) AS sum_before
  FROM EleGcPianificazioneRate
  GROUP BY PrIdImpianto
) pr ON pr.PrIdImpianto = i.PiId
WHERE i.PiIndex IS NOT NULL

```
Your query is syntactically invalid SQL: There is no table/view from which to collect the results (apart from within the subqueries).

The following (untested) query ...

* ... defines an indicator variable `after_cutoff` derived from the cutoff date condition
* ... deletes the base values for the summarions from the respective insignificant times
* performs the global summation
* ignores the `PiIndex` column - using this information while aggregating data only makes sense if it forms part of the partitioning of your data.

This should produce a single record with the summation results.

Note that the common SQL idiom to selectively sum values is the group by clause. However, the result would include summation results for the respective 'uninteresting' datetime intervals.

Which leads to the recommendation that you might want to check whether you really want the two summations to be applied to the *complementary* datetimes - the column names suggest that you want to sum *all* values to have absolute values and percentages for all dates (or at least the same period).

```
    select sum(sq2.base_sum_rate)       AS [Somma Rate]
         , sum(sq2.base_sum_pct_rate)   AS [Somma Percentuale Rate]
      from (   
               select sq1.after_cutoff
                    , sq1.PrImportoDaFatturare     * (1 - sq1.after_cutoff)  base_sum_rate
                    , sq1.PrPercentualeDaFatturare * sq1.after_cutoff        base_sum_pct_rate
                 from (
                              select *
                                   , CASE WHEN EleGcPianificazioneRate.PrDataFattura <= CONVERT(DATETIME, '2014-01-01', 102) THEN 0 ELSE 1 END after_cutoff
                                from EleGcPianificazioneRate
                         RIGHT OUTER JOIN EleGcImpianti ON EleGcPianificazioneRate.PrIdImpianto = EleGcImpianti.PiId
                               where EleGcImpianti.PiIndex IS NOT NULL
                      ) sq1
           ) sq2
         ;

```