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. . .
8 2504
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 -
SELECT ACBH.ACCOUNTNO,
-
CONVERT (VARCHAR,ACBH.INSTRTDTE,106) AS INSTRTDTE,
-
ACBH.ORIGBALANCE,
-
ACCT.CCY
-
FROM TheTableOrQueryWithAllDates a
-
left join ACBH b on a.YourDateField=b.YourDateField
-
left join ACCT c on a.YourDateField=c.YourDateField
-
-
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.
@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??
@nbiswas
Hi nbiswas, this sounds like a solution to my problem. Will let you know how it goes.
thanks a lot.
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)
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. -
CREATE FUNCTION fnCalendar (@StartDate datetime,@NumDays 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=@StartDate-1
-
Set @Cnt=@Cnt+1
-
END
-
RETURN
-
END
-
and you call it like this -
select * from dbo.fnCalendar('2009-01-01',10)
-
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? -
select * from dbo.fnCalendar('2009-01-01',365)
-
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 :)
Thanks a lot, i seem to be getting somewhere.
@Delerna
This method seems to work for me as well, thanks a lot for the continued help. Much appreciated.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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....
|
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
|
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...
| |