Please Help

I am trying to pull the last 30 records in a table. I'm trying to write a
dynamic stored procedure in order to do this. I am joining a couple tables
and the table i need to get the 30 records out of is the second table. I
can't seem to get it to work with my code. Below is the code that I am using
to try to do this, I have declared the variables earlier in this procedure:
(Select Distinct LotID, @.LowNum = max(LotID)-30, @.HighNum = max(LotID)
From dbo.schedulegovcom
Where LotID<=@.HighNum
and >=@.LowNum
Group by LotID) pTry:
SELECT TOP 30
[field list]
FROM
dbo.schedulegovcom
ORDER BY
LotID DESC
Mike
"A.B." <AB@.discussions.microsoft.com> wrote in message
news:F449DAD0-7B92-4E1A-9676-1604C2D79D3A@.microsoft.com...
>I am trying to pull the last 30 records in a table. I'm trying to write a
> dynamic stored procedure in order to do this. I am joining a couple tables
> and the table i need to get the 30 records out of is the second table. I
> can't seem to get it to work with my code. Below is the code that I am
> using
> to try to do this, I have declared the variables earlier in this
> procedure:
> (Select Distinct LotID, @.LowNum = max(LotID)-30, @.HighNum = max(LotID)
> From dbo.schedulegovcom
> Where LotID<=@.HighNum
> and >=@.LowNum
> Group by LotID) p|||You can not mix resultset with assigning value to a variable in the same
select statement.
-- wrong
select @.i = orderid, customerid from dbo.orders
AMB
"A.B." wrote:

> I am trying to pull the last 30 records in a table. I'm trying to write a
> dynamic stored procedure in order to do this. I am joining a couple tables
> and the table i need to get the 30 records out of is the second table. I
> can't seem to get it to work with my code. Below is the code that I am usi
ng
> to try to do this, I have declared the variables earlier in this procedure
:
> (Select Distinct LotID, @.LowNum = max(LotID)-30, @.HighNum = max(LotID)
> From dbo.schedulegovcom
> Where LotID<=@.HighNum
> and >=@.LowNum
> Group by LotID) p|||Thanks that worked
"Mike Jansen" wrote:

> Try:
> SELECT TOP 30
> [field list]
> FROM
> dbo.schedulegovcom
> ORDER BY
> LotID DESC
> Mike
> "A.B." <AB@.discussions.microsoft.com> wrote in message
> news:F449DAD0-7B92-4E1A-9676-1604C2D79D3A@.microsoft.com...
>
>

Pivot-related query

I have a couple of tables (see below) that tell me what days a particular
customer depot (LocationID) is scheduled to have deliveries on (DeliveryDays
table), and what days the Courier picks up for these deliveries
(CarrierCollections table)
I want to create a query (a view actually) that tells me for each depot,
which carrier collection days are possible/appropriate - this is to help the
people who are preparing the goods for despatch. The complication is that I
want one row for each Depot, eg:
LocationID, Mon, Tues, Weds, Thurs, Fri
8, null, 1, null, 1, null
9, null, null, null, 1, null
Pivots are fiddly enough as it is, but combining records in this way is
beyond me as yet. Has anyone got any ideas or solutions?
I can achieve the result I want by running one query within another (in my
ASP application) but this would be very inefficient and would significantly
slow down this page, so I would like to make SQL do the hard work so I only
need to do one query in my ASP app.
Thanks in advance...
Chris
Tables & Data:
CREATE TABLE [dbo].[CarrierCollections] (
[CarrierID] [int] NOT NULL ,
[CollectionDay] [tinyint] NOT NULL ,
[DeliveryDay] [tinyint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DeliveryDays] (
[LocationID] [int] NOT NULL ,
[DeliveryDay] [tinyint] NOT NULL
) ON [PRIMARY]
GO
Insert into DeliveryDays Values (8, 3)
Insert into DeliveryDays Values (8, 5)
Insert into DeliveryDays Values (9, 2)
Insert into DeliveryDays Values (9, 6)
Insert into CarrierCollections Values (119, 3, 4)
Insert into CarrierCollections Values (119, 3, 5)
Insert into CarrierCollections Values (119, 5, 2)
Insert into CarrierCollections Values (119, 5, 3)
Insert into CarrierCollections Values (119, 5, 6)
Note: 1 = Sunday, 7 = Saturday
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]Something like:
SELECT d1.LocationID,
MAX( CASE c1.DeliveryDay WHEN 1 THEN 1 END ) AS "Sunday",
MAX( CASE c1.DeliveryDay WHEN 2 THEN 1 END ) AS "Monday",
MAX( CASE c1.DeliveryDay WHEN 3 THEN 1 END ) AS "Tuesday",
MAX( CASE c1.DeliveryDay WHEN 4 THEN 1 END ) AS "Wednesday",
MAX( CASE c1.DeliveryDay WHEN 5 THEN 1 END ) AS "Thursday",
MAX( CASE c1.DeliveryDay WHEN 6 THEN 1 END ) AS "Friday",
MAX( CASE c1.DeliveryDay WHEN 7 THEN 1 END ) AS "Saturday"
FROM CarrierCollections c1
INNER JOIN DeliveryDays d1
ON c1.DeliveryDay = d1.DeliveryDay
GROUP BY d1.LocationID ;
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uMs6WifEGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Something like:
> SELECT d1.LocationID,
> MAX( CASE c1.DeliveryDay WHEN 1 THEN 1 END ) AS "Sunday",
> MAX( CASE c1.DeliveryDay WHEN 2 THEN 1 END ) AS "Monday",
> MAX( CASE c1.DeliveryDay WHEN 3 THEN 1 END ) AS "Tuesday",
> MAX( CASE c1.DeliveryDay WHEN 4 THEN 1 END ) AS "Wednesday",
> MAX( CASE c1.DeliveryDay WHEN 5 THEN 1 END ) AS "Thursday",
> MAX( CASE c1.DeliveryDay WHEN 6 THEN 1 END ) AS "Friday",
> MAX( CASE c1.DeliveryDay WHEN 7 THEN 1 END ) AS "Saturday"
> FROM CarrierCollections c1
> INNER JOIN DeliveryDays d1
> ON c1.DeliveryDay = d1.DeliveryDay
> GROUP BY d1.LocationID ;
Bingo!
Damn! I almost got this far but missed out the MAX() function. Seems obvious
in retrospect.
Thanks very much Anith.
Chris