I have this query:
SELECT NALOG1.GBRV, NALOG1.DATA, Min(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1].[POCKM]>0) And ([NALOG1.GBRV] Between [@gbr1] And [@gbr2]),[NALOG1.POCKM],Null)) AS pockm, Max(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1.GBRV] Between [@gbr1] And [@gbr2]),[NALOG1.KRAJKM],Null)) AS krajkm, [krajkm]-[pockm] AS Razlika, Max(NOVI.DATAP) AS MaxOfDATAP, Max(NOVI.DATAS) AS MaxOfDATAS
FROM NALOG1 INNER JOIN NOVI ON NALOG1.GBRV = NOVI.GBR
GROUP BY NALOG1.GBRV, NALOG1.DATA, [krajkm]-[pockm]
HAVING (((NALOG1.GBRV)="1001"))
ORDER BY NALOG1.DATA;
and as a result I get:
| 1001 | 07.10.2011 | 43208 | 43293 | 85 | 27.09.2011 | |
| 1001 | 07.10.2011 | 43107 | 43394 | 287 | 27.09.2011 | |
| 1001 | 08.10.2011 | 43394 | 43477 | 83 | 27.09.2011 | |
| 1001 | 08.10.2011 | 43477 | 43580 | 103 | 27.09.2011 | |
| 1001 | 10.10.2011 | 43679 | 43766 | 87 | 27.09.2011 | |
| 1001 | 10.10.2011 | 43580 | 43679 | 99 | 27.09.2011 | |
| 1001 | 10.10.2011 | 43766 | 43867 | 101 | 27.09.2011 |
but I want to have one row for every date. For example for DATA=08.10.2011 instead of
GBRV DATA pockm krajkm Razlika MaxOfDATAP MaxOfDATAS
1001 08.10.2011 43394 43477 83 27.09.2011
1001 08.10.2011 43477 43580 103 27.09.2011
I want to have :
GBRV DATA pockm krajkm Razlika MaxOfDATAP MaxOfDATAS
1001 08.10.2011 43394 43580 83 27.09.2011
Could anybody help me please? Thanks very much in advance.
Jignesh TrivediPosted Apr 25, 2012, 11:42 PM
I go through you query and result and found that fields "pockm", "krajkm" and "Razlika" have different value and also this filed are in group by so if group by find same value than only it return single Row else multiple rows.
I thing if you get single record for the field "Data" then remove fileds "pockm", "krajkm" and "Razlika" from group by clause. and applied aggregate function this filed like min or max.
hope this help.