Effective SQL

I am creating a report and there are two sections in my Report
1)      Payments To Date
2)      Payments for Week
For the above two sections the SQL is almost same except the join condition.

Payments for Week
This one is processing payments for each week it has the below join condition.

ON amttbl.datenumber <= endofweektbl.datenumber
and amttbl.datenumber > endofweektbl.datenumber-7

Payments To Date
This one is the cumulative sum to date, regardless of week so SQL just summarize the paid amount
And the join condition for this as below

SQL:
ON amttbl.datenumber <= endofweektbl.datenumber

Now I have two different SQL’s for each as both have different join conditions,
I want to know is there a way to write both in one SQL.


My SQL's Outline:

1) Select X, Y
   From endofweektbl
     LEFT OUTER JOIN
      amttbl
     ON amttbl.datenumber <= endofweektbl.datenumber
     and amttbl.datenumber > endofweektbl.datenumber-7

2) Select X, Y
   From endofweektbl
     LEFT OUTER JOIN
      amttbl
     ON amttbl.datenumber <= endofweektbl.datenumber


I have above two sql's can they both be written in using one SQL
shragiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
sure
try



Select      case when amttbl.datenumber > endofweektbl.datenumber-7 then X else -1 end as weeklyX,
                     case when amttbl.datenumber > endofweektbl.datenumber-7 then Y else -1 end as weeklyY,
                X as totalX, Y as totalY
   From endofweektbl
     LEFT OUTER JOIN
      amttbl
     ON amttbl.datenumber <= endofweektbl.datenumber
0
shragiAuthor Commented:

Select      case when amttbl.datenumber > endofweektbl.datenumber-7 then X else -1 end as weeklyX,
                     case when amttbl.datenumber > endofweektbl.datenumber-7 then Y else -1 end as weeklyY,
                X as totalX, Y as totalY
   From endofweektbl
     LEFT OUTER JOIN
      amttbl
     ON amttbl.datenumber <= endofweektbl.datenumber


This does not solve my problem I am asking about join condition, based on that join condition, all my select values changes.

0
Kent OlsenData Warehouse Architect / DBACommented:
Hi shraqi,

It's certainly possible, but you need to identify the common key.  That would be the critical item(s) on the report that you're showing.


STORE      payments to date      payments this week

CUSTOMER   payments to date      payments this week

VENDOR     payments to date      payments this week

Open in new window


If item in the leftmost column is known, it's a simple matter to sum the all payments grouped by that entity to generate column 2, and sum them again for the current week to generate column 3.



Kent
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

shragiAuthor Commented:
@kent

1) Select X, Sum(Y)
   From endofweektbl
     LEFT OUTER JOIN
      amttbl
     ON amttbl.datenumber <= endofweektbl.datenumber
     and amttbl.datenumber > endofweektbl.datenumber-7
    group by X

2) Select X, Sum(Y)
   From endofweektbl
     LEFT OUTER JOIN
      amttbl
     ON amttbl.datenumber <= endofweektbl.datenumber
     Group BY X


THe item in the left most column is X so now can you suggest me how to do this...
0
Kent OlsenData Warehouse Architect / DBACommented:
Sure.  Piece of cake.   :)

The easiest to "visualize" may to use those two queries inline.  But a simple join is probably the most efficient.

The query below will display all of the values for X in mytable even if they don't have payments.  If you only need items that have payments, just outer join t1 to t2.  


Kent

SELECT t0.X, T1.Total, T2.Total
FROM mytable t0
LEFT JOIN
(
  SELECT X, Sum(Y)
  FROM endofweektbl
  LEFT OUTER JOIN amttbl
    ON amttbl.datenumber <= endofweektbl.datenumber
   AND amttbl.datenumber > endofweektbl.datenumber-7
  GROUP BY X
) t1
  ON t0.X = t1.X
LEFT OUTER JOIN
(
  SELECT X, Sum(Y)
  FROM endofweektbl
  LEFT OUTER JOIN amttbl
    ON amttbl.datenumber <= endofweektbl.datenumber
  GROUP BY X
) t2
  ON t0.X = t2.X

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shragiAuthor Commented:
what is mytable t0 ?
0
Kent OlsenData Warehouse Architect / DBACommented:
Sorry..

That would be the amttbl, endofweektbl, or any "master" table that lists all of item X.


0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.