Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Create a Row of Data on Non Business Day

Status
Not open for further replies.

Cobby1812

IS-IT--Management
Jun 22, 2006
58
GB
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
 
you will need a table with date and left join this table to SOMETABLE
Code:
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'
 
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.
 
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:

[pre]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);
[/pre]

Tamar
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top