select top 10 * from aw_districts
select * from aw_districts

select count(*) from aw_daily_actual_weather
select count(*) from aw_daily_actual_weather_bak

select * from aw_daily_actual_weather_bak
select min(issued_on), max(issued_on) from aw_daily_actual_weather


select * from aw_district_imd_station_mapping

select top 5 * from aw_district_weather_forecast
--select top 1 * into aw_district_weather_forecast_2008 from aw_district_weather_forecast 
--nsert into aw_district_weather_forecast_2008 select * from aw_district_weather_forecast where issued_on between '2008-01-01' and '2008-12-31'
--nsert into aw_district_weather_forecast_2009 select * from aw_district_weather_forecast where issued_on between '2009-01-01' and '2009-12-31'
--nsert into aw_district_weather_forecast_2010 select * from aw_district_weather_forecast where issued_on between '2010-01-01' and '2010-12-31'
--nsert into aw_district_weather_forecast_2011 select * from aw_district_weather_forecast where issued_on between '2011-01-01' and '2011-12-31'
--nsert into aw_district_weather_forecast_2012 select * from aw_district_weather_forecast where issued_on between '2012-01-01' and '2012-12-31'

select count(*) from aw_district_weather_forecast where issued_on between '2012-01-01' and '2012-12-31'
select count(*) from aw_district_weather_forecast_2012
select min(issued_on), max(issued_on) from aw_district_weather_forecast where issued_on between '2012-01-01' and '2012-12-31'
select min(issued_on), max(issued_on) from aw_district_weather_forecast_2012
--elete from aw_district_weather_forecast where issued_on between '2011-01-01' and '2011-12-31'

SELECT MAX(x.custid),  -- change to MAX if you want the highest
         x.custname, 
         x.total
    FROM test x
    JOIN (SELECT p.custname,
                 MIN(total) AS max_total
            FROM test p
        GROUP BY p.custname) y ON y.custname = x.custname
                              AND y.max_total = x.total
GROUP BY x.custname, x.total

/* Following works perfectly */
SELECT x.custid,  -- change to MAX if you want the highest
         x.custname, 
         x.total, x.saledate
    FROM test x
    JOIN (SELECT p.custname,
                 MIN(total) AS max_total
            FROM test p
        GROUP BY p.custname) y ON y.custname = x.custname
                              AND y.max_total = x.total
--GROUP BY x.custname, x.total

select top 3* from aw_district_weather_forecast_2012

/* Using the above working perfectly sql as model for doing the weather search */
SELECT x.forecast_id, x.district_id, x.issued_on, x.forecast_for_date, x.rainfall, x.max_temp, x.min_temp
    FROM aw_district_weather_forecast_2012 x
    JOIN (SELECT p.district_id, p.issued_on,
                 MIN(forecast_for_date) AS min_fcst_date
            FROM aw_district_weather_forecast_2012 p
        GROUP BY p.district_id, p.issued_on) y ON y.district_id = x.district_id
                              AND y.min_fcst_date = x.forecast_for_date
ORDER BY 2,3,4

select top 50* from aw_district_weather_forecast_2012 where issued_on = forecast_for_date-1
order by 1,3,2,4
select count(*) from aw_district_weather_forecast_2012
select count(*) from bbg_district_weather_forecast_2012
select count(*) from aw_district_weather_forecast_2012 where forecast_for_date-issued_on=1
select count(*) from aw_district_weather_forecast_2012 where forecast_for_date-issued_on=2

select top 5* from aw_districts where district_id < 1

select count(*) from bbg_district_weather_forecast_2012 where district_id=50
select count(*) from bbg_district_weather_forecast_2012 where district_id=460

select * from aw_imd_stations

select * from aw_district_imd_station_mapping order by 2,1


select * from bbg_district_weather_forecast_2012 where district_id = 0 order by 2,3,4

select I.imd_station_name, W.issued_on, W.rainfall, W.max_temp, W.min_temp 
from aw_imd_stations I, bbg_district_weather_forecast_2012 W, aw_district_imd_station_mapping M
where W.district_id=M.district_id and M.station_id=I.imd_station_id and W.issued_on between '2012-05-01' and '2012-05-15'
order by 2,1


select top 1* into bbg_district_weather_forecast_2012 from aw_district_weather_forecast_2012
--elete from bbg_district_weather_forecast_2012
--nsert into bbg_district_weather_forecast_2012 select * from aw_district_weather_forecast_2012 where forecast_for_date-issued_on=1


/*
--Mapping stations to district final:
INSERT INTO aw_district_imd_station_mapping VALUES(470, 1);
INSERT INTO aw_district_imd_station_mapping VALUES(471, 2);
INSERT INTO aw_district_imd_station_mapping VALUES(117, 3);
INSERT INTO aw_district_imd_station_mapping VALUES(299, 4);
INSERT INTO aw_district_imd_station_mapping VALUES(473, 5);
INSERT INTO aw_district_imd_station_mapping VALUES(142, 6);
INSERT INTO aw_district_imd_station_mapping VALUES(246, 7);
INSERT INTO aw_district_imd_station_mapping VALUES(386, 8);
INSERT INTO aw_district_imd_station_mapping VALUES(3, 9);
INSERT INTO aw_district_imd_station_mapping VALUES(301, 10);
INSERT INTO aw_district_imd_station_mapping VALUES(479, 11);
INSERT INTO aw_district_imd_station_mapping VALUES(357, 12);
INSERT INTO aw_district_imd_station_mapping VALUES(207, 13);
INSERT INTO aw_district_imd_station_mapping VALUES(484, 14);
INSERT INTO aw_district_imd_station_mapping VALUES(121, 15);
INSERT INTO aw_district_imd_station_mapping VALUES(208, 16);
INSERT INTO aw_district_imd_station_mapping VALUES(64, 17);
INSERT INTO aw_district_imd_station_mapping VALUES(251, 18);
INSERT INTO aw_district_imd_station_mapping VALUES(373, 19);
INSERT INTO aw_district_imd_station_mapping VALUES(130, 20);
INSERT INTO aw_district_imd_station_mapping VALUES(410, 21);
INSERT INTO aw_district_imd_station_mapping VALUES(95, 22);
INSERT INTO aw_district_imd_station_mapping VALUES(460, 23);
INSERT INTO aw_district_imd_station_mapping VALUES(214, 24);
INSERT INTO aw_district_imd_station_mapping VALUES(440, 25);
INSERT INTO aw_district_imd_station_mapping VALUES(441, 26);
INSERT INTO aw_district_imd_station_mapping VALUES(543, 27);
INSERT INTO aw_district_imd_station_mapping VALUES(166, 28);
INSERT INTO aw_district_imd_station_mapping VALUES(43, 29);
INSERT INTO aw_district_imd_station_mapping VALUES(218, 30);
INSERT INTO aw_district_imd_station_mapping VALUES(417, 31);
INSERT INTO aw_district_imd_station_mapping VALUES(435, 32);
INSERT INTO aw_district_imd_station_mapping VALUES(10, 33);
INSERT INTO aw_district_imd_station_mapping VALUES(68, 34);
INSERT INTO aw_district_imd_station_mapping VALUES(115, 35);
INSERT INTO aw_district_imd_station_mapping VALUES(382, 36);
INSERT INTO aw_district_imd_station_mapping VALUES(259, 37);
INSERT INTO aw_district_imd_station_mapping VALUES(48, 38);
INSERT INTO aw_district_imd_station_mapping VALUES(260, 39);
INSERT INTO aw_district_imd_station_mapping VALUES(147, 40);
INSERT INTO aw_district_imd_station_mapping VALUES(18, 41);
INSERT INTO aw_district_imd_station_mapping VALUES(339, 42);
INSERT INTO aw_district_imd_station_mapping VALUES(263, 43);
INSERT INTO aw_district_imd_station_mapping VALUES(264, 44);
INSERT INTO aw_district_imd_station_mapping VALUES(102, 45);
INSERT INTO aw_district_imd_station_mapping VALUES(419, 46);
INSERT INTO aw_district_imd_station_mapping VALUES(420, 47);
INSERT INTO aw_district_imd_station_mapping VALUES(559, 48);
INSERT INTO aw_district_imd_station_mapping VALUES(178, 49);
INSERT INTO aw_district_imd_station_mapping VALUES(192, 50);
INSERT INTO aw_district_imd_station_mapping VALUES(369, 51);
INSERT INTO aw_district_imd_station_mapping VALUES(424, 52);
INSERT INTO aw_district_imd_station_mapping VALUES(6, 53);
INSERT INTO aw_district_imd_station_mapping VALUES(571, 54);
INSERT INTO aw_district_imd_station_mapping VALUES(233, 55);
INSERT INTO aw_district_imd_station_mapping VALUES(558, 56);
INSERT INTO aw_district_imd_station_mapping VALUES(426, 58);
INSERT INTO aw_district_imd_station_mapping VALUES(239, 59);
INSERT INTO aw_district_imd_station_mapping VALUES(11, 60);
INSERT INTO aw_district_imd_station_mapping VALUES(514, 61);
INSERT INTO aw_district_imd_station_mapping VALUES(10, 62);
INSERT INTO aw_district_imd_station_mapping VALUES(222, 63);
INSERT INTO aw_district_imd_station_mapping VALUES(560, 64);
INSERT INTO aw_district_imd_station_mapping VALUES(230, 65);
INSERT INTO aw_district_imd_station_mapping VALUES(246, 66);
INSERT INTO aw_district_imd_station_mapping VALUES(265, 68);
INSERT INTO aw_district_imd_station_mapping VALUES(449, 69);
INSERT INTO aw_district_imd_station_mapping VALUES(315, 70);
INSERT INTO aw_district_imd_station_mapping VALUES(113, 72);
INSERT INTO aw_district_imd_station_mapping VALUES(554, 74);
INSERT INTO aw_district_imd_station_mapping VALUES(215, 75);
INSERT INTO aw_district_imd_station_mapping VALUES(27, 76);
INSERT INTO aw_district_imd_station_mapping VALUES(400, 77);
INSERT INTO aw_district_imd_station_mapping VALUES(82, 78);
INSERT INTO aw_district_imd_station_mapping VALUES(98, 79);
INSERT INTO aw_district_imd_station_mapping VALUES(1, 80);
INSERT INTO aw_district_imd_station_mapping VALUES(321, 81);
INSERT INTO aw_district_imd_station_mapping VALUES(83, 82);
INSERT INTO aw_district_imd_station_mapping VALUES(111, 83);
INSERT INTO aw_district_imd_station_mapping VALUES(137, 84);
INSERT INTO aw_district_imd_station_mapping VALUES(8, 85);
INSERT INTO aw_district_imd_station_mapping VALUES(202, 86);
INSERT INTO aw_district_imd_station_mapping VALUES(323, 87);
INSERT INTO aw_district_imd_station_mapping VALUES(280, 88);
INSERT INTO aw_district_imd_station_mapping VALUES(292, 89);
INSERT INTO aw_district_imd_station_mapping VALUES(171, 90);
INSERT INTO aw_district_imd_station_mapping VALUES(326, 91);
INSERT INTO aw_district_imd_station_mapping VALUES(185, 92);
INSERT INTO aw_district_imd_station_mapping VALUES(170, 93);
INSERT INTO aw_district_imd_station_mapping VALUES(139, 94);
INSERT INTO aw_district_imd_station_mapping VALUES(58, 95);
INSERT INTO aw_district_imd_station_mapping VALUES(244, 96);
INSERT INTO aw_district_imd_station_mapping VALUES(462, 97);
INSERT INTO aw_district_imd_station_mapping VALUES(434, 98);
INSERT INTO aw_district_imd_station_mapping VALUES(128, 99);
INSERT INTO aw_district_imd_station_mapping VALUES(10, 100);
INSERT INTO aw_district_imd_station_mapping VALUES(20, 101);
-- End
*/





SELECT p.district_id, p.issued_on,
                 MIN(forecast_for_date) AS min_fcst_date
            FROM aw_district_weather_forecast_2012 p
        GROUP BY p.district_id, p.issued_on
order by 2,1,3

/* not working as expected
SELECT x.district_id, x.issued_on, MIN(x.forecast_for_date), x.max_temp, x.min_temp FROM aw_district_weather_forecast_2012 x
JOIN (SELECT p.district_id, p.issued_on, MIN(p.forecast_for_date) as min_forecast_for_date FROM aw_district_weather_forecast_2012 p
WHERE p.issued_on between '2012-12-01' and '2012-12-06 00:00:01'
GROUP BY p.district_id, p.issued_on) y ON y.district_id = x.district_id
				      AND y.issued_on = x.issued_on
				      AND y.min_forecast_for_date = x.forecast_for_date
GROUP BY x.district_id, x.issued_on, x.max_temp, x.min_temp
*/ORDER BY x.district_id, x.issued_on, x.forecast_for_date)




