March 14, 2010 at 9:23 pm
I have to alter the query below to filter the “ttl_av” column. I only want to see the record if the ttl_av column is less than 50. I tried a Derived table, but no luck. Can someone help me?
select ib.account, db.dealername,m.adminname,m.phone, CONVERT(INT,m.gpsPackages) + CONVERT(INT,m.ProPackages) + CONVERT(INT,m.ProPlatinumPackages) + CONVERT(INT,m.ProGoldpackages) + CONVERT(INT,m.PlusIVSpackages) as packageTotal,
ttl=(select count(i.ID) from passtimests.dbo.inventory i where ib.account = i.account and i.status != 'ROP'),
ttl_av=(select count(i.ID) from passtimests.dbo.inventory i where ib.account = i.account and (i.status='inventory' or i.status='shipped')),
ttl_ass=(select count(i.ID) from passtimests.dbo.inventory i where ib.account = i.account and i.status='installed')
from passtimests.dbo.inventory ib join passtimeagent.dbo.dealer db ON ib.account = db.dealernumber join passtimemaster.dbo.master m ON ib.account = m.dealernumber where db.Agent = '91' group by ib.account,db.dealername,m.gpsPackages,m.ProPackages,m.ProPlatinumPackages,m.ProGoldpackages,m.PlusIVSpackages, m.adminname,m.phone
Here is my current output, but if I could only show records where the ttl_av column is less than 50, it would work exactly as intended. So for example the first row in this result set would not show because the ttl_av is over 50.
accountdealernameadminnamephone packageTotalttlttl_avttl_ass
9999Demo Demo Demo2303.333.3333176 546187358
9998Test test 303.333.333325 923260
Thank you for your help!
March 14, 2010 at 11:58 pm
...
WHERE ttl_av < 50
?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 15, 2010 at 1:48 am
RBarryYoung (3/14/2010)
WHERE ttl_av < 50?
[font="Courier New"]Msg 207, Level 16, State 1, Line 6
Invalid column name 'ttl_av'.[/font]
There are 'better' ways, but this will work:
SELECT ib.account,
db.dealername,
m.adminname,
m.phone,
packageTotal =
CONVERT(INT,m.gpsPackages) +
CONVERT(INT,m.ProPackages) +
CONVERT(INT,m.ProPlatinumPackages) +
CONVERT(INT,m.ProGoldpackages) +
CONVERT(INT,m.PlusIVSpackages),
ttl =
(
SELECT count(i.ID)
FROM passtimests.dbo.inventory i
WHERE ib.account = i.account and i.status != 'ROP'
),
ttl_av =
(
SELECT count(i.ID)
FROM passtimests.dbo.inventory i
WHERE ib.account = i.account
AND (i.status = 'inventory' OR i.status = 'shipped')
),
ttl_ass=
(
SELECT count(i.ID)
FROM passtimests.dbo.inventory i
WHERE ib.account = i.account
AND i.status='installed'
)
FROM passtimests.dbo.inventory ib
JOIN passtimeagent.dbo.dealer db
ON ib.account = db.dealernumber
JOIN passtimemaster.dbo.master m
ON ib.account = m.dealernumber
WHERE db.Agent = '91'
AND (
-- Repeated expression for ttl_av
SELECT count(i.ID)
FROM passtimests.dbo.inventory i
WHERE ib.account = i.account
AND (i.status = 'inventory' OR i.status = 'shipped')
) < 50
GROUP BY
ib.account,
db.dealername,
m.gpsPackages,
m.ProPackages,
m.ProPlatinumPackages,
m.ProGoldpackages,
m.PlusIVSpackages,
m.adminname,m.phone;
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 15, 2010 at 2:28 am
One more way:
SELECT -- Use a column list
*
FROM (
SELECT ib.account,
db.dealername,
m.adminname,
m.phone,
packageTotal =
CONVERT(INT,m.gpsPackages) +
CONVERT(INT,m.ProPackages) +
CONVERT(INT,m.ProPlatinumPackages) +
CONVERT(INT,m.ProGoldpackages) +
CONVERT(INT,m.PlusIVSpackages),
ttl =
(
SELECT count(i.ID)
FROM passtimests.dbo.inventory i
WHERE ib.account = i.account and i.status != 'ROP'
),
ttl_av =
(
SELECT count(i.ID)
FROM passtimests.dbo.inventory i
WHERE ib.account = i.account
AND (i.status = 'inventory' OR i.status = 'shipped')
),
ttl_ass=
(
SELECT count(i.ID)
FROM passtimests.dbo.inventory i
WHERE ib.account = i.account
AND i.status='installed'
)
FROM passtimests.dbo.inventory ib
JOIN passtimeagent.dbo.dealer db
ON ib.account = db.dealernumber
JOIN passtimemaster.dbo.master m
ON ib.account = m.dealernumber
WHERE db.Agent = '91'
AND (
-- Repeated expression for ttl_av
SELECT count(i.ID)
FROM passtimests.dbo.inventory i
WHERE ib.account = i.account
AND (i.status = 'inventory' OR i.status = 'shipped')
) < 50
GROUP BY
ib.account,
db.dealername,
m.gpsPackages,
m.ProPackages,
m.ProPlatinumPackages,
m.ProGoldpackages,
m.PlusIVSpackages,
m.adminname,m.phone
) Subquery
WHERE ttl_av < 50;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 15, 2010 at 9:17 am
Paul, thank you sooo much for your help with this!! That worked perfectly!
Have a great week!
March 15, 2010 at 9:26 am
Thanks. You too.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply