SQL help Pleassseeeee!

This is a bugger :mad:

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

Code:

---

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

Code:

---

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

---

Any Suggestions :D

Thanks

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)

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 :confused:

Thanks in advance Vince.

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

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 :)

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.

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?

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

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

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...

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

Wok that worked thanks

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

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):

VB Code:

IF intNumber>5 AND intNumber<9 then
 
End If

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

VB Code:

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 :)

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. :rolleyes:

Gotcha

Neat

Vince