INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Create a Row of Data on Non Business Day

Create a Row of Data on Non Business Day

(OP)
Hi All,

I have a database that only updates on working days - for instance tonight it will roll forward and upload data from todays transactions then nothing for Saturday nothing for Sunday.

I have been asked to create a query (for averages) that will show all days in the month regardless if this is a business day or not.

SELECT AVERAGE(CURRENTACCOUNT), SUM(CURRENTACCOUNT), EXTRACTDATE
FROM SOMETABLE
WHERE EXTRACTDATE >= '01-APR-2016'

Any ideas on how to do this.

Many thanks

RE: Create a Row of Data on Non Business Day

you will need a table with date and left join this table to SOMETABLE

CODE --> t-sql

Select AVERAGE(CURRENTACCOUNT), SUM(CURRENTACCOUNT), DateField As EXTRACTDATE
From DatesTable DT
left join SOMETABLE St
on st.DateField=st.EXTRACTDATE
WHERE st.DateField >= '01-APR-2016' 

RE: Create a Row of Data on Non Business Day

(OP)
Thanks PWise - I will give it a whirl.

RE: Create a Row of Data on Non Business Day

I see minor issues. If you do the left join, because you expect dates in DatesTable, which do not exist in SOMETABLE, then the WHERE clause shouldn't sabotage that, also your join condition should have on st and one dt field.

Besides I would use a right join in such a case, main select is from the own table, not the dates table, but that's a matter of taste.

CODE

Select AVERAGE(CURRENTACCOUNT), SUM(CURRENTACCOUNT), DT.DateField As EXTRACTDATE
From Sometable ST
Right Join DatesTable DT on ST.EXTRACTDATE = DT.DateField
Where DT.DateField >= '01-APR-2016' 

Putting ST.EXTRACTDATE looks like the same condition, but for no matches ST.EXTRACTDATE is null and thus the where clause wouldn't be true, you turn a left join into an inner join and still have not satisfied the main problem to also have rows for dates without data in Someatable.

Bye, Olaf.

RE: Create a Row of Data on Non Business Day

If you don't want to maintain a table of dates, you can do this with a recursive CTE to generate the list. Here's an example that I wrote (previously) using Adventureworks' data:

DECLARE @StartDate DATETIME ;
DECLARE @EndDate DATETIME ;

SELECT @StartDate = MIN(Orderdate), 
       @EndDate = MAX(OrderDate)
  FROM [Sales].[SalesOrderHeader];

-- recursive CTE
WITH AllDates (tDate)
AS (
    SELECT @StartDate 
    UNION ALL
    SELECT DATEADD(DAY, 1, tDate)
    FROM AllDates
    WHERE tDate < @EndDate
    )

SELECT tDate,
       COUNT(SOH.SalesOrderID) AS OrderCount
FROM AllDates
  LEFT JOIN Sales.SalesOrderHeader AS SOH
    ON SOH.OrderDate >= tDate
    AND SOH.OrderDate < DATEADD(DAY, 1, tDate)
GROUP BY tDate
OPTION (MAXRECURSION 0);
 

Tamar

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close