Getting query to show all dates, even if date has no data.

[Paid The Umpire]

Hey,

I have a table which has records inserted into it, on a varying degree
of activity (some days have a lot of record on other days none).


Client_Reference_Number 7char
IMEI_Number 14char
Start_Date_And_Time datetime
Number_Of_Bytes int
Cost_To_Client currency

What i want to do through an SQL query, is to get a summary of all
records made during two dates.

The Total Number_Of_Bytes and the Total Cost_To_Client for each day.

So something like thus:
Client_Reference_Number, IMEI_Number, Start_Date_And_Time,
SUM(Number_Of_Bytes), SUM(Cost_To_Client)
1000000, 300003000129100, Jan 15 2006, 118, $0.38
1000000, 300003000129100, Jan 16 2006, 0, $0.00
1000000, 300003000129100, Jan 17 2006, 0, $0.00
1000000, 300003000129100, Jan 18 2006, 145, $9.50 etc.

I've got it grouped us correctly...

SELECT Client_Reference_Number, IMEI_number,
Format$([Start_Date_And_Time],'Long Date'),
Sum(SBD_Records.Number_Of_bytes_sent), Sum(SBD_Records.Cost_To_Client)
FROM SBD_Records
GROUP BY Client_Reference_Number, IMEI_number,
Format$([SBD_Records].[Start_Date_And_Time],'Long Date');

But this will only show record which have a record inserted in the
table. What i need is all dates, regardless of the amount of records in
the table.

Any help?

Regards,
Paid The Umpire!

 

[Rob Parker]

Hi,

I hope I've understood this correctly. You want your query to show all
dates, rather than just those for which there are records, OK?

To do that, you need a way to introduce all dates into your recordset.
There are probably other ways, but a simple one is to create a separate
table containing all the dates you will want - it only needs a single field.
(You might find it useful to generate this in Excel, so you can autofill to
generate all dates, then import into your database. The table needs a
single record for each date, and it does not need a time component.) For
the SQL below, I created a table SBD_Dates, with a field DateRecord, of
Date/Time type.

Include this in your query, with a right join to your existing table. If
you do this in the query design grid you will need to subsequently edit the
SQL for the join to force both sides to the long date format, if your
records have both date and time, so that the join is done on the date
portion only. The final result (which you won't be able to view in the
query design grid) will be:

SELECT SBD_Dates.DateRecord, SBD_Records.Client_Reference_Number,
SBD_Records.IMEI_Number, Sum(SBD_Records.Number_Of_Bytes_Sent) AS
SumOfNumber_Of_Bytes_Sent, Sum(SBD_Records.Cost_To_Client) AS
SumOfCost_To_Client
FROM SBD_Records RIGHT JOIN SBD_Dates ON
Format$(SBD_Records.Start_Date_And_Time,'Long Date') =
Format$(SBD_Dates.DateRecord,'Long Date')
GROUP BY SBD_Dates.DateRecord, SBD_Records.Client_Reference_Number,
SBD_Records.IMEI_Number, Format$(SBD_Records.Start_Date_And_Time,'Long
Date')
ORDER BY SBD_Dates.DateRecord;

If you want/need to format the date in the output, simply apply a format
string to the first field in this SQL, such as:

SELECT Format$(SBD_Dates.DateRecord,,'Long Date'), ...

HTH,

Rob

Hey,

I have a table which has records inserted into it, on a varying degree
of activity (some days have a lot of record on other days none).


Client_Reference_Number 7char
IMEI_Number 14char
Start_Date_And_Time datetime
Number_Of_Bytes int
Cost_To_Client currency

What i want to do through an SQL query, is to get a summary of all
records made during two dates.

The Total Number_Of_Bytes and the Total Cost_To_Client for each day.

So something like thus:
Client_Reference_Number, IMEI_Number, Start_Date_And_Time,
SUM(Number_Of_Bytes), SUM(Cost_To_Client)
1000000, 300003000129100, Jan 15 2006, 118, $0.38
1000000, 300003000129100, Jan 16 2006, 0, $0.00
1000000, 300003000129100, Jan 17 2006, 0, $0.00
1000000, 300003000129100, Jan 18 2006, 145, $9.50 etc.

I've got it grouped us correctly...

SELECT Client_Reference_Number, IMEI_number,
Format$([Start_Date_And_Time],'Long Date'),
Sum(SBD_Records.Number_Of_bytes_sent), Sum(SBD_Records.Cost_To_Client)
FROM SBD_Records
GROUP BY Client_Reference_Number, IMEI_number,
Format$([SBD_Records].[Start_Date_And_Time],'Long Date');

But this will only show record which have a record inserted in the
table. What i need is all dates, regardless of the amount of records in
the table.

Any help?

Regards,
Paid The Umpire!

 

[Paid The Umpire]

Thanks, that works fine.

But now i need to to do it by also Client_Reference_Number.

So i have a list of all dates with all totals for one and only one
client. When i try i don't get the details for the null days. I just
get the dates with data.

Any ideas?

 

[Paid The Umpire]

Anybody?