Help me with this SQL Query...

Question

I've got a SQL Server CE 3.5 table (Transactions) with the following Schema:

• ID
• Transaction_Date
• Category
• Description
• Amount

Query:

  SELECT Transaction_Date, SUM(Amount) 
    FROM Transactions 
GROUP BY Transaction_Date;

I'm trying to do a SUM(Amount) and group by transaction_date just so I can get the total amount for each day but I want to get back values even for days there were no transactions so basically the record for a day with no transactions would just have $0.00 for amount.

Thanks for the help!

Answer 1

You need a Calendar table to select over the dates. Alternatively, if you have a Numbers table, you could turn that effectively into a Calendar table. Basically, it's just a table with every date in it. It's easy enough to build and generate the data for it and it comes in very handy for these situations. Then you would simply use:

SELECT
    C.calendar_date,
    SUM(T.amount)
FROM
    Calendar C
LEFT OUTER JOIN Transactions T ON
    T.transaction_date = C.calendar_date
GROUP BY
    C.calendar_date
ORDER BY
    C.calendar_date

A few things to keep in mind:

If you're sending this to a front-end or reporting engine then you should just send the dates that you have (your original query) and have the front end fill in the $0.00 days itself if that's possible.

Also, I've assumed here that the date is an exact date value with no time component (hence the "=" in the join). Your calendar table could include a "start_time" and "end_time" so that you can use BETWEEN for working with dates that include a time portion. That saves you from having to strip off time portions and potentially ruining index usage. You could also just calculate the start and end points of the day when you use it, but since it's a prefilled work table it's easier IMO to include a start_time and end_time.

Answer 2

If you want dates that don't have transactions to appear you can add a DUMMY transaction for each day with the amount of zero it won't interfere with SUM and would so what you want

Answer 3

Not sure if any this works with CE

With common table expressions

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '2010-07-10'
SET @EndDate = '2010-07-20'

;WITH Dates AS (
    SELECT  @StartDate AS DateValue
    UNION ALL
    SELECT  DateValue + 1
    FROM    Dates
    WHERE   DateValue + 1 <= @EndDate
)
SELECT      Dates.DateValue, ISNULL(SUM(Transactions.Amount), 0)
FROM        Dates
LEFT JOIN   Transactions ON
                Dates.DateValue = Transactions.Transaction_Date
GROUP BY    Dates.DateValue;

With loop + temporary table

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '2010-07-10'
SET @EndDate = '2010-07-20'

SELECT @StartDate AS DateValue INTO #Dates

WHILE @StartDate <= @EndDate
BEGIN
    SET @StartDate = @StartDate + 1
    INSERT INTO #Dates VALUES (@StartDate)
END

SELECT      Dates.DateValue, ISNULL(SUM(Transactions.Amount), 0)
FROM        #Dates AS Dates
LEFT JOIN   Transactions ON
                Dates.DateValue = Transactions.Transaction_Date
GROUP BY    Dates.DateValue;

DROP TABLE  #Dates

Answer 4

You'll need to upper and lower bound your statement somehow, but perhaps this will help.

DECLARE @Start  smalldatetime, @End smalldatetime

SELECT @Start = 'Jan 1 2010', @End = 'Jan 18 2010';

--- make a CTE of range of dates we're interested in
WITH Cal AS (
    SELECT CalDate = convert(datetime, @Start) 
    UNION ALL
    SELECT CalDate = dateadd(d,1,convert(datetime, CalDate))  FROM Cal WHERE CalDate < @End

)

SELECT CalDate AS TransactionDate, ISNULL(SUM(Amount),0) AS TransactionAmount
FROM  Cal AS C
LEFT JOIN Transactions AS T On C.CalDate = T.Transaction_Date
GROUP BY CalDate ;

Answer 5

Once you have a Calendar table (more on that later) you can then do an inner join on the range of your data to fill in missing dates:

SELECT CalendarDate, NULLIF(SUM(t.Amount),0)
FROM (SELECT CalendardDate FROM Calendar
WHERE CalendarDate>= (SELECT MIN(TransactionDate) FROM Transactions) AND
  CalendarDate<= (SELECT MAX(TransactionDate) FROM Transactions)) c
 LEFT JOIN 
    Transactions t ON t.TransactionDate=c.CalendarDate
GROUP BY CalendarDate

To create a calendar table, you can use a CTE:

WITH CalendarTable
AS
(
  SELECT CAST('20090601' as datetime) AS [date]
  UNION ALL
  SELECT DATEADD(dd, 1, [date])
  FROM CTE_DatesTable
  WHERE DATEADD(dd, 1, [date]) <= '20090630' /* last date */
)
SELECT [date] FROM CTE_DatesTable
OPTION (MAXRECURSION 0);

Combining the two, we have

WITH CalendarTable
AS
(
  SELECT MIN(TransactionDate) FROM Transactions AS [date]
  UNION ALL
  SELECT DATEADD(dd, 1, [date])
  FROM CTE_DatesTable
  WHERE DATEADD(dd, 1, [date]) <= (SELECT MAX(TransactionDate) FROM Transactions) 
)
SELECT c.[date], NULLIF(SUM(t.Amount),0)
FROM Calendar c
 LEFT JOIN 
    Transactions t ON t.TransactionDate=c.[date]
GROUP BY c.[date]