By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Getting a proper Count for a month

P: 5
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.INSTRTDTE,106) AS INSTRTDTE,
ACBH.ORIGBALANCE,
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. . .
Attached Images
File Type: jpg SQL Issue.jpg (10.9 KB, 181 views)
Dec 3 '09 #1
Share this Question
Share on Google+
8 Replies


Delerna
Expert 100+
P: 1,134
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
Expand|Select|Wrap|Line Numbers
  1. SELECT ACBH.ACCOUNTNO,
  2.    CONVERT (VARCHAR,ACBH.INSTRTDTE,106) AS INSTRTDTE,
  3.    ACBH.ORIGBALANCE,
  4.    ACCT.CCY
  5. FROM TheTableOrQueryWithAllDates a
  6. left join ACBH b on a.YourDateField=b.YourDateField
  7. left join ACCT c on a.YourDateField=c.YourDateField
  8.  
  9.  
Dec 11 '09 #2

nbiswas
100+
P: 149
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

Expand|Select|Wrap|Line Numbers
  1. declare @tblEvent table(startdate date,enddate date)
  2. insert into @tblEvent 
  3.         select '11/3/2009','11/5/2009' union all
  4.         select '11/5/2009','11/9/2009' union all
  5.         select '11/12/2009','11/15/2009'
Query:

Expand|Select|Wrap|Line Numbers
  1. ;WITH datesCalender AS (
  2.      SELECT CAST('2009-11-01' AS DATETIME) 'dates'
  3.      UNION ALL
  4.      SELECT DATEADD(dd, 1, t.dates) 
  5.        FROM datesCalender t
  6.       WHERE DATEADD(dd, 1, t.dates) <= '2009-11-15')
  7. ,missingDates AS
  8. (
  9.     SELECT   d.dates
  10.       FROM datesCalender d 
  11.     EXCEPT
  12.     SELECT  d.dates
  13.       FROM datesCalender d 
  14.       JOIN @tblEvent t ON d.dates BETWEEN t.startdate AND t.enddate
  15. )
  16. select MissingDate = stuff(MissingDate,1,1,'') from 
  17. (select ',' + cast(dates as varchar(max)) from missingDates 
  18. for xml path(''))X(MissingDate)
Output:

Expand|Select|Wrap|Line Numbers
  1. MissingDate
  2. 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.
Dec 11 '09 #3

P: 5
@Delerna
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??
Dec 11 '09 #4

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

thanks a lot.
Dec 11 '09 #5

nbiswas
100+
P: 149
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)
Dec 11 '09 #6

Delerna
Expert 100+
P: 1,134
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.


Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION fnCalendar (@StartDate datetime,@NumDays bigint)
  2.  
  3.    RETURNS @tbl table(Dte DateTime)
  4.    AS  
  5.    BEGIN 
  6.       declare @Cnt bigint
  7.       set @Cnt=1
  8.       WHILE @Cnt<@NumDays
  9.       BEGIN
  10.              INSERT INTO @tbl
  11.              SELECT @StartDate
  12.  
  13.              Set @StartDate=@StartDate-1
  14.              Set @Cnt=@Cnt+1
  15.       END
  16.       RETURN
  17.    END
  18.  

and you call it like this
Expand|Select|Wrap|Line Numbers
  1. select * from dbo.fnCalendar('2009-01-01',10)
  2.  

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?
Expand|Select|Wrap|Line Numbers
  1. select * from dbo.fnCalendar('2009-01-01',365)
  2.  
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.....eventually :)
Dec 14 '09 #7

P: 5
Thanks a lot, i seem to be getting somewhere.
Dec 14 '09 #8

P: 5
@Delerna
This method seems to work for me as well, thanks a lot for the continued help. Much appreciated.
Dec 14 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.