Database

PIEBALDconsult wrote:

Sure it is. You should have the SQL do as much as possible and avoid having to do work in code. That's one of the functions of views.

jschell wrote:

I would say that without qualification that is not true.

jschell wrote:

producing a format that represents a 'report'. So it must of course provide
headers.

jschell wrote:

if I am writing a java/C# application whose point is to produce a report

PIEBALDconsult wrote:

I wrote such a reporting system on my last job and there is one at my new job. Neither has the ability to alter the column headings so it must all be done in the query. The engine simply outputs the contents of a DataTable.

I are Troll

PIEBALDconsult wrote:

That's generally a rather poor scheme, unless the reports are very specialized and something like Crystal ( ) won't do.

PIEBALDconsult wrote:

<layer>I wrote such a reporting system on my last job and there is one at my new job. Neither has the ability to alter the column headings so it must all be done in the query. The engine simply outputs the contents of a DataTable. I can add the ability to mine a couple of different ways.

select PID
      ,sum(qty)/(sysdate-min(date))  <<-- fixed this to account for sign.
  from (
  select PID
        ,qty
        ,date
    from your_table
   where date >= sysdate - 182
  )
group by PID;

SELECT
	PID,
	AVG(qty)/DATEDIFF	(d,MAX(date),	MIN(date))
FROM TableName
WHERE date > DATEADD(-6, GETDATE())
GROUP BY pid

SELECT ...
FROM DatesBetween(startdate, enddate) a
WHERE DATEPART(weekday, a.DateValue) = 1;

DECLARE @start DATETIME = '1950-01-01'
DECLARE @end DATETIME = '2011-03-31'

;WITH dateRange (date)
AS
(
	SELECT
        @start Date
    UNION ALL
    SELECT
        DATEADD(day, 1, Date) Date
    FROM
        dateRange
    WHERE
        Date < @end
) 
SELECT * FROM dateRange WHERE DATEPART(WEEKDAY,date) = 1
OPTION (MAXRECURSION 32767);

DECLARE @start DATETIME = '1950-01-01'
DECLARE @end DATETIME = '2011-03-31'
DECLARE @dates TABLE (date DATETIME)

WHILE (@start <= @end) 
BEGIN
      INSERT INTO @dates VALUES (@start);
      SET @start = DATEADD(day, 1, @start);
   END;
SELECT * FROM @dates WHERE DATEPART(WEEKDAY,date) = 1

DECLARE @start DATETIME = '2011-01-01'
DECLARE @end DATETIME = '2011-03-31'

;WITH dateRange (date)
AS
(
    SELECT
        @start Date
    UNION ALL
    SELECT
        DATEADD(day, 1, Date) Date
    FROM
        dateRange
    WHERE
        Date < @end
)
SELECT * FROM dateRange 
WHERE DATEPART(WEEKDAY,date) = 1 -- 1 is Sunday