SELECT t1.MAIN_ID,t1.DATE_TAKEN,t1.TIME,
t1.DAILY_RAINFALL,
CASE WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=3 
THEN t3.[3MthCumRf]
WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=6 
THEN t3.[6MthCumRf]
WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=9 
THEN t3.[9MthCumRf]
WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=12 
THEN t3.[12MthCumRf]
END AS CumRf 
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY DATE_TAKEN,MAIN_ID 
ORDER BY DAILY_RAINFALL DESC) AS RowNo,* FROM dbo.SEL)t1
INNER JOIN dbo.STATION_INFO t2 ON t2.MAIN_ID=t1.MAIN_ID AND 
t1.RowNo=1 INNER JOIN 
CROSS APPLY (SELECT TOP 1 * FROM dbo.LT 
             WHERE STATION_ID =t2.STATION_ID
             AND Month_Year <=t1.DATE_TAKEN
             ORDER BY Month_Year DESC)t3 

WHERE t2.STATE='SEL'
AND t1.DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-3,GETDATE()),101)
AND t1.DATE_TAKEN<CONVERT(VARCHAR(10), GETDATE(), 101)
ORDER BY MAIN_ID,DATE_TAKEN,TIME

quote:

---

Originally posted by wkm1925

shows errow below,
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'CROSS'.
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'ORDER'.

CROSS APPLY (SELECT TOP 1 * FROM dbo.LT
WHERE STATION_ID =t2.STATION_ID
AND Month_Year <=t1.DATE_TAKEN
ORDER BY Month_Year DESC)t3

In LT table there's no STATION_ID column

---

SELECT t1.MAIN_ID,t1.DATE_TAKEN,t1.TIME,
t1.DAILY_RAINFALL,
CASE WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=3 
THEN t3.[3MthCumRf]
WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=6 
THEN t3.[6MthCumRf]
WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=9 
THEN t3.[9MthCumRf]
WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=12 
THEN t3.[12MthCumRf]
END AS CumRf 
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY DATE_TAKEN,MAIN_ID 
ORDER BY DAILY_RAINFALL DESC) AS RowNo,* FROM dbo.SEL)t1
INNER JOIN dbo.STATION_INFO t2 
ON t2.MAIN_ID=t1.MAIN_ID 
AND t1.RowNo=1 
CROSS APPLY (SELECT TOP 1 * FROM dbo.LT 
             WHERE MAIN_ID =t2.MAIN_ID
             AND Month_Year <=t1.DATE_TAKEN
             ORDER BY Month_Year DESC)t3 
WHERE t2.STATE='SEL'
AND t1.DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-3,GETDATE()),101)
AND t1.DATE_TAKEN<CONVERT(VARCHAR(10), GETDATE(), 101)
ORDER BY MAIN_ID,DATE_TAKEN,TIME

quote:

---

Originally posted by wkm1925

where should i put
SELECT t1.MAIN_ID,t1.DATE_TAKEN,t1.TIME,
t1.DAILY_RAINFALL
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY DATE_TAKEN,MAIN_ID
ORDER BY DAILY_RAINFALL DESC) AS RowNo,* FROM dbo.SEL)t1
INNER JOIN dbo.STATION_INFO t2 ON t2.MAIN_ID=t1.MAIN_ID AND
t1.RowNo=1
AND t1.DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-3,GETDATE()),101)
AND t1.DATE_TAKEN<CONVERT(VARCHAR(10), GETDATE(), 101)
WHERE t2.STATE='SEL'

in query

SELECT CAST(CAST(m.Month AS varchar(2)) + '/1/' + CAST(m.Year AS varchar(4)) AS datetime) AS Date_Taken,m.Main_ID,m.Daily_Rainfall
FROM
(SELECT MONTH(s.Date_Taken) AS Month,
YEAR(s.Date_Taken) AS Year,
Main_ID,
SUM(s.Daily_Rainfall) As Daily_Rainfall
FROM SEL s
INNER JOIN Station_Info si
ON si.Main_ID=s.Main_ID
WHERE si.State='SEL'
GROUP BY MONTH(s.Date_Taken),YEAR(s.Date_Taken),Main_ID)m
ORDER BY Main_ID,Date_Taken

---

quote:

---

Originally posted by wkm1925

i'm try copy and paste, then run. it shows an error as follow

Msg 1033, Level 15, State 1, Line 29
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Msg 102, Level 15, State 1, Line 57
Incorrect syntax near 'SEL'.

---

DECLARE @MonthList varchar(8000),@Sql varchar(8000)

SELECT @MonthList=LEFT(ml.MonthList,LEN(ml.MonthList)-1)
FROM (
SELECT Date_Taken + ',' AS [text()]
FROM
(
SELECT CAST(m.Month AS varchar(2)) + '/1/' + CAST(m.Year AS varchar(4)) AS 
Date_Taken,m.Main_ID,m.Daily_Rainfall
FROM
(
SELECT MONTH(t.Date_Taken) AS Month,
YEAR(t.Date_Taken) AS Year,
t.Main_ID,
SUM(t.Daily_Rainfall) As Daily_Rainfall
FROM 
(
SELECT t1.MAIN_ID,t1.DATE_TAKEN,t1.TIME,
t1.DAILY_RAINFALL 
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY DATE_TAKEN,MAIN_ID 
ORDER BY DAILY_RAINFALL DESC) AS RowNo,* FROM dbo.SEL)t1
INNER JOIN dbo.STATION_INFO t2 ON t2.MAIN_ID=t1.MAIN_ID AND 
t1.RowNo=1
AND t1.DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-3,GETDATE()),101)
AND t1.DATE_TAKEN<CONVERT(VARCHAR(10), GETDATE(), 101)
WHERE t2.STATE='SEL')t
GROUP BY MONTH(t.Date_Taken),YEAR(t.Date_Taken),t.Main_ID)m
ORDER BY Date_Taken DESC,Main_ID ASC)r
WHERE r.Date_Taken<=@Date
FOR XML PATH('')
)ml(MonthList)


SELECT @Sql='
SELECT * FROM
(
SELECT CAST(CAST(m.Month AS varchar(2)) + '/1/' + CAST(m.Year AS varchar(4)) AS datetime) AS 
Date_Taken,m.Main_ID,m.Daily_Rainfall
FROM
(
SELECT MONTH(t.Date_Taken) AS Month,
YEAR(t.Date_Taken) AS Year,
t.Main_ID,
SUM(t.Daily_Rainfall) As Daily_Rainfall
FROM 
(
SELECT t1.MAIN_ID,t1.DATE_TAKEN,t1.TIME,
t1.DAILY_RAINFALL 
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY DATE_TAKEN,MAIN_ID 
ORDER BY DAILY_RAINFALL DESC) AS RowNo,* FROM dbo.SEL)t1
INNER JOIN dbo.STATION_INFO t2 ON t2.MAIN_ID=t1.MAIN_ID AND 
t1.RowNo=1
AND t1.DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-3,GETDATE()),101)
AND t1.DATE_TAKEN<CONVERT(VARCHAR(10), GETDATE(), 101)
WHERE t2.STATE='SEL')t
GROUP BY MONTH(t.Date_Taken),YEAR(t.Date_Taken),t.Main_ID)m
ORDER BY Date_Taken DESC,Main_ID ASC)r
PIVOT SUM(r.Daily_Rainfall) FOR r.Date_Taken IN (['+ REPLACE(@MonthList,',','],[') + '])p
ORDER BY r.Date_Taken DESC,r.Main_ID'

EXEC(@Sql)

DECLARE @MonthList varchar(8000),@Sql varchar(8000)

SELECT @MonthList=LEFT(ml.MonthList,LEN(ml.MonthList)-1)
FROM (
SELECT Date_Taken + ',' AS [text()]
FROM
(
SELECT TOP 100 PERCENT CAST(m.Month AS varchar(2)) + '/1/' + CAST(m.Year AS varchar(4)) AS 
Date_Taken,m.Main_ID,m.Daily_Rainfall
FROM
(
SELECT MONTH(t.Date_Taken) AS Month,
YEAR(t.Date_Taken) AS Year,
t.Main_ID,
SUM(t.Daily_Rainfall) As Daily_Rainfall
FROM 
(
SELECT t1.MAIN_ID,t1.DATE_TAKEN,t1.TIME,
t1.DAILY_RAINFALL 
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY DATE_TAKEN,MAIN_ID 
ORDER BY DAILY_RAINFALL DESC) AS RowNo,* FROM dbo.SEL)t1
INNER JOIN dbo.STATION_INFO t2 ON t2.MAIN_ID=t1.MAIN_ID AND 
t1.RowNo=1
AND t1.DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-3,GETDATE()),101)
AND t1.DATE_TAKEN<CONVERT(VARCHAR(10), GETDATE(), 101)
WHERE t2.STATE='SEL')t
GROUP BY MONTH(t.Date_Taken),YEAR(t.Date_Taken),t.Main_ID)m
ORDER BY Date_Taken DESC,Main_ID ASC)r
WHERE r.Date_Taken<=@Date
FOR XML PATH('')
)ml(MonthList)


SELECT @Sql='
SELECT * FROM
(
	SELECT CAST(CAST(m.Month AS varchar(2)) + '''/1/''' + CAST(m.Year AS varchar(4)) AS datetime) AS 
	Date_Taken,m.Main_ID,m.Daily_Rainfall
	FROM
	(
		SELECT MONTH(t.Date_Taken) AS Month,
		YEAR(t.Date_Taken) AS Year,
		t.Main_ID,
		SUM(t.Daily_Rainfall) As Daily_Rainfall
		FROM 
		(
			SELECT t1.MAIN_ID,t1.DATE_TAKEN,t1.TIME,
			t1.DAILY_RAINFALL 
			FROM
			(SELECT ROW_NUMBER() OVER(PARTITION BY DATE_TAKEN,MAIN_ID 
			ORDER BY DAILY_RAINFALL DESC) AS RowNo,* FROM dbo.SEL)t1
			INNER JOIN dbo.STATION_INFO t2 ON t2.MAIN_ID=t1.MAIN_ID AND 
			t1.RowNo=1
			AND t1.DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-3,GETDATE()),101)
			AND t1.DATE_TAKEN<CONVERT(VARCHAR(10), GETDATE(), 101)
			WHERE t2.STATE=''SEL''
		)t
		GROUP BY MONTH(t.Date_Taken),YEAR(t.Date_Taken),t.Main_ID
	)m
)r
PIVOT SUM(r.Daily_Rainfall) FOR r.Date_Taken IN (['+ REPLACE(@MonthList,',','],[') + '])p
ORDER BY r.Date_Taken DESC,r.Main_ID'

EXEC(@Sql)