Help needed

[-]

Hello to all
I need help on some sql statements and wondering is anyone out there is able
to help.
I've extracted some data from the database into a new table consisting of 4
fields

CustomerID Date Pallets Quantity
A0001 20050108 3 800
A0001 20050111 -2 -300
A0001 20050203 8 1200
A0001 20050212 -3 -500

my requirements:
1. to convert the date format from 20050118 to 18-Jan-2005
2. To have an option for selection by customer and date range :-
Selected Customer : A0001
Date Range : 01-Feb-2005 to 12-Feb-2005
3. the result to be display :-

CustomerID Date RunningPallet# RunningTotalQty
A0001 01-Feb-2005 1
500
A0001 03-Feb-2005 9
1,700
A0001 12-Feb-2005 6
1,200

hope someone can help me out on this as i am not an sql user

TQ

[Simon Hayes]

Re: Help needed


"-" <niteking@hotma il.com> wrote in message
news:4219a0af$1 _2@news.tm.net. my...[color=blue]
> Hello to all
> I need help on some sql statements and wondering is anyone out there is
> able to help.
> I've extracted some data from the database into a new table consisting of
> 4 fields
>
> CustomerID Date Pallets Quantity
> A0001 20050108 3 800
> A0001 20050111 -2 -300
> A0001 20050203 8 1200
> A0001 20050212 -3 -500
>
> my requirements:
> 1. to convert the date format from 20050118 to 18-Jan-2005
> 2. To have an option for selection by customer and date range :-
> Selected Customer : A0001
> Date Range : 01-Feb-2005 to 12-Feb-2005
> 3. the result to be display :-
>
> CustomerID Date RunningPallet# RunningTotalQty
> A0001 01-Feb-2005 1 500
> A0001 03-Feb-2005 9 1,700
> A0001 12-Feb-2005 6 1,200
>
> hope someone can help me out on this as i am not an sql user
>
> TQ
>
>[/color]

1. You should format the date in your client application, not in MSSQL. But
if you really want to do it on the server side, have a look at the CONVERT()
function in Books Online.

2. MSSQL is a server, and it has no concept of interfaces, prompts, menus
etc. To allow a user to select their own criteria, you would need to build
some sort of front end, in whatever your preferred tool/language is -
Access, ASP, C# etc. The criteria that the user selects should probably be
used as parameters to a stored procedure (see below).

3. You can use a query or stored procedure as shown below (not fully
tested). In some cases, you might find it easier to do running totals in the
front end when you present the data; you could also consider using a
reporting tool for more complex work.

If this doesn't help, or if you get unexpected results, I suggest you post
CREATE TABLE and INSERT statements to set up a test case:

http://www.aspfaq.com/etiquette.asp?id=5006

Simon


/* As a query */
select
CustomerID,
'20050201' as 'Date',
sum(Pallets) as 'Pallets',
sum(Quantity) as 'Quantity'
from
dbo.MyTable
where
[Date] < '20050202'
group by
CustomerID
union all
select
t1.CustomerID,
t1.[Date],
sum(t2.Pallets) ,
sum(t2.Quantity )
from
dbo.MyTable t1 join dbo.MyTable t2
on t1.CustomerID = t2.CustomerID
and t1.[Date] >= t2.[Date]
where
t1.CustomerID = 'A0001' and
t1.[Date] >='20050201' and
t1.[Date] < '20050213'
group by
t1.CustomerID,
t1.[Date]
order by
Date

/* As a proc */
create proc dbo.getTotals
@CustomerID char(5),
@StartDate datetime,
@EndDate datetime
as
set nocount on
begin

select
CustomerID,
@StartDate as 'Date',
sum(Pallets) as 'Pallets',
sum(Quantity) as 'Quantity'
from
dbo.MyTable
where
[Date] < dateadd(dd, 1, @StartDate)
group by
CustomerID
union all
select
t1.CustomerID,
t1.[Date],
sum(t2.Pallets) ,
sum(t2.Quantity )
from
dbo.MyTable t1 join dbo.MyTable t2
on t1.CustomerID = t2.CustomerID
and t1.[Date] >= t2.[Date]
where
t1.CustomerID = @CustomerID and
t1.[Date] >= @StartDate and
t1.[Date] < @EndDate
group by
t1.CustomerID,
t1.[Date]
order by
Date

end

[Hugo Kornelis]

Re: Help needed

On Mon, 21 Feb 2005 16:48:39 +0800, - wrote:
[color=blue]
>Hello to all
>I need help on some sql statements and wondering is anyone out there is able
>to help.
>I've extracted some data from the database into a new table consisting of 4
>fields[/color]

Hi TQ,

Is there any specific reason why you made a new table with a subset of the
data in the database? Since you are introducing redundancy, you now run
the risk of corrupting data integrity. And you might possibly be degrading
performance as well.
[color=blue]
>CustomerID Date Pallets Quantity
>A0001 20050108 3 800
>A0001 20050111 -2 -300
>A0001 20050203 8 1200
>A0001 20050212 -3 -500[/color]

Date is a reserved word in MS SQL Server. I suggest you to choose another
column name.
[color=blue]
>my requirements:
>1. to convert the date format from 20050118 to 18-Jan-2005[/color]

I assume that you have defined Date as a column of datatype datetime or
smalldatetime. In that case, the date representation in internal storage
is unlike anything you or I would recognise as a date. How you see it is
decided by the client software you're using. Even Query Analyzer and
Enterprise Manager (both by MS, both part of the SQL Server package) use
different formats to display dates.

If the column Date is not defined as [small]datetime, then you have a much
bigger problem - I suggest you fix that first before attempting to execute
any query that uses the dates for calculations, selections, ordering or
whatever.

[color=blue]
>2. To have an option for selection by customer and date range :-
> Selected Customer : A0001
> Date Range : 01-Feb-2005 to 12-Feb-2005
>3. the result to be display :-
>
> CustomerID Date RunningPallet# RunningTotalQty
> A0001 01-Feb-2005 1
>500
> A0001 03-Feb-2005 9
>1,700
> A0001 12-Feb-2005 6
>1,200[/color]

-- Check the datatypes below and change them to match
-- the datatype of the columns CustomerID and Date.
DECLARE @CustID char(5)
DECLARE @StartDate smalldatetime
DECLARE @EndDate smalldatetime
SET @CustID = 'A00001'
SET @StartDate = '20050201' -- Always use yyyymmdd for date literals
SET @EndDate = '20050212'

SELECT a.CustomerID, a."Date",
COALESCE(SUM(b. Pallets),0) AS RunningPallet#,
COALESCE(SUM(b. Quantity),0) AS RunningTotalQty
FROM (SELECT CustomerID, Date
FROM MyTable
WHERE CustomerID = @CustID
AND "Date" >= @StartDate
AND "Date" <= @EndDate
UNION
SELECT @CustomerID, @StartDate) AS a
LEFT OUTER JOIN MyTable AS b
ON b.CustomerID = a.CustomerID
AND b."Date" <= a.Date
GROUP BY a.CustomerID, a."Date"

(untested - post CREATE TABLE and INSERT statements in your question to
get tested results. See www.aspfaq.com/5006)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Re: Help needed

Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes:[color=blue]
> Date is a reserved word in MS SQL Server. I suggest you to choose another
> column name.[/color]

Date may show up in different colour in Query Analyzer, but it's certainly
not a reserved keyword:

CREATE TABLE date(date datetime NOT NULL)
The command(s) completed successfully.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[David Portas]

Re: Help needed

Books Online lists DATE under the Future Keywords section ("could be
reserved in future releases") and as an ODBC Reserved Keyword
("applicatio ns should avoid using these keywords"). DATE is also a
reserved word in ANSI/ISO Standard SQL.

However, the most important reason not to use the word DATE as a column
name, is that it's such a vague and uninformative name.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

Re: Help needed

David Portas (REMOVE_BEFORE_ REPLYING_dporta s@acm.org) writes:[color=blue]
> Books Online lists DATE under the Future Keywords section ("could be
> reserved in future releases") and as an ODBC Reserved Keyword
> ("applicatio ns should avoid using these keywords"). DATE is also a
> reserved word in ANSI/ISO Standard SQL.[/color]

As a datatype name, I guess? Datatypes are not reserved keywords in T-SQL,
they are just unreserved keywords:

CREATE TABLE confuse (int char(2) NOT NULL,
timestamp decimal(8,2) NOT NULL,
datetime int NOT NULL,
money uniqueidentifie r NOT NULL)

The are not case-sensitive in case-sensitive databases through.
[color=blue]
> However, the most important reason not to use the word DATE as a column
> name, is that it's such a vague and uninformative name.[/color]

True.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp