Showing posts with label qty. Show all posts
Showing posts with label qty. Show all posts

Sunday, March 25, 2012

Computing a Grand Total

I have inherited a table that has entries such as:
AccountName, [Invoice No], [Sales Price] Qty, etc...
I would like to have a SubTotal and GrandTotal. So, I have a select
statement something like this:
Select AccountName, Qty, [Invoice No], [Sales Price], [Sales Price] * Qty as
SubTotal
FROM ...
That works fine, I get an extra column with the product of Qty and Sales
Price.
However, how would I get a Grand Total per invoice? I can do it in VB.Net,
but would like to see how to do it in SQL.
TIA,
PaoloCOMPUTE SUM(SubTotal) BY [Invoice No]
Here's the Books ONline URL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/69009df2-dba5-4bcb-b2ae-
a7502537cb3e.htm
HTH. Ryan
"Paul" <PaulContactMe@.TheCornerStore.com> wrote in message
news:LesBf.1078$v81.173@.fe12.lga...
>I have inherited a table that has entries such as:
> AccountName, [Invoice No], [Sales Price] Qty, etc...
> I would like to have a SubTotal and GrandTotal. So, I have a select
> statement something like this:
> Select AccountName, Qty, [Invoice No], [Sales Price], [Sales Price] * Qty
> as SubTotal
> FROM ...
> That works fine, I get an extra column with the product of Qty and Sales
> Price.
> However, how would I get a Grand Total per invoice? I can do it in VB.Net,
> but would like to see how to do it in SQL.
> TIA,
> Paolo
>
>|||Try
SELECT [Invoice No], SUM([Sales Price] * Qty)
FROM [YourTable]
GROUP BY [Invoice No]
If you want grand totals for AccountName and [Invoice No]
SELECT AccountName, [Invoice No], SUM([Sales Price] * Qty) AS "Total"
FROM [YourTable]
GROUP BY AccountName, [Invoice No]
WITH ROLLUP
The out put of the above query will have rows like
AccountName [Invoice No] Total
---
Some Account NULL $3.50
A row with a NULL in the [Invoice No] column indicates the grand total
for that AccountName. You will also get a row like
AccountName [Invoice No] Total
---
NULL NULL $1500.78
which shows the total for all invoices and accounts.
"Paul" wrote:

> I have inherited a table that has entries such as:
> AccountName, [Invoice No], [Sales Price] Qty, etc...
> I would like to have a SubTotal and GrandTotal. So, I have a select
> statement something like this:
> Select AccountName, Qty, [Invoice No], [Sales Price], [Sales Price] * Qty
as
> SubTotal
> FROM ...
> That works fine, I get an extra column with the product of Qty and Sales
> Price.
> However, how would I get a Grand Total per invoice? I can do it in VB.Net,
> but would like to see how to do it in SQL.
> TIA,
> Paolo
>
>|||Try following examples on pubs database. You will have to use COMPUTE clause
.
use pubs
go
select ord_num, stor_id, qty from sales
order by ord_num
compute sum(qty) by ord_num
--using computed expression query will look like.
select ord_num, stor_id, (qty * 2) as subtotal from sales
order by ord_num
compute sum(qty * 2) by ord_num
"Paul" wrote:

> I have inherited a table that has entries such as:
> AccountName, [Invoice No], [Sales Price] Qty, etc...
> I would like to have a SubTotal and GrandTotal. So, I have a select
> statement something like this:
> Select AccountName, Qty, [Invoice No], [Sales Price], [Sales Price] * Qty
as
> SubTotal
> FROM ...
> That works fine, I get an extra column with the product of Qty and Sales
> Price.
> However, how would I get a Grand Total per invoice? I can do it in VB.Net,
> but would like to see how to do it in SQL.
> TIA,
> Paolo
>
>|||I agree with Mark. Using ROLLUP on a GROUP BY is the far better choice,
since it will always be returned in one result set. COMPUTE is pretty ugly
to deal with for the user program.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:799AF000-43FC-483A-94E1-D5DFC3FA3CEF@.microsoft.com...
> Try
> SELECT [Invoice No], SUM([Sales Price] * Qty)
> FROM [YourTable]
> GROUP BY [Invoice No]
> If you want grand totals for AccountName and [Invoice No]
> SELECT AccountName, [Invoice No], SUM([Sales Price] * Qty) AS "Total"
> FROM [YourTable]
> GROUP BY AccountName, [Invoice No]
> WITH ROLLUP
> The out put of the above query will have rows like
> AccountName [Invoice No] Total
> ---
> Some Account NULL $3.50
> A row with a NULL in the [Invoice No] column indicates the grand total
> for that AccountName. You will also get a row like
>
> AccountName [Invoice No] Total
> ---
> NULL NULL $1500.78
> which shows the total for all invoices and accounts.
>
> --
> "Paul" wrote:
>

Sunday, March 11, 2012

Complicated query

Hi,

I have two tables Trade table and Cons table. Records are inserted in
both the tables independent of each other. There are fields like
Exc_Ref, Qty, Date in both the tables.

I need to write a query which should give me records :

1. Where there is missing Exc_Ref value in either of the table. i.e.
If Trade table has a Exc_Ref value but missing in Cons table then that
record should be displayed. Similarly if Cons has a Exc_Ref value
which is not found in Trade table then that too should be displayed.

2. In case where both the tables have matching Exc_Ref data then it
should display the record only when the remaining column does not
match like Qty or Date.

Please help me to resolve this complicated query.

Thanks
NickSomething along these lines should do it.

SELECT COALESCE(A.Exc_Ref, B.Exc_Ref) as Exc_Ref,
CASE WHEN B.Exc_Ref IS NULL
THEN 'Trade'
WHEN A.Exc_Ref IS NULL
THEN 'Cons'
ELSE ' <'
End as Compare,
A.OtherCol1, B.OtherCol1,
...
A.OtherCol9, B.OtherCol9
FROM Trade as A
FULL OUTER
JOIN Cons as B
ON A.Exc_Ref = B.Exc_Ref
WHERE A.Exc_Ref IS NULL
OR B.Exc_Ref IS NULL
OR A.OtherCol1 <B.OtherCol1
OR ...
OR A.OtherCol9 <B.OtherCo9

This assumes that Exc_Ref is the unique key to both tables.

Roy Harvey
Beacon Falls, CT

On Thu, 06 Sep 2007 10:29:30 -0700, Nick
<nachiket.shirwalkar@.gmail.comwrote:

Quote:

Originally Posted by

>Hi,
>
>I have two tables Trade table and Cons table. Records are inserted in
>both the tables independent of each other. There are fields like
>Exc_Ref, Qty, Date in both the tables.
>
>I need to write a query which should give me records :
>
>1. Where there is missing Exc_Ref value in either of the table. i.e.
>If Trade table has a Exc_Ref value but missing in Cons table then that
>record should be displayed. Similarly if Cons has a Exc_Ref value
>which is not found in Trade table then that too should be displayed.
>
>2. In case where both the tables have matching Exc_Ref data then it
>should display the record only when the remaining column does not
>match like Qty or Date.
>
>Please help me to resolve this complicated query.
>
>Thanks
>Nick

|||On 6 Sep, 20:06, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

Something along these lines should do it.
>
SELECT COALESCE(A.Exc_Ref, B.Exc_Ref) as Exc_Ref,
CASE WHEN B.Exc_Ref IS NULL
THEN 'Trade'
WHEN A.Exc_Ref IS NULL
THEN 'Cons'
ELSE ' <'
End as Compare,
A.OtherCol1, B.OtherCol1,
...
A.OtherCol9, B.OtherCol9
FROM Trade as A
FULL OUTER
JOIN Cons as B
ON A.Exc_Ref = B.Exc_Ref
WHERE A.Exc_Ref IS NULL
OR B.Exc_Ref IS NULL
OR A.OtherCol1 <B.OtherCol1
OR ...
OR A.OtherCol9 <B.OtherCo9
>
This assumes that Exc_Ref is the unique key to both tables.
>
Roy Harvey
Beacon Falls, CT
>
On Thu, 06 Sep 2007 10:29:30 -0700, Nick
>
>
>
<nachiket.shirwal...@.gmail.comwrote:

Quote:

Originally Posted by

Hi,


>

Quote:

Originally Posted by

I have two tables Trade table and Cons table. Records are inserted in
both the tables independent of each other. There are fields like
Exc_Ref, Qty, Date in both the tables.


>

Quote:

Originally Posted by

I need to write a query which should give me records :


>

Quote:

Originally Posted by

1. Where there is missing Exc_Ref value in either of the table. i.e.
If Trade table has a Exc_Ref value but missing in Cons table then that
record should be displayed. Similarly if Cons has a Exc_Ref value
which is not found in Trade table then that too should be displayed.


>

Quote:

Originally Posted by

2. In case where both the tables have matching Exc_Ref data then it
should display the record only when the remaining column does not
match like Qty or Date.


>

Quote:

Originally Posted by

Please help me to resolve this complicated query.


>

Quote:

Originally Posted by

Thanks
Nick- Hide quoted text -


>
- Show quoted text -


Thanks Roy ! Your solution is too perfect.

Thanks

Saturday, February 25, 2012

Complex query

I've a table where there are 4 columns for Qty(Q1, Q2, Q3, Q4) and
another column as Virtual_Pack. I've to write a query to update
Virtual_Pack column. The logic is as follows

1 virtual pack consists of Q1 = 10, Q2 = 2, Q3 = 3.5, Q4 = 6

Lets say Q1 = 20, Q2 = 2, Q3 = 20, Q4 = 6 then the Virtual_Pack will
be = 1 because Q2 = 2

Example Q1 = 30, Q2 = 5, Q3 = 8, Q4 = 15 then the Virtual_Pack will be
= 2

If any of the Q1, Q2, Q3 or Q4 = 0 then Virtual_Pack will be = 0

I don't want to write a cursor or do it in a loop. Any thoughts how
virtual_pack column can be updated in a single query.

Thanks in advance.

SubodhI don't understand the logic of how you calculate Virtual_Pack from
Q1,Q2,Q3,Q4. If you want a complete solution maybe you could explain by
showing us a formula or a pseudo-code algorithm. "Virtual_Pack will be = 1
because Q2 = 2" doesn't explain much to me but maybe I'm just particularly
dense today!

Lookup the CASE statement in Books Online. I think that may help you. For
example:

Virtual_Pack =
CASE
WHEN Q2 = 2 THEN 1
WHEN Q2 = x THEN y
END

Hope this helps.

--
David Portas
SQL Server MVP
--|||Okay, it SOUNDS LIKE what you want is the largest number (Virtual_Pack)
such that no element of (10, 2, 3.5, 6)*(Virtual_Pack)
is greater than (Q1, Q2, Q3, Q4). Am I right?

In that case, what you're asking for is the smallest (integer)
from among Q1/10, Q2/2, Q3/3.5, Q4/6

Unfortunately, MSSQL has no built-in function such as SmallestOf(A,B,...)
You can do it with a gawdawful CASE statement.

Old SQL coders kludge SmallerOf(A,B) as: (A+B-abs(A-B))/2
This does NOT extend to 3,4,... arguments gracefully :-)
But assuming your Qty table has columns (Q1, Q2, Q3, Q4, F1,...)
you can write a query (preferrably for burial in a view):

select (Q1+VP-abs(Q1-VP))/2 as Virtual_Pack, *
from(
select (Q2+VP-abs(Q2-VP))/2 as VP, *
from(
select (Q3+Q4-abs(Q3-Q4))/2 as VP, *
from Qty
) X
) X

The 'X' tags are just a syntactic requirement for nested queries.

"Subodh" <sgoyal@.agline.on.ca> wrote in message
news:90104bf0.0407131244.28097221@.posting.google.c om...
> I've a table where there are 4 columns for Qty(Q1, Q2, Q3, Q4) and
> another column as Virtual_Pack. I've to write a query to update
> Virtual_Pack column. The logic is as follows
> 1 virtual pack consists of Q1 = 10, Q2 = 2, Q3 = 3.5, Q4 = 6
> Lets say Q1 = 20, Q2 = 2, Q3 = 20, Q4 = 6 then the Virtual_Pack will
> be = 1 because Q2 = 2
> Example Q1 = 30, Q2 = 5, Q3 = 8, Q4 = 15 then the Virtual_Pack will be
> = 2
> If any of the Q1, Q2, Q3 or Q4 = 0 then Virtual_Pack will be = 0
> I don't want to write a cursor or do it in a loop. Any thoughts how
> virtual_pack column can be updated in a single query.
> Thanks in advance.
> Subodh|||> In that case, what you're asking for is the smallest (integer)
> from among Q1/10, Q2/2, Q3/3.5, Q4/6

If that's correct then here's one method that gives the desired result:

CREATE TABLE Qty (Q1 INTEGER NOT NULL, Q2 INTEGER NOT NULL, Q3 INTEGER NOT
NULL, Q4 INTEGER NOT NULL /* , PRIMARY KEY ? */)

INSERT INTO Qty (Q1,Q2,Q3,Q4) VALUES (20, 2, 20, 6)
INSERT INTO Qty (Q1,Q2,Q3,Q4) VALUES (30, 5, 8, 15)

SELECT Q1,Q2,Q3,Q4,
(SELECT CAST(MIN(x) AS INTEGER)
FROM
(SELECT Q1/10.0 AS x UNION ALL
SELECT Q2/2.0 UNION ALL
SELECT Q3/3.5 UNION ALL
SELECT Q4/6.0) AS X)
AS virtual_pack
FROM Qty

Result:

Q1 Q2 Q3 Q4 virtual_pack
---- ---- ---- ---- ----
20 2 20 6 1
30 5 8 15 2

Mischa, I couldn't get your query to produce the answer that the OP wanted.
Maybe you could test it again against my sample data. I'm interested to see
if it can be extended in the way you suggest.

Hope this helps.

--
David Portas
SQL Server MVP
--|||Ummm ... what was I smoking. This one passes the smoke test:

select (Q1/10+V234-abs(Q1/10-V234))/2 as Virtual_Pack, *
from(
select (Q2/2+V34-abs(Q2/2-V34))/2 as V234, *
from(
select (Q3*2/7+Q4/6-abs(Q3*2/7-Q4/6))/2 as V34, *
from Qty
) X
) X

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:CuydncOuNJqcQmnd4p2dnA@.giganews.com...
> > In that case, what you're asking for is the smallest (integer)
> > from among Q1/10, Q2/2, Q3/3.5, Q4/6
> If that's correct then here's one method that gives the desired result:
> CREATE TABLE Qty (Q1 INTEGER NOT NULL, Q2 INTEGER NOT NULL, Q3 INTEGER NOT
> NULL, Q4 INTEGER NOT NULL /* , PRIMARY KEY ? */)
> INSERT INTO Qty (Q1,Q2,Q3,Q4) VALUES (20, 2, 20, 6)
> INSERT INTO Qty (Q1,Q2,Q3,Q4) VALUES (30, 5, 8, 15)
> SELECT Q1,Q2,Q3,Q4,
> (SELECT CAST(MIN(x) AS INTEGER)
> FROM
> (SELECT Q1/10.0 AS x UNION ALL
> SELECT Q2/2.0 UNION ALL
> SELECT Q3/3.5 UNION ALL
> SELECT Q4/6.0) AS X)
> AS virtual_pack
> FROM Qty
> Result:
> Q1 Q2 Q3 Q4 virtual_pack
> ---- ---- ---- ---- ----
> 20 2 20 6 1
> 30 5 8 15 2
> Mischa, I couldn't get your query to produce the answer that the OP
wanted.
> Maybe you could test it again against my sample data. I'm interested to
see
> if it can be extended in the way you suggest.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --

complex joins

All,
I have a messy query with alot of where clause. here is the original
select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
tbl_1,
tbl_2,
#TempTbl
where tbl_2.ticketnum = tbl_1.ticketnum
and #TempTbl.productserial = tbl_1.productserial
and #TempTbl.issuercode = tbl_1.issuercode
and #TempTbl.productcode = tbl_1.productcode
and tbl_2.price = #TempTbl.price
and convert(varchar(10),tbl_1.setldate,101) =
convert(varchar(10),#TempTbl.settlement,101)
and tbl_1.acctnum = #TempTbl.accountNumber
and #TempTbl.rownum = @.ctr
and tbl_2.trademethod = 'T'
and tbl_1.TradeType = 'S'
and tbl_1.status <> 'C'
and tbl_2.timestamp >= @.tbl_2From
and tbl_2.timestamp <= @.tbl_2To
and substring(tbl_1.misccode,8,1) = 1
and (substring(tbl_1.misccode,13,1)= '0' or substring(tbl_1.misccode,13,1)
is null)
I have altered the query to jjoin on the first 2 where's like this
select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
tbl_1 as a join tbl_2 as b on a.ticketnum = b.ticketnum
join #TempTbl as c on c.productserial = a.productserial
where #TempTbl.issuercode = tbl_1.issuercode
and #TempTbl.productcode = tbl_1.productcode
and tbl_2.price = #TempTbl.price
and convert(varchar(10),tbl_1.setldate,101) =
convert(varchar(10),#TempTbl.settlement,101)
and tbl_1.acctnum = #TempTbl.accountNumber
and #TempTbl.rownum = @.ctr
and tbl_2.trademethod = 'T'
and tbl_1.TradeType = 'S'
and tbl_1.status <> 'C'
and tbl_2.timestamp >= @.tbl_2From
and tbl_2.timestamp <= @.tbl_2To
and substring(tbl_1.misccode,8,1) = 1
and (substring(tbl_1.misccode,13,1)= '0' or substring(tbl_1.misccode,13,1)
is null)
In this example I still have a ton of where clause that says
" where a.col x = b.col x
and
a.col y = b.col y"
I think that there has to be a more efficient way of dealing with this!
Please help!
D"Detroit" <Detroit@.discussions.microsoft.com> wrote in message
news:89A81763-5B8D-4876-9552-7C364B5A7A03@.microsoft.com...
> All,
> I have a messy query with alot of where clause. here is the original
>
> select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
> tbl_1,
> tbl_2,
> #TempTbl
> where tbl_2.ticketnum = tbl_1.ticketnum
> and #TempTbl.productserial = tbl_1.productserial
> and #TempTbl.issuercode = tbl_1.issuercode
> and #TempTbl.productcode = tbl_1.productcode
> and tbl_2.price = #TempTbl.price
> and convert(varchar(10),tbl_1.setldate,101) =
> convert(varchar(10),#TempTbl.settlement,101)
> and tbl_1.acctnum = #TempTbl.accountNumber
> and #TempTbl.rownum = @.ctr
> and tbl_2.trademethod = 'T'
> and tbl_1.TradeType = 'S'
> and tbl_1.status <> 'C'
> and tbl_2.timestamp >= @.tbl_2From
> and tbl_2.timestamp <= @.tbl_2To
> and substring(tbl_1.misccode,8,1) = 1
> and (substring(tbl_1.misccode,13,1)= '0' or
> substring(tbl_1.misccode,13,1)
> is null)
>
> I have altered the query to jjoin on the first 2 where's like this
> select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
> tbl_1 as a join tbl_2 as b on a.ticketnum = b.ticketnum
> join #TempTbl as c on c.productserial = a.productserial
>
> where #TempTbl.issuercode = tbl_1.issuercode
> and #TempTbl.productcode = tbl_1.productcode
> and tbl_2.price = #TempTbl.price
> and convert(varchar(10),tbl_1.setldate,101) =
> convert(varchar(10),#TempTbl.settlement,101)
> and tbl_1.acctnum = #TempTbl.accountNumber
> and #TempTbl.rownum = @.ctr
> and tbl_2.trademethod = 'T'
> and tbl_1.TradeType = 'S'
> and tbl_1.status <> 'C'
> and tbl_2.timestamp >= @.tbl_2From
> and tbl_2.timestamp <= @.tbl_2To
> and substring(tbl_1.misccode,8,1) = 1
> and (substring(tbl_1.misccode,13,1)= '0' or
> substring(tbl_1.misccode,13,1)
> is null)
>
> In this example I still have a ton of where clause that says
> " where a.col x = b.col x
> and
> a.col y = b.col y"
> I think that there has to be a more efficient way of dealing with this!
> Please help!
> D
Without looking closely at your statements, if these are the necessary
conditions for the join to work properly and for the data you wish to
retrieve, then there is really no way around the issue with your current
schema.
You could simplify this procedure a bit by creating some views and the
querying on those views, however, at some point in the process, all of these
join conditions are going to need to occur.
Your other option is to change your schema. You *could* denormalize tables
and decrease the number of join issues and so forth, but this is generally
not recommended.
Rick Sawtell
MCT, MCSD, MCDBA|||I'd say that you need to properly NORMALIZE this beast. However, there are
a few issues here:
SELECT @.Count = COUNT(*)
,@.TicketTotal = SUM(tbl_2.qty) / 1000
FROM tbl_1
INNER JOIN
tbl_2
ON tbl_1.ticketnum = tbl_2.ticketnum
INNER JOIN
#TempTbl
ON #TempTbl.productserial = tbl_1.productserial
AND #TempTbl.issuercode = tbl_1.issuercode
AND #TempTbl.productcode = tbl_1.productcode
AND tbl_1.acctnum = #TempTbl.accountNumber
WHERE #TempTbl.rownum = @.ctr
AND tbl_2.trademethod = 'T'
AND tbl_1.TradeType = 'S'
AND tbl_1.status <> 'C'
AND tbl_2.timestamp BETWEEN @.tbl_2From AND @.tbl_2To
AND tbl_2.price = #TempTbl.price
AND CONVERT(VARCHAR(10), tbl_1.setldate, 101) =
CONVERT(VARCHAR(10), #TempTbl.settlement, 101)
AND SUBSTRING(tbl_1.misccode,8,1) = 1
AND (SUBSTRING(tbl_1.misccode,13,1)= '0'
OR SUBSTRING(tbl_1.misccode,13,1) IS NULL
)
Let's start by using proper syntax: JOIN conditions should be ANSI compliant
and be seperated from the Restriction Clause.
Ok, so the temp table has a composite key, could be better, but four columns
isn't too bad; however, there were numerous conditions that one couldn't
tell if it was a JOIN condition or a resultset restriction. Those I left in
the WHERE clause.
Most of what's there is properly formed restrictions; however, the equality
of price between the tables serves what purpose? This could be a JOIN
condition, but seems odd but shouldn't kill the performance.
Here's the good stuff. Those datetime to character conversions will kill
you. Is there some point in striping the time portion?
The substrings will kill your performance too. But, even better, you are
conditioning on multiple values within the same field just different
positions. You could have considered a LIKE operation in stead:
tbl_1.misccode LIKE '_______1____0%'. However, because you haven't prefixed
the first position, there aren't too many index strategies that will
help...like none. Then there is the AND value OR IS NULL. You can't
produce a NULL value from a SUBSTRING function unless the whole column were
NULL to begin with but, then, so would have the prior evaluation to 1 on
position 8--which was stated as a numeric but should have been character '1'
to boot.
Yes this could use some help, like some design as to what we are really
aiming at here.
Sincerely,
Anthony Thomas
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:urkdFoaxEHA.2316@.TK2MSFTNGP15.phx.gbl...
> "Detroit" <Detroit@.discussions.microsoft.com> wrote in message
> news:89A81763-5B8D-4876-9552-7C364B5A7A03@.microsoft.com...
>
> Without looking closely at your statements, if these are the necessary
> conditions for the join to work properly and for the data you wish to
> retrieve, then there is really no way around the issue with your current
> schema.
> You could simplify this procedure a bit by creating some views and the
> querying on those views, however, at some point in the process, all of
these
> join conditions are going to need to occur.
> Your other option is to change your schema. You *could* denormalize
tables
> and decrease the number of join issues and so forth, but this is generally
> not recommended.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

complex joins

All,
I have a messy query with alot of where clause. here is the original
select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
tbl_1,
tbl_2,
#TempTbl
where tbl_2.ticketnum = tbl_1.ticketnum
and #TempTbl.productserial = tbl_1.productserial
and #TempTbl.issuercode = tbl_1.issuercode
and #TempTbl.productcode = tbl_1.productcode
and tbl_2.price = #TempTbl.price
and convert(varchar(10),tbl_1.setldate,101) = convert(varchar(10),#TempTbl.settlement,101)
and tbl_1.acctnum = #TempTbl.accountNumber
and #TempTbl.rownum = @.ctr
and tbl_2.trademethod = 'T'
and tbl_1.TradeType = 'S'
and tbl_1.status <> 'C'
and tbl_2.timestamp >= @.tbl_2From
and tbl_2.timestamp <= @.tbl_2To
and substring(tbl_1.misccode,8,1) = 1
and (substring(tbl_1.misccode,13,1)= '0' or substring(tbl_1.misccode,13,1)
is null)
I have altered the query to jjoin on the first 2 where's like this
select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
tbl_1 as a join tbl_2 as b on a.ticketnum = b.ticketnum
join #TempTbl as c on c.productserial = a.productserial
where #TempTbl.issuercode = tbl_1.issuercode
and #TempTbl.productcode = tbl_1.productcode
and tbl_2.price = #TempTbl.price
and convert(varchar(10),tbl_1.setldate,101) = convert(varchar(10),#TempTbl.settlement,101)
and tbl_1.acctnum = #TempTbl.accountNumber
and #TempTbl.rownum = @.ctr
and tbl_2.trademethod = 'T'
and tbl_1.TradeType = 'S'
and tbl_1.status <> 'C'
and tbl_2.timestamp >= @.tbl_2From
and tbl_2.timestamp <= @.tbl_2To
and substring(tbl_1.misccode,8,1) = 1
and (substring(tbl_1.misccode,13,1)= '0' or substring(tbl_1.misccode,13,1)
is null)
In this example I still have a ton of where clause that says
" where a.col x = b.col x
and
a.col y = b.col y"
I think that there has to be a more efficient way of dealing with this!
Please help!
D"Detroit" <Detroit@.discussions.microsoft.com> wrote in message
news:89A81763-5B8D-4876-9552-7C364B5A7A03@.microsoft.com...
> All,
> I have a messy query with alot of where clause. here is the original
>
> select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
> tbl_1,
> tbl_2,
> #TempTbl
> where tbl_2.ticketnum = tbl_1.ticketnum
> and #TempTbl.productserial = tbl_1.productserial
> and #TempTbl.issuercode = tbl_1.issuercode
> and #TempTbl.productcode = tbl_1.productcode
> and tbl_2.price = #TempTbl.price
> and convert(varchar(10),tbl_1.setldate,101) => convert(varchar(10),#TempTbl.settlement,101)
> and tbl_1.acctnum = #TempTbl.accountNumber
> and #TempTbl.rownum = @.ctr
> and tbl_2.trademethod = 'T'
> and tbl_1.TradeType = 'S'
> and tbl_1.status <> 'C'
> and tbl_2.timestamp >= @.tbl_2From
> and tbl_2.timestamp <= @.tbl_2To
> and substring(tbl_1.misccode,8,1) = 1
> and (substring(tbl_1.misccode,13,1)= '0' or
> substring(tbl_1.misccode,13,1)
> is null)
>
> I have altered the query to jjoin on the first 2 where's like this
> select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
> tbl_1 as a join tbl_2 as b on a.ticketnum = b.ticketnum
> join #TempTbl as c on c.productserial = a.productserial
>
> where #TempTbl.issuercode = tbl_1.issuercode
> and #TempTbl.productcode = tbl_1.productcode
> and tbl_2.price = #TempTbl.price
> and convert(varchar(10),tbl_1.setldate,101) => convert(varchar(10),#TempTbl.settlement,101)
> and tbl_1.acctnum = #TempTbl.accountNumber
> and #TempTbl.rownum = @.ctr
> and tbl_2.trademethod = 'T'
> and tbl_1.TradeType = 'S'
> and tbl_1.status <> 'C'
> and tbl_2.timestamp >= @.tbl_2From
> and tbl_2.timestamp <= @.tbl_2To
> and substring(tbl_1.misccode,8,1) = 1
> and (substring(tbl_1.misccode,13,1)= '0' or
> substring(tbl_1.misccode,13,1)
> is null)
>
> In this example I still have a ton of where clause that says
> " where a.col x = b.col x
> and
> a.col y = b.col y"
> I think that there has to be a more efficient way of dealing with this!
> Please help!
> D
Without looking closely at your statements, if these are the necessary
conditions for the join to work properly and for the data you wish to
retrieve, then there is really no way around the issue with your current
schema.
You could simplify this procedure a bit by creating some views and the
querying on those views, however, at some point in the process, all of these
join conditions are going to need to occur.
Your other option is to change your schema. You *could* denormalize tables
and decrease the number of join issues and so forth, but this is generally
not recommended.
Rick Sawtell
MCT, MCSD, MCDBA|||I'd say that you need to properly NORMALIZE this beast. However, there are
a few issues here:
SELECT @.Count = COUNT(*)
,@.TicketTotal = SUM(tbl_2.qty) / 1000
FROM tbl_1
INNER JOIN
tbl_2
ON tbl_1.ticketnum = tbl_2.ticketnum
INNER JOIN
#TempTbl
ON #TempTbl.productserial = tbl_1.productserial
AND #TempTbl.issuercode = tbl_1.issuercode
AND #TempTbl.productcode = tbl_1.productcode
AND tbl_1.acctnum = #TempTbl.accountNumber
WHERE #TempTbl.rownum = @.ctr
AND tbl_2.trademethod = 'T'
AND tbl_1.TradeType = 'S'
AND tbl_1.status <> 'C'
AND tbl_2.timestamp BETWEEN @.tbl_2From AND @.tbl_2To
AND tbl_2.price = #TempTbl.price
AND CONVERT(VARCHAR(10), tbl_1.setldate, 101) = CONVERT(VARCHAR(10), #TempTbl.settlement, 101)
AND SUBSTRING(tbl_1.misccode,8,1) = 1
AND (SUBSTRING(tbl_1.misccode,13,1)= '0'
OR SUBSTRING(tbl_1.misccode,13,1) IS NULL
)
Let's start by using proper syntax: JOIN conditions should be ANSI compliant
and be seperated from the Restriction Clause.
Ok, so the temp table has a composite key, could be better, but four columns
isn't too bad; however, there were numerous conditions that one couldn't
tell if it was a JOIN condition or a resultset restriction. Those I left in
the WHERE clause.
Most of what's there is properly formed restrictions; however, the equality
of price between the tables serves what purpose? This could be a JOIN
condition, but seems odd but shouldn't kill the performance.
Here's the good stuff. Those datetime to character conversions will kill
you. Is there some point in striping the time portion?
The substrings will kill your performance too. But, even better, you are
conditioning on multiple values within the same field just different
positions. You could have considered a LIKE operation in stead:
tbl_1.misccode LIKE '_______1____0%'. However, because you haven't prefixed
the first position, there aren't too many index strategies that will
help...like none. Then there is the AND value OR IS NULL. You can't
produce a NULL value from a SUBSTRING function unless the whole column were
NULL to begin with but, then, so would have the prior evaluation to 1 on
position 8--which was stated as a numeric but should have been character '1'
to boot.
Yes this could use some help, like some design as to what we are really
aiming at here.
Sincerely,
Anthony Thomas
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:urkdFoaxEHA.2316@.TK2MSFTNGP15.phx.gbl...
> "Detroit" <Detroit@.discussions.microsoft.com> wrote in message
> news:89A81763-5B8D-4876-9552-7C364B5A7A03@.microsoft.com...
> > All,
> > I have a messy query with alot of where clause. here is the original
> >
> >
> > select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
> >
> > tbl_1,
> > tbl_2,
> > #TempTbl
> >
> > where tbl_2.ticketnum = tbl_1.ticketnum
> > and #TempTbl.productserial = tbl_1.productserial
> > and #TempTbl.issuercode = tbl_1.issuercode
> > and #TempTbl.productcode = tbl_1.productcode
> > and tbl_2.price = #TempTbl.price
> > and convert(varchar(10),tbl_1.setldate,101) => > convert(varchar(10),#TempTbl.settlement,101)
> > and tbl_1.acctnum = #TempTbl.accountNumber
> > and #TempTbl.rownum = @.ctr
> > and tbl_2.trademethod = 'T'
> > and tbl_1.TradeType = 'S'
> > and tbl_1.status <> 'C'
> > and tbl_2.timestamp >= @.tbl_2From
> > and tbl_2.timestamp <= @.tbl_2To
> > and substring(tbl_1.misccode,8,1) = 1
> > and (substring(tbl_1.misccode,13,1)= '0' or
> > substring(tbl_1.misccode,13,1)
> > is null)
> >
> >
> > I have altered the query to jjoin on the first 2 where's like this
> >
> > select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
> >
> > tbl_1 as a join tbl_2 as b on a.ticketnum = b.ticketnum
> > join #TempTbl as c on c.productserial = a.productserial
> >
> >
> > where #TempTbl.issuercode = tbl_1.issuercode
> > and #TempTbl.productcode = tbl_1.productcode
> > and tbl_2.price = #TempTbl.price
> > and convert(varchar(10),tbl_1.setldate,101) => > convert(varchar(10),#TempTbl.settlement,101)
> > and tbl_1.acctnum = #TempTbl.accountNumber
> > and #TempTbl.rownum = @.ctr
> > and tbl_2.trademethod = 'T'
> > and tbl_1.TradeType = 'S'
> > and tbl_1.status <> 'C'
> > and tbl_2.timestamp >= @.tbl_2From
> > and tbl_2.timestamp <= @.tbl_2To
> > and substring(tbl_1.misccode,8,1) = 1
> > and (substring(tbl_1.misccode,13,1)= '0' or
> > substring(tbl_1.misccode,13,1)
> > is null)
> >
> >
> >
> > In this example I still have a ton of where clause that says
> >
> > " where a.col x = b.col x
> > and
> > a.col y = b.col y"
> >
> > I think that there has to be a more efficient way of dealing with this!
> >
> > Please help!
> >
> > D
>
> Without looking closely at your statements, if these are the necessary
> conditions for the join to work properly and for the data you wish to
> retrieve, then there is really no way around the issue with your current
> schema.
> You could simplify this procedure a bit by creating some views and the
> querying on those views, however, at some point in the process, all of
these
> join conditions are going to need to occur.
> Your other option is to change your schema. You *could* denormalize
tables
> and decrease the number of join issues and so forth, but this is generally
> not recommended.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

complex joins

All,
I have a messy query with alot of where clause. here is the original
select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
tbl_1,
tbl_2,
#TempTbl
where tbl_2.ticketnum = tbl_1.ticketnum
and #TempTbl.productserial = tbl_1.productserial
and #TempTbl.issuercode = tbl_1.issuercode
and #TempTbl.productcode = tbl_1.productcode
and tbl_2.price = #TempTbl.price
and convert(varchar(10),tbl_1.setldate,101) =
convert(varchar(10),#TempTbl.settlement,101)
and tbl_1.acctnum = #TempTbl.accountNumber
and #TempTbl.rownum = @.ctr
and tbl_2.trademethod = 'T'
and tbl_1.TradeType = 'S'
and tbl_1.status <> 'C'
and tbl_2.timestamp >= @.tbl_2From
and tbl_2.timestamp <= @.tbl_2To
and substring(tbl_1.misccode,8,1) = 1
and (substring(tbl_1.misccode,13,1)= '0' or substring(tbl_1.misccode,13,1)
is null)
I have altered the query to jjoin on the first 2 where's like this
select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
tbl_1 as a join tbl_2 as b on a.ticketnum = b.ticketnum
join #TempTbl as c on c.productserial = a.productserial
where #TempTbl.issuercode = tbl_1.issuercode
and #TempTbl.productcode = tbl_1.productcode
and tbl_2.price = #TempTbl.price
and convert(varchar(10),tbl_1.setldate,101) =
convert(varchar(10),#TempTbl.settlement,101)
and tbl_1.acctnum = #TempTbl.accountNumber
and #TempTbl.rownum = @.ctr
and tbl_2.trademethod = 'T'
and tbl_1.TradeType = 'S'
and tbl_1.status <> 'C'
and tbl_2.timestamp >= @.tbl_2From
and tbl_2.timestamp <= @.tbl_2To
and substring(tbl_1.misccode,8,1) = 1
and (substring(tbl_1.misccode,13,1)= '0' or substring(tbl_1.misccode,13,1)
is null)
In this example I still have a ton of where clause that says
" where a.col x = b.col x
and
a.col y = b.col y"
I think that there has to be a more efficient way of dealing with this!
Please help!
D
"Detroit" <Detroit@.discussions.microsoft.com> wrote in message
news:89A81763-5B8D-4876-9552-7C364B5A7A03@.microsoft.com...
> All,
> I have a messy query with alot of where clause. here is the original
>
> select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
> tbl_1,
> tbl_2,
> #TempTbl
> where tbl_2.ticketnum = tbl_1.ticketnum
> and #TempTbl.productserial = tbl_1.productserial
> and #TempTbl.issuercode = tbl_1.issuercode
> and #TempTbl.productcode = tbl_1.productcode
> and tbl_2.price = #TempTbl.price
> and convert(varchar(10),tbl_1.setldate,101) =
> convert(varchar(10),#TempTbl.settlement,101)
> and tbl_1.acctnum = #TempTbl.accountNumber
> and #TempTbl.rownum = @.ctr
> and tbl_2.trademethod = 'T'
> and tbl_1.TradeType = 'S'
> and tbl_1.status <> 'C'
> and tbl_2.timestamp >= @.tbl_2From
> and tbl_2.timestamp <= @.tbl_2To
> and substring(tbl_1.misccode,8,1) = 1
> and (substring(tbl_1.misccode,13,1)= '0' or
> substring(tbl_1.misccode,13,1)
> is null)
>
> I have altered the query to jjoin on the first 2 where's like this
> select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
> tbl_1 as a join tbl_2 as b on a.ticketnum = b.ticketnum
> join #TempTbl as c on c.productserial = a.productserial
>
> where #TempTbl.issuercode = tbl_1.issuercode
> and #TempTbl.productcode = tbl_1.productcode
> and tbl_2.price = #TempTbl.price
> and convert(varchar(10),tbl_1.setldate,101) =
> convert(varchar(10),#TempTbl.settlement,101)
> and tbl_1.acctnum = #TempTbl.accountNumber
> and #TempTbl.rownum = @.ctr
> and tbl_2.trademethod = 'T'
> and tbl_1.TradeType = 'S'
> and tbl_1.status <> 'C'
> and tbl_2.timestamp >= @.tbl_2From
> and tbl_2.timestamp <= @.tbl_2To
> and substring(tbl_1.misccode,8,1) = 1
> and (substring(tbl_1.misccode,13,1)= '0' or
> substring(tbl_1.misccode,13,1)
> is null)
>
> In this example I still have a ton of where clause that says
> " where a.col x = b.col x
> and
> a.col y = b.col y"
> I think that there has to be a more efficient way of dealing with this!
> Please help!
> D
Without looking closely at your statements, if these are the necessary
conditions for the join to work properly and for the data you wish to
retrieve, then there is really no way around the issue with your current
schema.
You could simplify this procedure a bit by creating some views and the
querying on those views, however, at some point in the process, all of these
join conditions are going to need to occur.
Your other option is to change your schema. You *could* denormalize tables
and decrease the number of join issues and so forth, but this is generally
not recommended.
Rick Sawtell
MCT, MCSD, MCDBA
|||I'd say that you need to properly NORMALIZE this beast. However, there are
a few issues here:
SELECT @.Count = COUNT(*)
,@.TicketTotal = SUM(tbl_2.qty) / 1000
FROM tbl_1
INNER JOIN
tbl_2
ON tbl_1.ticketnum = tbl_2.ticketnum
INNER JOIN
#TempTbl
ON #TempTbl.productserial = tbl_1.productserial
AND #TempTbl.issuercode = tbl_1.issuercode
AND #TempTbl.productcode = tbl_1.productcode
AND tbl_1.acctnum = #TempTbl.accountNumber
WHERE #TempTbl.rownum = @.ctr
AND tbl_2.trademethod = 'T'
AND tbl_1.TradeType = 'S'
AND tbl_1.status <> 'C'
AND tbl_2.timestamp BETWEEN @.tbl_2From AND @.tbl_2To
AND tbl_2.price = #TempTbl.price
AND CONVERT(VARCHAR(10), tbl_1.setldate, 101) =
CONVERT(VARCHAR(10), #TempTbl.settlement, 101)
AND SUBSTRING(tbl_1.misccode,8,1) = 1
AND (SUBSTRING(tbl_1.misccode,13,1)= '0'
OR SUBSTRING(tbl_1.misccode,13,1) IS NULL
)
Let's start by using proper syntax: JOIN conditions should be ANSI compliant
and be seperated from the Restriction Clause.
Ok, so the temp table has a composite key, could be better, but four columns
isn't too bad; however, there were numerous conditions that one couldn't
tell if it was a JOIN condition or a resultset restriction. Those I left in
the WHERE clause.
Most of what's there is properly formed restrictions; however, the equality
of price between the tables serves what purpose? This could be a JOIN
condition, but seems odd but shouldn't kill the performance.
Here's the good stuff. Those datetime to character conversions will kill
you. Is there some point in striping the time portion?
The substrings will kill your performance too. But, even better, you are
conditioning on multiple values within the same field just different
positions. You could have considered a LIKE operation in stead:
tbl_1.misccode LIKE '_______1____0%'. However, because you haven't prefixed
the first position, there aren't too many index strategies that will
help...like none. Then there is the AND value OR IS NULL. You can't
produce a NULL value from a SUBSTRING function unless the whole column were
NULL to begin with but, then, so would have the prior evaluation to 1 on
position 8--which was stated as a numeric but should have been character '1'
to boot.
Yes this could use some help, like some design as to what we are really
aiming at here.
Sincerely,
Anthony Thomas

"Rick Sawtell" <quickening@.msn.com> wrote in message
news:urkdFoaxEHA.2316@.TK2MSFTNGP15.phx.gbl...
> "Detroit" <Detroit@.discussions.microsoft.com> wrote in message
> news:89A81763-5B8D-4876-9552-7C364B5A7A03@.microsoft.com...
>
> Without looking closely at your statements, if these are the necessary
> conditions for the join to work properly and for the data you wish to
> retrieve, then there is really no way around the issue with your current
> schema.
> You could simplify this procedure a bit by creating some views and the
> querying on those views, however, at some point in the process, all of
these
> join conditions are going to need to occur.
> Your other option is to change your schema. You *could* denormalize
tables
> and decrease the number of join issues and so forth, but this is generally
> not recommended.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>