SQL Simple Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Simple Question

Author

Topic

spacemonkey
Starting Member

8 Posts

Posted - 2010-01-04 : 13:52:29

Hi All,

I'm a beginner in SQL programming and was hoping to get some direction
I'm working with one table. I want to see which customers have performed an inquiry before performing a transaction.
So first an inquiry and then transaction.

Select
account
inquiry
order from customer
where cm_cde IN ('01','02)
And date>=Dec/29

I have tried this and a in a subquery and it doesn’t display the correct inquiry.
T
Thanks,
SpaceMonkey

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-04 : 13:56:16

You'll need to show us sample data in order for us to assist with this. We don't know what you mean by "first an inquiry and then transaction". It sounds like you've got some columns that would indicate this, but without sample data it's hard to tell.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

spacemonkey
Starting Member

8 Posts

Posted - 2010-01-04 : 14:28:24

Sorry, I apologize my query above is not correct.
The correct query would be
Select
account_number
Sm_code
from customer
where sm_code IN ('01','02)
And date>=Dec/29 And date <=Dec/30

I like to be able to see account numbers that meet both criteria.
First it meets 01 and then 02 in the given time frame. It MUST be both criteria in order for it to be displayed.

I'm trying to see what types of orders are being processed and in which sequence. I hope this clarifies things a bit.

The result could be

AccountNumber sm_cde
123333333 01/02

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-04 : 14:36:42

Maybe this??

select account_number
from customer 
where sm_code in ('01','02')
and date between '20091229' and '20091230'
group by account_number
having count(*) > 1

If it doesn't work, we really need to see some sample data from your actual table.

EDIT : Won't work if the same account_number can have more than one occurrence of same sm_code (eg..'01' occurring more than once for the same account_number)

spacemonkey
Starting Member

8 Posts

Posted - 2010-01-04 : 15:07:15

Thanks for you response.

Your query is doing exactly what I'm trying to rectify. I'm encountering the exact some issue.

It shows account number that meet only one of those criteria as opposes to both criteria between dec/29 to dec/30.

SM-CDE is a response from the system. I want it to generate account numbers that have FIRST generated response code=01 and then SECOND generated response code=02 in 1 day. I think this should be relatively simple, but I jut cant see to get this.

Hope this helps.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-04 : 15:08:32

Please post sample data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

spacemonkey
Starting Member

8 Posts

Posted - 2010-01-04 : 21:31:53

lets say this was the status of my records during the morning

Account /SM_CDE / Date
2323232 /01 /jan-01-2009 11.00AM******
4543343 /02 /jan-01-2009 10.00AM******
9834933 /01 /jan-01-2009 10.00
3749342 /01 /jan-01-2009 10.00
3113003 /01 /jan-01-2009 10.00
2209848 /02 /jan-01-2009 10.00
-------------------------------------------

This is the staus in the Afternoon

Account /SM_CDE /Date
2323232 /02 /jan-01-2009 12.00PM*****
4543343 /01 /jan-01-2009 12.00PM*****
9834933 /01 /jan-01-2009 10.00
3749342 /01 /jan-01-2009 10.00
3113003 /01 /jan-01-2009 10.00
2209848 /02 /jan-01-2009 10.00
---------------------------------------------

MY QUERY WOULD LOOK AT THIS AND RETRIVE THE FOLLOWING RECORDS

2323232
4543343

Both of these account have code 01 & 02 in specified time frame.
The rest of the account do not qualify as they dont have 01 followed by 02 during that time

I hope this helps.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-04 : 21:46:16

for the 2 Account 2323232 & 4543343, does the column SM_CDE & Date gets updated or you actually have the both the morning status and afternoon status in the table ?

KH
[spoiler]Time is always against us[/spoiler]

spacemonkey
Starting Member

8 Posts

Posted - 2010-01-04 : 22:00:50

I will query the table to give me records from 10:AM to 2:PM.
I want it to give me all the account number that have 01 and 02 sm_cde during that time.

Hope that makes sense
Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-04 : 22:07:24

can you show us what are the records in the table after 2 PM for Account 2323232 & 4543343 ?

KH
[spoiler]Time is always against us[/spoiler]

spacemonkey
Starting Member

8 Posts

Posted - 2010-01-04 : 22:24:11

Sorry, I meant to say at 12:PM
This is the staus in the Afternoon

Account /SM_CDE /Date
2323232 /02 /jan-01-2009 12.00PM*****
4543343 /01 /jan-01-2009 12.00PM*****
9834933 /01 /jan-01-2009 10.00
3749342 /01 /jan-01-2009 10.00
3113003 /01 /jan-01-2009 10.00
2209848 /02 /jan-01-2009 10.00

The SM_CDE record would be different in the afternoon as posted above and the query would show the two account numbers as pointed out. 2323232 & 4543343

In reality the SM_CDE may change several time and I will use the Between function to check the sm_cde status during those time.

Cheers!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-04 : 22:30:31

Do you have the morning status stored somewhere ?

If not, so how to determine there is a change in the SM_CDE and Date for Account 2323232 & 4543343 ?

KH
[spoiler]Time is always against us[/spoiler]

spacemonkey
Starting Member

8 Posts

Posted - 2010-01-04 : 22:36:50

Yes the records for sm_cde would change depending on customer inquiry.
Example-If you perform online balance inquiry on your banking self-serve protal and later on that day you perform tele banking to pay some bills.
The next day you call in the customer service rep and want to know of your recent activity. The rep can give you a detail break-down of your activity... I hope this clarifies things.

I need to know customer that have performed 01 and 02 in a given time frame. There is ONLY one table and ONLY one record (sm-cde)to query

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-04 : 22:48:49

Let me try to rephrase my question . .

for Account 2323232 :

in the morning, the status is
2323232 /01 /jan-01-2009 11.00AM

in the afternoon, the status is
2323232 /02 /jan-01-2009 12.00PM

I understand that the status changed from 01 to 02 and Date change from 11 AM to 12 PM.

Question
1. Do you update the record for 2323232 by changing the status from 01 to 02 ?
2. Or do you keep the original status for that record before change ? like an Audit Trail ?
3. Or you just insert the new status for 2323232 with SM_CDE = 02 ?

I asked "can you show us what are the records in the table after 2 PM for Account 2323232 & 4543343 ?"
and your reply

quote:

This is the staus in the Afternoon


Account /SM_CDE /Date
2323232 /02 /jan-01-2009 12.00PM
4543343 /01 /jan-01-2009 12.00PM
9834933 /01 /jan-01-2009 10.00
3749342 /01 /jan-01-2009 10.00
3113003 /01 /jan-01-2009 10.00
2209848 /02 /jan-01-2009 10.00

So based on the above status in the afternoon, how can i know what there is a changed for account 2323232 and 4543343 ?
There is no way any query will be able to know that there is a changed for the 2 accounts unless the changed is recorded.

You will need to change your design to cater for this.

One way is not to update the records but create new records

Account /SM_CDE / Date
2323232 /01 /jan-01-2009 11.00AM
4543343 /02 /jan-01-2009 10.00AM
9834933 /01 /jan-01-2009 10.00
3749342 /01 /jan-01-2009 10.00
3113003 /01 /jan-01-2009 10.00
2209848 /02 /jan-01-2009 10.00
2323232 /02 /jan-01-2009 12.00PM
4543343 /01 /jan-01-2009 12.00PM

The records in blue are those added.
And to find the Accounts with change of event


select account
from   yourtable
where  date >= '2009-01-01 10:00'
and    date <= '2009-01-01 12:00'
group by account
having count(*) > 1

and to list the details of change


select  t.*
from    yourtable t
        inner join
        (
          select account
          from   yourtable
          where  date >= '2009-01-01 10:00'
          and    date <= '2009-01-01 12:00'
          group by account
          having count(*) > 1
        ) c on t.account = c.account
where   t.date >= '2009-01-01 10:00'
and     t.date <= '2009-01-01 12:00'

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources