Group by 24 hr time period other than 00:00:00

[jcglenn]

I would like to group my sales qry by a 24 hr time period other than the standard group by day. Our sales periods go from 7:30pm to 7:30pm. I have searched several datetime grouping post but can't find exactly what I am looking for. TIA for your help.

QRY so far...

DECLARE @SDate as datetime
DECLARE @EDate as datetime

Set @SDate = '2006-08-31 19:30:00'
Set @EDate = '2006-09-20 19:29:59'


select p.invoiceid, p.processeddate, p.paymentamount, sum(p.paymentamount)

from payments p
Inner JOIN orders o on p.orderid = o.orderid

where p.ProcessedDate >= @SDate
and p.ProcessedDate < @EDate
and o.contractitemid = 2585
and p.paymentamount > 0
and p.paymenttype = 'initial'
and p.status = 'Y'

group by p.invoiceid, p.processeddate, p.paymentamount

order by p.processeddate

 

[gmmastros]

Try this query...

DECLARE @SDate as datetime
DECLARE @EDate as datetime

Set @SDate = '2006-08-31 19:30:00' 
Set @EDate = '2006-09-20 19:29:59'

Select InvoiceId, ReportDate, PaymentAmount, Sum(PaymentAmount)
From   (
		select p.invoiceid, p.processeddate, p.paymentamount, 
		       Case When ProcessDate - DateAdd(Day, DateDiff(Day, 0, processeddate), 0) > '19:30:00' 
					Then ProcessDate - DateAdd(Day, DateDiff(Day, 0, processeddate), 1)
					Else ProcessDate - DateAdd(Day, DateDiff(Day, 0, processeddate), 0)
					End As ReportDate
		from   payments p
		       Inner JOIN orders o on p.orderid = o.orderid
		where  p.ProcessedDate >= @SDate
		       and p.ProcessedDate < @EDate
		       and o.contractitemid = 2585
		       and p.paymentamount > 0
		       and p.paymenttype = 'initial'
		       and p.status = 'Y'
		) As A
group by    invoiceid, ReportDate, p.paymentamount
order by    ReportDate

If it doesn't work for you, then I suggest you provide some sample data and expected results. It will help us to help you.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

I would suggest a date table that has the date, the start time and the stop time. Fill with all the possible dates.
then join to this table to get the date and do the grouping.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[jcglenn]

OK, here is a short data set...
Invoice id ProcessedDate Amount Summary
500003105 2006-09-01 18:05:00 29.95 29.95
600007979 2006-09-01 18:17:00 29.95 29.95
500003107 2006-09-01 18:22:00 29.95 29.95
500003109 2006-09-01 18:37:00 29.95 29.95

500003110 2006-09-01 19:40:00 29.95 29.95
500003112 2006-09-01 19:47:00 29.95 29.95
500003114 2006-09-01 21:38:00 29.95 29.95
500003115 2006-09-01 21:59:00 29.95 29.95
500003117 2006-09-01 23:56:00 29.95 29.95

I broke the set where my grouping should apear. basically everyday at 7:30pm.

The qry you supplied receives...
The column prefix 'p' does not match with a table name or alias name used in the query.
I probably screwed it up.

Thanks so much for taking the time to assist me.

Craig

 

[gmmastros]

Change:
group by invoiceid, ReportDate, p.paymentamount

To
group by invoiceid, ReportDate, paymentamount

(Notice the [!]p.[/!] was removed).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Try this...

DECLARE @SDate as datetime
DECLARE @EDate as datetime

Set @SDate = '2006-08-31 19:30:00' 
Set @EDate = '2006-09-20 19:29:59'

Select InvoiceId, ReportDate, PaymentAmount, Sum(PaymentAmount)
From   (
        select p.invoiceid, p.processeddate, p.paymentamount, 
               Case When ProcessDate - DateAdd(Day, DateDiff(Day, 0, processeddate), 0) > '19:30:00' 
                    Then DateAdd(Day, DateDiff(Day, 0, processeddate), 1)
                    Else DateAdd(Day, DateDiff(Day, 0, processeddate), 0)
                    End As ReportDate
        from   payments p
               Inner JOIN orders o on p.orderid = o.orderid
        where  p.ProcessedDate >= @SDate
               and p.ProcessedDate < @EDate
               and o.contractitemid = 2585
               and p.paymentamount > 0
               and p.paymenttype = 'initial'
               and p.status = 'Y'
        ) As A
group by    invoiceid, ReportDate, paymentamount
order by    ReportDate

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jcglenn]

George!

It works!

It will take me a couple of days to figure out why, but thanks!

Craig

 

[gmmastros]

This is where the magic happens:

Case When ProcessDate - DateAdd(Day, DateDiff(Day, 0, processeddate), 0) > '19:30:00' 
     Then DateAdd(Day, DateDiff(Day, 0, processeddate), 1)
     Else DateAdd(Day, DateDiff(Day, 0, processeddate), 0)
     End As ReportDate

Basically, we are creating a new column in the data.

Examining this line...

ProcessDate - DateAdd(Day, DateDiff(Day, 0, processeddate), 0) > '19:30:00'

ProcessDate is a DateTime field.

This... DateAdd(Day, DateDiff(Day, 0, processeddate), 0) will remove the time component from the processeddate field. By subtracting the 2 numbers, we are left with a 'time only' value, which we compare to '7:30 pm'. If the time component is less than 7:30, then we return the date (without time). If the time is greater than '7:30 pm', then we return the Date + 1 (with the time component removed). Once we do this, it's simple to group the data based on this value.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom