?
Solved

Day Count

Posted on 2016-09-23
8
Medium Priority
?
61 Views
Last Modified: 2016-09-23
Experts,

I need to show a Day Count between [ValueDate] for Where T.FacilityType=Q_Disbursement.FacilityType.
I have attached a db with a query Q_DisbursementSum (as shown in the pic below)

Let me know if you need additional information.
Grateful for your help.

DayCountPicBalanceEE.accdb
0
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 23

Expert Comment

by:Ferruccio Accalai
ID: 41812266
My suggestion:
SELECT TABLE1.FacilityType, TABLE1.ValueDate, Table1.[Cumulative Drawn], TABLE1.Available, DATEDIFF("D", TABLE1.valueDate, MIN(Table2.valueDate)) AS DayCount FROM (SELECT Q_Disbursement.FacilityType, Q_Disbursement.ValueDate, (Select Sum(T.Amount) From Q_Disbursement As T Where T.FacilityType=Q_Disbursement.FacilityType And T.ValueDate <= Q_Disbursement.ValueDate) AS [Cumulative Drawn], [FacilityAmount]-[Cumulative Drawn] AS Available
FROM Q_Disbursement
GROUP BY Q_Disbursement.FacilityType, Q_Disbursement.ValueDate, "?", Q_Disbursement.FacilityAmount, Q_Disbursement.Amount
ORDER BY Q_Disbursement.FacilityType, Q_Disbursement.ValueDate)  TABLE1
LEFT JOIN (SELECT Q_Disbursement.FacilityType, Q_Disbursement.ValueDate, (Select Sum(T.Amount) From Q_Disbursement As T Where T.FacilityType=Q_Disbursement.FacilityType And T.ValueDate <= Q_Disbursement.ValueDate) AS [Cumulative Drawn], [FacilityAmount]-[Cumulative Drawn] AS Available
FROM Q_Disbursement
GROUP BY Q_Disbursement.FacilityType, Q_Disbursement.ValueDate, "?", Q_Disbursement.FacilityAmount, Q_Disbursement.Amount
ORDER BY Q_Disbursement.FacilityType, Q_Disbursement.ValueDate) TABLE2 on TABLE1.FacilityType = TABLE2.FacilityType AND TABLE2.ValueDate > TABLE1.ValueDate
Group by TABLE1.FacilityType, TABLE1.ValueDate,  TABLE1.[Cumulative Drawn], TABLE1.Available

Open in new window

0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 41812283
Gotta say I like the magnitude of those values.

So, without downloading your database, I use something like:

SELECT T.FacilityType
, T.DrawDate
, Min(T2.DrawDate) as NextDraw
, DateDiff("d", T.DrawDate, Min(T2.DrawDate)) as DayGap
FROM yourTable as T
LEFT JOIN yourTable as T1
ON T.FacilityType = T1.FacilityType
AND T.DrawDate < T1.DrawDate
GROUP BY T.FacilityType, T.DrawDate
1
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 41812364
In your query, use this simple expression (not a Group By):

DayCountx: DateDiff("d",[ValueDate],(Select Min(Q.ValueDate) From Q_Disbursement As Q Where Q.FacilityType = Q_Disbursement.FacilityType And Q.ValueDate > Q_Disbursement.ValueDate))

Demo
/gustav
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41812375
I noticed the double entry for PIF 2015-06-23.
You can get rid of that by changing your last column from Group By to Sum:

eesum.PNG
/gustav
0
 

Author Closing Comment

by:pdvsa
ID: 41812481
Perfect.  

Dale's and Ferrucios method did give the correct count however I needed it to follow my query I had.  I am not too familiar with the T method so its difficult for me to modify it.

thank you experts!
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41812484
You are welcome!

/gustav
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 41812491
pdvsa,

The "T method" is just a matter of aliasing table or query names so that your query is more readable.

I usually use an alias that is similar to the name of the table I'm working with, for example:

SELECT P.*
FROM tbl_People as P

or

SELECT P.*, F.Family_ID
FROM tbl_People as P
LEFT JOIN tbl_Families as F
ON P.Family_ID = F.Family_ID
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41812506
Also, alias can be mandatory - as Q (or T or whatever) in my Select .. above - to distinguish between two instances of the same table/query.

/gustav
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question