Can't get this right - 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)

Can't get this right

Author

Topic

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2006-11-01 : 08:03:34

I have been given the following query. It executes in 2 parts so I have drawn a divider to show this:

truncate table [ReportsStore2].[dbo].[LBMM_SickAwolCounter];
if (select count(*) from sysobjects where [name]='LBMM_SickAwolReport')>0
BEGIN
drop table LBMM_SickAwolReport;
END

BEGIN
select * into LBMM_SickAwolReport from (
SELECT *,
(case when sickhours>0 then 1 else 0 end) as sick,
(case when sickhours>0 and (dayofweek='Monday' and convert(varchar, dateadd(d,-3, date_List), 112)+convert(varchar(20), diallerid) in
(select convert(varchar(20), dateid)+convert(varchar(20), diallerid) from vw_LBMM_TimesheetReport where
dayofweek='Friday' and sickhours>0 or awolhours>0)
or
dayofweek='Friday' and convert(varchar, dateadd(d, 3, date_List), 112)+convert(varchar(20), diallerid) in
(select convert(varchar(20), dateid)+convert(varchar(20), diallerid) from vw_LBMM_TimesheetReport where
dayofweek='Monday' and sickhours>0 or awolhours>0) )

then 1 else 0 end) sickness_spanningWkend,
(case when awolhours>0 then 1 else 0 end) as awol,
(case when awolhours>0
and (dayofweek='Monday' and convert(varchar, dateadd(d,-3, date_List), 112)+convert(varchar(20), diallerid) in
(select convert(varchar(20), dateid)+convert(varchar(20), diallerid) from vw_LBMM_TimesheetReport where
dayofweek='Friday' and sickhours>0 or awolhours>0)
or
dayofweek='Friday' and convert(varchar, dateadd(d, 3, date_List), 112)+convert(varchar(20), diallerid) in
(select convert(varchar(20), dateid)+convert(varchar(20), diallerid) from vw_LBMM_TimesheetReport where
dayofweek='Monday' and sickhours>0 or awolhours>0) )
then 1 else 0 end) awol_spanningWkend
FROM vw_LBMM_TimesheetReport)
c where sick>0 or awol>0

declare @sql varchar(4000)
DECLARE @dateid int
DECLARE @diallerid int
DECLARE @date_list datetime
DECLARE @sick tinyint
DECLARE @sick_span tinyint
DECLARE @awol tinyint
DECLARE @awol_span tinyint
DECLARE @dateid_prev int
DECLARE @diallerid_prev int
DECLARE @date_list_prev datetime
DECLARE @sick_prev tinyint
DECLARE @sick_span_prev tinyint
DECLARE @awol_prev tinyint
DECLARE @awol_span_prev tinyint
DECLARE @dayofweek varchar(20)

set @dateid_prev =0
set @diallerid_prev =0
set @date_list_prev =convert(datetime, '01/01/1900', 103)
set @sick_prev =0
set @sick_span_prev =0
set @awol_prev =0
set @awol_span_prev =0

DECLARE Campaigns CURSOR FOR

select dateid, diallerid, date_list, sick, sickness_spanningwkend,
awol, awol_spanningwkend, dayofweek from LBMM_SickAwolReport
--ADDED
where LEFT(dateid, 4) > '2005'
order by diallerid, dateid

OPEN Campaigns
FETCH NEXT FROM Campaigns INTO @dateid, @diallerid, @date_list, @sick, @sick_span, @awol, @awol_span, @dayofweek
WHILE (@@FETCH_STATUS = 0)
BEGIN

if @diallerid_prev<>@diallerid
BEGIN

set @sql='
INSERT INTO [ReportsStore2].[dbo].[LBMM_SickAwolCounter]([AgentID], [DateofAbsence])
VALUES('+convert(varchar(20), @diallerid)+', '+convert(varchar(15), @dateid)+')
'
END
ELSE
BEGIN
IF dateadd(d, 1, @date_list_prev)=@date_list
BEGIN
set @sql=''
END
ELSE

BEGIN

IF @dayofweek='Monday' and (@sick_span=1 or @awol_span=1) and dateadd(d, 3, @date_list_prev)=@date_list
BEGIN
set @sql=''
END
ELSE

BEGIN
set @sql='
INSERT INTO [ReportsStore2].[dbo].[LBMM_SickAwolCounter]([AgentID], [DateofAbsence])
VALUES('+convert(varchar(20), @diallerid)+', '+convert(varchar(15), @dateid)+')

'
END
END
END

set @dateid_prev =@dateid
set @diallerid_prev =@diallerid
set @date_list_prev =@date_list
set @sick_prev =@sick
set @sick_span_prev =@sick_span
set @awol_prev =@awol
set @awol_span_prev =@awol_span

exec(@sql)

FETCH NEXT FROM Campaigns
INTO @dateid, @diallerid, @date_list, @sick, @sick_span, @awol, @awol_span, @dayofweek
END
CLOSE Campaigns
DEALLOCATE Campaigns

END
---------------------------------------------------
SELECT magent.AGENTNAME, COUNT(distinct dateofabsence) AS AbsenceCounter, LBMM_SickAwolCounter.AgentID, [name] as SalesManager
FROM LBMM_SickAwolCounter INNER JOIN
magent ON LBMM_SickAwolCounter.AgentID = magent.AgentID
inner join vw_LBMM_Agents_CurrSms v on magent.agentid=v.diallerid

inner join
(SELECT LBMM_SickAwolReport.DiallerID
FROM LBMM_SickAwolReport

where dateid=convert(int, convert(varchar, getdate()-1, 112))) b

ON LBMM_SickAwolCounter.AgentID = b.DiallerID
GROUP BY magent.AGENTNAME, LBMM_SickAwolCounter.AgentID, [name]
ORDER BY COUNT(*) DESC

This returns a result set that looks something like this:

AGENTNAME AbsenceCounter AgentID SalesManager
Hunt Stephan 17 1345566 Brian Smith
Smith Peter 12 3245567 David Hall

The first view this query references is vw_LBMM_TimesheetReport. This constitutes:
CREATE VIEW dbo.vw_LBMM_TimesheetReport
AS
SELECT TOP 100 PERCENT d.DayName, sum(dbo.LBMM_TimesheetFact.hours) as hours, sum(dbo.LBMM_TimesheetFact.SickHours) as sickhours, sum(dbo.LBMM_TimesheetFact.AWOLHours) as awolhours,
sum(dbo.LBMM_TimesheetFact.RDOHours) as rdohours, sum(dbo.LBMM_TimesheetFact.HolidayHours) as holidayhours, d.DateID, dbo.LBMM_Agents.keyid, dbo.LBMM_Agents.diallerid, d.DayOfWeek, d.Date_List
FROM dbo.LBMM_TimesheetFact INNER JOIN
dbo.LBMM_Agents ON dbo.LBMM_TimesheetFact.AgentID = dbo.LBMM_Agents.keyid AND
dbo.LBMM_TimesheetFact.[date] = dbo.LBMM_Agents.dateid INNER JOIN
dbo.vw_LBMM_CampaignAdmin ON dbo.LBMM_Agents.dateid = dbo.vw_LBMM_CampaignAdmin.LBMM_CampaignDate AND
dbo.LBMM_TimesheetFact.CampaignID = dbo.vw_LBMM_CampaignAdmin.LBMM_CampaignID INNER JOIN
dbo.Date_DayList d ON dbo.LBMM_TimesheetFact.[date] = d.DateID
group by d.DayName, d.DateID, dbo.LBMM_Agents.keyid, dbo.LBMM_Agents.diallerid, d.DayOfWeek, d.Date_List
ORDER BY dbo.LBMM_Agents.diallerid, d.DayOfWeek

Next is a table called LBMM_SickAwolReport. The structure for this is:
CREATE TABLE [dbo].[LBMM_SickAwolReport] (
[DayName] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[hours] [numeric](38, 2) NULL ,
[sickhours] [numeric](38, 2) NULL ,
[awolhours] [numeric](38, 2) NULL ,
[rdohours] [numeric](38, 2) NULL ,
[holidayhours] [numeric](38, 2) NULL ,
[DateID] [int] NOT NULL ,
[keyid] [int] NOT NULL ,
[diallerid] [int] NULL ,
[DayOfWeek] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date_List] [datetime] NOT NULL ,
[sick] [int] NOT NULL ,
[sickness_spanningWkend] [int] NOT NULL ,
[awol] [int] NOT NULL ,
[awol_spanningWkend] [int] NOT NULL
)
Other tables are LBMM_SickAwolCounter:
CREATE TABLE [dbo].[LBMM_SickAwolCounter] (
[AgentID] [int] NULL ,
[DateofAbsence] [int] NULL )

AND magent which is another view:
CREATE VIEW dbo.magent
AS
SELECT AgentID, Agent#, AgentName, LoginName, LoginPassword, SupervisorRights, Campaign, MultiCampaign, SelfVerify, SelfVerifyCount,
RequireSupvForLogoff, RecDeleted, IsVerificationAgent, IsInboundAgent, AllowManualDial, ParentAgentID
FROM (SELECT a.AgentID, a.Agent#, a.AgentName, a.LoginName, a.LoginPassword, a.SupervisorRights, a.Campaign, a.MultiCampaign, a.SelfVerify,
a.SelfVerifyCount, a.RequireSupvForLogoff, a.RecDeleted, a.IsVerificationAgent, a.IsInboundAgent, a.AllowManualDial,
isnull(a.ParentAgentID, - 1) AS ParentAgentID
FROM amcat.dbo.MAGENT AS a WITH (nolock)
UNION ALL
SELECT a.AgentID, a.Agent#, a.AgentName, a.LoginName, a.LoginPassword, a.SupervisorRights, a.Campaign, a.MultiCampaign, a.SelfVerify,
a.SelfVerifyCount, a.RequireSupvForLogoff, a.RecDeleted, a.IsVerificationAgent, a.IsInboundAgent, a.AllowManualDial, a.ParentAgentID
FROM reportsstore2.dbo.magent_groupusers AS a WITH (nolock)) C

In the vw_LBMM_TimesheetReport view there are 2 columns called 'sickhours' and 'awolhours'. These are the backbone for the whole report and the calculations are taken from these columns. There are also 2 columns called 'rdohours' and 'holidayhours'. Currently, this report does not look at these 2 columns but now it has to and this is where I am completely stuck.
Currently the query runs but when it encounters anything other than 'sickhours' or 'awolhours' (eg.'rdohours' or 'holidayhours') the period of absence is broken and ended. This has to now change so that when it encounters 'rdohours' or 'holidayhours' the Absence period will continue.

I appreciate this is quite convoluted but can anyone please help as I cannot figure out how I can do this. If you need any more information please just ask.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-01 : 08:08:11

Maybe if you provide some sample data for table LBMM_SickAwolReport, and the expected result based on that sample data, we might be able to help you.

Peter Larsson
Helsingborg, Sweden

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2006-11-01 : 08:54:05

Here is some data from vw_LBMM_TimesheetReport which populates LBMM_SickAwolReport (truncated as there are 511 rows).

DayName hours sickhours awolhours rdohours holidayhours DateID keyid diallerid DayOfWeek Date_List
30 8 0 0 0 0 20061030 106600 1204 Monday 00:00.0
30 8 0 0 4 0 20061030 106334 1266 Monday 00:00.0
30 8 0 0 0 0 20061030 105384 1279 Monday 00:00.0
30 8 0 0 0 0 20061030 106032 1287 Monday 00:00.0
30 8 0 0 0 0 20061030 106341 1293 Monday 00:00.0
30 8 0 0 0 0 20061030 106151 1349 Monday 00:00.0
30 8 0 0 0 0 20061030 105417 1362 Monday 00:00.0
30 8 0 0 0 0 20061030 106150 1367 Monday 00:00.0
30 8 0 0 8 0 20061030 105908 1406 Monday 00:00.0
30 8 0 0 0 0 20061030 105877 1417 Monday 00:00.0
30 8 0 0 0 0 20061030 106127 1436 Monday 00:00.0
30 8 0 0 0 0 20061030 106598 1461 Monday 00:00.0
30 8 0 0 0 0 20061030 105940 1519 Monday 00:00.0
30 8 0 0 0 0 20061030 106458 1584 Monday 00:00.0
30 8 0 0 0 0 20061030 105464 1914 Monday 00:00.0
30 8 0 0 0 0 20061030 105891 1936 Monday 00:00.0
30 8 0 0 0 0 20061030 105405 2281 Monday 00:00.0
30 8 0 0 0 0 20061030 105951 2755 Monday 00:00.0
30 8 0 0 0 0 20061030 105866 2896 Monday 00:00.0
30 8 0 0 0 0 20061030 105948 2915 Monday 00:00.0
30 8 0 0 0 8 20061030 105851 2978 Monday 00:00.0
30 8 0 0 0 0 20061030 105933 2991 Monday 00:00.0
30 8 0 0 0 0 20061030 105836 3034 Monday 00:00.0
30 8 0 0 0 0 20061030 105837 3255 Monday 00:00.0
30 8 0 0 0 0 20061030 105865 3682 Monday 00:00.0
30 8 0 0 8 0 20061030 105390 4059 Monday 00:00.0
30 8 0 0 0 0 20061030 105443 4298 Monday 00:00.0
30 8 0 0 0 0 20061030 106335 4322 Monday 00:00.0
30 8 0 0 0 0 20061030 106343 4352 Monday 00:00.0
30 8 0 0 0 0 20061030 106349 4376 Monday 00:00.0
30 8 0 0 0 0 20061030 105397 4426 Monday 00:00.0
30 8 0 0 0 0 20061030 105999 4436 Monday 00:00.0
30 8 0 0 8 0 20061030 106158 4521 Monday 00:00.0
30 8 0 0 0 0 20061030 106128 4565 Monday 00:00.0
30 8 0 0 0 0 20061030 105871 4620 Monday 00:00.0
30 8 0 0 0 0 20061030 106019 4655 Monday 00:00.0
30 8 0 0 0 0 20061030 105983 4750 Monday 00:00.0
30 8 0 0 0 0 20061030 106016 4821 Monday 00:00.0
30 8 0 0 0 0 20061030 105450 5107 Monday 00:00.0

This is what LBMM_SickAwolReport looks like:

DayName hours sickhours awolhours rdohours holidayhours DateID keyid diallerid DayOfWeek Date_List sick sickness_spanningWkend awol awol_spanningWkend
30 8 4 0 0 0 20061030 107758 14736659 Monday 00:00.0 1 0 0 0
30 8 8 0 0 0 20061030 109711 14737278 Monday 00:00.0 1 1 0 0
30 8 0 8 0 0 20061030 110244 14737486 Monday 00:00.0 0 0 1 1
30 8 8 0 0 0 20061030 110709 14737675 Monday 00:00.0 1 1 0 0
30 8 0 8 0 0 20061030 110600 14737736 Monday 00:00.0 0 0 1 0
30 8 8 0 0 0 20061030 105868 6521 Monday 00:00.0 1 0 0 0
30 8 0 8 0 0 20061030 110528 14737618 Monday 00:00.0 0 0 1 0
30 8 0 8 0 0 20061030 110927 14737750 Monday 00:00.0 0 0 1 0
30 8 8 0 0 0 20061030 109887 14737305 Monday 00:00.0 1 1 0 0
30 4 4 0 0 0 20061030 110776 14737693 Monday 00:00.0 1 0 0 0
30 8 8 0 0 0 20061030 105892 14735805 Monday 00:00.0 1 0 0 0
30 8 0 8 0 0 20061030 110700 14737669 Monday 00:00.0 0 0 1 0
30 8 0 8 0 0 20061030 110882 14737735 Monday 00:00.0 0 0 1 0
30 8 8 0 0 0 20061030 110803 14737716 Monday 00:00.0 1 0 0 0
30 8 8 0 0 0 20061030 110923 14737748 Monday 00:00.0 1 1 0 0
30 8 0 8 0 0 20061030 109100 14737049 Monday 00:00.0 0 0 1 0
30 8 8 0 0 0 20061030 109704 14737273 Monday 00:00.0 1 1 0 0
30 8 8 0 0 0 20061030 110794 14737718 Monday 00:00.0 1 0 0 0
30 8 8 0 0 0 20061030 105393 6307 Monday 00:00.0 1 0 0 0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-01 : 09:05:21

What is the logic behind all the CURSOR stuff?

Peter Larsson
Helsingborg, Sweden

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2006-11-01 : 09:09:13

I really don't know. I didn't create the original query. I have just been given the lovely task of making these alterations and I don't know where to begin.

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2006-11-01 : 13:57:27

Can no-one help me further wit this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-01 : 14:02:14

Of course we can. But you have to remember this is a free of charge forum.
We contribute as much as we can. And is this case, the CURSOR code and thingy is a huge task and takes some time to dwell in to...

Peter Larsson
Helsingborg, Sweden

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2006-11-01 : 14:34:10

Sorry for my impatience. I was thinking you'd given up on me. I should know better than that. :-)

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2006-11-02 : 11:38:22

Hi guys,

I really don't mean to be pain over this by hassling anyone but I am getting pressure to have this query sorted out and I cannot do it by myself. Does anybody have any idea how I can solve this please?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-02 : 13:17:36

I generally pass over these types of questions ... I'll gladly engage in a discussion about a particular approach, or a quick question ... but with something like this, the benefit isn't very good to me to get involved.

There are those here that usually will help with these types of things.

One thing you should do is when you are providing sample data and DDL, put it into a format that someone could cut-n-paste ... who in their right mind would take your sample data and put inserts around all of that?

Have you considered that getting someone on the internet to do your work for you may not really benefit you in the long term?

Jay White

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2006-11-02 : 16:30:13

This is a forum (and a very good one) and everyone has their own opinion on how it should function - which is how it should be...You've stated yours on my post and here's my response:

>I generally pass over these types of questions ... I'll gladly engage in a discussion about a >particular approach, or a quick question ... but with something like this, the benefit isn't >very good to me to get involved.

Then why post a reponse at all then? If this is your approach then fine, that's your right, but I don't understand why you respond when this is all you have to offer. Just do as you claim you do and pass it over. BTW - If you are only here to get some personal benefit from questions you answer then maybe you need to review your reasons for why you are here. I always thought that forums (when looking at it from the experts end) were about sharing your knowledge for the benefit of others.

>There are those here that usually will help with these types of things.

Yes, I agree, there are many good and talented people on this forum who give up their time to help people who are maybe less skilled or experienced than themselves. I have been very grateful for their help in the past, it is just that I don't see many of them offering much on this particular post so I politely asked if anyone had anything else they could offer.

>One thing you should do is when you are providing sample data and DDL, put it into a format >that someone could cut-n-paste ... who in their right mind would take your sample data and put >inserts around all of that?

I was not aware that there is a set method for copying sample data to the forum. The interface doesn't give you many options to do this properly. I would be grateful if someone could tell me how this should be done and which format would be acceptable for making your life easier. I have always done it this way with no complaints so I presumed this was right. I apologise to anyone who I may have inconvenienced in doing this. Please tell me the correct way and I will adopt the method in the future.

>Have you considered that getting someone on the internet to do your work for you may not really >benefit you in the long term?

Well, no. I totally disagree with you here. It is obvious that this, like many, many other posts on this forum, are work related and why is that of any relevence? A problem is a problem whether it is personal or work related. I do not know many people who sit in their houses designing and implementing complicated databases and queries for personal usage. I would say that the mass majority of posts on this forum are derived from work issues.

As for my long term benefit - If someone with greater skill and experience can show me a way to solve a problem that I simply do not have the knowledge to solve on my own then I can get an enourmous amount of benefit from that. Isn't this the fundamental theory being teaching - passing on knowledge for others to absorb and use? No, my friend, you are very, very wrong here. Yes, it would dig me out of a bit of a hole at work but so what? I will just turn around to my boss tomorrow and say "I cannot do this". She probably won't be too happy but she'll get over it and I'll be a little embarrased and I'll get over it. The problem is, if a similar query ever presents itself to me again I will be no nearer to solving it - whereas if someone showed me how to do this I can study what they have done and try to understand it. Then, I am benefiting from someone elses knowledge which is surely what a forum is all about.

pootle_flump

1064 Posts

Posted - 2006-11-02 : 16:41:50

Perfectly asked question recently - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74221.

DDL script.
Sample data in the form of DML (INSERT INTO...) allowing cut and paste into QA (your sample data would need to be keyed in I am afraid).
Use of code tags & formatting (makes interpretation much easier).

HTH

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-03 : 08:56:31

quote:
Originally posted by p.shaw3@ukonline.co.uk

Then why post a reponse at all then? If this is your approach then fine, that's your right, but I don't understand why you respond when this is all you have to offer.

This is my form of help on this question. I'm simply suggesting that if you pose your question differently, you are more likely to get a helpful response. In order for anyone to help you, they are going to need to gain an understanding of your business case and the problem you are encountering. You have posted in such a way that someone would need to parse through 195 lines of horribly formated SQL code without knowing squat about your table structures or the data. I simply won't waste my time with that crap.

quote:
I was not aware that there is a set method for copying sample data to the forum. The interface doesn't give you many options to do this properly. I would be grateful if someone could tell me how this should be done and which format would be acceptable for making your life easier.

If you put your self in the readers shoes it is really quite simple.

quote:
I have a problem with the following query returning duplicates

select a from t

Here is the table and some sample data

create table t(a varchar(10))
insert into t
select 'a' as 'a'
union all
select 'b'
union all
select 'a'

Anyone have any ideas?

See? Without the table and the data, how in the hell could anyone help you ... now, if you posted this:

quote:
I have a problem with the following query returning duplicates

select a from t

Table t looks like this
a
b
a

Anyone have any ideas?

... your reader has to do some extra work. Assuming they don't see the error right off (as is your case of 195 lines of crap), they are going to need to infer the table structure and then manually construct the insert statement. Why should I take your 58 rows of sample data and re-work that into an insert statement? Your are the one with the problem, not me ... you do the manual labor so as to make it easy for me to set up a test environment.

Is this the SQLTeam.com law? No ... but it is common sense, don't ya think?

quote:
A problem is a problem whether it is personal or work related.

My suggestion is not based on it being work related or not ... I am suggesting that you are passing off too much of a problem. How would you respond if I posted DDL for 9 fact tables, 47 dimension tables and 3000 source tables and asked SQLTeam.com to author me an ETL solution? Not likely I'll get responses. Whenever I post questions here, I narrow the problem area down to is rawest form and the simplest question I can that helps me get to the bigger solution I am after. What you are doing is saying "My boss gave me this task, I don't know how to do it and either don't feel like it or don't have the aptitude to resolve it. Can you do it for me?" There is a big effing difference, yeah? If you truely "don't know where to begin", then you've got a political problem with your boss, not a SQL problem.

If you feel the need to post anything further other than "thanks for the advise, let me try to rephrase my post", either there is a big disconnect between UK and USA culture (possible) or you've got much bigger issues than this query.

Jay White

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2006-11-03 : 09:56:14

>If you feel the need to post anything further other than "thanks for the advise, let me try to rephrase my post", either there is a big disconnect between UK and USA culture (possible) or you've got much bigger issues than this query.

Is this not exactly what you have just done yourself?

As for the rest of your response, you have done nothing but cover old ground. I already apologised for the poor state of my sample data but you have spent 3/4 of your reply reiterating this. Why?

>What you are doing is saying "My boss gave me this task, I don't know how to do it and either don't feel like it or don't have the aptitude to resolve it. Can you do it for me?" There is a big effing difference, yeah? If you truely "don't know where to begin", then you've got a political problem with your boss, not a SQL problem.

Actually, no. I have been tasked with a problem that I cannot solve (and I have tried). There is nothing more than that. Nothing more nothing less. From what I can see the nature of this SQL prevents it from being broken down to it's rawest form as that is where it is at already, or maybe I am wrong, but isn't that why I have come here for help on this issue?

As I said yesterday, if no-one can help with this then that's ok. I appreciate anyone who has at least looked which I am sure many have. What I do not appreciate is someone responding with an arrogant, non-beneficial reply. Except for the sentence or two about sample data etc. you have added no content to this whatsoever, the only benefactor being yourself and your ego. I would say it is you that has the issues mate.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-03 : 10:25:02

Best of luck to you.

Jay White

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-03 : 10:39:48

You know what? I'm not going to leave it at that ... Here is an idea, prove to me that I am the one with "issues". Print this entire thread out and go show it to your boss. She what she thinks. Tell her "I tried to get someone on the internet to fix this report for us but they couldn't do it either!" ... See how well that goes over? What, no? You don't think that would be a good idea? Well, maybe there is your first clue that you, sir, are in error. It is generally accepted that around here we don't do peoples homework. There is a reason for that and I contend that this is no different.

Secondly, this is SQLTeam.com. We do SQL Server stuff. This is not HelpWithMyLogic.com, ReportEnhancement.com or ImplementBusinessRules.com. Your post has zippy to do with SQL Server, as best I can tell. What you have is a classic situation of an inexperience programmer looking at a cluster-fuck of crap code while brandishing a roll of duct tape and a box of band-aids. Your problem is not with SQL Server in the least.

Jay White

mr_mist
Grunnio

1870 Posts

Posted - 2006-11-03 : 10:59:10

Two things.

First, Jay is right. You've not made this easy for any of us. You still haven't provided sample data in the from of INSERTs, even.

Second, I don't believe that you've actually explained what the issue is.

-------
Moo. :)

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2006-11-03 : 11:02:34

Jay,

You are getting very excited again.......I fear for the cardiac dept of your local hospital.

Go and sit in a dark room for at least one hour and repeat "they are not all out to get me, they are not all out to get me, they are not all out to get......."

Get the picture? In the meantime, I suggest you stay away from from anything that ends in "forum". They get you too excited.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-03 : 12:29:54

Yeah, it figures ... since you don't seem capable of engaging (neither with your organization, your troubled query nor in this thread) using approaches founded in logic and sound reasoning, it would only be appropriate for you to respond as above. So, yeah, I get the picture.

Jay White

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-11-03 : 12:56:49

p.shaw3,

Jay is correct, and you're out of line.

First, you post a bunch of convoluted SQL from two separate queries, and then say:

quote:

In the vw_LBMM_TimesheetReport view there are 2 columns called 'sickhours' and 'awolhours'. These are the backbone for the whole report and the calculations are taken from these columns. There are also 2 columns called 'rdohours' and 'holidayhours'. Currently, this report does not look at these 2 columns but now it has to and this is where I am completely stuck.

So what's your question? What's the query supposed to do with "sickhours" and "awolhours"? Since the report "currently doesn't look at these 2 columns", in what way are they "the backbone for the whole report"? What exactly is your question?

You were next asked by Peter (Peso) (emphasis mine):

quote:

Maybe if you provide some sample data for table LBMM_SickAwolReport, and the expected result based on that sample data, we might be able to help you.

You then dumped a bunch of sample data in a useless format, but still didn't provide the expected result.

You then got rude to Jay when he asked for useful sample data and the expected output (along with providing paragraphs of other useless and offensively phrased text):

quote:

Then why post a reponse at all then? If this is your approach then fine, that's your right, but I don't understand why you respond when this is all you have to offer.

May I offer a little advice? When you post to a forum looking for someone's help for FREE, be extremely careful about your attitude and the way you treat people. Failing to provide information requested and being rude for no reason to someone has a tendency for other people to avoid trying to help as well.

And Jay? Give it up. It's a lost cause. :-)

Kristen
Test

22859 Posts

Posted - 2006-11-03 : 13:50:05

I just arrived here not having tracked this thread before because the REPLIES count was quite high and I thought it would be either interesting or still in need of some help.

I have a limited amount of time to help people on SQL Team, and rude people go straight to the bottom of the heap, along with people who can't provide a reasonable set of DDL, sample data, and expected results after prompting; so, sorry, but like Jay, I've put you on my Don't Bother list.

In response to your earlier comment "Then why post a response at all then?" I've posted this so that you know that I am choosing not to reply, and why. I expect there are others who have taken the same view, and not bothered to say so, and hopefully you learning something from that will help you, if not now then in the future.

Kristen

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources