something unusual - 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

SQL Server 2000 Forums

Transact-SQL (2000)

something unusual

Author

Topic

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-03-28 : 19:18:21

I have an insert statement that I run as a stored procedure. Whats weird is that after looking at the table i have inserted too I get one result and when I when I run just the select statement after commenting out the insert statement, one record produces different results. What is going on here? Has anyone seen this before. Here is the statement:

insert into kpi.dbo.employee_data(DateofTrack, Salonuid, Employeename, TotalSessions, TotalProductSales,
LPPA, STP, STPNL, SBP, SBPNL, PenetrationRate, NewClients, NewClientPenetrationRate, ncstprate)
Select @startdate, l.salonname, l.employeename, Sessions, Totalproductsales, ((TotalProductSales*1.0) / (Sessions*1.0)),
STP, STPNL, SBP, SBPNL, (((UnitsSold*1.0)/(sessions*1.0))*100), TotalNewClients,
newclientpenetrationRate,(ncstp / totalnewclients)*100
From kpi.dbo.temptable_sessions l
left outer join @LPPADATA s
on l.employeename = s.employeename and l.salonname = s.salonname
left outer join @finalNewProductTable f
on l.employeename = f.employeename and l.salonname = f.salonname
left outer join kpi.dbo.temptable_eftcount e
on l.employeename = e.employeename and l.salonname = e.salonname
left outer join @ncstps n
on l.employeename = n.employeename and l.salonname = n.salonname

The Yak Village Idiot

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-03-28 : 19:26:53

First Result Set
Loc SRP UTS VAL
GGE HK 18 134
CRR HK 18 12

Second Result Set
Loc SRP UTS VAL
GGE HK 18 12
CRR HK 18 12

The first one is correct and without the insert statement. The second is incorrect and is created with the insert statement. I hope this all helps.

The Yak Village Idiot

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-28 : 19:31:10

Your result sets don't match the columns in your query. So how is the VAL column being calculated?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-03-28 : 19:34:51

oops sorry,

loc is salonname, srp is employeename, uts = sessions, val = product sales

The Yak Village Idiot

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-28 : 19:42:25

Could you post entire result sets with correct column names?

Also where is sessions and productsales coming from? You don't have an alias or the two part naming convention for the columns, so I'm not sure how we are supposed to know which table it is coming from.

If you want us to help you, you need to provide accurate information so that we don't have to go back and forth with this.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-03-28 : 20:11:52

I have no problems giving you any information you would like. Can I send it to you directly rather than posting it here?

The Yak Village Idiot

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-28 : 20:23:29

No.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-03-28 : 20:45:10

Ok, here is pretty much the first half of the result set, the rest didnt fit without it being confusing.

Correct data set without the insert statement
DateofTrack Salonuid EmployeeName TotalSessions TotalProductSales LPPA STP STPNL SBP SBPNL PenetrationRate
3/14/2007 CR HK 18 12 0.67 0 0 0 0 11.11111111
3/14/2007 GG HK 18 134 7.44 0 0 0 0 38.88888889

Incorrect data set derived from the table used by the insert statement
DateofTrack Salonuid EmployeeName TotalSessions TotalProductSales LPPA STP STPNL SBP SBPNL PenetrationRate
3/14/2007 CR HK 18 12 0.67 0 0 0 0 11.11111111
3/14/2007 GG HK 18 12 0.67 0 0 0 0 11.11111111

Here is the column names with prefixes for the above represented columns.

insert into kpi.dbo.employee_data(DateofTrack, Salonuid, Employeename, TotalSessions, TotalProductSales,
LPPA, STP, STPNL, SBP, SBPNL, PenetrationRate, NewClients, NewClientPenetrationRate, ncstprate)
Select @startdate, l.salonname, l.employeename, l.Sessions, s.Totalproductsales, ((s.TotalProductSales*1.0) / (l.Sessions*1.0)), STP, STPNL, SBP, SBPNL, (((UnitsSold*1.0)/(sessions*1.0))*100), TotalNewClients,
newclientpenetrationRate,(ncstp / totalnewclients)*100
From kpi.dbo.temptable_sessions l
left outer join @LPPADATA s
on l.employeename = s.employeename and l.salonname = s.salonname
left outer join @finalNewProductTable f
on l.employeename = f.employeename and l.salonname = f.salonname
left outer join kpi.dbo.temptable_eftcount e
on l.employeename = e.employeename and l.salonname = e.salonname
left outer join @ncstps n
on l.employeename = n.employeename and l.salonname = n.salonname

Thanks for your help.

The Yak Village Idiot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources