GROUP BY Question

I need to generate daily aggregates (sums) from some hourly data and althoug
h
I have query that works, I think I've gone overboard on the GROUP BY
statement. Can anyone give me some feedback on this? I'm sure there is a
better way to do this, but I'm at a loss.
SELECT
max(LP.NO_FINISH_LINE) NO_FINISH_LINE,
CONVERT(CHAR(10),max(LP.DT_FINISHED),126) DT_FINISHED,
CAST(sum(LP.NO_WEIGHT_PROD) as DECIMAL(8,2)) NO_WEIGHT_PROD,
CAST(sum(LP.NO_WEIGHT_REJECT) as DECIMAL(8,2)) NO_WEIGHT_REJECT,
FROM LOT_PRODUCTION LP
WHERE (LP.DT_FINISHED BETWEEN CONVERT(CHAR(10), DATEADD(mm, - 1,
(DATEADD(dd, 1 - DAY(GETDATE()), GETDATE()))), 126) AND GETDATE()) AND
LP.NO_FINISH_LINE = 'L2'
GROUP BY DAY(DT_FINISHED),MONTH(DT_FINISHED),YEAR
(DT_FINISHED)
ORDER BY DT_FINISHED ASC;
Thanks in advance,
RaulRaul
Please post DDL+ sample data + expected result
"Raul" <raul@.nothere.com> wrote in message
news:7DE39B83-F304-463C-97E2-C353571F6892@.microsoft.com...
>I need to generate daily aggregates (sums) from some hourly data and
>although
> I have query that works, I think I've gone overboard on the GROUP BY
> statement. Can anyone give me some feedback on this? I'm sure there is a
> better way to do this, but I'm at a loss.
> SELECT
> max(LP.NO_FINISH_LINE) NO_FINISH_LINE,
> CONVERT(CHAR(10),max(LP.DT_FINISHED),126) DT_FINISHED,
> CAST(sum(LP.NO_WEIGHT_PROD) as DECIMAL(8,2)) NO_WEIGHT_PROD,
> CAST(sum(LP.NO_WEIGHT_REJECT) as DECIMAL(8,2)) NO_WEIGHT_REJECT,
> FROM LOT_PRODUCTION LP
> WHERE (LP.DT_FINISHED BETWEEN CONVERT(CHAR(10), DATEADD(mm, - 1,
> (DATEADD(dd, 1 - DAY(GETDATE()), GETDATE()))), 126) AND GETDATE()) AND
> LP.NO_FINISH_LINE = 'L2'
> GROUP BY DAY(DT_FINISHED),MONTH(DT_FINISHED),YEAR
(DT_FINISHED)
> ORDER BY DT_FINISHED ASC;
> Thanks in advance,
> Raul|||I think you group by cluase can be just
GROUP BY convert(varchar(10),DT_FINISHED,101)
--check the syntax of convert though ;)|||Try:
SELECT
max(LP.NO_FINISH_LINE) as NO_FINISH_LINE,
CONVERT(CHAR(10), LP.DT_FINISHED, 126) as DT_FINISHED,
CAST(sum(LP.NO_WEIGHT_PROD) as DECIMAL(8,2)) as NO_WEIGHT_PROD,
CAST(sum(LP.NO_WEIGHT_REJECT) as DECIMAL(8,2)) as NO_WEIGHT_REJECT,
FROM
dbo.LOT_PRODUCTION LP
WHERE
(LP.DT_FINISHED BETWEEN CONVERT(CHAR(10), DATEADD(mm, - 1, (DATEADD(dd, 1 -
DAY(GETDATE()), GETDATE()))), 126) AND GETDATE())
AND LP.NO_FINISH_LINE = 'L2'
GROUP BY
-- DAY(DT_FINISHED),MONTH(DT_FINISHED),YEA
R(DT_FINISHED)
CONVERT(CHAR(10), LP.DT_FINISHED, 126)
ORDER BY
DT_FINISHED ASC;
go
AMB
"Raul" wrote:

> I need to generate daily aggregates (sums) from some hourly data and altho
ugh
> I have query that works, I think I've gone overboard on the GROUP BY
> statement. Can anyone give me some feedback on this? I'm sure there is a
> better way to do this, but I'm at a loss.
> SELECT
> max(LP.NO_FINISH_LINE) NO_FINISH_LINE,
> CONVERT(CHAR(10),max(LP.DT_FINISHED),126) DT_FINISHED,
> CAST(sum(LP.NO_WEIGHT_PROD) as DECIMAL(8,2)) NO_WEIGHT_PROD,
> CAST(sum(LP.NO_WEIGHT_REJECT) as DECIMAL(8,2)) NO_WEIGHT_REJECT,
> FROM LOT_PRODUCTION LP
> WHERE (LP.DT_FINISHED BETWEEN CONVERT(CHAR(10), DATEADD(mm, - 1,
> (DATEADD(dd, 1 - DAY(GETDATE()), GETDATE()))), 126) AND GETDATE()) AND
> LP.NO_FINISH_LINE = 'L2'
> GROUP BY DAY(DT_FINISHED),MONTH(DT_FINISHED),YEAR
(DT_FINISHED)
> ORDER BY DT_FINISHED ASC;
> Thanks in advance,
> Raul|||Why not just group by
CONVERT(CHAR(10),LP.DT_FINISHED,126)
and remove the max from your select list for this column?
"Raul" <raul@.nothere.com> wrote in message
news:7DE39B83-F304-463C-97E2-C353571F6892@.microsoft.com...
> I need to generate daily aggregates (sums) from some hourly data and
although
> I have query that works, I think I've gone overboard on the GROUP BY
> statement. Can anyone give me some feedback on this? I'm sure there is a
> better way to do this, but I'm at a loss.
> SELECT
> max(LP.NO_FINISH_LINE) NO_FINISH_LINE,
> CONVERT(CHAR(10),max(LP.DT_FINISHED),126) DT_FINISHED,
> CAST(sum(LP.NO_WEIGHT_PROD) as DECIMAL(8,2)) NO_WEIGHT_PROD,
> CAST(sum(LP.NO_WEIGHT_REJECT) as DECIMAL(8,2)) NO_WEIGHT_REJECT,
> FROM LOT_PRODUCTION LP
> WHERE (LP.DT_FINISHED BETWEEN CONVERT(CHAR(10), DATEADD(mm, - 1,
> (DATEADD(dd, 1 - DAY(GETDATE()), GETDATE()))), 126) AND GETDATE()) AND
> LP.NO_FINISH_LINE = 'L2'
> GROUP BY DAY(DT_FINISHED),MONTH(DT_FINISHED),YEAR
(DT_FINISHED)
> ORDER BY DT_FINISHED ASC;
> Thanks in advance,
> Raul|||Thank you very much.
This was very helpful, and I have incorporated the suggested changes into
the query.
Thanks again,
Raul
"Raul" wrote:

> I need to generate daily aggregates (sums) from some hourly data and altho
ugh
> I have query that works, I think I've gone overboard on the GROUP BY
> statement. Can anyone give me some feedback on this? I'm sure there is a
> better way to do this, but I'm at a loss.
> SELECT
> max(LP.NO_FINISH_LINE) NO_FINISH_LINE,
> CONVERT(CHAR(10),max(LP.DT_FINISHED),126) DT_FINISHED,
> CAST(sum(LP.NO_WEIGHT_PROD) as DECIMAL(8,2)) NO_WEIGHT_PROD,
> CAST(sum(LP.NO_WEIGHT_REJECT) as DECIMAL(8,2)) NO_WEIGHT_REJECT,
> FROM LOT_PRODUCTION LP
> WHERE (LP.DT_FINISHED BETWEEN CONVERT(CHAR(10), DATEADD(mm, - 1,
> (DATEADD(dd, 1 - DAY(GETDATE()), GETDATE()))), 126) AND GETDATE()) AND
> LP.NO_FINISH_LINE = 'L2'
> GROUP BY DAY(DT_FINISHED),MONTH(DT_FINISHED),YEAR
(DT_FINISHED)
> ORDER BY DT_FINISHED ASC;
> Thanks in advance,
> Raul|||Why are you formatting the data in the query? That is always done in
the front end. Why are you computing all those silly things in SQL,
which is not a computational language?
Because you are thinking like a COBOL programmer whose data is stored
in strings; think like an SQL programmer who works with abstract data
types.
First build an auxiliary table with your reporting periods. Remember,
tables and not computations:
CREATE TABLE ReportPeriods
(report_period_name CHAR(10) NOT NULL PRIMARY KEY,
rpt_start_date DATETIME NOT NULL,
rpt_end_date DATETIME NOT NULL,
CHECK (rpt_start_date < rpt_end_date));
Next, do a simple join to get your reports:
SELECT R.report_period_name,
MAX(LP.finish_line_nbr)AS finish_line_max,
SUM(LP.prod_wgt) AS prod_wgt_tot,
SUM(LP.reject_wgt) AS reject_wgt_tot,
FROM LotProduction AS LP,
ReportPeriods AS R
WHERE LP.finish_date
BETWEEN R.rpt_start_date AND R.rpt_end_date
GROUP BY R.report_period_name;
Since your data element did not follow ISO-11179 rules, I tried to
guess at corrections. You might also want to start writing Standard SQL
instead of dialect CURRENT_TIMESTAMP instead of the old getdate(),
CAST() instead of CONVERT(), etc.

Gridlines in excel

We currently using reporting services to generate excel reports. I was unable
to switch on gridlines for my excel reports and reverted to the method where
I gave each cell a border to simulate gridlines.
These 'styles' prove to be problematic, especially with large spreadsheats.
I have a large 47MB spreadsheet which takes a few minutes to load. By
replacing the cell borders with gridlines and resizing the cells the size
drops to 36MB and it loads instantaniously.
How can I generate excel reports with gridlines 'switched on' by default.Not possible to enable grid lines from RS for excel, may be after excel is
created you can enable it by going to tools ->options->view click on check
box for gridlines. But I dont think you require like this to do that too each
and every users?
Amarnath, MCTS
"JRMalherbe" wrote:
> We currently using reporting services to generate excel reports. I was unable
> to switch on gridlines for my excel reports and reverted to the method where
> I gave each cell a border to simulate gridlines.
> These 'styles' prove to be problematic, especially with large spreadsheats.
> I have a large 47MB spreadsheet which takes a few minutes to load. By
> replacing the cell borders with gridlines and resizing the cells the size
> drops to 36MB and it loads instantaniously.
> How can I generate excel reports with gridlines 'switched on' by default.