prevent lots of subquery's - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

prevent lots of subquery's

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2011-01-20 : 03:51:47

Hi, I'm making a complex sp, but for the forum, I will try to keep it simple to explain the problem.

I want to create an export of users from the users table.
In that export I want to include the memebership payments for this year and next year (people can pay 1 year in advance).
So this is where I am now (simplified):

SELECT Distinct
u.UserID,
(select top 1
ept.ShopTransactionId AS ShopTransactionIDThisYear
from
PaymentTransaction ept
where
((ept.UserOrAssociation = 'U') or (ept.UserOrAssociation = 'C'))
and
ept.TransactionResult = 'OK'
and
((ept.[IsDeleted] IS null) or (ept.[IsDeleted] = 'false'))
and
ept.MembershipYear = year(GetDate())
and
ept.UserId = u.UserID
) AS [ShopTransactionIDThisYear],
(select top 1
convert(Datetime, str(ept.[OperationYear]) + '-' + str(ept.[OperationMonth]) + '-' + str(ept.[OperationDay]) + ' ' + str(ept.[OperationHour]) + ':' + str(ept.[OperationMinute]) + ':' + str(ept.[OperationSeconds]), 120) as [DateTimeThisYear]
from
PaymentTransaction ept
where
((ept.UserOrAssociation = 'U') or (ept.UserOrAssociation = 'C'))
and
ept.TransactionResult = 'OK'
and
((ept.[IsDeleted] IS null) or (ept.[IsDeleted] = 'false'))
and
ept.MembershipYear = year(GetDate())
and
ept.UserId = u.UserID
) AS [DateTimeThisYear],
(select top 1
ept.ShopTransactionId AS ShopTransactionIDThisYear
from
PaymentTransaction ept
where
((ept.UserOrAssociation = 'U') or (ept.UserOrAssociation = 'C'))
and
ept.TransactionResult = 'OK'
and
((ept.[IsDeleted] IS null) or (ept.[IsDeleted] = 'false'))
and
ept.MembershipYear = year(GetDate()) + 1
and
ept.UserId = u.UserID
) AS [ShopTransactionIDNextYear],
(select top 1
convert(Datetime, str(ept.[OperationYear]) + '-' + str(ept.[OperationMonth]) + '-' + str(ept.[OperationDay]) + ' ' + str(ept.[OperationHour]) + ':' + str(ept.[OperationMinute]) + ':' + str(ept.[OperationSeconds]), 120) as [DateTimeThisYear]
from
PaymentTransaction ept
where
((ept.UserOrAssociation = 'U') or (ept.UserOrAssociation = 'C'))
and
ept.TransactionResult = 'OK'
and
((ept.[IsDeleted] IS null) or (ept.[IsDeleted] = 'false'))
and
ept.MembershipYear = year(GetDate()) + 1
and
ept.UserId = u.UserID
) AS [DateTimeNextYear]

FROM
dbo.Users AS u
WHERE
....
etc.

So I am doing 4 subquery's.
My question is, can I do this any easier, perhaps return two columns values in 1 subquery or something.

I've also tried other stuff like joins, but this causes other problems
because users can change a role and then have multiple transactions in the same year, which results to users being in the export multiple times.

The secret to creativity is knowing how to hide your sources. (Einstein)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-20 : 04:30:34

a quick glance at your query see that you have 2 diff sub-query, one for current year, one for next year.

You can replace the 2 sub-query for current year with CROSS APPLY or OUTER APPLY and similary with the query for next year

KH
[spoiler]Time is always against us[/spoiler]

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2011-01-20 : 04:34:48

So hoq would I do that?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-20 : 04:39:11

something like this


select *
from   dbo.Users AS u
       cross apply
       (
select top 1
       ept.ShopTransactionId AS ShopTransactionIDThisYear,
      convert(Datetime, str(ept.[OperationYear]) + '-' + str(ept.[OperationMonth]) + '-' + str(ept.[OperationDay]) + ' ' 
             + str(ept.[OperationHour]) + ':' + str(ept.[OperationMinute]) + ':' + str(ept.[OperationSeconds]), 120) as [DateTimeThisYear]
from  PaymentTransaction ept
where ((ept.UserOrAssociation = 'U') or (ept.UserOrAssociation = 'C'))
and   ept.TransactionResult = 'OK'
and   ((ept.[IsDeleted] IS null) or (ept.[IsDeleted] = 'false'))
and   ept.MembershipYear = year(GetDate())
and   ept.UserId = u.UserID 
ORDER BY  .....
       ) ty

KH
[spoiler]Time is always against us[/spoiler]

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-20 : 14:47:10

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions AND formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL AS much AS possible AND not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

You are doing everything wrong and we need to start over.

Are you actually using flags in SQL? We keep the date of an event. But even if flags were a good design, why did you allow NULLs instead of having simpl:

deletion_flg CHAR(1) DEFAULT 'F' NOT NULL
CHECK (deletion_flg IN ('T', ',F'))

The proprietary TOP(n) syntax? SELECT DISTINCT? Why are the table names singular?

Look at

((EPT.user_or_association = 'U') OR (EPT.user_or_association = 'C'))

1) Why extra parentheses?
2) what entity is a "user_or_association"? Pick one! It needs a single data elemnt name and we need to know what this is -- an id, a count, a weight, what?
3) SQL programmers would write this with SQL shorthand, not BASIC predicate chains.
4) My guess at valid SQL is : EPT.member_type IN ('U', 'C')

Why did you write this monster?:

CONVERT(DATETIME, STR(EPT.[operation_year) + '-' + STR(EPT.operation_month) + '-' + STR(EPT.[operation_day) + ' ' + STR(EPT.[operation_hour) + ':' + STR(EPT.operation_minute) + ':' + STR(EPT.operation_econds), 120) AS something_datetime_this_year

Handling temporal data in strings is COBOL and not SQL. We have DATE data types now and you do not need the time parts for a transaction.

Why write soooo much d=repeated code when you can write:

EPT.membership_year IN (YEAR(CURRENT_TIMESTAMP, YEAR(CURRENT_TIMESTAMP) +1)

Post DDL and we can try to fix. Your code ought to be improved by 2-3 orders of magnitude. Then you need to read some books.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources