SQL Update statement help

[Rage187]

I do support for a POS system and the users have found some way to screw things up.

I have two table that are giving me issues

1. Shifts - with columns SfActionDate, SfTillClose, Sfcode

2. Receiptbase - with columns rcbShiftDate, rcbActionDate, rcbShiftnum


When someone logs in for the day, it puts a 0 in the SftillClose, when they logout for the day it puts a 1.

Somehow the users have been able to have the 0 on one date and the closing on another. If this happens the program will not allow them to "close the drawer" for the day.

I had been identifying which Sfcode has the issue by doing the following:

Select * from shifts where sftillclose=0 and sfactiondate= '2007-??-??'
-ed where the ?? equals the date I can not close the drawer on

I then find the offending SFcode which joins Receiptbase on rcbShiftnum

select * from receiptbase where rcbshiftnum=???

I had manually been fixing these everyday it happened using the following script updated with that date and the offending shiftnum(s)

update shifts set sfactiondate= '2007-03-26' where sfcode in (7172,7241)
update recieptbase set rcbshiftdate = '2007-3-26' where rcbshiftnum in
(7172,7241)

when the update is ran, all the closing and opening happen on the same date and all the sales have the same date. I can then finally close the drawer.

But I have been doing this for 2 months while waiting on the devs to fix it. And it's mind numbing,

What I need is a update statement that will automate the process without having to give it dates or shift numbers.

 

[lozina]

Sounds to me like the application is always doing an insert into Shifts, so it's inserting one row when the person logs in with a 0, then they logout it creates an entirely new row with the 1 and the same date.

So when you run that statement to select all shifts where there is a 0, you will of course get results since that row with the 0 is still there in addition to the one with a 1.

Is that right?

If so maybe a quick workaround could be:

Select * from shifts where shifts.sftillclose=0 and shifts.sfactiondate= '2007-??-??' and not exists (select 1 from shifts where sftillclose=1 and sfactiondate= '2007-??-??' and sfcode = shifts.sfcode)

assuming sfcode is the primary key for that table..

but the real fix would be that instead of always inserting, when they logout it needs to update the previous row by setting it to 1 so then there is no duplicate rows for the same day but different data.

Apologies if I misunderstood something!

 

[Rage187]

I just need to be able to update both tables and make sure that the sfactiondate, rcbshiftdate, rcbactiondate all are using the same date as the row in Shifts where SfTillclose=0 and not exists in shifts where sftillclose=1

I just don't want to have to update the statement with a date or shiftnum. I could even set it up as a job to run at the end of the day to resolve the issue until the devs get a chance to look at it.

 

[KLin]

I don't understand why you are using sfactiondate in the where clause to find the record where sftillclose is = 0. Is the date wrong also in the shifts table?


EDIT: Can you give a small data example? a couple records from shifts and a few related records from receiptbase?

 

[Rage187]

Originally posted by: KLin
I don't understand why you are using sfactiondate in the where clause to find the record where sftillclose is = 0. Is the date wrong also in the shifts table?


EDIT: Can you give a small data example? a couple records from shifts and a few related records from receiptbase?

YHPM

 

[KLin]

Originally posted by: Rage187

Originally posted by: KLin
I don't understand why you are using sfactiondate in the where clause to find the record where sftillclose is = 0. Is the date wrong also in the shifts table?


EDIT: Can you give a small data example? a couple records from shifts and a few related records from receiptbase?

YHPM

Right back at ya.

 

[ncage]

Originally posted by: Rage187
I do support for a POS system and the users have found some way to screw things up.

I have two table that are giving me issues

1. Shifts - with columns SfActionDate, SfTillClose, Sfcode

2. Receiptbase - with columns rcbShiftDate, rcbActionDate, rcbShiftnum


When someone logs in for the day, it puts a 0 in the SftillClose, when they logout for the day it puts a 1.

Somehow the users have been able to have the 0 on one date and the closing on another. If this happens the program will not allow them to "close the drawer" for the day.

I had been identifying which Sfcode has the issue by doing the following:

Select * from shifts where sftillclose=0 and sfactiondate= '2007-??-??'
-ed where the ?? equals the date I can not close the drawer on

I then find the offending SFcode which joins Receiptbase on rcbShiftnum

select * from receiptbase where rcbshiftnum=???

I had manually been fixing these everyday it happened using the following script updated with that date and the offending shiftnum(s)

update shifts set sfactiondate= '2007-03-26' where sfcode in (7172,7241)
update recieptbase set rcbshiftdate = '2007-3-26' where rcbshiftnum in
(7172,7241)

when the update is ran, all the closing and opening happen on the same date and all the sales have the same date. I can then finally close the drawer.

But I have been doing this for 2 months while waiting on the devs to fix it. And it's mind numbing,

What I need is a update statement that will automate the process without having to give it dates or shift numbers.

Rage i write SQL every day so i'm pretty good at it. With you description i'm kind cloudy on your business rules and if thats the only columns in those two tables above then the developer/dba needs to be shot especially if those two tables are supposed to be related. Where is the Primary Key? There are just to many inderminates in the information you give:
1) I assuming that multiple people log in for the day so how do you tell one person from another? SFCode (whatever that stands for)?
2) I assume when someone logs in/out then two records will be created for them. One with a SFTillClose = 0 and one with a SFTTillClose = 1.


I you can provide me details of exactly what happens with these tables and when they get either get updated/inserted then im sure i can help you.

Ncage