Prev: Cursor problem
Next: system view for functions
From: Sammy on 10 Jul 2010 20:07
I have the procedure below

I want simply modify the periods procedure below to be -- perpost >= '201001' and <= '201012' so I want a range of periods rather than a single period as in the example below
I would really appreciate it if someone can help me out on this.


Thanks
Sammy

USE DEMOAPP
Declare @perpost nvarchar(8)
/* Do not edit Above
change the Period Post Date below between the Single quotation marks to equal the results you want */

Set @perpost >= '201010' and <= '201012'

/* Do not edit Below
The Results will appear in a Table below. Copy the contents and Save to a black excel file. */
SELECT det.invtid as 'Part number',
det.descr as Description,
sum(det.QtyShip) as 'Quantity shipped',
det.cost as 'Ship Cost',
sum(det.QtyShip * det.cost) as TotalCost
FROM Vw_DWS_SOShipHeader hdr
INNER JOIN Vw_DWS_SOShipLine det ON hdr.ShipperID = det.ShipperID
WHERE hdr.perpost = @perpost
and hdr.BUYERNAME = 'REP'
and hdr.ShipCountry = 'USA'
and hdr.CustID NOT IN ('105756100','715130001')
and Cancelled = 0
GROUP BY det.invtid, det.descr, det.cost
Order by det.invtid


From: Tom Cooper on 10 Jul 2010 22:48
USE DEMOAPP
Declare @perpostBegin nvarchar(8)
Declare @perpostEnd nvarchar(8)
/* Do not edit Above
change the Period Post Date below between the Single quotation marks to equal the results you want */

Set @perpostBegin = '201010'
Set @perpostEnd = '201012'

/* Do not edit Below
The Results will appear in a Table below. Copy the contents and Save to a black excel file. */
SELECT det.invtid as 'Part number',
det.descr as Description,
sum(det.QtyShip) as 'Quantity shipped',
det.cost as 'Ship Cost',
sum(det.QtyShip * det.cost) as TotalCost
FROM Vw_DWS_SOShipHeader hdr
INNER JOIN Vw_DWS_SOShipLine det ON hdr.ShipperID = det.ShipperID
WHERE hdr.perpost = BETWEEN @perpostBegin AND @perpostEND
and hdr.BUYERNAME = 'REP'
and hdr.ShipCountry = 'USA'
and hdr.CustID NOT IN ('105756100','715130001')
and Cancelled = 0
GROUP BY det.invtid, det.descr, det.cost
Order by det.invtid
"Sammy" <s_commar(a)hotmail.com> wrote in message news:86106278-2789-4E42-86E3-48F383D1DE28(a)microsoft.com...
I have the procedure below

I want simply modify the periods procedure below to be -- perpost >= '201001' and <= '201012' so I want a range of periods rather than a single period as in the example below
I would really appreciate it if someone can help me out on this.


Thanks
Sammy

USE DEMOAPP
Declare @perpost nvarchar(8)
/* Do not edit Above
change the Period Post Date below between the Single quotation marks to equal the results you want */

Set @perpost >= '201010' and <= '201012'

/* Do not edit Below
The Results will appear in a Table below. Copy the contents and Save to a black excel file. */
SELECT det.invtid as 'Part number',
det.descr as Description,
sum(det.QtyShip) as 'Quantity shipped',
det.cost as 'Ship Cost',
sum(det.QtyShip * det.cost) as TotalCost
FROM Vw_DWS_SOShipHeader hdr
INNER JOIN Vw_DWS_SOShipLine det ON hdr.ShipperID = det.ShipperID
WHERE hdr.perpost = @perpost
and hdr.BUYERNAME = 'REP'
and hdr.ShipCountry = 'USA'
and hdr.CustID NOT IN ('105756100','715130001')
and Cancelled = 0
GROUP BY det.invtid, det.descr, det.cost
Order by det.invtid


From: Sammy on 11 Jul 2010 08:13
thanks very much Tom. Appreciate all your help
"Tom Cooper" <tomcooper(a)comcast.net> wrote in message news:O4M2pOKILHA.3560(a)TK2MSFTNGP05.phx.gbl...
USE DEMOAPP
Declare @perpostBegin nvarchar(8)
Declare @perpostEnd nvarchar(8)
/* Do not edit Above
change the Period Post Date below between the Single quotation marks to equal the results you want */

Set @perpostBegin = '201010'
Set @perpostEnd = '201012'

/* Do not edit Below
The Results will appear in a Table below. Copy the contents and Save to a black excel file. */
SELECT det.invtid as 'Part number',
det.descr as Description,
sum(det.QtyShip) as 'Quantity shipped',
det.cost as 'Ship Cost',
sum(det.QtyShip * det.cost) as TotalCost
FROM Vw_DWS_SOShipHeader hdr
INNER JOIN Vw_DWS_SOShipLine det ON hdr.ShipperID = det.ShipperID
WHERE hdr.perpost = BETWEEN @perpostBegin AND @perpostEND
and hdr.BUYERNAME = 'REP'
and hdr.ShipCountry = 'USA'
and hdr.CustID NOT IN ('105756100','715130001')
and Cancelled = 0
GROUP BY det.invtid, det.descr, det.cost
Order by det.invtid
"Sammy" <s_commar(a)hotmail.com> wrote in message news:86106278-2789-4E42-86E3-48F383D1DE28(a)microsoft.com...
I have the procedure below

I want simply modify the periods procedure below to be -- perpost >= '201001' and <= '201012' so I want a range of periods rather than a single period as in the example below
I would really appreciate it if someone can help me out on this.


Thanks
Sammy

USE DEMOAPP
Declare @perpost nvarchar(8)
/* Do not edit Above
change the Period Post Date below between the Single quotation marks to equal the results you want */

Set @perpost >= '201010' and <= '201012'

/* Do not edit Below
The Results will appear in a Table below. Copy the contents and Save to a black excel file. */
SELECT det.invtid as 'Part number',
det.descr as Description,
sum(det.QtyShip) as 'Quantity shipped',
det.cost as 'Ship Cost',
sum(det.QtyShip * det.cost) as TotalCost
FROM Vw_DWS_SOShipHeader hdr
INNER JOIN Vw_DWS_SOShipLine det ON hdr.ShipperID = det.ShipperID
WHERE hdr.perpost = @perpost
and hdr.BUYERNAME = 'REP'
and hdr.ShipCountry = 'USA'
and hdr.CustID NOT IN ('105756100','715130001')
and Cancelled = 0
GROUP BY det.invtid, det.descr, det.cost
Order by det.invtid


 | 
Pages: 1
Prev: Cursor problem
Next: system view for functions