Hi, I need some help for my query. Below is my query.
CREATE VIEW STUD1 AS
SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY R.ContractID
ORDER BY CONVERT(DATE, LEFT(EVAL_PERIOD , charindex('-', EVAL_PERIOD) - 1), 103) DESC)
FROM SCMD3.DBO.CPE R;
GO
CREATE VIEW STUD2 AS
SELECT C.ContractID,
GRED1 = MAX(CASE WHEN RN = 2 THEN ALL_GRADE END),
MARK1 = MAX(CASE WHEN RN = 2 THEN ALL_SCORE END),
GRED2 = MAX(CASE WHEN RN = 1 THEN ALL_GRADE END),
MARK2 = MAX(CASE WHEN RN = 1 THEN ALL_SCORE END)
FROM STUD1 C
GROUP BY C.ContractID;
GO
SELECT s.ContractTitle, x.GRED1, x.MARK1, x.GRED2, x.MARK2 FROM STUD2 x INNER JOIN SCMD3.DBO.CONTRACTMASTER s ON x.ContractID = s.ID;
Above is my current query and need to run on my db but the application I used cannot use view function. Is there any other way that I can get the same result? Help would be greatly appreciated
I've just cut & pasted your code, NOT tested it , but it would be something like this:
;WITH STUD1 AS
(
SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY R.ContractID
ORDER BY CONVERT(DATE, LEFT(EVAL_PERIOD , charindex('-', EVAL_PERIOD) - 1), 103) DESC)
FROM SCMD3.DBO.CPE R
),
STUD2 AS
(
SELECT C.ContractID,
GRED1 = MAX(CASE WHEN RN = 2 THEN ALL_GRADE END),
MARK1 = MAX(CASE WHEN RN = 2 THEN ALL_SCORE END),
GRED2 = MAX(CASE WHEN RN = 1 THEN ALL_GRADE END),
MARK2 = MAX(CASE WHEN RN = 1 THEN ALL_SCORE END)
FROM STUD1 C
GROUP BY C.ContractID
)
SELECT s.ContractTitle, x.GRED1, x.MARK1, x.GRED2, x.MARK2
FROM STUD2 x
INNER JOIN SCMD3.DBO.CONTRACTMASTER s ON x.ContractID = s.ID
Thank you for the solution..everything like OK except for Conversion failed when converting date and/or time from character string error I found since this query created.
it is always very dangerous storing a date in a text field ... bound to be illegal dates entered if they are not validated and stored in a datatype column that enforces validity.
You may also find that your CHARINDEX() won't find a "-" and that will give you an error to (in effect your will then do a LEFT "-1")
You could try:
SET DATEFORMAT dmy
SELECT R.ContractID, LEFT(EVAL_PERIOD , charindex('-', EVAL_PERIOD) - 1)
FROM SCMD3.DBO.CPE R
WHERE ISDate(LEFT(EVAL_PERIOD , charindex('-', EVAL_PERIOD) - 1) = 0
but it would be better to create a new DATE column in the table and Parse the EVAL_PERIOD data into it. You could use a TRIGGER, to populate the new date column (in future) and that could RAISERROR if an invalid date was encountered.