| Author |
 Topic  |
|
|
anishap
Yak Posting Veteran
58 Posts |
 Posted - 10/11/2012 : 19:17:02
|
My report has a case statment which pulls multiple dates based on various types. I want to list only the latest date from the date values.
I tried MAX(case ....) because I need only one value. Currently it's listing a date but not the latest one.
Can someone help me with this?
*********************
Here is the sample output from the below query.
Last Name First ID Rubeola Date
AGRICOLA BRIAN 5500858 12/13/2006
AGY MICHAEL 7600153 07/31/2007
AHRENS CHUL 6600602 03/31/2000
ALDIANO METHU 7200204 07/02/2001
BALDSSARI AUDREY 8000568 05/17/2011
BELLNCA RITA 7200894 08/01/1975
............
SELECT
E.FLDLNAME,
E.FLDFNAME,
E.FLDID,
E.FLDWORKGRP,
W.FLDDESCR AS WORK,
D.FLDDESCR AS DEPT,
MAX(CASE WHEN MEA.FLDDATEDUE IS NULL AND MEAS.FLDTYPE IN ('126','MEAAB','MMR') THEN CONVERT(VARCHAR(10),MEAS.FLDDATE, 101) END) AS RUBEOLA_DATE,
TB_Exp = CONVERT(VARCHAR(10),PPDx.FLDDATEDUE,101),
TB_Comp = CONVERT(VARCHAR(10),PPDx.FLDDATELAST,101),
VAC = CONVERT(VARCHAR(10),VACC.FLDDATEDUE, 101),
VAC_Comp = CONVERT(VARCHAR(10),VACC.FLDDATELAST,101),
PEP = CONVERT(VARCHAR(10),PEPID.FLDDATEDUE, 101),
PEP_Comp = CONVERT(VARCHAR(10),PEPID.FLDDATELAST, 101),
TD = CONVERT(VARCHAR(10),TETD.FLDDATEDUE, 101),
TD_Comp = CONVERT(VARCHAR(10),TETD.FLDDATELAST, 101),
AHA = CONVERT(VARCHAR(10),AHA.FLDDATEDUE, 101),
AHA_Comp = CONVERT(VARCHAR(10),AHA.FLDDATELAST, 101),
RESP = CONVERT(VARCHAR(10),RESP.FLDDATEDUE, 101),
RESP_Comp = CONVERT(VARCHAR(10),RESP.FLDDATELAST, 101)
FROM
EMPLOYEE E
INNER JOIN DEPT D
ON E.FLDDEPT = D.FLDCODE
INNER JOIN WORKGRP W
ON E.FLDWORKGRP = W.FLDCODE
LEFT OUTER JOIN REQEXAM RESP
ON RESP.FLDEMPLOYEE = E.FLDREC_NUM
AND RESP.FLDPHYSICAL = 'RESPCL'
LEFT OUTER JOIN REQEXAM AHA
ON AHA.FLDEMPLOYEE = E.FLDREC_NUM
AND AHA.FLDPHYSICAL ='158'
LEFT OUTER JOIN REQEXAM PPDx
ON PPDx.FLDEMPLOYEE = E.FLDREC_NUM
AND PPDx.FLDPHYSICAL IN ('110','TBSS')
LEFT OUTER JOIN REQEXAM VACC
ON VACC.FLDEMPLOYEE = E.FLDREC_NUM
AND VACC.FLDPHYSICAL = 'VAC'
LEFT OUTER JOIN REQEXAM PEPID
ON PEPID.FLDEMPLOYEE = E.FLDREC_NUM
AND PEPID.FLDPHYSICAL = 'PEP 1D'
LEFT OUTER JOIN REQEXAM TETD
ON TETD.FLDEMPLOYEE = E.FLDREC_NUM
AND TETD.FLDPHYSICAL = 'TD'
LEFT OUTER JOIN REQEXAM MEA
ON MEA.FLDEMPLOYEE = E.FLDREC_NUM
AND MEA.FLDPHYSICAL = '126'
LEFT OUTER JOIN PHYSLOG MEAS
ON MEAS.FLDEMPLOYEE = E.FLDREC_NUM
WHERE
E.FLDWORKGRP = @work_group AND
E.FLDSTATUS = 'A'
GROUP BY
E.FLDLNAME,
E.FLDFNAME,
E.FLDID,
E.FLDWORKGRP,
W.FLDDESCR,
D.FLDDESCR,
PPDx.FLDDATEDUE,
PPDx.FLDDATELAST,
VACC.FLDDATEDUE,
VACC.FLDDATELAST,
PEPID.FLDDATEDUE,
PEPID.FLDDATELAST,
TETD.FLDDATEDUE,
TETD.FLDDATELAST,
AHA.FLDDATEDUE,
AHA.FLDDATELAST,
RESP.FLDDATEDUE,
RESP.FLDDATELAST
ORDER BY E.FLDLNAME, E.FLDFNAME |
Edited by - anishap on 10/12/2012 11:22:06
|
|
|
shilpash
Yak Posting Veteran
72 Posts |
Posted - 10/12/2012 : 11:50:04
|
WITH BaseQuery
AS (SELECT E.FLDLNAME
,E.FLDFNAME
,E.FLDID
,E.FLDWORKGRP
,W.FLDDESCR AS WORK
,D.FLDDESCR AS DEPT
,MAX(CASE WHEN MEA.FLDDATEDUE IS NULL
AND MEAS.FLDTYPE IN ('126','MEAAB','MMR') THEN CONVERT(VARCHAR(10),MEAS.FLDDATE,101)
END) AS RUBEOLA_DATE
,TB_Exp = CONVERT(VARCHAR(10),PPDx.FLDDATEDUE,101)
,TB_Comp = CONVERT(VARCHAR(10),PPDx.FLDDATELAST,101)
,VAC = CONVERT(VARCHAR(10),VACC.FLDDATEDUE,101)
,VAC_Comp = CONVERT(VARCHAR(10),VACC.FLDDATELAST,101)
,PEP = CONVERT(VARCHAR(10),PEPID.FLDDATEDUE,101)
,PEP_Comp = CONVERT(VARCHAR(10),PEPID.FLDDATELAST,101)
,TD = CONVERT(VARCHAR(10),TETD.FLDDATEDUE,101)
,TD_Comp = CONVERT(VARCHAR(10),TETD.FLDDATELAST,101)
,AHA = CONVERT(VARCHAR(10),AHA.FLDDATEDUE,101)
,AHA_Comp = CONVERT(VARCHAR(10),AHA.FLDDATELAST,101)
,RESP = CONVERT(VARCHAR(10),RESP.FLDDATEDUE,101)
,RESP_Comp = CONVERT(VARCHAR(10),RESP.FLDDATELAST,101)
FROM EMPLOYEE E
INNER JOIN DEPT D
ON E.FLDDEPT = D.FLDCODE
INNER JOIN WORKGRP W
ON E.FLDWORKGRP = W.FLDCODE
LEFT OUTER JOIN REQEXAM RESP
ON RESP.FLDEMPLOYEE = E.FLDREC_NUM
AND RESP.FLDPHYSICAL = 'RESPCL'
LEFT OUTER JOIN REQEXAM AHA
ON AHA.FLDEMPLOYEE = E.FLDREC_NUM
AND AHA.FLDPHYSICAL = '158'
LEFT OUTER JOIN REQEXAM PPDx
ON PPDx.FLDEMPLOYEE = E.FLDREC_NUM
AND PPDx.FLDPHYSICAL IN ('110','TBSS')
LEFT OUTER JOIN REQEXAM VACC
ON VACC.FLDEMPLOYEE = E.FLDREC_NUM
AND VACC.FLDPHYSICAL = 'VAC'
LEFT OUTER JOIN REQEXAM PEPID
ON PEPID.FLDEMPLOYEE = E.FLDREC_NUM
AND PEPID.FLDPHYSICAL = 'PEP 1D'
LEFT OUTER JOIN REQEXAM TETD
ON TETD.FLDEMPLOYEE = E.FLDREC_NUM
AND TETD.FLDPHYSICAL = 'TD'
LEFT OUTER JOIN REQEXAM MEA
ON MEA.FLDEMPLOYEE = E.FLDREC_NUM
AND MEA.FLDPHYSICAL = '126'
LEFT OUTER JOIN PHYSLOG MEAS
ON MEAS.FLDEMPLOYEE = E.FLDREC_NUM
WHERE E.FLDWORKGRP = '7/1/2012'
AND E.FLDSTATUS = 'A'
GROUP BY E.FLDLNAME
,E.FLDFNAME
,E.FLDID
,E.FLDWORKGRP
,W.FLDDESCR
,D.FLDDESCR
,PPDx.FLDDATEDUE
,PPDx.FLDDATELAST
,VACC.FLDDATEDUE
,VACC.FLDDATELAST
,PEPID.FLDDATEDUE
,PEPID.FLDDATELAST
,TETD.FLDDATEDUE
,TETD.FLDDATELAST
,AHA.FLDDATEDUE
,AHA.FLDDATELAST
,RESP.FLDDATEDUE
,RESP.FLDDATELAST
)
SELECT MAX(RUBEOLA_DATE) MaxRUBEOLA_DATE
FROM BaseQuery |
 |
|
|
anishap
Yak Posting Veteran
58 Posts |
Posted - 10/12/2012 : 12:17:12
|
I'm getting the below errors when I tried With BaseQuery as in above.
**************
[rsFieldReference] The Value expression for the textbox ‘FLDLNAME’ refers to the field ‘FLDLNAME’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Value expression for the textbox ‘FLDFNAME’ refers to the field ‘FLDFNAME’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Value expression for the textbox ‘FLDID’ refers to the field ‘FLDID’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Value expression for the textbox ‘DEPT’ refers to the field ‘DEPT’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Value expression for the textbox ‘TB’ refers to the field ‘TB_Comp’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Value expression for the textbox ‘TBSS’ refers to the field ‘TB_Exp’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Value expression for the textbox ‘textbox9’ refers to the field ‘VAC_Comp’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Value expression for the textbox ‘textbox11’ refers to the field ‘VAC’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Value expression for the textbox ‘textbox15’ refers to the field ‘PEP_Comp’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Value expression for the textbox ‘textbox13’ refers to the field ‘PEP’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Value expression for the textbox ‘textbox19’ refers to the field ‘TD_Comp’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Value expression for the textbox ‘textbox17’ refers to the field ‘TD’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Value expression for the textbox ‘textbox23’ refers to the field ‘AHA_Comp’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Value expression for the textbox ‘textbox21’ refers to the field ‘AHA’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Value expression for the textbox ‘textbox27’ refers to the field ‘RESP_Comp’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Value expression for the textbox ‘textbox25’ refers to the field ‘RESP’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Value expression for the textbox ‘textbox29’ refers to the field ‘MEA’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope. |
|
|
|
shilpash
Yak Posting Veteran
72 Posts |
Posted - 10/12/2012 : 12:22:17
|
| so u r applying this on ssrs not in query(ssms) |
|
|
|
anishap
Yak Posting Veteran
58 Posts |
Posted - 10/12/2012 : 12:32:22
|
| on ssrs |
|
|
|
shilpash
Yak Posting Veteran
72 Posts |
Posted - 10/12/2012 : 12:51:46
|
create a new dataset and use this query--
SELECT MAX(MEAS.FLDDATE) AS RUBEOLA_DATE
FROM EMPLOYEE E
LEFT OUTER JOIN PHYSLOG MEAS
ON MEAS.FLDEMPLOYEE = E.FLDREC_NUM
LEFT OUTER JOIN REQEXAM MEA
ON MEA.FLDEMPLOYEE = E.FLDREC_NUM
AND MEA.FLDPHYSICAL = '126'
WHERE MEA.FLDDATEDUE IS NULL
AND MEAS.FLDTYPE IN ('126','MEAAB','MMR')
then on that field in the report use expression--
=First(Fields!RUBEOLA_DATE
.Value, "DataSet1")
here dataset1 is the newdataset,,so if u use diff name just replace dataset1 with that name,,, |
|
|
|
anishap
Yak Posting Veteran
58 Posts |
Posted - 10/12/2012 : 14:04:29
|
I tried a new dataset but now I'm getting same date on all rows.
|
|
|
|
shilpash
Yak Posting Veteran
72 Posts |
Posted - 10/12/2012 : 14:58:35
|
Yes it will display only one max date.I thought You need only one value date which is the max date.Or Could you please provide some datas how the output should be,may be I am not understanding your requirements.
Thanks |
|
|
|
anishap
Yak Posting Veteran
58 Posts |
Posted - 10/12/2012 : 15:13:36
|
As I mentioned above each employee may have multiple values (rubeola dates) based on the condition so I tried Max(case) to get one date value. The output is showing correctly for those who have only one date value, if there is multiple date value I need only the latest date.
So if the employee has 11/09/1984,10/2/2012, 10/12/2012 as rubeola dates I need only 10/12/2012.
Another employee may have only one date 01/11/2011 so the output should be 01/11/2011
Another one may have 04/12/1975, 12/12/2011 , here output should be 12/12/2011
If I try my query, I will get
Rubeola Date
11/09/1984
01/11/2011
04/12/1975
What I'm expecting
Rubeola Date
12/12/2012
01/11/2011
12/12/2011
|
Edited by - anishap on 10/12/2012 15:26:04 |
|
|
|
shilpash
Yak Posting Veteran
72 Posts |
Posted - 10/12/2012 : 15:31:44
|
tRY THIS--
WITH MaxDate
AS (SELECT MEAS.FLDEMPLOYEE
,MAX(MEAS.FLDDATE) AS RUBEOLA_DATE
FROM EMPLOYEE E
LEFT OUTER JOIN PHYSLOG MEAS
ON MEAS.FLDEMPLOYEE = E.FLDREC_NUM
LEFT OUTER JOIN REQEXAM MEA
ON MEA.FLDEMPLOYEE = E.FLDREC_NUM
AND MEA.FLDPHYSICAL = '126'
WHERE MEA.FLDDATEDUE IS NULL
AND MEAS.FLDTYPE IN ('126','MEAAB','MMR')
GROUP BY MEAS.FLDEMPLOYEE
)
SELECT E.FLDLNAME
,E.FLDFNAME
,E.FLDID
,E.FLDWORKGRP
,W.FLDDESCR AS WORK
,D.FLDDESCR AS DEPT
,RUBEOLA_DATE = CONVERT(VARCHAR(10),M.RUBEOLA_DATE,101)
,TB_Exp = CONVERT(VARCHAR(10),PPDx.FLDDATEDUE,101)
,TB_Comp = CONVERT(VARCHAR(10),PPDx.FLDDATELAST,101)
,VAC = CONVERT(VARCHAR(10),VACC.FLDDATEDUE,101)
,VAC_Comp = CONVERT(VARCHAR(10),VACC.FLDDATELAST,101)
,PEP = CONVERT(VARCHAR(10),PEPID.FLDDATEDUE,101)
,PEP_Comp = CONVERT(VARCHAR(10),PEPID.FLDDATELAST,101)
,TD = CONVERT(VARCHAR(10),TETD.FLDDATEDUE,101)
,TD_Comp = CONVERT(VARCHAR(10),TETD.FLDDATELAST,101)
,AHA = CONVERT(VARCHAR(10),AHA.FLDDATEDUE,101)
,AHA_Comp = CONVERT(VARCHAR(10),AHA.FLDDATELAST,101)
,RESP = CONVERT(VARCHAR(10),RESP.FLDDATEDUE,101)
,RESP_Comp = CONVERT(VARCHAR(10),RESP.FLDDATELAST,101)
FROM EMPLOYEE E
INNER JOIN DEPT D
ON E.FLDDEPT = D.FLDCODE
INNER JOIN WORKGRP W
ON E.FLDWORKGRP = W.FLDCODE
LEFT OUTER JOIN REQEXAM RESP
ON RESP.FLDEMPLOYEE = E.FLDREC_NUM
AND RESP.FLDPHYSICAL = 'RESPCL'
LEFT OUTER JOIN REQEXAM AHA
ON AHA.FLDEMPLOYEE = E.FLDREC_NUM
AND AHA.FLDPHYSICAL = '158'
LEFT OUTER JOIN REQEXAM PPDx
ON PPDx.FLDEMPLOYEE = E.FLDREC_NUM
AND PPDx.FLDPHYSICAL IN ('110','TBSS')
LEFT OUTER JOIN REQEXAM VACC
ON VACC.FLDEMPLOYEE = E.FLDREC_NUM
AND VACC.FLDPHYSICAL = 'VAC'
LEFT OUTER JOIN REQEXAM PEPID
ON PEPID.FLDEMPLOYEE = E.FLDREC_NUM
AND PEPID.FLDPHYSICAL = 'PEP 1D'
LEFT OUTER JOIN REQEXAM TETD
ON TETD.FLDEMPLOYEE = E.FLDREC_NUM
AND TETD.FLDPHYSICAL = 'TD'
LEFT OUTER JOIN REQEXAM MEA
ON MEA.FLDEMPLOYEE = E.FLDREC_NUM
AND MEA.FLDPHYSICAL = '126'
LEFT OUTER JOIN MaxDate M
ON M.FLDEMPLOYEE = E.FLDREC_NUM
WHERE E.FLDWORKGRP = '7/1/2012'
AND E.FLDSTATUS = 'A'
ORDER BY E.FLDLNAME
,E.FLDFNAME
|
|
|
|
anishap
Yak Posting Veteran
58 Posts |
Posted - 10/12/2012 : 15:55:55
|
It's working now !!
Thank you very much for your help. |
|
|
| |
Topic |
|
|
|
Help with report
