Hi All,
I have a requirment to pull a query that would result in something like the
following:
[Service Line] | [POCount] | W
End-04-02-2005 | W
End-04-9-2005 |
W
End-04-016-2005 | W
End-04-26-2005
MNS 15 3
2 10
etc...
I create a temp table and dump the data into it and find the W
ending by
using a function.
My problme is that I can't get it to group like indicated above.
Below is my entire code, excluding the Function.
Please Help... I'm desperate :)
John.
CREATE PROCEDURE dbo.usp_RPT_OPW_FLASH
@.startdate SmallDateTime,
@.enddate SmallDateTime
AS
SET NOCOUNT ON
BEGIN TRANSACTION
---
--Create Temporary Table To Hold the data
---
Create Table #tmpOPW_FLASH (
[Service Line] VarChar(50),
[Total Of PONumber]INT,
RC DateTime,
RS DateTime,
CycleTime Decimal(18,2),
W
Ending DateTime
)
---
--Insert Data into temporary table for DATE_ORDERED (Issued)
---
INSERT INTO #tmpOPW_FLASH([Service Line], [Total Of PONumber], RC, RS)
SELECT T1.DataSource AS [Service Line], COUNT(T1.PoNumber) AS [Total Of
PONumber],
T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME) AS RC,
T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME) AS RS
FROM OPW T1 INNER JOIN
(SELECT PoNumber
FROM OPW
GROUP BY PoNumber
HAVING COUNT(*) > 1) T2 ON T1.PoNumber = T2.PoNumber
WHERE (T1.ReqSubmitDate BETWEEN @.startdate AND @.enddate)
GROUP BY T1.DataSource, T1.REQCreateDate + CAST(T1.REQCreateTime AS
DATETIME), T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME)
---
--UPDATE CycleTime and W
Ending Column
---
UPDATE #tmpOPW_FLASH
SET CycleTime = DateDiff(hh, RC, RS),
W
Ending = DBO.usf_W
Ending(RS)
----
SELECT [Service Line], [Total Of PONumber], W
Ending
FROM #tmpOPW_FLASH
GROUP BY [Service Line], [Total Of PONumber], W
Ending
DROP TABLE #tmpOPW_FLASH
COMMIT TRANSACTIONI think that you wont need your Temp table although i cant understand your
intention, but these update can be done in an inline statement, because you
are only calling a function to do this. Please post some DDL and smaple data
which can be read a bit better then the pasting of your last post, and well
fix it together :-)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"John Rugo" <jrugo@.patmedia.net> schrieb im Newsbeitrag
news:uAbrsS1SFHA.3544@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I have a requirment to pull a query that would result in something like
> the following:
> [Service Line] | [POCount] | W
End-04-02-2005 | W
End-04-9-2005 |
> W
End-04-016-2005 | W
End-04-26-2005
> MNS 15 3 2
> 10
> etc...
> I create a temp table and dump the data into it and find the W
ending by
> using a function.
> My problme is that I can't get it to group like indicated above.
> Below is my entire code, excluding the Function.
> Please Help... I'm desperate :)
> John.
>
> CREATE PROCEDURE dbo.usp_RPT_OPW_FLASH
> @.startdate SmallDateTime,
> @.enddate SmallDateTime
> AS
> SET NOCOUNT ON
> BEGIN TRANSACTION
> ---
> --Create Temporary Table To Hold the data
> ---
> Create Table #tmpOPW_FLASH (
> [Service Line] VarChar(50),
> [Total Of PONumber]INT,
> RC DateTime,
> RS DateTime,
> CycleTime Decimal(18,2),
> W
Ending DateTime
> )
> ---
> --Insert Data into temporary table for DATE_ORDERED (Issued)
> ---
> INSERT INTO #tmpOPW_FLASH([Service Line], [Total Of PONumber], RC, RS)
> SELECT T1.DataSource AS [Service Line], COUNT(T1.PoNumber) AS [Total Of
> PONumber],
> T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME) AS RC,
> T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME) AS RS
> FROM OPW T1 INNER JOIN
> (SELECT PoNumber
> FROM OPW
> GROUP BY PoNumber
> HAVING COUNT(*) > 1) T2 ON T1.PoNumber = T2.PoNumber
> WHERE (T1.ReqSubmitDate BETWEEN @.startdate AND @.enddate)
> GROUP BY T1.DataSource, T1.REQCreateDate + CAST(T1.REQCreateTime AS
> DATETIME), T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME)
> ---
> --UPDATE CycleTime and W
Ending Column
> ---
> UPDATE #tmpOPW_FLASH
> SET CycleTime = DateDiff(hh, RC, RS),
> W
Ending = DBO.usf_W
Ending(RS)
> ----
> SELECT [Service Line], [Total Of PONumber], W
Ending
> FROM #tmpOPW_FLASH
> GROUP BY [Service Line], [Total Of PONumber], W
Ending
> DROP TABLE #tmpOPW_FLASH
> COMMIT TRANSACTION
>|||Your approach is too procedural. Create a table of reporting periods:
CREATE TABLE ReportPeriods
(period_name CHAR (20) NOT NULL PRIMARY KEY,
period_start_date DATETIME NOT NULL,
period_end_date DATETIME NOT NULL,
CHECK (period_start_date < period_end_date));
Then write something like:
SELECT service_line, po_cnt,
CASE WHEN OPW.some_date BETWEEN R1.period_start_date and
period_end_date
AND R1.period_name = ''W
End-2005-04-02'
THEN 1 ELSE 0 END AS w
end-2005-04-02,
etc.
FROM ReportPeriods AS R1, OPW
WHERE ... ;
In short, think in terms of joins and not computations.
You are also making the classic Newbie error of trying to format the
data and the column headers in the backend. The whole point of a
tiered architecture is that you do this only in the front end.
Also, please post DDL, so that people do not have to guess what the
keys, constraints, Declarative Referential Integrity, datatypes, etc.
in your schema are. Sample data is also a good idea, along with clear
specifications.|||/* general this is the type of data and structure I am working with.
I need to show the data as indicated before below:
Basically I did this on the Applications Front end and sent in a Dynamic SQL
string. I know there must be a way to do this via a procedure; I'm just
learning the more advanced stuff now.
One of my current delemas is getting the W
End items to be grouped
columniarly as show below.
*/
CREATE TABLE OPW (
DataSource VarChar(50),
PoNumber VarChar(22),
ReqCreateDate datetime
ReqCreateTime varchar(10),
ReqSubmitDate datetime
ReqSubmitTime varchar(10),
NamePrep varchar(240))
INSERT INTO OPW ('MNS', PW02939, '04/01/2005', '11:39:58', '04/04/2005',
'12:30:00', 'JOHN')
INSERT INTO OPW ('MNS', PW02939, '04/01/2005', '11:39:58', '04/04/2005',
'12:30:00', 'BILL')
INSERT INTO OPW ('MNS', PW02937, '04/03/2005', '11:39:58', '04/07/2005',
'12:30:00', 'JOHN')
INSERT INTO OPW ('MNS', PW02934, '04/20/2005', '11:39:58', '04/23/2005',
'12:30:00', 'BOB')
INSERT INTO OPW ('MNS', PW02937, '04/03/2005', '11:39:58', '04/07/2005',
'12:30:00', 'JOHN')
INSERT INTO OPW ('MNS', PW02933, '04/17/2005', '11:39:58', '04/20/2005',
'12:30:00', 'JOHN')
/*
Service Line PO Created By Total Of PONumber 04/02/2005 04/09/2005
04/16/2005 04/23/2005 04/30/2005
EVPN MURPHY, ANTOINETTE J 4 4
MNS ADELER, LISA 13 11 2
MNS ARAGON, JENNIFER 59 35 24
MNS BERIG, CARI 22 12 10
MNS KIRBY, DANIEL 101 76 25
MNS KRENKEL, LISA R 60 60
MNS LEONARDELLI, JOHN MICHAEL 143 40 103
MNS MADSEN, SHANE 24 14 10
MNS MURPHY, ANTOINETTE J 3 3
MNS PYELL, CLAUDETTE 11 11
MNS SCARBOROUGH, MERNA 80 10 70
MNS WHALEY, LEE 6 6
*/
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OwD31a1SFHA.580@.TK2MSFTNGP15.phx.gbl...
>I think that you wont need your Temp table although i cant understand
>your intention, but these update can be done in an inline statement,
>because you are only calling a function to do this. Please post some DDL
>and smaple data which can be read a bit better then the pasting of your
>last post, and well fix it together :-)
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "John Rugo" <jrugo@.patmedia.net> schrieb im Newsbeitrag
> news:uAbrsS1SFHA.3544@.TK2MSFTNGP10.phx.gbl...
>|||Sorry i read for about 10 minutes you ddl and output (whch is formatted in a
strange way) and even now i dont know what you are going to do. I am
willing to help you but i cant understand your question. :-(
If this oist will last too long and i loose track of it, jst write me an
email to see wheter we can check it off ngposting and post the results back.
Jens Suessmeyer.
"John Rugo" <jrugo@.patmedia.net> schrieb im Newsbeitrag
news:%23RbaS61SFHA.900@.TK2MSFTNGP10.phx.gbl...
> /* general this is the type of data and structure I am working with.
> I need to show the data as indicated before below:
> Basically I did this on the Applications Front end and sent in a Dynamic
> SQL string. I know there must be a way to do this via a procedure; I'm
> just learning the more advanced stuff now.
> One of my current delemas is getting the W
End items to be grouped
> columniarly as show below.
> */
> CREATE TABLE OPW (
> DataSource VarChar(50),
> PoNumber VarChar(22),
> ReqCreateDate datetime
> ReqCreateTime varchar(10),
> ReqSubmitDate datetime
> ReqSubmitTime varchar(10),
> NamePrep varchar(240))
> INSERT INTO OPW ('MNS', PW02939, '04/01/2005', '11:39:58', '04/04/2005',
> '12:30:00', 'JOHN')
> INSERT INTO OPW ('MNS', PW02939, '04/01/2005', '11:39:58', '04/04/2005',
> '12:30:00', 'BILL')
> INSERT INTO OPW ('MNS', PW02937, '04/03/2005', '11:39:58', '04/07/2005',
> '12:30:00', 'JOHN')
> INSERT INTO OPW ('MNS', PW02934, '04/20/2005', '11:39:58', '04/23/2005',
> '12:30:00', 'BOB')
> INSERT INTO OPW ('MNS', PW02937, '04/03/2005', '11:39:58', '04/07/2005',
> '12:30:00', 'JOHN')
> INSERT INTO OPW ('MNS', PW02933, '04/17/2005', '11:39:58', '04/20/2005',
> '12:30:00', 'JOHN')
>
> /*
> Service Line PO Created By Total Of PONumber 04/02/2005 04/09/2005
> 04/16/2005 04/23/2005 04/30/2005
> EVPN MURPHY, ANTOINETTE J 4 4
> MNS ADELER, LISA 13 11 2
> MNS ARAGON, JENNIFER 59 35 24
> MNS BERIG, CARI 22 12 10
> MNS KIRBY, DANIEL 101 76 25
> MNS KRENKEL, LISA R 60 60
> MNS LEONARDELLI, JOHN MICHAEL 143 40 103
> MNS MADSEN, SHANE 24 14 10
> MNS MURPHY, ANTOINETTE J 3 3
> MNS PYELL, CLAUDETTE 11 11
> MNS SCARBOROUGH, MERNA 80 10 70
> MNS WHALEY, LEE 6 6
> */
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OwD31a1SFHA.580@.TK2MSFTNGP15.phx.gbl...
>
scoobyjw wrote:
