Query problem

Question

Post reply

Query problem

Delinda

SSC Rookie

Points: 26
More actions
June 10, 2008 at 9:08 am

#188943

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 had try almost hundred time to adjust the above SQL but still fail. Please help me, i'm really-really stuck. :sick:

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I think the join you are looking for is:

left outer join longterm_rf_temp

on date_taken = dateadd(month, -1, month_year) + 1

and ?.main_id = longterm_rf_temp.main_id

You'll need to replace the question mark with the name of the table or query that you're joining it to, but once you do that, it should work.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Delinda SSC Rookie Points: 26 More actions · Answer 2

After follow your advice, this my 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

----------------------------------------------------------------------------------------------------------

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?

GSquared SSC Guru Points: 260824 More actions · Answer 3

Change the formula to:

dateadd(day, -1 * datepart(day, ltrf.month_year), ltrf.month_year) + 1

That should do it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon