Need Help Coalescing

Hi,
Below is the code I am struggling with. I want to return only those
"item_id(s)" that pertain to each member. The current results are
giving me every "item_id" for every member.
What am I doing wrong.
Thanks!!!
declare @.Indicatorgroup varchar(8000)
select
@.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
varchar)
from #temp_BLAH
group by Member, item_id
select +@.Indicatorgroup, Member
from #temp_BLAH
group by Member
So the results now are like this:
A,B,C,D,E....Z Smith, Ben
A,B,C,D,E....Z Jones, Dave
But Ben only has A, C, and D.
So I'd like the results to be like:
A,C,D Smith,Ben
A,J,Q Jones,DaveHi
The safe way to do this is to use a cursor if using SQL 2000 see
http://tinyurl.com/yat5xr
John
"Dubs" wrote:

> Hi,
> Below is the code I am struggling with. I want to return only those
> "item_id(s)" that pertain to each member. The current results are
> giving me every "item_id" for every member.
> What am I doing wrong.
> Thanks!!!
> declare @.Indicatorgroup varchar(8000)
> select
> @.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
> varchar)
> from #temp_BLAH
> group by Member, item_id
> select +@.Indicatorgroup, Member
> from #temp_BLAH
> group by Member
> So the results now are like this:
> A,B,C,D,E....Z Smith, Ben
> A,B,C,D,E....Z Jones, Dave
> But Ben only has A, C, and D.
> So I'd like the results to be like:
> A,C,D Smith,Ben
> A,J,Q Jones,Dave
>sql

Need Help Coalescing

Hi,
Below is the code I am struggling with. I want to return only those
"item_id(s)" that pertain to each member. The current results are
giving me every "item_id" for every member.
What am I doing wrong.
Thanks!!!
declare @.Indicatorgroup varchar(8000)
select
@.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
varchar)
from #temp_BLAH
group by Member, item_id
select +@.Indicatorgroup, Member
from #temp_BLAH
group by Member
So the results now are like this:
A,B,C,D,E....Z Smith, Ben
A,B,C,D,E....Z Jones, Dave
But Ben only has A, C, and D.
So I'd like the results to be like:
A,C,D Smith,Ben
A,J,Q Jones,Dave
Hi
The safe way to do this is to use a cursor if using SQL 2000 see
http://tinyurl.com/yat5xr
John
"Dubs" wrote:

> Hi,
> Below is the code I am struggling with. I want to return only those
> "item_id(s)" that pertain to each member. The current results are
> giving me every "item_id" for every member.
> What am I doing wrong.
> Thanks!!!
> declare @.Indicatorgroup varchar(8000)
> select
> @.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
> varchar)
> from #temp_BLAH
> group by Member, item_id
> select +@.Indicatorgroup, Member
> from #temp_BLAH
> group by Member
> So the results now are like this:
> A,B,C,D,E....Z Smith, Ben
> A,B,C,D,E....Z Jones, Dave
> But Ben only has A, C, and D.
> So I'd like the results to be like:
> A,C,D Smith,Ben
> A,J,Q Jones,Dave
>

Need Help Coalescing

Hi,
Below is the code I am struggling with. I want to return only those
"item_id(s)" that pertain to each member. The current results are
giving me every "item_id" for every member.
What am I doing wrong.
Thanks!!!
declare @.Indicatorgroup varchar(8000)
select
@.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
varchar)
from #temp_BLAH
group by Member, item_id
select +@.Indicatorgroup, Member
from #temp_BLAH
group by Member
So the results now are like this:
A,B,C,D,E....Z Smith, Ben
A,B,C,D,E....Z Jones, Dave
But Ben only has A, C, and D.
So I'd like the results to be like:
A,C,D Smith,Ben
A,J,Q Jones,DaveHi
The safe way to do this is to use a cursor if using SQL 2000 see
http://tinyurl.com/yat5xr
John
"Dubs" wrote:
> Hi,
> Below is the code I am struggling with. I want to return only those
> "item_id(s)" that pertain to each member. The current results are
> giving me every "item_id" for every member.
> What am I doing wrong.
> Thanks!!!
> declare @.Indicatorgroup varchar(8000)
> select
> @.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
> varchar)
> from #temp_BLAH
> group by Member, item_id
> select +@.Indicatorgroup, Member
> from #temp_BLAH
> group by Member
> So the results now are like this:
> A,B,C,D,E....Z Smith, Ben
> A,B,C,D,E....Z Jones, Dave
> But Ben only has A, C, and D.
> So I'd like the results to be like:
> A,C,D Smith,Ben
> A,J,Q Jones,Dave
>

Need help ASAP 3414 error SQL 2005 Developer Edition

Hello ,

This morning I was running a report in my ssrs and I had a power failure. My Machine rebooted and I can not connect to my database below is the error log can some one tell me what I need to do to recover I really do not want to have to reinstall everything if possible.

2007-06-15 07:59:49.88 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2007-06-15 07:59:49.88 Server (c) 2005 Microsoft Corporation.
2007-06-15 07:59:49.88 Server All rights reserved.
2007-06-15 07:59:49.88 Server Server process ID is 1600.
2007-06-15 07:59:49.88 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2007-06-15 07:59:49.90 Server This instance of SQL Server last reported using a process ID of 880 at 6/14/2007 5:36:25 PM (local) 6/14/2007 9:36:25 PM (UTC). This is an informational message only; no user action is required.
2007-06-15 07:59:49.90 Server Registry startup parameters:
2007-06-15 07:59:49.90 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2007-06-15 07:59:49.90 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2007-06-15 07:59:49.90 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2007-06-15 07:59:50.01 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-06-15 07:59:50.01 Server Detected 1 CPUs. This is an informational message; no user action is required.
2007-06-15 07:59:50.36 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-06-15 07:59:50.46 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2007-06-15 07:59:51.16 Server The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
2007-06-15 07:59:51.25 Server Database Mirroring Transport is disabled in the endpoint configuration.
2007-06-15 07:59:51.29 spid5s Starting up database 'master'.
2007-06-15 07:59:51.70 spid5s WARNING: did not see LOP_CKPT_END.
2007-06-15 07:59:51.70 spid5s Error: 3414, Severity: 21, State: 2.
2007-06-15 07:59:51.70 spid5s An error occurred during recovery, preventing the database 'master' (database ID 1) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
2007-06-15 07:59:51.70 spid5s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

The error message pretty much tells you what to do. It tellsyou to look at the SQL Server books online, it gives you an error code to look up.

A quick search of Google gives a link to Microsoft's support page, which should lead you in the right direction:

http://support.microsoft.com/kb/306366

sql

Need files from Companion CD of SQL Server 2000 High Availability

Dear All,
Last time I bought ebook entitle "Microsoft SQL Server 2000 High
Availability".
I need the files below :
- LRQ.zip
-Sysperfinfo_Examples.sql
-Database_Capacity_ and_Disk_Capacity_Monitor.zip
-Defrag.zip
Please some one could send me the files.
Thanks
Robert LieRobert Lie wrote:
> Dear All,
> Last time I bought ebook entitle "Microsoft SQL Server 2000 High
> Availability".
> I need the files below :
> - LRQ.zip
> -Sysperfinfo_Examples.sql
> -Database_Capacity_ and_Disk_Capacity_Monitor.zip
> -Defrag.zip
> Please some one could send me the files.
> Thanks
> Robert Lie
Have you checked the author's or publishers web site?
--
David Gugick
Imceda Software
www.imceda.com

Need advice on searching text fields

I have a table with 2 million + rows. I need to allow the user to do a quer
y
such as is shown below. I have full text search enabled on this database,
and have built a text search catalog for it. The problem is, that I keep
getting timeouts on the query. Granted, I can increase the timout value to
a
value that works, but I need this to come back fairly quick. I need advice
on how to set up this application. Think of eBay. You can search on any
term. They have to have millions of rows in their database also. How do
they return results so quick? They have to be doing very similar query logi
c?
Select * From [Table] Where ([Field1] + [Field2] Like '%term1%')
AND ([Field1] + [Field2] Like '%term2%')Does the LIKE operator utilize the full-text search engine? Perhaps you be
using the CONTAINS and FREETEXT functions instead.
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:88ECA16B-12D3-4AE9-ABDB-E666A633F381@.microsoft.com...
>I have a table with 2 million + rows. I need to allow the user to do a
>query
> such as is shown below. I have full text search enabled on this database,
> and have built a text search catalog for it. The problem is, that I keep
> getting timeouts on the query. Granted, I can increase the timout value
> to a
> value that works, but I need this to come back fairly quick. I need
> advice
> on how to set up this application. Think of eBay. You can search on any
> term. They have to have millions of rows in their database also. How do
> they return results so quick? They have to be doing very similar query
> logic?
> Select * From [Table] Where ([Field1] + [Field2] Like '%term1%')
> AND ([Field1] + [Field2] Like
> '%term2%')|||Oh, I assumed that 'Like' utilized the full text engine. Maybe I'm wrong.
I'll try the other two verbs and see what happens.
"JT" wrote:

> Does the LIKE operator utilize the full-text search engine? Perhaps you be
> using the CONTAINS and FREETEXT functions instead.
> "Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
> news:88ECA16B-12D3-4AE9-ABDB-E666A633F381@.microsoft.com...
>
>|||Perhaps you're not taking advantage of the full text search with the
LIKE and the concatenated columns.
Try using FREETEXT instead of LIKE
... where ( FREETEXT (Field1, 'term1') or FREETEXT(Field2, 'term1') )
and ( FREETEXT (Field1, 'term2') or FREETEXT(Field2, 'term2') )
Brian Kitt wrote:
> I have a table with 2 million + rows. I need to allow the user to do a qu
ery
> such as is shown below. I have full text search enabled on this database,
> and have built a text search catalog for it. The problem is, that I keep
> getting timeouts on the query. Granted, I can increase the timout value t
o a
> value that works, but I need this to come back fairly quick. I need advic
e
> on how to set up this application. Think of eBay. You can search on any
> term. They have to have millions of rows in their database also. How do
> they return results so quick? They have to be doing very similar query lo
gic?
> Select * From [Table] Where ([Field1] + [Field2] Like '%term1%')
> AND ([Field1] + [Field2] Like '%term2%')[/colo
r]|||Even with the full-text search functioning, you may not get the crisp
response time you are looking for. After all, eBay has more processing power
at their disposal than 95% of us do.
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:13DF6525-9595-41EB-B413-844EC57A09F2@.microsoft.com...
> Oh, I assumed that 'Like' utilized the full text engine. Maybe I'm wrong.
> I'll try the other two verbs and see what happens.
> "JT" wrote:
>|||Brian,
I can assure that T-SQL LIKE does NOT use the same technology as the
Full-Text Search (FTS) predicates of CONTAINS or FREETEXT !!
-- John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:13DF6525-9595-41EB-B413-844EC57A09F2@.microsoft.com...
> Oh, I assumed that 'Like' utilized the full text engine. Maybe I'm wrong.
> I'll try the other two verbs and see what happens.
> "JT" wrote:
>|||Brian,
While you may not have the processing power of eBay, you can get good
performance out of FTS with a bit of tuning on both the FT Indexing (Change
Tracking with Update Index in Background) and with using CONTAINSTABLE or
FREETEXTTABLE with the Top_N_Rank parameter and limiting your results to the
top 2000 by RANK, for example:
SELECT TOP 200 T.* FROM TableWithFTColumn as T,
CONTAINSTABLE(TableWIthFTColumn,*,'John'
,300) as CT
WHERE T.key=CT.key AND T.a > 5
ORDER BY CT.rank
Where 300 is the Top_N_by_RANK value. See KB artilce 240833 "FIX: Full-Text
Search Performance Improved via Support for TOP" at
http://support.microsoft.com//defau...kb;EN-US;240833 for more
details. Also, review SQL Server 200 Books Online (BOL) title "Full-text
Search Recommendations" for more tips!
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"JT" <someone@.microsoft.com> wrote in message
news:O2tY0S8sFHA.1940@.TK2MSFTNGP14.phx.gbl...
> Even with the full-text search functioning, you may not get the crisp
> response time you are looking for. After all, eBay has more processing
> power at their disposal than 95% of us do.
> "Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
> news:13DF6525-9595-41EB-B413-844EC57A09F2@.microsoft.com...
>

Need Advice on Reporting, End User Query and Data warehousing Tools

Hi:
I need an advice on Reporting, End User Query and Data warehousing
Tools
Below is my customer requirement:
Reporting:
- Flexible Reporting and Web Publishing
- Development and / or customization of reports by systems users using
a GUI based reports designer
- Export Capability to Office Automation Software
- Charting Facility
- Browser-Based User Interface
End User Query:
- Export Capability to Office Automation Software
- Web Publishing Output
- Charting Facility
- Reporting Facility
- Browser-Based User Interface
- Performance Monitoring/Tracking
- Administration
- Row and Column Level Security
Data warehouse:
- ETL and Analyst Tools
SQL2005 Enterprise, SSIS, SSAS, SSRS and Report Builder seem to meet
the requirement, but the problem is the target database is Oracle10g,
can i use those services on Oracle database ?
How about the solution from BusinessObject and Microstrategy, compare
with the services from SQL2005 Enterprise ?
Please Help.
Thanks
JCvoonHi
Reporting Services will certainly cover all your reporting needs including
the automatic generation and delivery that you have specified. You may want
to read the articles at
http://www.microsoft.com/sql/technologies/reporting/default.mspx if you have
not already. It sounds like you will not be running reports directly of you
Oracle database (which I think is possible although I have no experience of
doing this), but using Analysis Services will eliminate the need query this
data http://www.microsoft.com/sql/technologies/analysis/default.mspx.
An important factor will be a good design of the data warehouse. A poor
design will make reporting significantly more difficult, regardless of the
tool used. After using Business Objects a little I would say that for report
generation I do prefer Reporting Services.
One of the biggest factors may be licencing costs, if you are already a SQL
Server house, you will have not extra licence costs for AS or RS.
HTH
John
"jcvoon" wrote:
> Hi:
> I need an advice on Reporting, End User Query and Data warehousing
> Tools
> Below is my customer requirement:
> Reporting:
> - Flexible Reporting and Web Publishing
> - Development and / or customization of reports by systems users using
> a GUI based reports designer
> - Export Capability to Office Automation Software
> - Charting Facility
> - Browser-Based User Interface
> End User Query:
> - Export Capability to Office Automation Software
> - Web Publishing Output
> - Charting Facility
> - Reporting Facility
> - Browser-Based User Interface
> - Performance Monitoring/Tracking
> - Administration
> - Row and Column Level Security
> Data warehouse:
> - ETL and Analyst Tools
>
> SQL2005 Enterprise, SSIS, SSAS, SSRS and Report Builder seem to meet
> the requirement, but the problem is the target database is Oracle10g,
> can i use those services on Oracle database ?
> How about the solution from BusinessObject and Microstrategy, compare
> with the services from SQL2005 Enterprise ?
> Please Help.
> Thanks
> JCvoon
>|||John Bell:
Thanks for your reply.
In fact I plan to use the SSRS to generate report from Oracle database,
and use SSIS to build the Oracle data warehouse, for SSRS I think it
shouldn't be a problem, but for SSIS and SSAS I'm not sure.
Regards
JCVoon

Nee help - How To get AVG in his situation?

Hi All,
Below is my initial query, that returns totals for each w within the
month specified. I hard coded the W Columns to make it easier to work
with. This works fine.
/*---*/
Select T1.DataSource AS [Service Line],
COUNT(T1.PoNumber) AS [Total Of PONumber],
Sum(Case When T1.ReqSubmitDate
Between '04/01/2005' AND '04/2/2005' Then 1 End) [04/2/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/03/2005' AND '04/9/2005' Then 1 End) [04/9/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/10/2005' AND '04/16/2005' Then 1 End) [04/16/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/17/2005' AND '04/23/2005' Then 1 End) [04/23/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/24/2005' AND '04/30/2005' Then 1 End) [04/30/2005]
FROM OPW AS T1,
(SELECT PoNumber
FROM OPW
GROUP BY PoNumber
HAVING COUNT(*)>1) AS T2
WHERE (T1.PoNumber = T2.PoNumber) AND (T1.ReqSubmitDate BETWEEN '04/01/2005'
AND '04/30/2005')
GROUP BY T1.DataSource
/*---*/
The current resulting data is:
[Service Line] [Total Of PONumber] [04/2/2005] [04/9/2005] [04/16/2005]
[04/23/2005] [04/30/2005]
EVPN 4 NULL NULL 4 NULL NULL
MNS 526 NULL 209 313 NULL 4
/*---*/
Now, one of my tasks assign to me is to find the average cycle time for each
w per [Service Line]. This will include the following fields to the
query:
REQCreateDate DateTime,
REQCreateTime VarChar(10),
ReqSubmitTime VarChar(10),
ReqSubmitDate DateTime (This one is already used within the above query)
So, I'm thinking I need to concatinate the following columns, then get the
total number per w and derive the average:
T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME) AS RC,
T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME) AS RS
I hope I am making sense and you can help m understand this.
Thanks for taking the time though.
John.John,
If you can provide some sample data and also show
the exact results you want, it will be easier to help. You
may know exactly what "average cycle time" means, but
I don't. Sample data will also help to show why and how
you are storing values like REQCreateTime as VarChar(10),
since the time columns appear to be important.
See http://www.aspfaq.com/etiquett_e.asp?id=5006
Steve Kass
Drew University
John Rugo wrote:

>Hi All,
>Below is my initial query, that returns totals for each w within the
>month specified. I hard coded the W Columns to make it easier to work
>with. This works fine.
>/*---*/
>Select T1.DataSource AS [Service Line],
> COUNT(T1.PoNumber) AS [Total Of PONumber],
> Sum(Case When T1.ReqSubmitDate
> Between '04/01/2005' AND '04/2/2005' Then 1 End) [04/2/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/03/2005' AND '04/9/2005' Then 1 End) [04/9/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/10/2005' AND '04/16/2005' Then 1 End) [04/16/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/17/2005' AND '04/23/2005' Then 1 End) [04/23/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/24/2005' AND '04/30/2005' Then 1 End) [04/30/2005]
>FROM OPW AS T1,
>(SELECT PoNumber
> FROM OPW
> GROUP BY PoNumber
> HAVING COUNT(*)>1) AS T2
>WHERE (T1.PoNumber = T2.PoNumber) AND (T1.ReqSubmitDate BETWEEN '04/01/2005
'
>AND '04/30/2005')
>GROUP BY T1.DataSource
>/*---*/
>The current resulting data is:
>[Service Line] [Total Of PONumber] [04/2/2005] [04/9/2005] [04/16/2005]
>[04/23/2005] [04/30/2005]
>EVPN 4 NULL NULL 4 NULL NULL
>MNS 526 NULL 209 313 NULL 4
>/*---*/
>Now, one of my tasks assign to me is to find the average cycle time for eac
h
>w per [Service Line]. This will include the following fields to the
>query:
>REQCreateDate DateTime,
>REQCreateTime VarChar(10),
>ReqSubmitTime VarChar(10),
>ReqSubmitDate DateTime (This one is already used within the above query)
>So, I'm thinking I need to concatinate the following columns, then get the
>total number per w and derive the average:
>T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME) AS RC,
>T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME) AS RS
>I hope I am making sense and you can help m understand this.
>Thanks for taking the time though.
>John.
>
>|||The times are stored in a varchar format because they are derived solely
from an Excel Spreadsheet import that has the time in a spererate column,
and I was asked to mimic the data structure of the excel file. I don't like
it ether :(.
The Cycle Time is the Number of Minutes between ReqCreated Date/Time and
ReqSubmit Date/Time.
At the bottom of the my current message I have my newest version of the
query that at least shows the total Cycle Times per w. But I need to
show the averages, not the total.
Thanks very much for helping me.
/*Data*/
/*--*/
Select DataSource, PoNumber, REQCreateDate, REQCreateTime, ReqSubmitDate,
ReqSubmitTime
FROM OPW
DataSource | PoNumber | REQCreateDate | REQCreateTime | ReqSubmitDate |
ReqSubmitTime
EVPN PO82959 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
9:18:26
EVPN PO82956 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
9:18:26
EVPN PO82957 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
9:18:26
EVPN PO82958 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
9:18:26
EVPN PO82957 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
9:18:26
EVPN PO82959 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
9:18:26
MNS PO82755 2005-03-30 00:00:00.000 4:42:27 2005-03-31 00:00:00.000 1:33:18
MNS PO82840 2005-04-13 00:00:00.000 3:31:16 2005-04-14 00:00:00.000 10:27:57
MNS PO81971 2005-03-29 00:00:00.000 4:36:08 2005-03-29 00:00:00.000 5:03:08
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO81971 2005-03-29 00:00:00.000 4:36:08 2005-03-29 00:00:00.000 5:03:08
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82968 2005-04-15 00:00:00.000 2:35:37 2005-04-15 00:00:00.000 2:42:01
MNS PO81971 2005-03-29 00:00:00.000 4:36:08 2005-03-29 00:00:00.000 5:03:08
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
/*Newest Query*/
/*--*/
Select T1.DataSource AS [Service Line],
COUNT(T1.PoNumber) AS [Total Of PONumber],
Sum(Case When T1.ReqSubmitDate
Between '04/01/2005' AND '04/2/2005' Then
CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
(T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
Decimal(9,2))
End) [04/2/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/03/2005' AND '04/9/2005' Then
CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
(T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
Decimal(9,2))
End) [04/9/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/10/2005' AND '04/16/2005' Then
CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
(T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
Decimal(9,2))
End) [04/16/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/17/2005' AND '04/23/2005' Then
CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
(T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
Decimal(9,2))
End) [04/23/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/24/2005' AND '04/30/2005' Then
CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
(T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
Decimal(9,2))
End) [04/30/2005]
FROM OPW AS T1,
(SELECT PoNumber
FROM OPW
GROUP BY PoNumber
HAVING COUNT(*)>1) AS T2
WHERE (T1.PoNumber = T2.PoNumber) AND (T1.ReqSubmitDate BETWEEN '04/01/2005'
AND '04/30/2005')
GROUP BY T1.DataSource
/*--*/
"Steve Kass" <skass@.drew.edu> wrote in message
news:u8Ejbk$SFHA.3620@.TK2MSFTNGP09.phx.gbl...
> John,
> If you can provide some sample data and also show
> the exact results you want, it will be easier to help. You
> may know exactly what "average cycle time" means, but
> I don't. Sample data will also help to show why and how
> you are storing values like REQCreateTime as VarChar(10),
> since the time columns appear to be important.
> See http://www.aspfaq.com/etiquett_e.asp?id=5006
> Steve Kass
> Drew University
> John Rugo wrote:
>|||John,
Did you try using AVG() instead of SUM() ? To be safe from rounding
surprises, write any AVG() expression as AVG(1.0*(yourvalue)) if yourvalue
is an integer.
SK
John Rugo wrote:

>The times are stored in a varchar format because they are derived solely
>from an Excel Spreadsheet import that has the time in a spererate column,
>and I was asked to mimic the data structure of the excel file. I don't lik
e
>it ether :(.
>The Cycle Time is the Number of Minutes between ReqCreated Date/Time and
>ReqSubmit Date/Time.
>At the bottom of the my current message I have my newest version of the
>query that at least shows the total Cycle Times per w. But I need to
>show the averages, not the total.
>Thanks very much for helping me.
>/*Data*/
>/*--*/
>Select DataSource, PoNumber, REQCreateDate, REQCreateTime, ReqSubmitDate,
>ReqSubmitTime
>FROM OPW
>DataSource | PoNumber | REQCreateDate | REQCreateTime | ReqSubmitDate |
>ReqSubmitTime
>EVPN PO82959 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
>9:18:26
>EVPN PO82956 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
>9:18:26
>EVPN PO82957 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
>9:18:26
>EVPN PO82958 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
>9:18:26
>EVPN PO82957 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
>9:18:26
>EVPN PO82959 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
>9:18:26
>MNS PO82755 2005-03-30 00:00:00.000 4:42:27 2005-03-31 00:00:00.000 1:33:18
>MNS PO82840 2005-04-13 00:00:00.000 3:31:16 2005-04-14 00:00:00.000 10:27:5
7
>MNS PO81971 2005-03-29 00:00:00.000 4:36:08 2005-03-29 00:00:00.000 5:03:08
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO81971 2005-03-29 00:00:00.000 4:36:08 2005-03-29 00:00:00.000 5:03:08
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82968 2005-04-15 00:00:00.000 2:35:37 2005-04-15 00:00:00.000 2:42:01
>MNS PO81971 2005-03-29 00:00:00.000 4:36:08 2005-03-29 00:00:00.000 5:03:08
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>/*Newest Query*/
>/*--*/
>Select T1.DataSource AS [Service Line],
> COUNT(T1.PoNumber) AS [Total Of PONumber],
> Sum(Case When T1.ReqSubmitDate
> Between '04/01/2005' AND '04/2/2005' Then
> CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
> (T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
>Decimal(9,2))
> End) [04/2/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/03/2005' AND '04/9/2005' Then
> CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
> (T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
>Decimal(9,2))
> End) [04/9/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/10/2005' AND '04/16/2005' Then
> CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
> (T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
>Decimal(9,2))
> End) [04/16/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/17/2005' AND '04/23/2005' Then
> CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
> (T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
>Decimal(9,2))
> End) [04/23/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/24/2005' AND '04/30/2005' Then
> CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
> (T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
>Decimal(9,2))
> End) [04/30/2005]
>FROM OPW AS T1,
>(SELECT PoNumber
> FROM OPW
> GROUP BY PoNumber
> HAVING COUNT(*)>1) AS T2
>WHERE (T1.PoNumber = T2.PoNumber) AND (T1.ReqSubmitDate BETWEEN '04/01/2005
'
>AND '04/30/2005')
> GROUP BY T1.DataSource
>/*--*/
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:u8Ejbk$SFHA.3620@.TK2MSFTNGP09.phx.gbl...
>
>
>