SQL help Pleassseeeee!

[Gary.Lowe]

This is a bugger

I have an application which enables a user to search through till transaction via shifts

i.e.
Lunch = 12:00:00 - 18:29:59
Dinner = 18:30:00 - 08:30:00
All Shifts = 12:00:00 - 08:30:00

The report can be run on these shifts or All Shifts 12:00:00 - 08:30:00

The following is the SQL for the Lunch and Dinner Shift and the results

SELECT SUM(TrnPayAmount) AS TrnPayAmount, COUNT(PayDescription)AS CountPayDescription, PayDescription 
FROM  tblTransactionPayments 
INNER JOIN  tblPaymentMethods ON tblTransactionPayments.TrnPayPayMethod = tblPaymentMethods.PayNo 
INNER JOIN  tblTillTransactionHeader ON tblTillTransactionHeader.TTrnHdrTranNo = tblTransactionPayments.TrnPayBillNo 
WHERE (TTrnHdrTillNo=7 AND TrnPayVoid = 0 AND TTrnHdrSessionDate between '12-12-2001' AND '12-12-2001' AND CONVERT(Char(12),TTrnHdrTimeStamp,108)>= '12:00:00') OR 
(TTrnHdrTillNo=7 AND TrnPayVoid = 0 AND TTrnHdrSessionDate between '12-12-2001' AND '12-12-2001' AND CONVERT(Char(12),TTrnHdrTimeStamp,108)<= '18:29:59') 
GROUP BY PayDescription


SELECT SUM(TrnPayAmount) AS TrnPayAmount, COUNT(PayDescription)AS CountPayDescription, PayDescription 
FROM  tblTransactionPayments 
INNER JOIN  tblPaymentMethods ON tblTransactionPayments.TrnPayPayMethod = tblPaymentMethods.PayNo 
INNER JOIN  tblTillTransactionHeader ON tblTillTransactionHeader.TTrnHdrTranNo = tblTransactionPayments.TrnPayBillNo 
WHERE (TTrnHdrTillNo=7 AND TrnPayVoid = 0 AND TTrnHdrSessionDate between '12-12-2001' AND '12-12-2001' AND CONVERT(Char(12),TTrnHdrTimeStamp,108)>= '18:30:00') OR 
(TTrnHdrTillNo=7 AND TrnPayVoid = 0 AND TTrnHdrSessionDate between '12-12-2001' AND '12-12-2001' AND CONVERT(Char(12),TTrnHdrTimeStamp,108)<= '08:30:00') 
GROUP BY PayDescription


RESULTS...

TrnPayAmount          CountPayDescription PayDescription       
--------------------- ------------------- -------------------- 
3616.8500             19                  Complimentary 100%
117.2500              4                   Management
59.5000               1                   Visa

(3 row(s) affected)

TrnPayAmount          CountPayDescription PayDescription       
--------------------- ------------------- -------------------- 
3544.1000             18                  Complimentary 100%
75.7500               3                   Management
59.5000               1                   Visa

Now the first set of results (Lunch Shift) brings back the same results for the All Shift

The results for the Lunch should be as follows

TrnPayAmount          CountPayDescription PayDescription       
--------------------- ------------------- -------------------- 
72.7400               1                   Complimentary 100%
42.5000               1                   Management

Any Suggestions

Thanks

[Ecniv]

Gimme some time I'll have a go ...

But... Would it be possible to store the shifts in a look up table then assign ppl to that ?

Regards

Vince
(atm busy trying to code summat but free later)

[Gary.Lowe]

Ecniv

The shifts are in a look up table, I assign them to variables in the code and that's where the time in the seach criteria come from.

I can't understand why it brings back the right info for the dinner shift but brings back the luch and dinner info for the lunch shift

[Gary.Lowe]

Thanks in advance Vince.

You don't work at British Gas in Solihull do you per chance?

[Wokawidget]

You dinner shift is 18:30:00 - 08:30:00, which suggests it includes midnight, which would mean searching on 2 different dates.
You have between '12-12-2001' AND '12-12-2001'
Shouldn't it be between '12-12-2001' AND '12-13-2001'?

That would mean your search string should be longer to include the 2 dates

[Gary.Lowe]

Hi Wok


Yes the TTrnHdrTimeStamp would be for example 13/12/2001 03:30:00 that's why I convert it to just the time as the TTrnHdrSessionDate, this will always be the same day so I use this to search on the date.

[Wokawidget]

Lunch = 12:00:00 - 18:29:59
Dinner = 18:30:00 - 08:30:00
All Shifts = 12:00:00 - 08:30:00

By the above I am assuming that:

Lunch: Midday - 6:29:59pm
Dinner: 6:30pm - 8:30am (which comprises of 2 dates!)

All Shifts: Midday - 8:30am (which comprises of 2 dates!)


Is that correct?

[Gary.Lowe]

yes

But the sessiondate will always be the same

i.e.

A trasnaction is created at 2:30 in the morning it would be stored as follows

Sessiondate = 12/12/2001 00:00:00
Timestamp = 13/12/2001 02:30:00

[Ecniv]

Used to work for Transco in Solihull

Might have the answer - the filtering on your query :
WHERE
(TTrnHdrTillNo=7 AND TrnPayVoid = 0 AND TTrnHdrSessionDate between '12-12-2001' AND '12-12-2001' AND CONVERT(Char(12),TTrnHdrTimeStamp,108)>= '12:00:00') OR

(TTrnHdrTillNo=7 AND TrnPayVoid = 0 AND TTrnHdrSessionDate between '12-12-2001' AND '12-12-2001' AND CONVERT(Char(12),TTrnHdrTimeStamp,108)<= '18:29:59')

this would return everything after 12:00:00 and everything before 18:29:59 - ergo the all shifts

You need to put a between on the time stamps for both bits perhaps something like :
(TTrnHdrTillNo=7 AND TrnPayVoid = 0 AND TTrnHdrSessionDate between '12-12-2001' AND '12-12-2001' AND CONVERT(Char(12),TTrnHdrTimeStamp,108) between '12:00:00' and '18:29:59') OR
(TTrnHdrTillNo=7 AND TrnPayVoid = 0 AND TTrnHdrSessionDate between '12-12-2001' AND '12-12-2001' AND CONVERT(Char(12),TTrnHdrTimeStamp,108) between '12:00:00' and '18:29:59')

Regards

Vince

[Wokawidget]

Try these. They should work:

LUNCH:

WHERE TTrnHdrTillNo=7 AND TrnPayVoid = 0 AND TTrnHdrSessionDate between '12-12-2001' AND '12-12-2001'
AND ( CONVERT(Char(12),TTrnHdrTimeStamp,108)>= '12:00:00'AND CONVERT(Char(12),TTrnHdrTimeStamp,108)<= '18:29:59')
GROUP BY PayDescription

DINNER:

WHERE TTrnHdrTillNo=7 AND TrnPayVoid = 0 AND TTrnHdrSessionDate between '12-12-2001' AND '12-12-2001'
AND ( CONVERT(Char(12),TTrnHdrTimeStamp,108)>= '18:30:00' OR CONVERT(Char(12),TTrnHdrTimeStamp,108)<= '08:30:00')
GROUP BY PayDescription

BOTH SHIFTS:

WHERE TTrnHdrTillNo=7 AND TrnPayVoid = 0 AND TTrnHdrSessionDate between '12-12-2001' AND '12-12-2001'
GROUP BY PayDescription

Hope it works...Tell me if it does...

[Ecniv]

Wokawidget - almost : your 'OR' is in the wrong place though

DINNER:

WHERE TTrnHdrTillNo=7 AND TrnPayVoid = 0 AND TTrnHdrSessionDate between '12-12-2001' AND '12-12-2001'
AND ( CONVERT(Char(12),TTrnHdrTimeStamp,108)>= '18:30:00' OR CONVERT(Char(12),TTrnHdrTimeStamp,108)<= '08:30:00')
GROUP BY PayDescription

But its near enough what I said 8 mins before ya...
Just another way...

Regards

Vince

[Gary.Lowe]

Wok that worked thanks

Why is it though the lunch had to be 'AND' and the Dinner had to be OR?

[Wokawidget]

The OR isn't in the wrong place...not that I can see...

The 1st Dinner has an AND because it's boundries and enclosed between 2 times. The lunch has an OR coz the times are either side of 2 times. Ie

An if statement for anything between 5 and 9 (Dinner):

IF intNumber>5 AND intNumber<9 then
 
End If

An if statement for anything before 3 or greater than 7 (Lunch)

If intNumber<3 OR intNumber>7 Then
 
End If

1 uses AND, and the other uses OR...Understand?
Think I confused myself, but it works so I am off to the mental home

[Wokawidget]

Even though Lunch spans across Midnight, you are still using the same date to record the entry. So anything after 6:30pm is in the Lunch period, and anything before 8:30am is also in the Lunch period. Coz these times have the same date, the select statement is anything after 6:30pm OR before 8:30am...See what I'm getting at. I am crap at explaining things.

[Ecniv]

Gotcha

Neat

Vince