Calculating the AVG value per GROUP in the GROUP BY Clause

I'm working on a query in SQL Server 2005 that looks at a table of recorded phone calls, groups them by the hour of the day, and computes the average wait time for each hour in the day.

I have a query that I think works, but I'm having trouble convincing myself it's right.

SELECT
    DATEPART(HOUR, CallTime) AS Hour,
    (AVG(calls.WaitDuration) / 60) AS WaitingTimesInMinutes
FROM (
    SELECT
        CallTime,
        WaitDuration
    FROM Calls
    WHERE DATEADD(day, DATEDIFF(Day, 0, CallTime), 0) = DATEADD(day, DATEDIFF(Day, 0, GETDATE()), 0)
        AND DATEPART(HOUR, CallTime) BETWEEN 6 AND 18
) AS calls
GROUP BY DATEPART(HOUR, CallTime)
ORDER BY DATEPART(HOUR, CallTime);

To clarify what I think is happening, this query looks at all calls made on the same day as today, and where the hour of the call is between 6 and 18 -- the times are recorded and SELECTed in 24-hour time, so this between hours is to get calls between 6am and 6pm.

Then, the outer query computes the average of the WaitDuration column (and converts seconds to minutes) and then groups each average by the hour.

What I'm uncertain of is this: Are the reported BY HOUR averages only for the calls made in that hour's timeframe? Or does it compute each reported average using all the calls made on the day and between the hours? I know the AVG function has a optional OVER/PARTITION clause, and it's been a while since I used the AVG group function. What I would like is that each result grouped by an hour shows ONLY the average wait time for that specific hour of the day.

Thanks for your time in this.

Bring through a newly created calculated column in another query

I have 2 separate queries below which run correctly.Now I've created a calculated column to provide a count of working days by YMs and would like to bring this through to query1(the join would be query1.Period = query2.Yms) please see the query and outputs below.

SELECT        Client, ClientGroup, Type, Value, Period, PeriodName, PeriodNumber, ClientName
FROM            metrics.dbo.vw_KPI_001_Invoice 



select YMs,sum(case when IsWorkDay = 'X' then 1 else 0 end) from IESAONLINE.Dbo.DS_Dates
where Year > '2013'
group by YMs 

Query 1
Client  ClientGroup Type    Value   Period  PeriodName        PeriodNumber   ClientName
0LG0    KarroFoods  Stock   5691.68 201506  Week 06 2015    35  Karro Foods Scunthorpe

Query 2
YMs (No column name)
201401  23

can not select Id of a persian value from the microsoft sql server in android

i have MSSQL DB,wich is SQL_Latin1_General_CP1_CI_AS collation and filled out with some persian values, the type of the field stored in DB is String(nvarchar(50), null) I want to get the ID of this String,that is Integer. so i open my Connection and write this in my code :

                ConnectionHelper connectionHelper = new ConnectionHelper();
                statement2 = connectionHelper.getConnection().createStatement();
                setType();
                ResultSet resultSet = statement2.executeQuery("select Id from tblProductCategory where Name='" + getType() + "'");

But i couldnt. getType() return a string in persion from my GridView.

After hours, i insert Latin String in my tblProductCategory and select its ID, and it worked correctly. My problem is with Persian Language. i searched and find that mssql server doesnt have utf-8, but nvarchar support this. any Idea?

MS SQL try catch ignored when executed by php mssql_query

I simplified my problem with a simple stored procedure in MS SQL 2005 with a try catch block.

It goes like that:

CREATE PROCEDURE testError

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    BEGIN TRY
    DECLARE @X INT
    ---- Divide by zero to generate Error
        SET @X = 1/0
        select 'Command after error in TRY block' AS retour
    END TRY
    BEGIN CATCH
        select 'Error Detected' AS retour
        return
    END CATCH
    select 'Command after TRY/CATCH blocks' AS retour
END

When I execute it in management studio, I get the following result: Error Detected, which is what I want.

But when I execute it in PHP with mssql_query, I get the following result: Command after error in TRY block

Why my catch block is not triggered when an SQL error is encountered?

I want SQL to stop further execution and go to the catch block, as it does in management studio.

Thanks a lot for your help!

Convert time from British to American format

I have the below code which is failing at the 'CAST('10-10-2014' AS DATETIME)', please can someone assist?

SELECT Sum(poval)
FROM   iesa_dwhs.dbo.vw_an_purch_bkb_010_sources vw_AN_Purch_BKB_010_Sources
WHERE  Upper(plant) = Upper(('0LH0'))
       AND dt BETWEEN Cast('10-10-2014' AS DATETIME) AND Getdate() - 7
       AND Upper(matcat) = 'CODED'

Advanced sql with windowing claus

SELECT a.*,
       SUM(s.amount) over(ORDER BY s.month rows unbounded preceding) AS a ,
       SUM(s.amount) over(PARTITION BY s.month ORDER BY s.month rows unbounded preceding) AS b,
       SUM(s.amount) over(PARTITION BY s.month ) AS c_1,
       SUM(s.amount) over(PARTITION BY s.month ORDER BY s.month rows BETWEEN unbounded preceding AND unbounded following) AS c,
       SUM(s.amount) over(PARTITION BY s.month ORDER BY s.month rows BETWEEN 1 preceding AND unbounded following) AS d,
       SUM(s.amount) over(PARTITION BY s.month ORDER BY s.month rows BETWEEN 1 preceding AND 1 following) AS e,
       SUM(s.amount) over(PARTITION BY s.month ORDER BY s.month rows BETWEEN unbounded preceding AND 1 following) AS f,
       SUM(s.amount) over(PARTITION BY s.month ORDER BY s.month rows CURRENT ROW) AS g
  FROM all_sales s,
       (SELECT *
      FROM all_sales) a
 WHERE s.rowid = a.rowid;

/ --above query give the result shown below what is difference between c_1 and c column.

    YEAR  MONTH PRD_TYPE_ID EMP_ID  AMOUNT  A   B   C_1 C   D   E   F   G
1   2006    1   1             21    1.00    1   1   10  10  10  3   3   1
2   2006    1   1             21    2.00    3   3   10  10  10  6   6   2
3   2005    1   2             21    3.00    6   6   10  10  9   9   10  3
4   2005    1   2             22    4.00    10  10  10  10  7   7   10  4
5   2006    2   1             21    5.00    15  5   11  11  11  11  11  5
6   2005    2   1             21    6.00    21  11  11  11  11  11  11  6
7   2005    3   1             21            21      7   7   7   7   7   
8   2006    3   2             21    7.00    28  7   7   7   7   7   7   7
9   2005    4   1             21    8.00    36  8   17  17  17  17  17  8
10  2006    4   2             21    9.00    45  17  17  17  17  17  17  9
11  2006    5   2             21            45      10  10  10  10  10  
12  2005    5   1             21    10.00   55  10  10  10  10  10  10  10
13  2006    6   1             21    11.00   66  11  23  23  23  23  23  11
14  2005    6   1             21    12.00   78  23  23  23  23  23  23  12
15  2005    7   2             21    13.00   91  13  27  27  27  27  27  13
16  2006    7   1             21    14.00   105 27  27  27  27  27  27  14
17  2005    8   2             21    15.00   120 15  31  31  31  31  31  15
18  2006    8   1             21    16.00   136 31  31  31  31  31  31  16
19  2005    9   2             21    17.00   153 17  35  35  35  35  35  17
20  2006    9   1             21    18.00   171 35  35  35  35  35  35  18
21  2005    10  2             21    19.00   190 19  39  39  39  39  39  19
22  2006    10  1             21    20.00   210 39  39  39  39  39  39  20
23  2006    11  1             21    21.00   231 21  43  43  43  43  43  21
24  2005    11  1             21    22.00   253 43  43  43  43  43  43  22
25  2006    12  2             21    23.00   276 23  47  47  47  47  47  23
26  2005    12  1             21    24.00   300 47  47  47  47  47  47  24

TSQL Null data to '' replace

I have the following query:

SELECT pics.e_firedate FROM et_pics

Result:

NULL
2014-12-01 00:00:00.000
2015-04-03 00:00:00.000
NULL
NULL

I want to replace NULL values to ''.

CASE 
        WHEN pics.e_firedate IS NULL THEN ''
        ELSE pics.e_firedate
END

makes NULL transforming to 1900-01-01 00:00:00.000, which I've tried to cast and replace with no success also.

How can I achive my goal?