Help with SQL code, historical info needed

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)

Help with SQL code, historical info needed

Author

Topic

furrelkt
Starting Member

49 Posts

Posted - 2005-10-17 : 13:41:46

I write (try to) Actuate reports and use sql to make the report. To pull all the information in, I use the SQL Query analyzer for our windows sql server 2000. I am quite new to really complex sql coding. Any help will really be appreciated.

My query returns one week of status given the timeperiod selected. What i want is to show a history of what the status is given the week(s) in the current month. I cannot figure out how to pass another week(s) status to the outer query so that i will have another EndDate, the end date of the previous period, say 5000600, end date for that period is 10/08/05

select
tp.prfinish-1 EndDate,
r1.FULL_NAME Mgr,
r.FULL_NAME Resource,
case when tsstatus IS NULL OR tsstatus = 0 then 'X' else '' end TS,
case when tsstatus=1 then 'X' else '' end TS1
from niku.PRTimePeriod tp,
niku.SRM_RESOURCES r
LEFT JOIN niku.SRM_RESOURCES r1 ON r1.user_id = r.manager_id
LEFT JOIN niku.PRJ_RESOURCES p ON r.id = p.prid
LEFT JOIN
(SELECT prResourceID rid, MAX(prStatus) tsstatus FROM niku.PRTimeSheet
WHERE prTimePeriodID=5000601
GROUP BY prResourceID) tss ON r.ID=tss.rid
where tp.prID=5000601
and (tss.tsstatus < 2 OR tss.tsstatus IS NULL)
and r.IS_ACTIVE=1
and p.prisrole=0
order by r1.full_name,r.full_name

the result of the query shows the EndDate, Mgr, Staff Name, TS (Status), TS1 (Status1)

The results are for only one period. I would like to see the other periods before this date but only to the beginning of the current month.
We are in period 4 of this month.The end date is the day minus 1.
The periods are like this in the database:
prid, prstart, prfinish
5000599, 10/1/2005, 10/2/2005
5000600, 10/2/2005, 10/9/2005
5000601, 10/9/2005, 10/16/2005
5000602, 10/16/2005, 10/23/2005
5000603, 10/23/2005, 10/30/2005

So the final result i would like to see is the status for the 2 previous weeks of the month.
showing the End Date(10/15/2005), Mgr, Staff Name, TS (Status), TS1 (Status1)
End Date (10/8/2005),Mgr, Staff Name, TS (Status), TS1 (Status1)
End Date (10/1/2005),Mgr, Staff Name, TS (Status), TS1 (Status1)

Any help is appreciated. Thanks so very much. If more info is needed please just ask, I will give you as much as you need.

Keri

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-18 : 01:19:45

can you provide the dml,ddl, sample data and expected results?

i'd like to help

--------------------
keeping it simple...

furrelkt
Starting Member

49 Posts

Posted - 2005-10-18 : 16:26:47

I shall try, like i said very new and I don't design any tables, just query so i can make reports. I will give it a whirl...

Keri

furrelkt
Starting Member

49 Posts

Posted - 2005-10-18 : 16:52:09

how many tables and insert statements should i give?

thanks,

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-18 : 21:25:19

only those that are included in your query and enough records to show us what you need to retrieve

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 02:20:46

Like how described here
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Madhivanan

Failing to plan is Planning to fail

furrelkt
Starting Member

49 Posts

Posted - 2005-10-19 : 13:50:14

Okay, this is my first try at this so i hope this works, I created tables in a test database, and queried the results.
So, i hope this is right.

CREATE TABLE [SRM_RESOURCES]
(
[ID] [numeric](18, 0) NOT NULL ,
[USER_ID] [numeric](18, 0) NULL ,
[MANAGER_ID] [numeric](18, 0) NULL ,
[IS_ACTIVE] [numeric](18, 0) NOT NULL ,
[FULL_NAME] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IS_EXTERNAL] [int] NOT NULL

)

INSERT INTO SRM_RESOURCES (ID, USER_ID, MANAGER_ID, IS_ACTIVE, FULL_NAME, IS_EXTERNAL)
VALUES('5000001','5000010','5000099','1','Willman, Jerra','1')
INSERT INTO SRM_RESOURCES (ID, USER_ID, MANAGER_ID, IS_ACTIVE, FULL_NAME, IS_EXTERNAL)
VALUES('5000002','5000011','5000010','1','Taylor, Keri','1')
INSERT INTO SRM_RESOURCES (ID, USER_ID, MANAGER_ID, IS_ACTIVE, FULL_NAME, IS_EXTERNAL)
VALUES('5000003','5000012','5000010','1','Giarratani, Ruthann','1')
INSERT INTO SRM_RESOURCES (ID, USER_ID, MANAGER_ID, IS_ACTIVE, FULL_NAME, IS_EXTERNAL)
VALUES('5000004','5000013','5000010','1','Flinstone, Fred','1')
INSERT INTO SRM_RESOURCES (ID, USER_ID, MANAGER_ID, IS_ACTIVE, FULL_NAME, IS_EXTERNAL)
VALUES('5000005','5000099','5000010','1','Angel, Martinez','1')

CREATE TABLE [PRJ_RESOURCES]
(
[PRID] [numeric](10, 0) NULL ,
[PRISROLE] [numeric](10, 0) NOT NULL
)

INSERT INTO PRJ_RESOURCES (PRID, PRISROLE)
VALUES('5000001','0')
INSERT INTO PRJ_RESOURCES (PRID, PRISROLE)
VALUES('5000002','0')
INSERT INTO PRJ_RESOURCES (PRID, PRISROLE)
VALUES('5000003','0')
INSERT INTO PRJ_RESOURCES (PRID, PRISROLE)
VALUES('5000004','0')
INSERT INTO PRJ_RESOURCES (PRID, PRISROLE)
VALUES('5000005','0')

CREATE TABLE [PRTIMEPERIOD]
(
[PRID] [numeric](10, 0) NULL ,
[PRSTART] [datetime] NULL ,
[PRFINISH] [datetime] NULL ,
)

INSERT INTO PRTIMEPERIOD (PRID, PRSTART, PRFINISH)
VALUES('5000599','10/1/2005','10/2/2005')
INSERT INTO PRTIMEPERIOD (PRID, PRSTART, PRFINISH)
VALUES('5000600','10/2/2005','10/9/2005')
INSERT INTO PRTIMEPERIOD (PRID, PRSTART, PRFINISH)
VALUES('5000601','10/9/2005','10/16/2005')
INSERT INTO PRTIMEPERIOD (PRID, PRSTART, PRFINISH)
VALUES('5000602','10/16/2005','10/23/2005')
INSERT INTO PRTIMEPERIOD (PRID, PRSTART, PRFINISH)
VALUES('5000603','10/23/2005','10/30/2005')
INSERT INTO PRTIMEPERIOD (PRID, PRSTART, PRFINISH)
VALUES('5000604','10/30/2005','11/1/2005')

CREATE TABLE [PRTIMESHEET]
(
[PRID] [numeric](10, 0) NULL ,
[PRTIMEPERIODID] [numeric](10, 0) NULL ,
[PRRESOURCEID] [numeric](10, 0) NULL ,
[PRSTATUS] [numeric](5, 0) NULL ,
)

INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000001','5000599','5000001','4')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000002','5000599','5000002','4')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000003','5000599','5000003','4')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000004','5000599','5000004','4')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000005','5000599','5000005','4')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000006','5000600','5000001','4')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000007','5000600','5000002','4')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000008','5000600','5000003','4')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000009','5000600','5000004','4')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000010','5000600','5000005','4')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000011','5000601','5000001','4')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000012','5000601','5000002','1')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000013','5000601','5000003','1')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000014','5000601','5000004','3')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000015','5000601','5000005','4')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000016','5000602','5000001','0')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000017','5000602','5000002','1')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000018','5000602','5000003','1')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000019','5000602','5000004','3')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000020','5000602','5000005','0')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000021','5000603','5000002','0')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000022','5000603','5000003','0')
INSERT INTO PRTIMESHEET (PRID, PRTIMEPERIODID, PRRESOURCEID, PRSTATUS)
VALUES('5000023','5000604','5000002','0')

CREATE
VIEW PRRESOURCE ( PRID, PRISROLE ) AS
SELECT PRID, PRISROLE
FROM (SRM_RESOURCES INNER JOIN PRJ_RESOURCES ON SRM_RESOURCES.ID = PRJ_RESOURCES.PRID

My query to get the results:

SELECT
tp.prFinish-1 EndDate,
r1.FULL_NAME Mgr,
r.FULL_NAME Resource,
case when tsstatus IS NULL OR tsstatus = 0 then 'X' else '' end TS,
case when tsstatus=1 then 'X' else '' end TS1
FROM PRTimePeriod tp,
SRM_RESOURCES r
LEFT JOIN SRM_RESOURCES r1 ON r1.user_id = r.manager_id
inner join prResource r2 ON r.id=r2.prid
LEFT JOIN PRJ_RESOURCES p ON r.id = p.prid
LEFT JOIN
(SELECT prResourceID rid, MAX(prStatus) tsstatus FROM PRTimeSheet
WHERE prTimePeriodID=5000602
GROUP BY prResourceID) tss ON r.ID=tss.rid
WHERE
tp.prID=5000602
AND r2.prisRole=0
AND (tss.tsstatus < 2 OR tss.tsstatus IS NULL)
AND r.IS_ACTIVE=1

My question and challange: I want to see multiple status for a period of 1 month. So, the TS, TS1 fields is for the period selected, which in this case is 5000602, which gives the prstart as 10/16/2005 and prfinish as 10/22/2005 (with the prfinish-1).

The results are correct but i want to see not only the end week of 10/22/05, i want to see the other weeks too in the current month.

furrelkt
Starting Member

49 Posts

Posted - 2005-10-20 : 07:28:41

additionally, I want to be able to select a date range instead of the actual 'timeperiod'.

furrelkt
Starting Member

49 Posts

Posted - 2005-10-26 : 10:27:33

Did i do something wrong? did i not set it up right? let me know, thanks

~K

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-26 : 10:35:23

Post the result you want

Madhivanan

Failing to plan is Planning to fail

furrelkt
Starting Member

49 Posts

Posted - 2005-10-26 : 11:18:25

question, is there a way to attach a text file, so the results come out the actual way?

"I can accept failure, but I can't accept not trying." - Michael Jordan

~K

furrelkt
Starting Member

49 Posts

Posted - 2005-10-26 : 11:32:00

[code]
Current results:
EndDate Mgr Resource TS TS1
2005-10-22 00:00:00.000 Angel, Martinez Willman, Jerra X
2005-10-22 00:00:00.000 Willman, Jerra Taylor, Keri X
2005-10-22 00:00:00.000 Willman, Jerra Giarratani, Ruthann X
2005-10-22 00:00:00.000 Willman, Jerra Angel, Martinez X

Want to see:
EndDate Mgr Resource TS TS1
2005-10-15 00:00:00.000 Willman, Jerra Taylor, Keri X
2005-10-15 00:00:00.000 Willman, Jerra Giarratani, Ruthann X
2005-10-22 00:00:00.000 Angel, Martinez Willman, Jerra X
2005-10-22 00:00:00.000 Willman, Jerra Taylor, Keri X
2005-10-22 00:00:00.000 Willman, Jerra Giarratani, Ruthann X
2005-10-22 00:00:00.000 Willman, Jerra Angel, Martinez X
2005-10-29 00:00:00.000 Angel, Martinez Willman, Jerra X
2005-10-29 00:00:00.000 Willman, Jerra Taylor, Keri X
2005-10-29 00:00:00.000 Willman, Jerra Giarratani, Ruthann X
2005-10-29 00:00:00.000 Willman, Jerra Flinstone, Fred X
2005-10-29 00:00:00.000 Willman, Jerra Angel, Martinez X
2005-10-31 00:00:00.000 Angel, Martinez Willman, Jerra X
2005-10-31 00:00:00.000 Willman, Jerra Taylor, Keri X
2005-10-31 00:00:00.000 Willman, Jerra Giarratani, Ruthann X
2005-10-31 00:00:00.000 Willman, Jerra Flinstone, Fred X
2005-10-31 00:00:00.000 Willman, Jerra Angel, Martinez X

[/code]

~"I can accept failure, but I can't accept not trying." - Michael Jordan ~

~K

furrelkt
Starting Member

49 Posts

Posted - 2005-11-11 : 17:33:21

I am guessing I can't get any help on this? :(

Keri

~"I can accept failure, but I can't accept not trying." - Michael Jordan ~

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-11 : 18:18:14

so if i understand this correctly you want grouping by weeks?
if so this'll be of help:
http://www.sqlteam.com/item.asp?ItemID=851

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources