sql query to get daily payments for a month even if no payments on a given day

Question

I am using SQL Server 2005 and trying to write a query where I want to retrieve payments for a given month. I currently have:

select sum(p1.paymentamount) as subtotal, 
       CONVERT(char(10), p1.paymentdate, 103) as paymentdate
  from tblpayment p1
 where 1=1
   and p1.paymentdate >= @fromdate
   and p1.paymentdate <= @todate
group by p1.paymentdate
order by p1.paymentdate

Schema:

CREATE TABLE [dbo].[tblPayment]
( 
    [paymentid] [int] IDENTITY(1,1) NOT NULL, 
    [userid] [int] NULL , 
    [paymentdate] [datetime] NOT NULL, 
    [paymentamount] [int] NULL, 
    [paymenttype] [varchar](50) NULL, 
    [paymentnotes] [varchar](200) NULL, 
    [paymentcurrency] [nchar](10) NULL 
)

This query gives me what I want but it doesnt give me the dates where no payments were made. What I want is a query that gives me all days even if there were no payments made on that day and jut shows the subtotal as 0 for that day.

There is another catch. The currency of payments is different. So how can I have another column in the query that gives me eurototal and sterlingtotal based on @currency parameter passed in ? Assuming there is a column in the table for "paymentcurrency"

Answer 1

try something like this perhaps?

select sum(p1.paymentamount) as subtotal, 
       CASE WHEN (CONVERT(char(10), p1.paymentdate, 103) = 0) THEN 'No Sale'
       ELSE 
        CONVERT(char(10), p1.paymentdate, 103)

       END as paymentdate 
FROM   tblpayment
where  paymentdate BETWEEN @fromdate and @todate

Answer 2

If there are no dummy records in tblPayment for the dates without any payment, those dates will not appear in a query that selects only from tblPayment.

I handle this by creating a separate table with nothing but dates in it (one row per date), checking to make sure that I have all the dates to cover my query, and then LEFT JOINing my main table (in this case tblPayment) on the date table:

SELECT * FROM tblPayment LEFT OUTER JOIN tblDates
ON tblPayment.PaymentDate = tblDates.PossibleDate

This basic idea can be enhanced with GROUP BY to get the summary figures you want.

Answer 3

You have to work backwards. In order to get rows for dates that don't exist, you need to outer join them to rows that do have those dates. In order to outer join, you need to have a sequence to join to. Since you don't have a sequence, you need to create one.

To create that sequence, you have two options:

• Create a static date sequence and store it in a permanent table (Larry's answer); or
• Use an existing numeric sequence (such as spt_values) to create one on the fly.

Let's assume you want the flexibility of the second approach. Here's a common snippet I use for things like that:

SELECT DATEADD(DAY, v.number, @fromdate)
FROM master.dbo.spt_values v
WHERE v.type = 'P'
AND v.number <= DATEDIFF(DAY, @fromdate, @todate)

Now just toss that in a CTE and join it:

WITH Dates_CTE (dt) AS
(
    -- // Paste the snippet above in here
)
SELECT d.dt AS paymentdate, ISNULL(SUM(p.paymentamount), 0) AS subtotal
FROM Dates_CTE d
LEFT JOIN tblpayment p
ON p.paymentdate = d.dt
GROUP BY d.dt
ORDER BY d.dt

(Update: I left out the WHERE clause in the main query because it's technically handled by the the join, but in some instances you might get better performance by leaving it in)

As for the currency conversion, look up the syntax for PIVOT.

---

Update on PIVOT: You should be able to just enclose that entire query in parentheses, then go:

SELECT paymentdate, [Euro] AS euroamount, [Pound] as poundamount
FROM
(
    -- // Insert the full query from above in here
) p
PIVOT
(
    SUM(subtotal)
    FOR paymentcurrency IN ([Euro], [Pound])
) AS pvt

Hard to verify without knowing exactly what kind of data is in there, but try that as a starting point.

Answer 4

Here is one approach

Create the following function:

CREATE FUNCTION [dbo].[DateTable] (@StartDate DATETIME, @endDate DATETIME)
RETURNS @Itms TABLE
(
    TheDate DATETIME
)
AS
BEGIN
    DECLARE @theDate DATETIME

    SET @TheDate = @StartDate
    WHILE @TheDate <= @endDate
    BEGIN
     INSERT @Itms VALUES (@theDate)
     SET @TheDate =dateAdd(d,1,@theDate)
    END
    RETURN
END;

Then here is a query that should do what you want

select sum(p1.paymentamount) as subtotal, 
       CONVERT(char(10), p1.paymentdate, 103) as paymentdate
  from 
    (select * from tblpayment p1
      where 1=1
      and p1.paymentdate >= @fromDate
      and p1.paymentdate <= @toDate
     union
        select theDate as paymentDate,0 as paymentAmount 
          from dbo.dateTable (@fromDate,@toDate)
    ) p1
group by p1.paymentdate

Answer 5

As mentioned before you have to use a separate table (temp or permanent). The currency conversion can be done using a CASE statement. Check out the below (I made up the conversion factors ;)

declare @dates table (dateitem datetime)
declare @lower datetime
declare @upper datetime
set @lower = '12/1/9'
set @upper = '12/31/9'

while @lower <= @upper
  begin
    insert into  @dates values (@lower)
    set @lower = dateadd(day, 1, @lower)
  end


select dateitem, paymentcurrency,
   paymentindollars = case paymentcurrency when 'dollars' then total when 'euro' then total * 1.7 else 0 end,
   paymentineuros = case paymentcurrency when 'dollars' then total * 0.73 when 'euro' then total else 0 end
   from 
   (select dateitem, paymentcurrency, sum(paymentamount) as total
    from @dates DT left join tblpayment on DT.dateitem = tblpayment.paymentdate group by dateitem, paymentcurrency
    ) IQ order by dateitem

Caveats to watch out for:

• your payementdate might have times in it that you will have to remove (through casting) for the join/grouping to work properly
• for the conversions to work right you have to separate the differnt currency types, you could always wrap them in another sql to get a grand total for the day
• currency conversion is usually only good for the day so applying a general conversion against a period of time is not going to give you good financial results, only decent ballpark figures (ie don't try and file it on your taxes ;)

Hope that helps a bit.