Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Help with report

Author  Topic 

anishap
Yak Posting Veteran

61 Posts
Posted - 2012-10-11 : 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

shilpash
Posting Yak Master

103 Posts
Posted - 2012-10-12 : 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
Go to Top of Page

anishap
Yak Posting Veteran

61 Posts
Posted - 2012-10-12 : 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.
Go to Top of Page

shilpash
Posting Yak Master

103 Posts
Posted - 2012-10-12 : 12:22:17
so u r applying this on ssrs not in query(ssms)
Go to Top of Page

anishap
Yak Posting Veteran

61 Posts
Posted - 2012-10-12 : 12:32:22
on ssrs
Go to Top of Page

shilpash
Posting Yak Master

103 Posts
Posted - 2012-10-12 : 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,,,
Go to Top of Page

anishap
Yak Posting Veteran

61 Posts
Posted - 2012-10-12 : 14:04:29
I tried a new dataset but now I'm getting same date on all rows.
Go to Top of Page

shilpash
Posting Yak Master

103 Posts
Posted - 2012-10-12 : 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
Go to Top of Page

anishap
Yak Posting Veteran

61 Posts
Posted - 2012-10-12 : 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
Go to Top of Page

shilpash
Posting Yak Master

103 Posts
Posted - 2012-10-12 : 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




Go to Top of Page

anishap
Yak Posting Veteran

61 Posts
Posted - 2012-10-12 : 15:55:55
It's working now !!

Thank you very much for your help.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site