Query problem - To many joint - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Query problem - To many joint

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2008-06-10 : 22:40:24

CREATE TABLE [dbo].[SEL](
[MAIN_ID] [int] NULL,
[DATE_TAKEN] [smalldatetime] NULL,
[TIME] [int] NULL,
[DAILY_RAINFALL] [int] NULL
) ON [PRIMARY]

insert into SEL
values(194,'6/1/2006 12:00:00 AM',730,11)
insert into SEL
values(194,'6/1/2006 12:00:00 AM',930,4)
insert into SEL
values(194,'6/1/2006 12:00:00 AM',1830,10)
insert into SEL
values(194,'6/1/2006 12:00:00 AM',1930,20)
insert into SEL
values(194,'6/1/2006 12:00:00 AM',2130,14)
insert into SEL
values(194,'6/1/2006 12:00:00 AM',2230,0)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',730,22)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',930,43)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',1830,0)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',1930,54)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',2130,21)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',2230,6)

CREATE TABLE [dbo].[station_info](
[STATE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STATION_NAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MAIN_ID] [int] NOT NULL
) ON [PRIMARY]

insert into station_info
values('SEL','PUCHONG',196)
insert into station_info
values('JHR','BulohKasap',5)

Above script showing table as follow
SEL
MAIN_ID | DATE_TAKEN | TIME | DAILY_RAINFALL
--------------------------------------------------------
194 | 6/1/2006 12:00:00 AM | 730 | 11
194 | 6/1/2006 12:00:00 AM | 930 | 4
..........
..........
202 | 6/1/2006 12:00:00 AM | 450 | 23
..........
..........
*This table storing DAILY_RAINFALL everyday from time to time for each MAIN_ID.

station_info
STATE | STATION_NAME | MAIN_ID
---------------------------------------
SEL | Puchong | 194
JHR | BulohKasap | 5
.........
.........
*This table storing MAIN_ID description. Main_ID is a primary key.

1. 1 day having many TIME. So, we only take which time having MAX(DAILY_RAINFALL) per day per MAIN_ID to do the SUM(DAILY_RAINFALL) for that month.

So far, i've this 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(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,-12,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 Main_ID,Date_Taken
*Assume GETDATE()=6/10/2007

After run above SQL, I got below resultset,
DATE_TAKEN |MAIN_ID|DAILY_RAINFALL
---------------------------------------------------
2006-06-01 00:00:00.000 |194 |329
2006-07-01 00:00:00.000 |194 |160
2006-08-01 00:00:00.000 |194 |388
2007-04-01 00:00:00.000 |194 |394
...........
...........
2006-06-01 00:00:00.000 |195 |145
2006-07-01 00:00:00.000 |195 |82
2006-08-01 00:00:00.000 |195 |225
...........
...........

I'm stuck when joint table below. The purpose of joint is to pickup [cumrf1year] column
(if DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-12,GETDATE()),101)
AND t1.DATE_TAKEN<=CONVERT(VARCHAR(10), GETDATE(), 101))
and pickup [month_year] value depend on DATE_TAKEN (if DATE_TAKEN=6/1/2006, [month_year]=6/30/2006, if DATE_TAKEN=7/1/2006, [month_year]=7/31/2006).
MAIN_ID is foreign key to relate [longterm_rf_temp] table below.

CREATE TABLE [dbo].[longterm_rf_temp](
[main_id] [int] NOT NULL,
[month_year] [datetime] NULL,
[cumrf1mth] [float] NULL,
[cumrf3mth] [float] NULL,
[cumrf6mth] [float] NULL,
[cumrf9mth] [float] NULL,
[cumrf1year] [float] NULL
) ON [PRIMARY]

insert into longterm_rf_temp
values(194,'6/30/2006',207.94,550.7,850.7,1150.7,1450.7)
insert into longterm_rf_temp
values(194,'7/31/2006',200.64,590.4,858.7,1260.7,1550.7)
insert into longterm_rf_temp
values(194,'8/30/2006',222.64,390.4,958.7,1460.7,1750.7)
insert into longterm_rf_temp
values(195,'6/30/2006',217.94,550.7,840.7,1150.7,1324.7)
insert into longterm_rf_temp
values(195,'7/31/2006',202.64,590.4,858.7,1260.7,1659.7)
insert into longterm_rf_temp
values(195,'8/30/2006',222.64,490.4,958.7,1460.7,1733.7)

After joint, I should get below resultset,
DATE_TAKEN |MAIN_ID|DAILY_RAINFALL | [cumrf1year]| DiFF | DEV
----------------------------------------------------------------------------------
2006-06-01 00:00:00.000 |194 |329 | 1450.7 | -1121.7 | -0.773
2006-07-01 00:00:00.000 |194 |160 | 1550.7 | -1390.7 | -0.896
2006-08-01 00:00:00.000 |194 |388 | 1750.7
2007-04-01 00:00:00.000 |194 |394 | ......
...........
...........
2006-06-01 00:00:00.000 |195 |145 | 1324.7
2006-07-01 00:00:00.000 |195 |82 | 1659.7
2006-08-01 00:00:00.000 |195 |225 | 1733.7
...........
...........

This is the formula,
DIFF = DAILY_RAINFALL - [cumrf1year]
DEV = DIFF/[cumrf1year]

I almost give up to adjust the above SQL but still fail. Please help me, i'm really-really stuck.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 23:48:09

[code]SELECT main.DATE_TAKEN,
main.MAIN_ID,
main.DAILY_RAINFALL,
tmp.cumrf1year,
main.DAILY_RAINFALL-tmp.cumrf1year AS DIFF,
(main.DAILY_RAINFALL-tmp.cumrf1year)/tmp.cumrf1year AS DEV
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,-12,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 Main_ID,Date_Taken
)main
INNER JOIN longterm_rf_temp tmp
ON tmp.main_id=main.main_id
AND MONTH(tmp.month_year)=MONTH(main.DATE_TAKEN)
AND YEAR(tmp.month_year)=YEAR(main.DATE_TAKEN)[/code]

wkm1925
Posting Yak Master

207 Posts

Posted - 2008-06-11 : 01:17:33

SELECT main.DATE_TAKEN,
main.MAIN_ID,
main.DAILY_RAINFALL,
tmp.cumrf1year,
main.DAILY_RAINFALL-tmp.cumrf1year AS DIFF,
(main.DAILY_RAINFALL-tmp.cumrf1year)/tmp.cumrf1year AS DEV
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,-12,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 Main_ID,Date_Taken
)main
INNER JOIN longterm_rf_temp tmp
ON tmp.main_id=main.main_id
AND MONTH(tmp.month_year)=MONTH(main.DATE_TAKEN)
AND YEAR(tmp.month_year)=YEAR(main.DATE_TAKEN)
**got error,
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.

so far, i've below query
SELECT t3.DATE_TAKEN,t3.MAIN_ID,t3.DAILY_RAINFALL,ltrf.month_year,
ltrf.cumrf1year
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 WHERE TIME>=15)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,-12,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)t3

LEFT OUTER JOIN longterm_rf_temp ltrf
ON t3.DATE_TAKEN = dateadd(month, -1, ltrf.month_year) + 1
AND t3.MAIN_ID = ltrf.MAIN_ID
ORDER BY t3.Main_ID,t3.Date_Taken

Finally, i got what i'm expect, but having a logic error in resultset as follow
DATE_TAKEN | MAIN_ID | DAILY_RAINFALL | main_id | month_year | cumfr1year
----------------------------------------------------------------------------------------------------------
2006-06-01 00:00:00.000 |194 | 329 | NULL |NULL |NULL
2006-07-01 00:00:00.000 |194 | 160 | 194 |2006-07-31 00:00:00.000 |1550.7
.................
.................
.................
2006-06-01 00:00:00.000 |195 | 93 | NULL |NULL |NULL
2006-07-01 00:00:00.000 |195 | 82 | 195 |2006-07-31 00:00:00.000 |1659.7
.................
.................

I'm suspect,
if
DATE_TAKEN | MAIN_ID
-----------------------------------
2006-06-01 00:00:00.000 | 194

in ltrf table
month_year | cumrf1year
--------------------------------------
6/30/2006 12:00:00 AM | 1450.7
*it cannot take it this rows because of 6/30/2006 i guest.

but if
DATE_TAKEN | MAIN_ID
-----------------------------------
2006-07-01 00:00:00.000 | 194

in ltrf table
month_year | cumrf1year
--------------------------------------
7/31/2006 12:00:00 AM | 1550.7
*it successfull take out the rows

how to adjust
LEFT OUTER JOIN longterm_rf_temp ltrf
ON t3.DATE_TAKEN = dateadd(month, -1, ltrf.month_year) + 1
AND t3.MAIN_ID = ltrf.MAIN_ID
ORDER BY t3.Main_ID,t3.Date_Taken

to cater the above condition?

raky
Aged Yak Warrior

767 Posts

Posted - 2008-06-11 : 01:19:20

what is the error??please post error message..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 01:28:44

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 02:40:51

quote:

Originally posted by wkm1925

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
								) AS t1
						INNER JOIN	dbo.STATION_INFO AS t2 ON t2.MAIN_ID = t1.MAIN_ID
						WHERE		t1.RowNo = 1
								AND t1.DATE_TAKEN >= CONVERT(VARCHAR(10), DATEADD(m, -12, GETDATE()), 101)
								AND t1.DATE_TAKEN <= CONVERT(VARCHAR(10), GETDATE(), 101)
								AND t2.STATE = 'SEL'
					) AS t
			GROUP BY	MONTH(t.Date_Taken),
					YEAR(t.Date_Taken),
					t.Main_ID
		) AS m
ORDER BY	m.Main_ID,
		m.Date_Taken

This piece of code seems easier to read, understand and maintain

SELECT		DATEADD(MONTH, DATEDIFF(MONTH, '19000101', Date_Taken), '19000101') AS Month_Taken,
		Main_ID,
		SUM(Daily_Rainfall) AS Monthly_Rainfall
FROM		(
			SELECT		s.Main_ID,
					s.Date_Taken,
					MAX(s.Daily_Rainfall) AS Daily_Rainfall
			FROM		@Sel AS s
			INNER JOIN	@Station_Info AS si ON si.Main_ID = s.Main_ID
			WHERE		s.Date_Taken >= DATEADD(YEAR, -1, GETDATE())
					AND s.Date_Taken < GETDATE()
			GROUP BY	s.Main_ID,
					s.Date_Taken
		) AS d
GROUP BY	Main_ID,
		DATEDIFF(MONTH, '19000101', Date_Taken)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 02:58:05

[code]-- Prepare sample data
DECLARE @Sel TABLE
(
Main_ID INT,
Date_Taken SMALLDATETIME,
[Time] INT,
Daily_Rainfall INT
)

SET DATEFORMAT MDY

INSERT @Sel
SELECT 194, '11/1/2007', 730, 11 UNION ALL
SELECT 194, '11/1/2007', 930, 4 UNION ALL
SELECT 194, '11/2/2007', 1830, 10 UNION ALL
SELECT 194, '11/2/2007', 1930, 20 UNION ALL
SELECT 194, '12/3/2007', 2130, 14 UNION ALL
SELECT 194, '12/3/2007', 2230, 0 UNION ALL
SELECT 195, '11/1/2007', 730, 22 UNION ALL
SELECT 195, '11/1/2007', 930, 43 UNION ALL
SELECT 195, '11/2/2007', 1830, 0 UNION ALL
SELECT 195, '11/2/2007', 1930, 54 UNION ALL
SELECT 195, '12/3/2007', 2130, 21 UNION ALL
SELECT 195, '12/3/2007', 2230, 6

DECLARE @Station_Info TABLE
(
[State] VARCHAR(20),
Station_Name VARCHAR(50),
Main_ID INT
)

INSERT @Station_Info
SELECT 'SEL', 'PUCHONG', 194 UNION ALL
SELECT 'JHR', 'BulohKasap', 195

DECLARE @Longterm TABLE
(
Main_ID INT,
Month_Year DATETIME,
Cumrf1Mth SMALLMONEY,
Cumrf3Mth SMALLMONEY,
Cumrf6Mth SMALLMONEY,
Cumrf9Mth SMALLMONEY,
Cumrf1Year SMALLMONEY
)

INSERT @Longterm
SELECT 194, '9/30/2007', 207.94, 550.7, 850.7, 1150.7, 1450.7 UNION ALL
SELECT 194, '10/31/2007', 200.64, 590.4, 858.7, 1260.7, 1550.7 UNION ALL
SELECT 194, '11/30/2007', 222.64, 390.4, 958.7, 1460.7, 1750.7 UNION ALL
SELECT 195, '9/30/2007', 217.94, 550.7, 840.7, 1150.7, 1324.7 UNION ALL
SELECT 195, '10/31/2007', 202.64, 590.4, 858.7, 1260.7, 1659.7 UNION ALL
SELECT 195, '11/30/2007', 222.64, 490.4, 958.7, 1460.7, 1733.7[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 02:58:46

[code]-- Initialize CTE
;WITH Yak (Month_Taken, Main_ID, Monthly_Rainfall)
AS (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', Date_Taken), '19000101'),
Main_ID,
SUM(Daily_Rainfall)
FROM (
SELECT s.Main_ID,
s.Date_Taken,
MAX(s.Daily_Rainfall) AS Daily_Rainfall
FROM @Sel AS s
INNER JOIN @Station_Info AS si ON si.Main_ID = s.Main_ID
WHERE s.Date_Taken >= DATEADD(YEAR, -1, GETDATE())
AND s.Date_Taken < GETDATE()
GROUP BY s.Main_ID,
s.Date_Taken
) AS d
GROUP BY Main_ID,
DATEDIFF(MONTH, '19000101', Date_Taken)
)

-- Show expected output
SELECT y.Month_Taken,
y.Main_ID,
y.Monthly_Rainfall,
l.Cumrf1Year,
y.Monthly_Rainfall - l.Cumrf1Year AS Diff,
(y.Monthly_Rainfall - l.Cumrf1Year) / NULLIF(l.Cumrf1Year, 0) AS Dev
FROM Yak AS y
LEFT JOIN @Longterm AS l ON l.Main_ID = y.Main_ID
AND DATEDIFF(MONTH, l.Month_Year, y.Month_Taken) = 0
ORDER BY y.Main_ID,
y.Month_Taken[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 03:15:55

Also see http://www.dbforums.com/showthread.php?p=6343397&posted=1#post6343397

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 03:27:21

Hrrrm..
I see now that OP has posted same query on at least 4 other forums.
What a waste of our time...

E 12°55'05.25"
N 56°04'39.16"