Is it possible to get bi-weekly subtotals using SQL? 2

[Spork52]

I have a table of dated invoices. I would like to display bi-weekly sub-totals of invoice amounts. E.g.:

1/1/9 - 1/14/9 $500
1/15/9 - 1/28/9 $392
etc.

The starting date (1/1/9 in the example) is variable.

Is this possible with a SQL query? Or, will I have to do the summing with, say, an ASP script? Or a stored procedure?

 

[markros]

Yes, it is possible, but I think we would need to first create a temp table with be-weekly dates, e.g.

create procedure usp_GetBiWeeklyTotals (@StartDate datetime)as
begin
declare @Max_Date datetime, @CurDate datetime
declare @Temp table (ID int Identity, dt Datetime)
select @Max_Date = max(date) from Invoices
set @CurDate = @StartDate
while @CurDate < @Max_Date
begin
set @CurDate = dateadd(week, 2, @CurDate)
insert into @Temp values (@CurDate)
end

select * from @Temp

See, if we get correctly created @Temp table using this code, I wrote it from the top of my head (not tested).

If yes, I think we can start from here.

 

[Spork52]

Okay, that got me thinking. I haven't written stored procedures before, so please excuse syntactical and other blunders. At this point I am more interested in finding a workable method than perfect code.

So, is this something like what you had in mind? (I've made some minor changes to your part of the code.)

create procedure usp_GetBiWeeklyTotals (@StartDate datetime, @Max_Date datetime, @customer_id int) as
begin
declare @Max_Date datetime, @CurDate datetime
declare @Temp table (ID int Identity, dt Datetime)
set @CurDate = @StartDate
while @CurDate < @Max_Date
  begin
    insert into @Temp values (@CurDate)
    set @CurDate = dateadd(week, 2, @CurDate)
  end

SELECT SUM(invoices.amount) AS subtotal, @Temp.dt FROM invoices CROSS JOIN @Temp
WHERE invoices.created BETWEEN @Temp.dt AND DATEADD(day, 13, @Temp.dt) 
AND invoices.customer_id = @customer_id
GROUP by @Temp.dt

end

 

[markros]

Yes, I think I meant something like this.

 

[Spork52]

I got the stored procedure working in a query analyzer program (starting to see why people like SPs), but how do I call it from an ASP page? I tried:

Set rsOrders = Server.CreateObject("ADODB.Command")
rsOrders.ActiveConnection = Application("connectstring")
rsOrders.CommandText = "usp_GetBiWeeklyDates"
rsOrders.CommandType = 4
rsOrders.CommandTimeout = 0
rsOrders.Prepared = true

rsOrders.Parameters(1).Value = first_perf
rsOrders.Parameters(2).Value = last_perf
rsOrders.Parameters(3).Value = request_show

rsOrders.Execute()

No error until I did this:

if NOT rsOrders.EOF then

at which point I got this error:

Microsoft VBScript runtime error '800a01b6'
Object doesn't support this property or method: 'EOF'

I assume this has something to do with how I called the SP. Do I have to somewhere specify CursorType, CursorLocation, LockType . . . ? In the SP itself?

Here's the SP, in case that makes any difference:

ALTER PROCEDURE [dbo].[usp_GetBiWeeklyDates] (@first_perf datetime, @last_perf datetime, @show_id int) as
BEGIN
DECLARE @current_date datetime
DECLARE @tempdate TABLE (tempdate_id int Identity, tempdate_date Datetime)

IF DATEPART(dw, @first_perf) > 4
    SET @current_date = DATEADD(day, -2-DATEPART(dw, @first_perf), @first_perf)
ELSE
    SET @current_date = DATEADD(day, -9-DATEPART(dw, @first_perf), @first_perf)

WHILE @current_date < @last_perf
  BEGIN
    INSERT INTO @tempdate (tempdate_date) VALUES (@current_date)
    SET @current_date = dateadd(week, 2, @current_date)
  END

SELECT tempdate_date, 
SUM(CASE ReservationStyle WHEN 'comp' THEN ISNULL(orderline.TicketCount,0) ELSE 0 END) AS comps, 
SUM(CASE WHEN DiscountValue = 100 AND DiscountMethod = 'percent' THEN ISNULL(orderline.TicketCount,0) ELSE 0 END) AS zerotix,
SUM(CASE WHEN (ISNULL(DiscountValue, 0) <> 100 OR ISNULL(DiscountMethod,'') <> 'percent') AND ReservationStyle = 'paid' AND ReservationSubStyle = 'cod' THEN ISNULL(orderline.TicketCount,0) ELSE 0 END) AS cods,
SUM(CASE WHEN (ISNULL(DiscountValue, 0) <> 100 OR ISNULL(DiscountMethod,'') <> 'percent') AND ReservationStyle = 'paid' AND ReservationSubStyle = 'prepaid' THEN ISNULL(orderline.TicketCount,0) ELSE 0 END) AS prepaids,
SUM(CASE WHEN (ISNULL(DiscountValue, 0) <> 100 OR ISNULL(DiscountMethod,'') <> 'percent') AND ReservationStyle = 'hold' THEN ISNULL(orderline.TicketCount,0) ELSE 0 END) AS holds,
SUM(CASE WHEN (ISNULL(DiscountValue, 0) <> 100 OR ISNULL(DiscountMethod,'') <> 'percent') AND ReservationStyle = 'paid' AND ReservationPayClient = 1 THEN ISNULL(orderline.TicketCount,0) ELSE 0 END) AS sales,
SUM(CASE WHEN (ISNULL(DiscountValue, 0) <> 100 OR ISNULL(DiscountMethod,'') <> 'percent') AND ReservationStyle = 'paid' AND ReservationPayClient = 1 THEN ISNULL(orderline.TicketCount * (orderline.TicketPrice - orderline.TicketDiscount),0) ELSE 0 END) AS amount

FROM (show LEFT JOIN runall ON show.show_id = runall.show_id
LEFT JOIN (orders JOIN orderline on orders.orderID = orderline.orderID AND transactionvalid = 1 AND order_status = 1
JOIN reservationcode ON orders.reservationID = reservationcode.reservationID AND Reservation_Status = 1 AND ReservationPending = 0)
ON runall.runall_id = orderline.PerformanceID
LEFT JOIN promocodes ON orderline.promoID = promocodes.promoID)
CROSS JOIN @tempdate 

WHERE runall.date BETWEEN tempdate_date AND DATEADD(day, 13, tempdate_date) AND runall.date >= @first_perf
AND show.show_id = @show_id
GROUP BY tempdate_date
ORDER BY tempdate_date

END

 

[markros]

I don't think it has anything to do with the procedure, even if I didn't check its code.

The error is clearly on this line

if NOT rsOrders.EOF then

You declared rsOrders as Server.CreateObject("ADODB.Command")

Should you create a separate RecordSet object to get the results?

I'm sorry, I haven't used ASP (not ASP.NET) for quite a long time, but just logically thinking I believe it should be a separate object.

 

[hmckillop]

What Markros is saying is you need

Set rsResults = Server.CreateObject("ADODB.RecordSet")
Set rsOrders = Server.CreateObject("ADODB.Command")
rsOrders.ActiveConnection = Application("connectstring")
rsOrders.CommandText = "usp_GetBiWeeklyDates"
rsOrders.CommandType = 4
rsOrders.CommandTimeout = 0
rsOrders.Prepared = true

rsOrders.Parameters(1).Value = first_perf
rsOrders.Parameters(2).Value = last_perf
rsOrders.Parameters(3).Value = request_show

SET rsResults = rsOrders.Execute()

'then
if not rsResults.eof
....

"I'm living so far beyond my income that we may almost be said to be living apart

 

[markros]

Thanks, hmckillop. Exactly what I was thinking.

 

[Spork52]

Thanks, that worked. All I had to do was add SET NOCOUNT ON to the SP to prevent this error:

ADODB.Recordset error #3704
"Operation is not allowed when the object is closed.