SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Help with report
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

anishap
Yak Posting Veteran

61 Posts

Posted - 10/11/2012 :  19:17:02  Show Profile  Reply with Quote
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
Posting Yak Master

101 Posts

Posted - 10/12/2012 :  11:50:04  Show Profile  Reply with Quote
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 - 10/12/2012 :  12:17:12  Show Profile  Reply with Quote
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

101 Posts

Posted - 10/12/2012 :  12:22:17  Show Profile  Reply with Quote
so u r applying this on ssrs not in query(ssms)
Go to Top of Page

anishap
Yak Posting Veteran

61 Posts

Posted - 10/12/2012 :  12:32:22  Show Profile  Reply with Quote
on ssrs
Go to Top of Page

shilpash
Posting Yak Master

101 Posts

Posted - 10/12/2012 :  12:51:46  Show Profile  Reply with Quote
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 - 10/12/2012 :  14:04:29  Show Profile  Reply with Quote
I tried a new dataset but now I'm getting same date on all rows.
Go to Top of Page

shilpash
Posting Yak Master

101 Posts

Posted - 10/12/2012 :  14:58:35  Show Profile  Reply with Quote
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 - 10/12/2012 :  15:13:36  Show Profile  Reply with Quote
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
Go to Top of Page

shilpash
Posting Yak Master

101 Posts

Posted - 10/12/2012 :  15:31:44  Show Profile  Reply with Quote
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 - 10/12/2012 :  15:55:55  Show Profile  Reply with Quote
It's working now !!

Thank you very much for your help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000