Grouping Problem

I have a table that contains a region, year, part, and quantity. I want the
query output to be one line per region with the years as columns for the sum
of the quantities. I've tried this, but I get one line per year instead of
one line per region.
SELECT
PART_ID,
REGION,
CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
SUM(Total_Inbound) AS TOTAL_QTY
FROM dbo.tblGlobalInboundVolumes
GROUP BY PART_ID, Region, Report_Year
HAVING (PART_ID = 'KRC12110/3 R11F')Never mind...I figured it out
"Phill" wrote:

> I have a table that contains a region, year, part, and quantity. I want t
he
> query output to be one line per region with the years as columns for the s
um
> of the quantities. I've tried this, but I get one line per year instead o
f
> one line per region.
> SELECT
> PART_ID,
> REGION,
> CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
> CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
> CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
> CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
> CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
> CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
> CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
> SUM(Total_Inbound) AS TOTAL_QTY
> FROM dbo.tblGlobalInboundVolumes
> GROUP BY PART_ID, Region, Report_Year
> HAVING (PART_ID = 'KRC12110/3 R11F')|||Try:
SELECT
REGION,
CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
SUM(Total_Inbound) AS TOTAL_QTY
FROM dbo.tblGlobalInboundVolumes
WHERE (PART_ID = 'KRC12110/3 R11F')
GROUP BY Region
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:457D9064-2B3B-4243-8648-E5884B22102E@.microsoft.com...
I have a table that contains a region, year, part, and quantity. I want the
query output to be one line per region with the years as columns for the sum
of the quantities. I've tried this, but I get one line per year instead of
one line per region.
SELECT
PART_ID,
REGION,
CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
SUM(Total_Inbound) AS TOTAL_QTY
FROM dbo.tblGlobalInboundVolumes
GROUP BY PART_ID, Region, Report_Year
HAVING (PART_ID = 'KRC12110/3 R11F')

Grouping on one line

Okay...i haven't been able to find any answers to this question mostly
because i think its hard to search for. If someone can provide a link
to a website, that would be super. I have a report that is current
laid out, with grouping, like so
Header1
Header2.1
Header3
Details
Details
Details
Details
Header2.2
Header3
Details
Details
Details
Details
Does anyone know how to make it lay out like this...
Header1 Header2.1 Header3 Details
Details
Details
Details
Header2.2 Header3 Details
Details
Details
Details
I've played with some grouping, but it's not working.
Solutions/suggestionsI figured out my own problem, and i hope this will help others.
Keep the grouping, but remove the headers. Put all your fields on the
detail line and in the properties page, set hide duplicates to the
grouping that corresponds to the fields you're in.
Hope this helps someone else!
KellyLeia@.gmail.com wrote:
> Okay...i haven't been able to find any answers to this question mostly
> because i think its hard to search for. If someone can provide a link
> to a website, that would be super. I have a report that is current
> laid out, with grouping, like so
> Header1
> Header2.1
> Header3
> Details
> Details
> Details
> Details
> Header2.2
> Header3
> Details
> Details
> Details
> Details
> Does anyone know how to make it lay out like this...
> Header1 Header2.1 Header3 Details
> Details
> Details
> Details
> Header2.2 Header3 Details
> Details
> Details
> Details
> I've played with some grouping, but it's not working.
> Solutions/suggestions

Grouping consecutive rows of data

This is driving me crazy, I have the following table
Unit Line ProductCode StartTime EndTime
-- -- -- --
--
3 1 120064 2002-08-30 12:31:26.810 2003-05-27
11:39:51.157
3 1 283724 2003-05-27 11:39:51.157 2003-05-27
12:51:52.423
3 1 283724 2003-05-27 12:51:52.423 2003-05-30
07:12:38.997
3 1 285775 2003-05-30 07:12:38.997 2003-06-16
10:37:01.813
3 1 230571 2003-06-16 10:37:01.813 2003-07-04
12:40:32.097
3 1 260775 2003-07-04 12:40:32.097 2003-07-07
14:25:18.483
3 1 260775 2003-07-07 14:25:18.483 2003-07-08
07:02:04.303
3 1 265775 2003-07-08 07:02:04.303 2003-07-14
12:37:42.513
3 1 215690 2003-07-14 12:37:42.513 2003-07-14
12:38:23.450
3 1 255779 2003-07-14 12:38:23.450 2003-07-15
08:09:30.030
3 1 255779 2003-07-15 08:09:30.030 2003-07-15
16:05:53.323
3 1 205716 2003-07-15 16:05:53.323 2003-07-15
16:06:17.527
3 1 203716 2003-07-15 16:06:17.527 2003-07-18
10:53:48.927
3 1 203716 2003-07-18 10:53:48.927 2003-07-18
10:55:17.677
3 1 102001 2003-07-18 10:55:17.677 2003-07-18
10:55:54.190
3 1 203716 2003-07-18 10:55:54.190 2003-07-18
15:40:57.113
3 1 203716 2003-07-18 15:40:57.113 2003-07-18
15:41:05.660
3 1 203716 2003-07-18 15:41:05.660 2003-07-18
16:22:23.050
3 1 203716 2003-07-18 16:22:23.050 2003-07-23
16:25:37.407
I want to be able to group each Unit, Line and ProductCode together so
I can pick up the StartTime and EndTime for consecutive product runs.
i.e.ProductCode 203716 starts at 2003-07-15 16:06:17.527 and runs
until 2003-07-18 10:55:17.677.
This is then replaced by ProductCode 102001 which runs from 2003-07-18
10:55:17.677 until 2003-07-18 10:55:54.190 and is then replaced by
203716 which runs from 2003-07-18 10:55:54.190 until 2003-07-23
16:25:37.407.
I tried the following view:
SELECT TOP 100 PERCENT Unit, Line, ProductCode, MIN(StartTime) AS
StartTime, MAX(EndTime) AS EndTime FROM D_ProductionLog
GROUP BY Unit, Line, ProductCode
ORDER BY StartTime, Unit, Line
But it takes the first time a Product is run and the last time it was
run i.e.
for ProductCode 203716
StartTime 2003-07-15 16:06:17.527
EndTime 2003-07-23 16:25:37.407
Instead of two records.
Many thanks
JimThis is a multi-part message in MIME format.
--=_NextPart_000_014B_01C35111.500514B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
select
Unit
, Line
, ProductCode
, Type
, case when Type =3D 1 then min (StartTime) else max (EndTime)
from
MyTable
cross join
(
select 1 as Type
union all
select 2
) as x
group by
Unit
, Line
, ProductCode
, Type
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jim" <jim.holmes@.devro-casings.com> wrote in message =news:68dfae14.0307230741.2f1a9b49@.posting.google.com...
This is driving me crazy, I have the following table
Unit Line ProductCode StartTime EndTime
-- -- -- --
--
3 1 120064 2002-08-30 12:31:26.810 2003-05-27
11:39:51.157
3 1 283724 2003-05-27 11:39:51.157 2003-05-27
12:51:52.423
3 1 283724 2003-05-27 12:51:52.423 2003-05-30
07:12:38.997
3 1 285775 2003-05-30 07:12:38.997 2003-06-16
10:37:01.813
3 1 230571 2003-06-16 10:37:01.813 2003-07-04
12:40:32.097
3 1 260775 2003-07-04 12:40:32.097 2003-07-07
14:25:18.483
3 1 260775 2003-07-07 14:25:18.483 2003-07-08
07:02:04.303
3 1 265775 2003-07-08 07:02:04.303 2003-07-14
12:37:42.513
3 1 215690 2003-07-14 12:37:42.513 2003-07-14
12:38:23.450
3 1 255779 2003-07-14 12:38:23.450 2003-07-15
08:09:30.030
3 1 255779 2003-07-15 08:09:30.030 2003-07-15
16:05:53.323
3 1 205716 2003-07-15 16:05:53.323 2003-07-15
16:06:17.527
3 1 203716 2003-07-15 16:06:17.527 2003-07-18
10:53:48.927
3 1 203716 2003-07-18 10:53:48.927 2003-07-18
10:55:17.677
3 1 102001 2003-07-18 10:55:17.677 2003-07-18
10:55:54.190
3 1 203716 2003-07-18 10:55:54.190 2003-07-18
15:40:57.113
3 1 203716 2003-07-18 15:40:57.113 2003-07-18
15:41:05.660
3 1 203716 2003-07-18 15:41:05.660 2003-07-18
16:22:23.050
3 1 203716 2003-07-18 16:22:23.050 2003-07-23
16:25:37.407
I want to be able to group each Unit, Line and ProductCode together so
I can pick up the StartTime and EndTime for consecutive product runs.
i.e.ProductCode 203716 starts at 2003-07-15 16:06:17.527 and runs
until 2003-07-18 10:55:17.677.
This is then replaced by ProductCode 102001 which runs from 2003-07-18
10:55:17.677 until 2003-07-18 10:55:54.190 and is then replaced by
203716 which runs from 2003-07-18 10:55:54.190 until 2003-07-23
16:25:37.407.
I tried the following view:
SELECT TOP 100 PERCENT Unit, Line, ProductCode, MIN(StartTime) AS
StartTime, MAX(EndTime) AS EndTime FROM D_ProductionLog
GROUP BY Unit, Line, ProductCode
ORDER BY StartTime, Unit, Line
But it takes the first time a Product is run and the last time it was
run i.e.
for ProductCode 203716 StartTime 2003-07-15 16:06:17.527 EndTime 2003-07-23 16:25:37.407
Instead of two records.
Many thanks
Jim
--=_NextPart_000_014B_01C35111.500514B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try:
select
=Unit
, =Line
, ProductCode
, =Type
, case when =Type =3D 1 then min (StartTime) else max (EndTime)
from
=MyTable
cross join
(
select 1 as =Type union all select 2) as x
group by
=Unit
, =Line
, ProductCode
, =Type-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jim" wrote in message news:68dfae=14.0307230741.2f1a9b49@.posting.google.com...This is driving me crazy, I have the following tableUnit =Line ProductCode StartTime &nbs=p; EndTime-- -- -- ----3  =; 1 120064 2002-08-30 =12:31:26.810 2003-05-2711:39:51.1573 =1 283724 2003-05-27 11:39:51.157 2003-05-2712:51:52.4233 =1 283724 2003-05-27 12:51:52.423 2003-05-3007:12:38.9973 =1 285775 2003-05-30 07:12:38.997 2003-06-1610:37:01.8133 =1 230571 2003-06-16 10:37:01.813 2003-07-0412:40:32.0973 =1 260775 2003-07-04 12:40:32.097 2003-07-0714:25:18.4833 =1 260775 2003-07-07 14:25:18.483 2003-07-0807:02:04.3033 =1 265775 2003-07-08 07:02:04.303 2003-07-1412:37:42.5133 =1 215690 2003-07-14 12:37:42.513 2003-07-1412:38:23.4503 =1 255779 2003-07-14 12:38:23.450 2003-07-1508:09:30.0303 =1 255779 2003-07-15 08:09:30.030 2003-07-1516:05:53.3233 =1 205716 2003-07-15 16:05:53.323 2003-07-1516:06:17.5273 =1 203716 2003-07-15 16:06:17.527 2003-07-1810:53:48.9273 =1 203716 2003-07-18 10:53:48.927 2003-07-1810:55:17.6773 =1 102001 2003-07-18 10:55:17.677 2003-07-1810:55:54.1903 =1 203716 2003-07-18 10:55:54.190 2003-07-1815:40:57.1133 =1 203716 2003-07-18 15:40:57.113 2003-07-1815:41:05.6603 =1 203716 2003-07-18 15:41:05.660 2003-07-1816:22:23.0503 =1 203716 2003-07-18 16:22:23.050 2003-07-2316:25:37.407I want to be able to group each Unit, =Line and ProductCode together soI can pick up the StartTime and EndTime for consecutive product runs.i.e.ProductCode 203716 starts at 2003-07-15 =16:06:17.527 and runsuntil 2003-07-18 10:55:17.677.This is =then replaced by ProductCode 102001 which runs from =2003-07-1810:55:17.677 until 2003-07-18 10:55:54.190 and is then replaced by203716 which runs =from 2003-07-18 10:55:54.190 until 2003-07-2316:25:37.407.I tried =the following view:SELECT TOP 100 PERCENT =Unit, Line, ProductCode, MIN(StartTime) ASStartTime, MAX(EndTime) AS =EndTime FROM D_ProductionLogGROUP BY Unit, Line, ProductCodeORDER =BY StartTime, Unit, LineBut it takes the first time a Product is =run and the last time it wasrun i.e.for ProductCode 203716 StartTime 2003-07-15 16:06:17.527 EndTime 2003-07-23 16:25:37.407 Instead =of two records.Many thanksJim

---

--=_NextPart_000_014B_01C35111.500514B0--|||I am confused, do you want to get one record per ProductCode or you just
want to order them together?
"Jim" <jim.holmes@.devro-casings.com> wrote in message
news:68dfae14.0307230741.2f1a9b49@.posting.google.com...
> This is driving me crazy, I have the following table
>
> Unit Line ProductCode StartTime EndTime
> -- -- -- --
> --
> 3 1 120064 2002-08-30 12:31:26.810 2003-05-27
> 11:39:51.157
> 3 1 283724 2003-05-27 11:39:51.157 2003-05-27
> 12:51:52.423
> 3 1 283724 2003-05-27 12:51:52.423 2003-05-30
> 07:12:38.997
> 3 1 285775 2003-05-30 07:12:38.997 2003-06-16
> 10:37:01.813
> 3 1 230571 2003-06-16 10:37:01.813 2003-07-04
> 12:40:32.097
> 3 1 260775 2003-07-04 12:40:32.097 2003-07-07
> 14:25:18.483
> 3 1 260775 2003-07-07 14:25:18.483 2003-07-08
> 07:02:04.303
> 3 1 265775 2003-07-08 07:02:04.303 2003-07-14
> 12:37:42.513
> 3 1 215690 2003-07-14 12:37:42.513 2003-07-14
> 12:38:23.450
> 3 1 255779 2003-07-14 12:38:23.450 2003-07-15
> 08:09:30.030
> 3 1 255779 2003-07-15 08:09:30.030 2003-07-15
> 16:05:53.323
> 3 1 205716 2003-07-15 16:05:53.323 2003-07-15
> 16:06:17.527
> 3 1 203716 2003-07-15 16:06:17.527 2003-07-18
> 10:53:48.927
> 3 1 203716 2003-07-18 10:53:48.927 2003-07-18
> 10:55:17.677
> 3 1 102001 2003-07-18 10:55:17.677 2003-07-18
> 10:55:54.190
> 3 1 203716 2003-07-18 10:55:54.190 2003-07-18
> 15:40:57.113
> 3 1 203716 2003-07-18 15:40:57.113 2003-07-18
> 15:41:05.660
> 3 1 203716 2003-07-18 15:41:05.660 2003-07-18
> 16:22:23.050
> 3 1 203716 2003-07-18 16:22:23.050 2003-07-23
> 16:25:37.407
> I want to be able to group each Unit, Line and ProductCode together so
> I can pick up the StartTime and EndTime for consecutive product runs.
> i.e.ProductCode 203716 starts at 2003-07-15 16:06:17.527 and runs
> until 2003-07-18 10:55:17.677.
> This is then replaced by ProductCode 102001 which runs from 2003-07-18
> 10:55:17.677 until 2003-07-18 10:55:54.190 and is then replaced by
> 203716 which runs from 2003-07-18 10:55:54.190 until 2003-07-23
> 16:25:37.407.
> I tried the following view:
> SELECT TOP 100 PERCENT Unit, Line, ProductCode, MIN(StartTime) AS
> StartTime, MAX(EndTime) AS EndTime FROM D_ProductionLog
> GROUP BY Unit, Line, ProductCode
> ORDER BY StartTime, Unit, Line
> But it takes the first time a Product is run and the last time it was
> run i.e.
> for ProductCode 203716
> StartTime 2003-07-15 16:06:17.527
> EndTime 2003-07-23 16:25:37.407
> Instead of two records.
> Many thanks
> Jim|||Greetings,
Unless you breathe,sniff,snort and live S2k sql queries like
this are difficult to construct.An easy way to solve this
problem is to create a column with the same value for every
combination of Unit,Line and ProductCode based on the sort
order of StartTime.This new column or rank can then be used
as a grouping column to find the min and max times for each
Unit,Line and ProductCode combination.Current versions of
Oracle and DB2 have this sql99 functionality,S2k does not.
create table #D_ProductionLog(Unit int,Line int,
ProductCode int,StartTime datetime,EndTime datetime)
go
insert #D_ProductionLog values(3,1,120064,'2002-08-30
12:31:26.810','2003-05-27 11:39:51.157')
insert #D_ProductionLog values(3,1,283724,'2003-05-27
11:39:51.157','2003-05-27 12:51:52.423')
insert #D_ProductionLog values(3,1,283724,'2003-05-27
12:51:52.423','2003-05-30 07:12:38.997')
insert #D_ProductionLog values(3,1,285775,'2003-05-30
07:12:38.997','2003-06-16 10:37:01.813')
insert #D_ProductionLog values(3,1,230571,'2003-06-16
10:37:01.813','2003-07-04 12:40:32.097')
insert #D_ProductionLog values(3,1,260775,'2003-07-04
12:40:32.097','2003-07-07 14:25:18.483')
insert #D_ProductionLog values(3,1,260775,'2003-07-07
14:25:18.483','2003-07-08 07:02:04.303')
insert #D_ProductionLog values(3,1,265775,'2003-07-08
07:02:04.303','2003-07-14 12:37:42.513')
insert #D_ProductionLog values(3,1,215690,'2003-07-14
12:37:42.513','2003-07-14 12:38:23.450')
insert #D_ProductionLog values(3,1,255779,'2003-07-14
12:38:23.450','2003-07-15 08:09:30.030')
insert #D_ProductionLog values(3,1,255779,'2003-07-15
08:09:30.030','2003-07-15 16:05:53.323')
insert #D_ProductionLog values(3,1,205716,'2003-07-15
16:05:53.323','2003-07-15 16:06:17.527')
insert #D_ProductionLog values(3,1,203716,'2003-07-15
16:06:17.527','2003-07-18 10:53:48.927')
insert #D_ProductionLog values(3,1,203716,'2003-07-18
10:53:48.927','2003-07-18 10:55:17.677')
insert #D_ProductionLog values(3,1,102001,'2003-07-18
10:55:17.677','2003-07-18 10:55:54.190')
insert #D_ProductionLog values(3,1,203716,'2003-07-18
10:55:54.190','2003-07-18 15:40:57.113')
insert #D_ProductionLog values(3,1,203716,'2003-07-18
15:40:57.113','2003-07-18 15:41:05.660')
insert #D_ProductionLog values(3,1,203716,'2003-07-18
15:41:05.660','2003-07-18 16:22:23.050')
insert #D_ProductionLog values(3,1,203716,'2003-07-18
16:22:23.050','2003-07-23 16:25:37.407')
You can use the RAC utility for S2k to easily create this
virtual column.The 'drank' column binds together the same Unit,Line
and ProductCode combinations in the sort order of StartTime.
Exec Rac
@.transform='_dummy_',@.style='121',@.datelen='25',
@.rows='Unit & Line & ProductCode & StartTime(date) & EndTime(date)',
@.rowsort='StartTime & Unit & Line & ProductCode',
@.pvtcol='Report Mode',
@.from='#D_ProductionLog',
-- create the virtual column drank using the @.rowindicators parameter.
@.rowindicators='ProductCode{drank}',@.counterdatatype='integer',
@.grand_totals='n',@.rowbreak='n',@.racheck='y',@.defaultexceptions='dumy'
This result shows drank* column and how it binds.It is simply an
incrementing integer for each combination.
Unit Line ProductCode StartTime EndTime
drank*
-- -- -- -- --
-- --
3 1 120064 2002-08-30 12:31:26.810 2003-05-27 11:39:51.157
1
3 1 283724 2003-05-27 11:39:51.157 2003-05-27 12:51:52.423
2
3 1 283724 2003-05-27 12:51:52.423 2003-05-30 07:12:38.997
2
3 1 285775 2003-05-30 07:12:38.997 2003-06-16 10:37:01.813
3
3 1 230571 2003-06-16 10:37:01.813 2003-07-04 12:40:32.097
4
3 1 260775 2003-07-04 12:40:32.097 2003-07-07 14:25:18.483
5
3 1 260775 2003-07-07 14:25:18.483 2003-07-08 07:02:04.303
5
3 1 265775 2003-07-08 07:02:04.303 2003-07-14 12:37:42.513
6
3 1 215690 2003-07-14 12:37:42.513 2003-07-14 12:38:23.450
7
3 1 255779 2003-07-14 12:38:23.450 2003-07-15 08:09:30.030
8
3 1 255779 2003-07-15 08:09:30.030 2003-07-15 16:05:53.323
8
3 1 205716 2003-07-15 16:05:53.323 2003-07-15 16:06:17.527
9
3 1 203716 2003-07-15 16:06:17.527 2003-07-18 10:53:48.927
10
3 1 203716 2003-07-18 10:53:48.927 2003-07-18 10:55:17.677
10
3 1 102001 2003-07-18 10:55:17.677 2003-07-18 10:55:54.190
11
3 1 203716 2003-07-18 10:55:54.190 2003-07-18 15:40:57.113
12
3 1 203716 2003-07-18 15:40:57.113 2003-07-18 15:41:05.660
12
3 1 203716 2003-07-18 15:41:05.660 2003-07-18 16:22:23.050
12
3 1 203716 2003-07-18 16:22:23.050 2003-07-23 16:25:37.407
12
Using the drank column in a GROUP BY easily solves the problem in
a single execution of RAC.
Exec Rac
@.transform='_dummy_',@.style='121',@.datelen='25',
@.rows='Unit & Line & ProductCode & StartTime(date) & EndTime(date)',
@.rowsort='StartTime & Unit & Line & ProductCode',
@.pvtcol='Report Mode',
@.from='#D_ProductionLog',
@.rowindicators='ProductCode{drank}',@.counterdatatype='integer',
@.grand_totals='n',@.rowbreak='n',@.racheck='y',
-- Use a simple group by query to solve the problem.
-- You could also include the count of each combination if desired.
@.select='select Unit,Line,ProductCode,MIN(StartTime) AS StartTime,
MAX(EndTime) AS EndTime
from rac
group by drank,Unit,Line,ProductCode
order by drank'
Unit Line ProductCode StartTime EndTime
-- -- -- -- --
--
3 1 120064 2002-08-30 12:31:26.810 2003-05-27 11:39:51.157
3 1 283724 2003-05-27 11:39:51.157 2003-05-30 07:12:38.997
3 1 285775 2003-05-30 07:12:38.997 2003-06-16 10:37:01.813
3 1 230571 2003-06-16 10:37:01.813 2003-07-04 12:40:32.097
3 1 260775 2003-07-04 12:40:32.097 2003-07-08 07:02:04.303
3 1 265775 2003-07-08 07:02:04.303 2003-07-14 12:37:42.513
3 1 215690 2003-07-14 12:37:42.513 2003-07-14 12:38:23.450
3 1 255779 2003-07-14 12:38:23.450 2003-07-15 16:05:53.323
3 1 205716 2003-07-15 16:05:53.323 2003-07-15 16:06:17.527
3 1 203716 2003-07-15 16:06:17.527 2003-07-18 10:55:17.677
3 1 102001 2003-07-18 10:55:17.677 2003-07-18 10:55:54.190
3 1 203716 2003-07-18 10:55:54.190 2003-07-23 16:25:37.407
RAC v2.2 and QALite released.
www.rac4sql.net

Grouping a query in 30 seconds

Hi,

How can I make a query and group the registries in a interval of 30 seconds...like

for each line I have a datetime field that have all the day, and I need it to return just like

TIME Contador_type1 Contador_type2 Total

01-01-2006 00:00:30.000 2 5 7

01-01-2006 00:01:00.000 3 7 10

It's just an example...but that's the result that I need and my table is

data_hora -- datetime field

tipo - 1 or 2 -- count

nrtelefone - that's is the number dialed.

Thanks

Hi there and welcome to the groups,

see if that one helps:

As you didn��t specified your logic in your request (if it should be rounded up or down if its perhaps 00:15 (00:00 vs. 00:30)), you possible have to tweak the case branch.

DROP TABLE SomeTable

GO

CREATE TABLE SomeTable

(

SomeColumn datetime,

SomeValue int

)

INSERT INTO SomeTable

VALUES(GETDATE(),1)

INSERT INTO SomeTable

VALUES(DATEADD(mi,30,GETDATE()),1)

SELECT SUm(SomeValue),

DATEADD(mi,Minutes,DATEADD(hh,hours,Date))

FROM

(

SELECT

CONVERT(VARCHAR(10),SOmeColumn,112) AS Date ,

SomeValue,

DATEPART(hh,SomeColumn) Hours,

(CASE WHEN DATEPART(mi,SomeColumn) <30 THEN 30 ELSE 0 END) Minutes

From SomeTable

) SUbQUery

GROUP BY DATEADD(mi,Minutes,DATEADD(hh,hours,Date))

Drop Table SomeTable

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Use a similar solution to that suggested by sussemeyer above, but use integer division of 30 (rounds result to nearest whole number) with the seconds, as this would be more efficient, and would do away with the CASE statement, which is computationally costly.

HTH For more SQL tips, check out my blog below:

|||

Hi,

I tried that and didn't worked, I need to count how many rows are, in a period of 30 seconds.

Thanks for helping almost there...

|||

As I the code above didn't work can you give me an example ?

Thanks

|||

Hi,

OK you didn��t mention that you just wanted to have the count, then just replace the SUM() with a COUNT(*) and you��ll be fine.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

OK,

But in the case of 30 seconds, it don't work ?

I tried to change...but it increase the minutes wrongly..

Thanks

|||Coudl you please post some sample data ?

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Does this help?

Select Floor(Convert(Float, data_hora) * 24 * 60 *2) As TimeChunk, count(*)

From myTable

Group By Floor(Convert(Float, data_hora) * 24 * 60 *2);

|||

Here it is..with the columns

COD_CLIENTE,DATA_HORA,NRTELEFONE,RAMAL,TEMPO_SEGUNDOS,TIPO,TEMPO_ATENDIMENTO,JAPROCESSADO VALOR,VALOR_CONC,VALOR_TARIFA,VALOR_TARIFA_CONC,CLASSIFICA,LOCALIDADE,VALOR_TEMPO,NUMERO_E1, BLOQUEADO,TABELA_TELEFONICA
149,2006-03-01 09:48:26.000,0800784403,6935,1.0,2,0,NULL,NULL,NULL,NULL,NULL,ESP,0800 NACIONAL,.5000,01132816900,1,1
149,2006-03-01 09:44:16.000,01144145754,6922,324.0,2,0,NULL,.6600,.7536,.1100,.1256,CONUR,ATIBAIA - SP,6.0000,01132816900,1,1
149,2006-03-01 09:49:25.000,01137417505,6935,108.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 09:53:59.000,01159258359,6941,103.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 09:57:58.000,01332995733,6922,228.0,2,0,NULL,1.0440,1.9800,.2900,.5500,DDD,SANTOS - SP,3.6000,01132816900,1,1
149,2006-03-01 10:01:20.000,01184262728,6923,76.0,2,0,NULL,.5500,.6710,.5000,.6100,VC1,AREA 011 - CELULAR - SP,1.1000,01132816900,1,1
149,2006-03-01 09:55:32.000,01121874200,6932,457.0,2,0,NULL,.6800,.9600,.0850,.1200,LOCAL,SAO PAULO - SP,8.0000,01132816900,1,1
149,2006-03-01 10:02:24.000,05534125161,6937,104.0,2,0,NULL,.6720,.8800,.4200,.5500,DDD,SANTA MARIA - RS,1.6000,01132816900,1,1
149,2006-03-01 10:05:05.000,01121617500,6935,67.0,2,0,NULL,.0850,.1200,.0850,.1200,LOCAL,SAO PAULO - SP,1.0000,01132816900,1,1
149,2006-03-01 10:04:41.000,01934627164,6937,136.0,2,0,NULL,.8820,1.1550,.4200,.5500,DDD,AMERICANA - SP,2.1000,01132816900,1,1
149,2006-03-01 10:11:05.000,01934582333,6936,46.0,2,0,NULL,.4200,.5500,.4200,.5500,DDD,SANTA BARBARA D OESTE - SP,1.0000,01132816900,1,1
149,2006-03-01 10:12:14.000,01121617525,6935,9.0,2,0,NULL,.0000,.0000,.0850,.1200,LOCAL,SAO PAULO - SP,.0000,01132816900,1,1
149,2006-03-01 10:08:37.000,01161035061,6913,255.0,2,0,NULL,.4250,.6000,.0850,.1200,LOCAL,SAO PAULO - SP,5.0000,01132816900,1,1
149,2006-03-01 10:11:35.000,01434546110,6941,263.0,2,0,NULL,1.7640,2.3100,.4200,.5500,DDD,BAURU - SP,4.2000,01132816900,1,1
149,2006-03-01 10:18:37.000,01934063393,6936,87.0,2,0,NULL,.5460,.7150,.4200,.5500,DDD,AMERICANA - SP,1.3000,01132816900,1,1
149,2006-03-01 10:26:12.000,01121617525,6935,11.0,2,0,NULL,.0000,.0000,.0850,.1200,LOCAL,SAO PAULO - SP,.0000,01132816900,1,1
149,2006-03-01 10:25:25.000,01132536576,6920,78.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 10:23:20.000,01162222734,6904,244.0,2,0,NULL,.3400,.4800,.0850,.1200,LOCAL,SAO PAULO - SP,4.0000,01132816900,1,1
149,2006-03-01 10:29:40.000,01991638344,6924,87.0,2,0,NULL,1.4820,1.5600,1.1400,1.2000,VC2,AREA 019 - CELULAR - SP,1.3000,01132816900,1,1
149,2006-03-01 10:33:54.000,011102,6926,70.0,2,0,NULL,.0850,.1200,.0850,.1200,LOCAL,SAO PAULO - SP,1.0000,01132816900,1,1
149,2006-03-01 10:35:22.000,01934692606,6926,110.0,2,0,NULL,.7140,.9350,.4200,.5500,DDD,AMERICANA - SP,1.7000,01132816900,1,1
149,2006-03-01 10:41:11.000,01161280656,6924,28.0,2,0,NULL,.0850,.1200,.0850,.1200,LOCAL,SAO PAULO - SP,1.0000,01132816900,1,1
149,2006-03-01 10:38:32.000,01381340603,6921,257.0,2,0,NULL,4.6740,4.9200,1.1400,1.2000,VC2,AREA 013 - CELULAR - SP,4.1000,01132816900,1,1
149,2006-03-01 10:50:16.000,01121617500,6935,73.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 10:50:46.000,08007728486,6923,186.0,2,0,NULL,NULL,NULL,NULL,NULL,ESP,0800 NACIONAL,2.9000,01132816900,1,1
149,2006-03-01 10:54:16.000,01169574019,6923,124.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 10:57:38.000,01155102193,6915,87.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 10:58:53.000,01171484387,6947,47.0,2,0,NULL,.3000,.3660,.5000,.6100,VC1,AREA 011 - CELULAR - SP,.6000,01132816900,1,1
149,2006-03-01 11:00:36.000,01155922414,6935,11.0,2,0,NULL,.0000,.0000,.0850,.1200,LOCAL,SAO PAULO - SP,.0000,01132816900,1,1
149,2006-03-01 10:59:43.000,01171484387,6925,104.0,2,0,NULL,.8000,.9760,.5000,.6100,VC1,AREA 011 - CELULAR - SP,1.6000,01132816900,1,1

And what I need to return is something like(ex not using the above)

DATA_HORA TOTAL

01/03/2006 00:00:30.000 5

01/03/2006 00:01:00.000 2

01/03/2006 00:01:30.000 6

Thanks

|||It's almost that...but I need the role day time from 01/03/2006 00:00:00.000 to 01/03/2006 23:59:59.997 for example|||

How about this then:

Select Convert(date, Floor(Convert(Float, data_hora) * 24 * 60 *2)/2880.0) As TimeChunk, count(*)

From myTable

Group By Convert(date, Floor(Convert(Float, data_hora) * 24 * 60 *2)/2880.0);

|||

OK, this works, but it does not group by an interval of 30 seconds, it's just put together all registries that has the same time....

Did you get the idea or not ? Like I did something(based on your idea) like a while the increase the time from 01/01/2006 00:00:00.000 to 02/01/2006 23:59:30.000 just using dateadd(ss,30,@.date) but the problem is....to fill the total field I need to do a select that counts and it takes a long time to complete...

I'm asking if there is a faster way...

|||

I am not sure what you are after if my last SQL statement does not produce the result you are looking for. The statement I gave you will group all records together that fall withing the 30 intervals. If it does not then I did something wrong.

|||

Hum...ok..I'll check again...but do you Know webchart ? I'm using this query to build a smoothlinechart, but it's getting an strange format..

Do you think that I need to pass the whole interval, or you think that the values that is missing the graph will automatically put 0 ?

Thanks anyway, it worked with Select Floor(Convert(Float, data_hora) * 24 * 60 *2) As TEMPO, count(*) AS TOTAL From pabx WHERE cod_cliente = 221 AND data_hora BETWEEN '20060201' AND '20060203' Group By Floor(Convert(Float, data_hora) * 24 * 60 *2) ORDER BY Floor(Convert(Float, data_hora) * 24 * 60 *2)

sql

Group By Problem

The following sql statement is giving error when i insert the line Group By...(to get the total amount of Job_id's):

SELECT Isnull(tbl1.Job_no, tbl2.Job_no) As Job_No, tbl1.Amount, tbl1.Entry_id
FROM tbl2 FULL OUTER JOIN tbl1
ON tbl1.colx = tbl2.coly
WHERE <Condition>
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);

tbl1 and tbl2 have columns Job_no. But one has a null value if the other value other that null. So the statement above will list the job_no (combined from the two tables), the Amount and the Entry_ID. What i'm trying to arrive at is to add all amount on the same Job_no.

any comment will be greatly appreciated.

Thanks!

Quote:

Originally Posted by Merio

The following sql statement is giving error when i insert the line Group By...(to get the total amount of Job_id's):

SELECT Isnull(tbl1.Job_no, tbl2.Job_no) As Job_No, tbl1.Amount, tbl1.Entry_id
FROM tbl2 FULL OUTER JOIN tbl1
ON tbl1.colx = tbl2.coly
WHERE <Condition>
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);

tbl1 and tbl2 have columns Job_no. But one has a null value if the other value other that null. So the statement above will list the job_no (combined from the two tables), the Amount and the Entry_ID. What i'm trying to arrive at is to add all amount on the same Job_no.

any comment will be greatly appreciated.

Thanks!

Instead of:
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);
Try
GROUP BY Job_No.

But i belive that that work either,
What you might have to do is group by all tthe other fields
GROUP BY tbl1.Amount, tbl1.Entry_id|||

Quote:

Originally Posted by tezza98

Instead of:
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);
Try
GROUP BY Job_No.

But i belive that that work either,
What you might have to do is group by all tthe other fields
GROUP BY tbl1.Amount, tbl1.Entry_id

------------

The problem was solved when i changed the first line with this:
SELECT Isnull(tbl1.Job_no, tbl2.Job_no) As Job_no, SUM(tbl1.amount) As Amount

I needed to put the SUM on tbl1.amount. - I thought that tbl1.amount would be totaled automatically when GROUP BY is used... I was wrong. :0

Thanks for your comment :)

GROUP BY on same line?

Hi,
I want to do a group by in a Report (RS 2005), but the group by has to be on
the same line. For instance:
Imagine I have a table tblPerson (Name, FirtsName, Country, DateOfBirth,
...)
- Simpson, Homer, USA, 12-12-1970
- Johnson, Bart, Belgium, 05-02-1978
and a tblCars (Brand, Color, Year)
- Toyota, Blue, 2000
- Ford, Red, 2002
- Mercedes, Yellow, 1998
And Homer has the totyota and the Ford, and Bart has the Mercedes.
I would like to have my report-output like this:
Simpson, Homer, USA, Totoya, Blue
Ford, Red
Johnson, Bart, Belgium, Mercedes, Yellow
And not like this:
Simpson, Homer, USA
Totoya, Blue
Ford, Red
Johnson, Bart, Belgium
Mercedes, Yellow
How can I do this?
Thanks a lot in advance,
PieterHi,
Can be done. Done give the Give the "Name" e.g. Simpson in group header.
Instead give it in the detail itself and just right click the name field
textbox and check the "hide duplicate"
Amarnath
"Pieter Coucke" wrote:
> Hi,
> I want to do a group by in a Report (RS 2005), but the group by has to be on
> the same line. For instance:
> Imagine I have a table tblPerson (Name, FirtsName, Country, DateOfBirth,
> ...)
> - Simpson, Homer, USA, 12-12-1970
> - Johnson, Bart, Belgium, 05-02-1978
> and a tblCars (Brand, Color, Year)
> - Toyota, Blue, 2000
> - Ford, Red, 2002
> - Mercedes, Yellow, 1998
> And Homer has the totyota and the Ford, and Bart has the Mercedes.
> I would like to have my report-output like this:
> Simpson, Homer, USA, Totoya, Blue
> Ford, Red
> Johnson, Bart, Belgium, Mercedes, Yellow
> And not like this:
> Simpson, Homer, USA
> Totoya, Blue
> Ford, Red
> Johnson, Bart, Belgium
> Mercedes, Yellow
> How can I do this?
> Thanks a lot in advance,
> Pieter
>
>|||Thanks! That worked!

GridView, XML, and .NET 2005 Application

Hello, I'm in the processing of learning .NET 2005 and an trying to rewrite
a simple command line application that I wrote a couple years ago. I have
the following requirements:
1) Read an XML file that I create with a list of .EXE and .DLL files.
2) Scan a directory based on the path stored within the XML file and extract
the production version information
3) Add the Path, Filename and version information to a GridView control.
Now I have item #3 working today which displays output to screen using
console.write.
Question 1:
How can I add headers to a datagrid and update with my own information based
on me scanning a directory?
Question 2:
My application today reads the file path information, etc., from SQL Server
and then scans the directory for the version information. I would like to
instead use an XML file to contain this information instead of SQL Server.
This way I can use ClickOnce easily deploy my application without having a
SQL Server requirement. Most of my updates will be done to the XML file
only, so I would simply like to republish my ClickOnce to have user
redownload new XML file.
What is the best way for me to create an XML file of my data that can be
maintained? I have columsn such as:
1) Filename
2) Path of file
3) Description
4) Shortname of file
Each row within the XML will be unique. I need a way that I can easily
update/create this file. I currently have about 200 rows in SQL that I need
to get into my XML file. I really do not want to retype all the
information, but I can if I have to.
Any help or direction with this would be appreciated.
You can save a DataSet as XML.
And something else you might want to think about. If the data displayed is
static, not requiring any user interaction, you might want to think about
optimizing your app by using an XSL transform to create the data in HTML in
the page, rather than a DataGrid. You can create an XSL style sheet, and use
an Xml WebControl to display it in any format you choose (as HTML).
HTH,
Kevin Spencer
Microsoft MVP
..Net Developer
If you push something hard enough,
it will fall over.
- Fudd's First Law of Opposition
"dm1608" <dm1608@.spam.net> wrote in message
news:u3Uq5nU9FHA.1148@.tk2msftngp13.phx.gbl...
> Hello, I'm in the processing of learning .NET 2005 and an trying to
> rewrite a simple command line application that I wrote a couple years ago.
> I have the following requirements:
> 1) Read an XML file that I create with a list of .EXE and .DLL files.
> 2) Scan a directory based on the path stored within the XML file and
> extract the production version information
> 3) Add the Path, Filename and version information to a GridView control.
>
> Now I have item #3 working today which displays output to screen using
> console.write.
> Question 1:
> How can I add headers to a datagrid and update with my own information
> based on me scanning a directory?
> Question 2:
> My application today reads the file path information, etc., from SQL
> Server and then scans the directory for the version information. I would
> like to instead use an XML file to contain this information instead of SQL
> Server. This way I can use ClickOnce easily deploy my application without
> having a SQL Server requirement. Most of my updates will be done to the
> XML file only, so I would simply like to republish my ClickOnce to have
> user redownload new XML file.
> What is the best way for me to create an XML file of my data that can be
> maintained? I have columsn such as:
> 1) Filename
> 2) Path of file
> 3) Description
> 4) Shortname of file
> Each row within the XML will be unique. I need a way that I can easily
> update/create this file. I currently have about 200 rows in SQL that I
> need to get into my XML file. I really do not want to retype all the
> information, but I can if I have to.
> Any help or direction with this would be appreciated.
>
>
>
|||Thanks for the reply, Keven.
I'm trying to do away with using SQL Server. If I use the SaveToXML option
of ADO.NET, is the XML file the is created something that can easily be
maintained going forward? I guess I cannot control the output or format of
the XML file, so that is why I'm thinking it may not be what I want.
Opinions?
"Kevin Spencer" <kevin@.DIESPAMMERSDIEtakempis.com> wrote in message
news:%23WA93ra9FHA.3876@.TK2MSFTNGP09.phx.gbl...
> You can save a DataSet as XML.
> And something else you might want to think about. If the data displayed is
> static, not requiring any user interaction, you might want to think about
> optimizing your app by using an XSL transform to create the data in HTML
> in the page, rather than a DataGrid. You can create an XSL style sheet,
> and use an Xml WebControl to display it in any format you choose (as
> HTML).
> --
> HTH,
> Kevin Spencer
> Microsoft MVP
> .Net Developer
> If you push something hard enough,
> it will fall over.
> - Fudd's First Law of Opposition
> "dm1608" <dm1608@.spam.net> wrote in message
> news:u3Uq5nU9FHA.1148@.tk2msftngp13.phx.gbl...
>
|||I don't think it s a good idea to replace the database with an XML file. You
will loose all the benefits of concurrency control, backup/restore,
maintenance etc that the database provides you with.
You can store your XML in the database though either shredded (if it is
relational in nature) or natively...
Best regards
Michael
"dm1608" <dm1608@.spam.net> wrote in message
news:es1WSjb9FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Thanks for the reply, Keven.
> I'm trying to do away with using SQL Server. If I use the SaveToXML
> option of ADO.NET, is the XML file the is created something that can
> easily be maintained going forward? I guess I cannot control the output
> or format of the XML file, so that is why I'm thinking it may not be what
> I want.
> Opinions?
> "Kevin Spencer" <kevin@.DIESPAMMERSDIEtakempis.com> wrote in message
> news:%23WA93ra9FHA.3876@.TK2MSFTNGP09.phx.gbl...
>

GridView, XML, and .NET 2005 Application

Hello, I'm in the processing of learning .NET 2005 and an trying to rewrite
a simple command line application that I wrote a couple years ago. I have
the following requirements:
1) Read an XML file that I create with a list of .EXE and .DLL files.
2) Scan a directory based on the path stored within the XML file and extract
the production version information
3) Add the Path, Filename and version information to a GridView control.
Now I have item #3 working today which displays output to screen using
console.write.
Question 1:
How can I add headers to a datagrid and update with my own information based
on me scanning a directory?
Question 2:
My application today reads the file path information, etc., from SQL Server
and then scans the directory for the version information. I would like to
instead use an XML file to contain this information instead of SQL Server.
This way I can use ClickOnce easily deploy my application without having a
SQL Server requirement. Most of my updates will be done to the XML file
only, so I would simply like to republish my ClickOnce to have user
redownload new XML file.
What is the best way for me to create an XML file of my data that can be
maintained? I have columsn such as:
1) Filename
2) Path of file
3) Description
4) Shortname of file
Each row within the XML will be unique. I need a way that I can easily
update/create this file. I currently have about 200 rows in SQL that I need
to get into my XML file. I really do not want to retype all the
information, but I can if I have to.
Any help or direction with this would be appreciated.You can save a DataSet as XML.
And something else you might want to think about. If the data displayed is
static, not requiring any user interaction, you might want to think about
optimizing your app by using an XSL transform to create the data in HTML in
the page, rather than a DataGrid. You can create an XSL style sheet, and use
an Xml WebControl to display it in any format you choose (as HTML).
HTH,
Kevin Spencer
Microsoft MVP
.Net Developer
If you push something hard enough,
it will fall over.
- Fudd's First Law of Opposition
"dm1608" <dm1608@.spam.net> wrote in message
news:u3Uq5nU9FHA.1148@.tk2msftngp13.phx.gbl...
> Hello, I'm in the processing of learning .NET 2005 and an trying to
> rewrite a simple command line application that I wrote a couple years ago.
> I have the following requirements:
> 1) Read an XML file that I create with a list of .EXE and .DLL files.
> 2) Scan a directory based on the path stored within the XML file and
> extract the production version information
> 3) Add the Path, Filename and version information to a GridView control.
>
> Now I have item #3 working today which displays output to screen using
> console.write.
> Question 1:
> How can I add headers to a datagrid and update with my own information
> based on me scanning a directory?
> Question 2:
> My application today reads the file path information, etc., from SQL
> Server and then scans the directory for the version information. I would
> like to instead use an XML file to contain this information instead of SQL
> Server. This way I can use ClickOnce easily deploy my application without
> having a SQL Server requirement. Most of my updates will be done to the
> XML file only, so I would simply like to republish my ClickOnce to have
> user redownload new XML file.
> What is the best way for me to create an XML file of my data that can be
> maintained? I have columsn such as:
> 1) Filename
> 2) Path of file
> 3) Description
> 4) Shortname of file
> Each row within the XML will be unique. I need a way that I can easily
> update/create this file. I currently have about 200 rows in SQL that I
> need to get into my XML file. I really do not want to retype all the
> information, but I can if I have to.
> Any help or direction with this would be appreciated.
>
>
>|||Thanks for the reply, Keven.
I'm trying to do away with using SQL Server. If I use the SaveToXML option
of ADO.NET, is the XML file the is created something that can easily be
maintained going forward? I guess I cannot control the output or format of
the XML file, so that is why I'm thinking it may not be what I want.
Opinions?
"Kevin Spencer" <kevin@.DIESPAMMERSDIEtakempis.com> wrote in message
news:%23WA93ra9FHA.3876@.TK2MSFTNGP09.phx.gbl...
> You can save a DataSet as XML.
> And something else you might want to think about. If the data displayed is
> static, not requiring any user interaction, you might want to think about
> optimizing your app by using an XSL transform to create the data in HTML
> in the page, rather than a DataGrid. You can create an XSL style sheet,
> and use an Xml WebControl to display it in any format you choose (as
> HTML).
> --
> HTH,
> Kevin Spencer
> Microsoft MVP
> .Net Developer
> If you push something hard enough,
> it will fall over.
> - Fudd's First Law of Opposition
> "dm1608" <dm1608@.spam.net> wrote in message
> news:u3Uq5nU9FHA.1148@.tk2msftngp13.phx.gbl...
>|||I don't think it s a good idea to replace the database with an XML file. You
will loose all the benefits of concurrency control, backup/restore,
maintenance etc that the database provides you with.
You can store your XML in the database though either shredded (if it is
relational in nature) or natively...
Best regards
Michael
"dm1608" <dm1608@.spam.net> wrote in message
news:es1WSjb9FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Thanks for the reply, Keven.
> I'm trying to do away with using SQL Server. If I use the SaveToXML
> option of ADO.NET, is the XML file the is created something that can
> easily be maintained going forward? I guess I cannot control the output
> or format of the XML file, so that is why I'm thinking it may not be what
> I want.
> Opinions?
> "Kevin Spencer" <kevin@.DIESPAMMERSDIEtakempis.com> wrote in message
> news:%23WA93ra9FHA.3876@.TK2MSFTNGP09.phx.gbl...
>