Counting field values per day between specific date ranges

Question

Counting field values per day between specific date ranges

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

sc-w Hall of Fame Points: 3667 More actions · Answer 1

Hi again,

Thank you for your detailed reply Luis Cazares I have only just seen your reply, I am going to read the article. I must confess I was a bit stupid and didn't realise the thread had gone over two pages hence me triplicating my question about your option 2, I thought I couldn't post as it didn't appear on page one so I tried a few times!

MMartin1 thank you for your reply, just seen this too! It is for reporting but there is a filter to remove cancelled bookings. I will however look into the fact table for a data cube. This is great to learn other ways to handle this data. Thanks you.

MY ISSUE: From option 1 of Luis Cazares original post.

I have a question I am hoping you can help with. I have been playing with these date queries, in particular Luis example below. I wanted to check the results against a simple query that just looks at a single date and totals the adults and children. My thoughts being, if I spot check some dates and they tally up the same results with the two queries everything is correct. This wasn't the case. See below.

Here is Luis query.

WITH

E(n) AS(

SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

),

E2(n) AS(

SELECT a.n FROM E a, E b

),

E4(n) AS(

SELECT a.n FROM E2 a, E2 b

),

cteTally(n) AS(

SELECT 0 UNION ALL

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n

FROM E4

),

cteCalendar(CalDate) AS(

SELECT DATEADD( dd, n, '2016/07/24') AS CalDate

FROM cteTally

)

SELECT c.CalDate,

SUM( b.No_Adults) AS No_Adults,

SUM( b.No_Children) AS No_Children

FROM [wce_bookings] b

RIGHT

JOIN cteCalendar c ON CAST( b.Arrival_Date AS date) <= c.CalDate

AND b.Departure_Date > c.CalDate

GROUP BY CalDate

ORDER BY CalDate;

If you look at the date 25/07/2016 (GMT) the results for adults shows 44 and children 18

However when I run my query:

SELECT quote_no, Arrival_Date, departure_Date, no_adults, no_children FROM wce_bookings WHERE '20160725' BETWEEN Arrival_Date AND departure_Date

SELECT sum(No_Adults), SUM(no_children) FROM wce_bookings WHERE '20160725' BETWEEN Arrival_Date AND departure_Date

My results which show 37 adults and 18 children

I checked a few other dates and some days the results with both queries for adults and children the counts are the same. For example 12/08/2016 (GMT) adults 3 and children 0.

I'm scratching my head for a logical answer here and I know there is one. Any advice would be much appreciated.

Many thanks.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

That's because you're not taking the hours into account. The difference comes from 2 quotes that you're not including (973 & 1057). Check them and decide if they belong to the date. If they not, change the JOIN clause.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sc-w Hall of Fame Points: 3667 More actions · Answer 3

Ahhh, the times were impacting it! Thank you for the help.