Resettin Running Total -- sql server programming

Scorcel
8/13/2004 11:45:01 PM

Hi!
Thanks John for the Reply but when I executed your query it returns nothing:
SELECT intPalletNo AS [ PLT #],
intQuantity AS [ QTY ],
CONVERT(varchar(20), dateStart, 113) AS [ DATE START ],
CONVERT(varchar(20), dateEnd, 113) AS [ DATE END ],
(SELECT SUM(pro1.intQuantity) FROM CDMS_tbl_Palletizer_Pallets pro1
WHERE pro1.DateStart <= pro.DateStart AND Pro1.txtPackRepNo ='42230190CB'
AND pro1.txtLotCode = pro.txtLotCode)
AS[Sum PO],
txtUserStart AS [ OPERATOR START ],
txtUserEnd AS [ OPERATOR END ],
txtPackRepNo AS [ PR# ]
FROM CDMS_tbl_Palletizer_Pallets pro
WHERE Pro.txtPackRepNo ='42230190CB'

Output:
PLT # QTY DATE START DATE END
Sum PO OPERATOR START OPERATOR END
PR#
----------- ----------- -------------------------
--------------------------- ----------- ------------------------------
------------------------------ ----------------------------------

(0 row(s) affected)

[quoted text, click to view]

Scorcel
8/14/2004 3:21:02 AM

Thanks again John:
Yes I may Opt to show the txtLotCode Field later on.
OK I managed to simplify a bit my query to this one below:

SELECT CDMS_tbl_Palletizer_Pallets_tmp.intPalletNo AS [ PLT #],
CDMS_tbl_Palletizer_Pallets_tmp.intQuantity AS [ QTY ],
CONVERT(varchar(20), CDMS_tbl_Palletizer_Pallets_tmp.dateStart, 113) AS [
DATE START ],
CONVERT(varchar(20), CDMS_tbl_Palletizer_Pallets_tmp.dateEnd, 113) AS [
DATE END ],
SUM(CDMS_tbl_Palletizer_Pallets_tmp.intQuantity) AS [SUM PO],
CDMS_tbl_Palletizer_Pallets_tmp.txtUserStart AS [
OPERATOR START ],
CDMS_tbl_Palletizer_Pallets_tmp.txtUserEnd AS [ OPERATOR END
],
CDMS_tbl_Palletizer_Pallets_tmp.txtPackRepNo AS [
PR# ],CDMS_tbl_Palletizer_Pallets_tmp.txtLotCode
FROM CDMS_tbl_Palletizer_Pallets_tmp INNER JOIN
CDMS_tbl_Palletizer_Pallets_tmp CDMS_tbl_Palletizer_Pallets_tmp_1
ON CDMS_tbl_Palletizer_Pallets_tmp.txtPackRepNo =
CDMS_tbl_Palletizer_Pallets_tmp_1.txtPackRepNo AND
CDMS_tbl_Palletizer_Pallets_tmp.dateStart >=
CDMS_tbl_Palletizer_Pallets_tmp_1.dateStart
GROUP BY CDMS_tbl_Palletizer_Pallets_tmp.intPalletNo,
CDMS_tbl_Palletizer_Pallets_tmp.intQuantity,
CONVERT(varchar(20), CDMS_tbl_Palletizer_Pallets_tmp.dateStart, 113),
CONVERT(varchar(20), CDMS_tbl_Palletizer_Pallets_tmp.dateEnd, 113),
CDMS_tbl_Palletizer_Pallets_tmp.dateStart,
CDMS_tbl_Palletizer_Pallets_tmp.txtPackRepNo,
CDMS_tbl_Palletizer_Pallets_tmp.txtUserEnd,
CDMS_tbl_Palletizer_Pallets_tmp.txtUserStart,CDMS_tbl_Palletizer_Pallets_tmp.txtLotCode
HAVING (CDMS_tbl_Palletizer_Pallets_tmp.txtPackRepNo = 1)
ORDER BY CDMS_tbl_Palletizer_Pallets_tmp.dateStart,
CDMS_tbl_Palletizer_Pallets_tmp.intPalletNo

Here is a sample data that could be played with:
Insert into CDMS_tbl_Palletizer_Pallets_tmp VALUES('1',1,45,'1/1/04
1:01:00','1/2/04 1:20:00',1,'Jay','Jay',1,'1/1/04','A')
Insert into CDMS_tbl_Palletizer_Pallets_tmp VALUES('1',1,25,'1/1/04
1:21:00','1/2/04 1:40:00',1,'Jay','Jay',1,'1/1/04','A')
Insert into CDMS_tbl_Palletizer_Pallets_tmp VALUES('1',1,10,'1/1/04
1:41:00','1/2/04 1:50:00',1,'Jay','Jay',1,'1/1/04','A')
Insert into CDMS_tbl_Palletizer_Pallets_tmp VALUES('1',1,25,'1/1/04
1:51:00','1/2/04 1:55:00',1,'Jay','Jay',1,'1/1/04','A')
Insert into CDMS_tbl_Palletizer_Pallets_tmp VALUES('1',1,25,'1/1/04
2:01:00','1/2/04 2:10:00',1,'Jay','Jay',1,'1/1/04','B')
Insert into CDMS_tbl_Palletizer_Pallets_tmp VALUES('1',1,25,'1/1/04
2:21:00','1/2/04 2:30:00',1,'Jay','Jay',1,'1/1/04','B')
Insert into CDMS_tbl_Palletizer_Pallets_tmp VALUES('1',1,25,'1/1/04
2:31:00','1/2/04 2:40:00',1,'Jay','Jay',1,'1/1/04','B')

I'm expecting to see a result column out of this data like "Total Quantity
Per LotCode"
"Total Quantity Per LotCode" LotCode
45 A
70 A
80 A
105 A
25(Reset) B
50 B
75 B

Again Thanks a LOT!

[quoted text, click to view]

John Bell
8/14/2004 7:38:54 AM

Hi

This is untested but try:

SELECT intPalletNo AS [ PLT #],
intQuantity AS [ QTY ],
CONVERT(varchar(20), dateStart, 113) AS [ DATE START ],
CONVERT(varchar(20), dateEnd, 113) AS [ DATE END ],
(SELECT SUM(pro1.intQuantity) FROM CDMS_tbl_Palletizer_Pallets pro1
WHERE pro1.DateStart <= pro.DateStart AND Pro1.txtPackRepNo =@txtPackRepNo
AND pro1.txtLotCode = pro.txtLotCode)
AS[Sum PO],
txtUserStart AS [ OPERATOR START ],
txtUserEnd AS [ OPERATOR END ],
txtPackRepNo AS [ PR# ]
FROM CDMS_tbl_Palletizer_Pallets pro
WHERE Pro.txtPackRepNo =@txtPackRepNo

John

[quoted text, click to view]

Joe Celko
8/14/2004 9:02:16 AM

Your *real* problem is that you do not know how to program in SQL.

1) All the columns are NULL-able, so this can never have a key. Without
a key, this is not a table and I'll bet that ity is full of garbage.

No DRI, no defaults, no CHECK() constraints; this is a poorly designed
file at best. And the only date format allowed in Standard SQL is
ISO-8601.

2) You never read ISO-11179 or took a data modeling course, so you are
puting those silly datatype prefixes on the columns. This destroys the
data dictionary (which you probably do not have anyway).

3) Running totals are a report. We do reports in the front end with
report writers and format data in the front end. You do not do this in
the database. This the whole idea of tiered architectures.

[quoted text, click to view]
is I want to have a resetting running total, meaning I will have a
running total on the intQuantity Field [sic] Per txtLotCode, and would
reset to 0 every new value in txtlotCode. <<

This is a description of a sequential file being read one record at a
time. Rows are NOT records, columns are NOT fields!! Reset? Explain
how you reset in an unordered set.

[quoted text, click to view]

Please at least read a book on RDBMS, if your boss will not pay for your
much needed training.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

John Bell
8/14/2004 10:13:19 AM

Hi

You may want to show txlLotCode in the result set. You have not said why
the subquery is limited on txtPackRepNo?

Example data ( as INSERT statements
http://vyaskn.tripod.com/code.htm#inserts ) would also help.

John

[quoted text, click to view]

Hugo Kornelis
8/14/2004 5:57:49 PM

[quoted text, click to view]

Hi Scorcel,

You can get this result by addind the following to your select list:

(SELECT SUM(aa.intQuantity)
FROM CDMS_tbl_Palletizer_Pallets_tmp AS aa
WHERE aa.txtLotCode = CDMS_tbl_Palletizer_Pallets_tmp.txtLotCode
AND aa.dateStart <= CDMS_tbl_Palletizer_Pallets_tmp.dateStart)
AS "Running Total"

(I advise you to use short aliases for both copies of the tables you are
joining together, using meaningful short mnemonic codes instead of using
the whole length table name plus a meaningless integer - but that's up to
you, of course)

Best, Hugo
--

John Bell
8/14/2004 9:58:20 PM

Hi

Taking out the txtPackRepNo =@txtPackRepNo conditions that is exactly what I
got with your data:

SELECT intPalletNo AS [ PLT #],
intQuantity AS [ QTY ],
CONVERT(varchar(20), dateStart, 113) AS [ DATE START ],
CONVERT(varchar(20), dateEnd, 113) AS [ DATE END ],
txtLotCode,
(SELECT SUM(pro1.intQuantity) FROM CDMS_tbl_Palletizer_Pallets pro1
WHERE pro1.DateStart <= pro.DateStart
AND pro1.txtLotCode = pro.txtLotCode)
AS[Sum PO],
txtUserStart AS [ OPERATOR START ],
txtUserEnd AS [ OPERATOR END ],
txtPackRepNo AS [ PR# ]
FROM CDMS_tbl_Palletizer_Pallets pro

John

[quoted text, click to view]
CDMS_tbl_Palletizer_Pallets_tmp.txtUserStart,CDMS_tbl_Palletizer_Pallets_tmp
..txtLotCode
[quoted text, click to view]

Scorcel
8/14/2004 10:37:01 PM

Thanks Celko: Your right! I was not sent to a RDMS training so I missed lots
of important Items to take note in creating databases. But this system has
been running for almost five years now! and was created by other people and
is now forwarded to me. Well, yes I'll find some time reading the Relational
Database Management System (RDMS) is this correct?. Anyway, at least you
could have told me how make the my SQL Statement give me the correct
information, or did you understand what I mean. So far its returning other
information that I asked from my query like the Over-All running Total its
doing OK, exept this resetting Running Total. Again Thank you very much.

[quoted text, click to view]

Scorcel
8/14/2004 10:41:01 PM

Hello Hugo!

The query you gave me would only give me a continuing running Total, what
I've been asking is a Resetting Running Total. Like this one.
intQuantity "Total Quantity Per LotCode" LotCode
45 45 A
25 70 A
10 80 A
25 105 A
25 25(Reset For New LotCode) B
25 50 B
25 75 B

Thanks so much for taking time.

[quoted text, click to view]

John Bell
8/15/2004 9:52:44 AM

Hi

Just to add... you results are not guarenteed to be in an order without an
ORDER BY clause, therefore add ORDER BY txtLotCode, CONVERT(varchar(20),
dateStart, 113)

John

[quoted text, click to view]

Scorcel
8/15/2004 3:57:02 PM

HELLO JOHN!!!!

YOU MADE IT!!!! I was already thinking that my problem is a hopeless one.
Thank you so much!!!! and thank you for taking time with my problem....
YOU'RE THE MAN!!!! Praise the LORD!!!!

[quoted text, click to view]

G.C.Mandrake
8/16/2004 2:00:12 PM

[quoted text, click to view]

G.C.Mandrake
8/16/2004 2:22:33 PM

With Yukon on the horizon it's important that users,especially
those just coming to the sql world, have as clear an understanding
of concepts as possible.For example,

[quoted text, click to view]

Yet in the thread:
From: Joe Celko
Subject: Re: Alternative for TOP
Newsgroups: microsoft.public.sqlserver.programming
Date: 2004-08-04 11:09:22 PST
http://tinyurl.com/4q7hz

You write:
'The best answer given uses a subquery to establish a subset based on a
count.'

SELECT DISTINCT salary
FROM Personnel AS P1
WHERE @n >= (SELECT COUNT(*) - 1 -- control parameter
FROM Personnel AS P2
WHERE P1.salary < P2.salary)

Now the same correlated subquery idea can, of course, when used
with *SUM*, give a running total.You are simply telling users that
the *same* construct is appropriate or non appropriate based
on its intent, ie. display(Select) vs. filter(Where).
Is this the message you really want to give?

Hugo Kornelis
8/16/2004 2:26:26 PM

[quoted text, click to view]

Hi Scorcel,

Did you test it? Could you please copy and paste the results of your
testing in a reply message? This is something we really ned to look into,
as my testing returns the following data:

PLT # QTY Running Total txtLotCode
----------- ----------- ------------- ------------
1 45 45 A
1 25 70 A
1 10 80 A
1 25 105 A
1 25 25 B
1 25 50 B
1 25 75 B

I got these results by copying and pasting the DDL from your first
message, then copying and pasting the INSERT statements and the SELECT
statement from a later message, fixing the table name (the DDL used a
slightly different name than the INSERT and SELECT stmts) and adding the
extra subquery from my own post.

Did you copy and paste my subquery when you tested it, or did you retype
it manually? If so, could you check for possible typing errors?

If you're sure you didn't make an error when copying my subquery and your
results are indeed wrong, then please post your results. And also post the
output when you run SELECT @@VERSION - we might have found a bug here!

Best, Hugo
--

G.C.Mandrake
8/16/2004 2:51:43 PM

[quoted text, click to view]

That's a marketing question.Are you sure you want me
to answer?<g>

Roji. P. Thomas
8/16/2004 11:55:36 PM

whats that about?
Do you really have a TARGET abt the number of
questions replied ;) ?

--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com

[quoted text, click to view]

Roji. P. Thomas
8/17/2004 12:20:32 AM

(woth Courtesy to Louis)

SELECT col1, col2, sum(col3) as Total
GROUP BY col1, col2

is considered okay, so why u always recommend a tool instead of

Select col1, col2, concatenate(col2, varcharcolumn) as UDAggregate
GROUP BY col1, col2

--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com

[quoted text, click to view]

G.C.Mandrake
8/17/2004 11:45:11 AM

[quoted text, click to view]

No answer, what's the problem? No DDL? :)

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Resettin Running Total