CODEWITHSUNDEEP
sql-server
Rahul
Rahul

Reputation: 5636

How to make Query for that

I have a table structure from there i have to make a query in some different way

Table Structure

  1. id unique identifier

  2. code varchar(5) Not Null

  3. Recording_date Datetime Not Null

  4. Max_Temp numeric(5,2) Not Null

  5. Min_Temp numeric(5,2) Not Null

We have some data as well in this table.We have data only for 2013 year and for first 3 months. But the main thing is that i have to return's data in such a format like

enter image description here

Please help me to create a query for such a logic.

Thanks in advance.

Upvotes: 0

Views: 69

Answers (3)

T I
T I

Reputation: 9933

Presuming you have one recording per day then 

SELECT
    DATEPART(m, Month_Start) + ' ' + DATEPART(yyyy, Month_Start)
    , Max_Temp_Days
    , CASE
        WHEN Denominator = 0 THEN 0
        ELSE (Max_Temp_Days / Denominator) * 100
      END AS Percent_Max_Temp_Days
    , Min_Temp_Days
    , CASE
        WHEN Denominator = 0 THEN 0
        ELSE (Min_Temp_Days / Denominator) * 100
      END AS Percent_Max_Temp_Days
FROM (
    SELECT 
        DATEADD(MONTH, DATEDIFF(MONTH, 0, Recording_Date), 0) Month_Start
        , Sum(CASE WHEN Max_Temp <= 0 THEN 1 END) Max_Temp_Days
        , Sum(CASE WHEN Min_Temp <= 0 THEN 1 END) Min_Temp_Days
        , COUNT(*) Denominator
    FROM TemperatureRecordings
    WHERE Recording_Date BETWEEN '2013-01-01' AND '2013-03-31'
    GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Recording_Date), 0)
) t
ORDER BY Month_Start

Upvotes: 1

Ian Bruce
Ian Bruce

Reputation: 13

SELECT 
      MONTH(Recording_date),
      SUM(CASE WHEN Max_Temp <= 0 THEN 1 ELSE 0 END), 
      SUM(CASE WHEN Max_Temp <= 0 THEN 1 ELSE 0 END) / COUNT(*),
      SUM( CASE WHEN Min_Temp <= 0 THEN 1 ELSE 0 END ),
      SUM( CASE WHEN Min_Temp <= 0 THEN 1 ELSE 0 END )  / COUNT(*)
FROM temperatures
GROUP BY MONTH(Recording_date)

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

This works for all month data

DECLARE @maxTempratureTable table(monthName varchar(20), [No. of days Max<=0] int,[Percentage Max <=0] float)
  INSERT INTO maxTempratureTable 
  SELECT RIGHT(CONVERT(VARCHAR, Recording_date, 106), 8) , 
           COUNT(*) ,
           COUNT(*) / DAY(DATEADD(mm,DATEDIFF(mm,-1,Recording_date),-1)) * 100 

    FROM tablename
    WHERE Max_Temp <=0
    GROUP BY RIGHT(CONVERT(VARCHAR, Recording_date, 106), 8)

DECLARE @minTempratureTable table(monthName varchar(20), [No. of days Min<=0] int,[Percentage Min<=0] float)
  INSERT INTO @minTempratureTable 
  SELECT RIGHT(CONVERT(VARCHAR, Recording_date, 106), 8) , 
           COUNT(*) ,
           COUNT(*) / DAY(DATEADD(mm,DATEDIFF(mm,-1,Recording_date),-1)) * 100 

    FROM tablename
    WHERE Min_Temp <=0
    GROUP BY RIGHT(CONVERT(VARCHAR, Recording_date, 106), 8)


SELECT * FROM @minTempratureTable min
INNER JOIN @maxTempratureTable max
ON min.monthName = max.monthName

Upvotes: 1

Related Questions