multiple dateparts

1. Join Date
Dec 1969
Posts
73

## multiple dateparts

Need to SUM the values between 2 tables using DATEPART. Here&#039;s the query I wrote, but it returns NULL instead of a value:<BR><BR>CREATE PROCEDURE ActiveOrders<BR><BR>(<BR>@Date datetime = null<BR>)<BR><BR>AS<BR><BR>DECLARE @ActiveOrders money<BR><BR>SELECT @ActiveOrders = SUM(t1.OrdersActive)+SUM(t2.OrdersActive)<BR>FROM table1 t1, table2 t2<BR>WHERE (DATEPART(dy,t1.OrderDT) = DATEPART(dy,@Date)<BR>AND DATEPART(dy,t2.OrderDT) = DATEPART(dy,@Date)<BR>AND DATEPART(yy,t1.OrderDT) = DATEPART(yy,@Date)<BR>AND DATEPART(yy,t2.OrderDT) = DATEPART(yy,@Date))<BR><BR>SELECT @ActiveOrders<BR><BR>I know what the problem is - the use of multiple DATEPART. But I haven&#039;t figured out the answer. I was thinking about pulling the date and then using it as a variable, but no luck yet.<BR><BR>I even broke them out into separate SUM queries, but still can&#039;t get the total value for some reason.<BR><BR>Thanks.

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Why do you think...

...it is the use of the DATEPARTs????<BR><BR>I think it is simply because you haven&#039;t done any JOIN in there!!!<BR><BR>You have *nothing* that links the two tables together.<BR><BR>You *might* do:<BR><BR>SELECT @ActiveOrders = SUM(t1.OrdersActive)+SUM(t2.OrdersActive)<BR>FROM table1 t1, table2 t2<BR>WHERE (DATEPART(dy,t1.OrderDT) = DATEPART(dy,@Date)<BR>AND DATEPART(dy,t2.OrderDT) = DATEPART(dy,t1.OrderDT)<BR>AND YEAR(t1.OrderDT) = YEAR(@Date)<BR>AND YEAR(t2.OrderDT) = YEAR(t1.OrderDT)<BR><BR>That will, at least, force a join based on the OrderDT fields in the two tables. [I just used YEAR( ) to show you it existed...there&#039;s no practical difference between it and DATEPART( ).]<BR><BR>If you don&#039;t *WANT* to do a JOIN, then you should probably be doing a UNION query, instead of what you are doing.<BR><BR>Does it work properly if you do just one table?<BR><BR>SELECT @ActiveOrders = SUM(t1.OrdersActive)<BR>FROM table1 t1<BR>WHERE (DATEPART(dy,t1.OrderDT) = DATEPART(dy,@Date)<BR>AND DATEPART(yy,t1.OrderDT) = DATEPART(yy,@Date)<BR><BR>???? <BR><BR>If *that* doesn&#039;t work, then you have other problems.<BR><BR><BR><BR>

3. Join Date
Dec 1969
Posts
73

## RE: Why do you think...

Bill:<BR><BR>Thanks for the reply. Yes, I can get the sum for 1 table, but when one field has no value (or 0) it comes up NULL.<BR><BR>Thanks for the union tip - duh, forgot about that one! I don&#039;t believe I need a join because the tables aren&#039;t related.<BR><BR>Trying union right now.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•