Getting a proper Count for a month

[Innocent2104]

Hi there,
The script below displays the attached output but as shown, it skips certain days and i need to include these to calculate my avg balance for a certain month, i.e.Nov. How do i update the script to include for example between 01 Nov and 05 Nov, there was no entries, therefore my balance should remain the same & display my missing dates 02,03,04 Nov??

DECLARE
@STARTDATE DATETIME,
@ENDDATE DATETIME

SET @STARTDATE = '2009-11-01'
SET @ENDDATE = '2009-11-30'

SELECT ACBH.ACCOUNTNO,
CONVERT (VARCHAR,ACBH.I NSTRTDTE,106) AS INSTRTDTE,
ACBH.ORIGBALANC E,
ACCT.CCY

FROM ACBH,ACCT
WHERE ACBH.ACCOUNTNO = '31400000782'
AND ACBH.ACCOUNTNO = ACCT.ACCOUNTNO
AND ACBH.INSTRTDTE BETWEEN @STARTDATE AND @ENDDATE

Thanks in advance for assistance. . .

[Delerna]

You need another table or view that contains all dates between your date range.
You then use that table or view as the main table and left join all the rest to that

A rough "partial" example to give you an idea
[code=sql]
SELECT ACBH.ACCOUNTNO,
CONVERT (VARCHAR,ACBH.I NSTRTDTE,106) AS INSTRTDTE,
ACBH.ORIGBALANC E,
ACCT.CCY
FROM TheTableOrQuery WithAllDates a
left join ACBH b on a.YourDateField =b.YourDateFiel d
left join ACCT c on a.YourDateField =c.YourDateFiel d

[/code]

[nbiswas]

Getting a proper Count for a month

Hi,
I am giving u a sample which recently I solved in my organization. It goes like in a month there can be some events for some days and the other days will be vacant. I need to find the vacant days.

Solution in Sql Server 2005+

Sample data

declare @tblEvent table(startdate date,enddate date)
insert into @tblEvent 
        select '11/3/2009','11/5/2009' union all
        select '11/5/2009','11/9/2009' union all
        select '11/12/2009','11/15/2009'

Query:

;WITH datesCalender AS (
     SELECT CAST('2009-11-01' AS DATETIME) 'dates'
     UNION ALL
     SELECT DATEADD(dd, 1, t.dates) 
       FROM datesCalender t
      WHERE DATEADD(dd, 1, t.dates) <= '2009-11-15')
,missingDates AS
(
	SELECT   d.dates
	  FROM datesCalender d 
	EXCEPT
	SELECT  d.dates
	  FROM datesCalender d 
	  JOIN @tblEvent t ON d.dates BETWEEN t.startdate AND t.enddate
)
select MissingDate = stuff(MissingDate,1,1,'') from 
(select ',' + cast(dates as varchar(max)) from missingDates 
for xml path(''))X(MissingDate)

Output:

MissingDate
Nov  1 2009 12:00AM,Nov  2 2009 12:00AM,Nov 10 2009 12:00AM,Nov 11 2009 12:00AM

First I am generating a date calender between the date ranges(here Startdate:2009-11-01 and Enddate: 2009-11-15) and then generating the total dates for each event i.e. if for Event 1 the given date range is 4th Nov 2009 and End is 10th Nov 2009, I am generating all the dates inclusive to the start and end(i.e. 4th, 5th, 6th,7th,8th,9th ,10th Nov 2009). And using the Except Set operator, I am getting the vacant dates( Total Date Calender Dates - Total Event Dates).

Finally by using For Xml Path() I am just formating the display(columns in a single row with comma delimited)

I presented you the concept.

Hope now you can go ahead.

Best of luck.

[Innocent2104]

You need another table or view that contains all dates between your date range.
You then use that table or view as the main table and left join all the rest to that

A rough "partial" example to give you an idea
[code=sql]
SELECT ACBH.ACCOUNTNO,
CONVERT (VARCHAR,ACBH.I NSTRTDTE,106) AS INSTRTDTE,
ACBH.ORIGBALANC E,
ACCT.CCY
FROM TheTableOrQuery WithAllDates a
left join ACBH b on a.YourDateField =b.YourDateFiel d
left join ACCT c on a.YourDateField =c.YourDateFiel d

[/code]

Thanks a mil Delerna, unfortunately i don't have a table with all the dates. Does it mean i need to generate the dates first using a different script??

[Innocent2104]

Hi,
I am giving u a sample which recently I solved in my organization. It goes like in a month there can be some events for some days and the other days will be vacant. I need to find the vacant days.

Solution in Sql Server 2005+

Sample data

declare @tblEvent table(startdate date,enddate date)
insert into @tblEvent 
        select '11/3/2009','11/5/2009' union all
        select '11/5/2009','11/9/2009' union all
        select '11/12/2009','11/15/2009'

Query:

;WITH datesCalender AS (
     SELECT CAST('2009-11-01' AS DATETIME) 'dates'
     UNION ALL
     SELECT DATEADD(dd, 1, t.dates) 
       FROM datesCalender t
      WHERE DATEADD(dd, 1, t.dates) <= '2009-11-15')
,missingDates AS
(
	SELECT   d.dates
	  FROM datesCalender d 
	EXCEPT
	SELECT  d.dates
	  FROM datesCalender d 
	  JOIN @tblEvent t ON d.dates BETWEEN t.startdate AND t.enddate
)
select MissingDate = stuff(MissingDate,1,1,'') from 
(select ',' + cast(dates as varchar(max)) from missingDates 
for xml path(''))X(MissingDate)

Output:

MissingDate
Nov  1 2009 12:00AM,Nov  2 2009 12:00AM,Nov 10 2009 12:00AM,Nov 11 2009 12:00AM

First I am generating a date calender between the date ranges(here Startdate:2009-11-01 and Enddate: 2009-11-15) and then generating the total dates for each event i.e. if for Event 1 the given date range is 4th Nov 2009 and End is 10th Nov 2009, I am generating all the dates inclusive to the start and end(i.e. 4th, 5th, 6th,7th,8th,9th ,10th Nov 2009). And using the Except Set operator, I am getting the vacant dates( Total Date Calender Dates - Total Event Dates).

Finally by using For Xml Path() I am just formating the display(columns in a single row with comma delimited)

I presented you the concept.

Hope now you can go ahead.

Best of luck.

Hi nbiswas, this sounds like a solution to my problem. Will let you know how it goes.

thanks a lot.

[nbiswas]

Getting a proper Count for a month

Yes. You need to generate a calendar table.

If you are using SQL SERVER 2005+ you can take help of CTE

;WITH datesCalender AS (
SELECT CAST('1900-01-01' AS DATETIME) 'dates'
UNION ALL
SELECT DATEADD(dd, 1, t.dates)
FROM datesCalender t
WHERE DATEADD(dd, 1, t.dates) <= '4000-12-31')

You can even look into TONY ROGERSON'S RAMBLINGS ON SQL SERVER for the same or some other ways after googling(which ever u feel comfortable)

[Delerna]

Does it mean i need to generate the dates first using a different script??

You could also use a user defined function that returns a table filled with dates.
Think of a UDF that returns a table as a view that has parameters.


[code=sql]
CREATE FUNCTION fnCalendar (@StartDate datetime,@NumDa ys bigint)

RETURNS @tbl table(Dte DateTime)
AS
BEGIN
declare @Cnt bigint
set @Cnt=1
WHILE @Cnt<@NumDays
BEGIN
INSERT INTO @tbl
SELECT @StartDate

Set @StartDate=@Sta rtDate-1
Set @Cnt=@Cnt+1
END
RETURN
END
[/code]


and you call it like this
[code=sql]
select * from dbo.fnCalendar( '2009-01-01',10)
[/code]


You see....it behaves just like a view but has parameters.
The UDF as written returns all dates from '2009-01-01' and 10 days previous to that.

Want a years worth of dates?
[code=sql]
select * from dbo.fnCalendar( '2009-01-01',365)
[/code]

You can join to that UDF just like you would any view or table


nbiswas method looks valid and interesting also (I am still on SQL 2000 so...)
Makes me look even more forward to upgrading.....e ventually :)

[Innocent2104]

Thanks a lot, i seem to be getting somewhere.

[Innocent2104]

You could also use a user defined function that returns a table filled with dates.
Think of a UDF that returns a table as a view that has parameters.


[code=sql]
CREATE FUNCTION fnCalendar (@StartDate datetime,@NumDa ys bigint)

RETURNS @tbl table(Dte DateTime)
AS
BEGIN
declare @Cnt bigint
set @Cnt=1
WHILE @Cnt<@NumDays
BEGIN
INSERT INTO @tbl
SELECT @StartDate

Set @StartDate=@Sta rtDate-1
Set @Cnt=@Cnt+1
END
RETURN
END
[/code]


and you call it like this
[code=sql]
select * from dbo.fnCalendar( '2009-01-01',10)
[/code]


You see....it behaves just like a view but has parameters.
The UDF as written returns all dates from '2009-01-01' and 10 days previous to that.

Want a years worth of dates?
[code=sql]
select * from dbo.fnCalendar( '2009-01-01',365)
[/code]

You can join to that UDF just like you would any view or table


nbiswas method looks valid and interesting also (I am still on SQL 2000 so...)
Makes me look even more forward to upgrading.....e ventually :)

This method seems to work for me as well, thanks a lot for the continued help. Much appreciated.