473,320 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Getting a proper Count for a month

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, 214 views)
Dec 3 '09 #1
8 2504
Delerna
1,134 Expert 1GB
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
149 100+
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
@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
@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
149 100+
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
1,134 Expert 1GB
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
Thanks a lot, i seem to be getting somewhere.
Dec 14 '09 #8
@Delerna
This method seems to work for me as well, thanks a lot for the continued help. Much appreciated.
Dec 14 '09 #9

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

Similar topics

3
by: Dagpauk | last post by:
Assume the following table holding information about the planning date and execution date for an imaginary "objects" ObjectPlan ObjectID PlannedDate ExecutedDate 1 19.03.04 28.03.04...
4
by: alexandre.brisebois | last post by:
Hi, I am using access 2003, I would like to know if there is an option to reorganize the tables in a maner that is readable, as we can do in sql sever 2000 or 2005. I have been given a database...
4
by: Sjef ten Koppel | last post by:
Hi, I've a small problem. I have a table in which one column is date. I want to count the records for statiscs in a temptable grouped by months lets say 12 months back. e.g. month 1 counts 164...
5
by: whitsey | last post by:
Here is what I have: SELECT (SELECT COUNT(*) AS SEARCHES FROM SEARCHES INNER JOIN GROUPS ON SEARCHES.SITE_ID = GROUPS.SITE_ID WHERE
0
by: uninvitedm | last post by:
Heya I've got a table of invoices, which have dates and customer_id's. What I need to get is the number of occurances for this customer for a 12-month range window. For example, if there's an...
0
by: preeti13 | last post by:
i have a two tables employeenominations and reason if someone storing a data first time it will store into the employeenominations table if name is already exist it will store into the reason table...
2
by: srusskinyon | last post by:
I need some help getting unique records from our database! I work for a small non-profit homeless shelter. We keep track of guest information as well as what services we have offered for...
12
by: petter | last post by:
Hi! I have two questions: one question that regards the COUNT-function, and one about how to display a month even if I don’t have any data for that month. I have an Access database where I want...
1
by: kummu4help | last post by:
hi, i am using mysql. i have 3 tables with following structures. these are not actual tables i am working on. table A id varchar(16),name varchar(255),InDate datetime table B id...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.