Create a Row of Data on Non Business Day

[Cobby1812]

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

 

[PWise]

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

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'

 

[Cobby1812]

Thanks PWise - I will give it a whirl.

 

[Olaf Doschke]

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.

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.

 

[TamarGranor]

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