advanced query help please - 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

advanced query help please

Author

Topic

hansvantendeloo
Starting Member

3 Posts

Posted - 2012-09-04 : 02:58:26

Hi all

I've been working with sql and .net for a year now and i have to write a query that i need a lot of help with

.

My programs let's a user create a maintenance to perform. Along with a frequency when it has to reoccur. So when the maintenace is finished i calculate the next date based on the number of days filled in in the frequency field. All simple but now i would like to give the user a report whenever he creates a new maintenance so he can see what other maintenances are performed on the occuring days (for 1 year in the future).

So i would have to calculate on wich days this maintenance will occur (startdate + frequency UNTIL date + 1 year). And then compare this to ALL active maintenances (calcualte the dates based on their last date + each ones frequency until enddate is reached. Because i only have 1 date in the future and thats the next planned date.

I've been working on a stored procedure and this is what i have so far. This actualy only writes the new maintenance to a temptable. I was thinking on this as a start but i'm not sure if it's the rigth way to start. Any help is very much appreciated!

Thank you all

ALTER PROCEDURE [dbo].[RPplanningPreview](@datumBegin datetime, @datumEnd datetime, @freq int, @Operator int)

AS
SET NOCOUNT ON;

WHILE (@datumBegin < @datumEnd)

BEGIN

INSERT INTO TempTable

select a.ID as PlanID, @datumBegin as PlanDate, b.naam as Machine, b.gebouw, c.naam as Deel, d.soort as Onderhoud, b.verdieping, e.naam as Operator, e.werkuur, e.werkrust, l.leverancier as Leverancier, mconderhoud.frequentie, mconderhoud.omschrijving, mconderhoud.tijd
from mconderhoudplan as a
left outer join mconderhoud on a.onderhoudID = mconderhoud.ID
left outer join mcmachine as b on mconderhoud.machineID = b.ID
left outer join mconderhoudstuk on mconderhoudstuk.onderhoudID = mconderhoud.ID
left outer join mcmachinestuk as c on mconderhoudstuk.machinestukID = c.ID
left outer join mconderhoudsoort as d on mconderhoudstuk.onderhoudsoortID = d.ID
left outer join prOperator as e on mconderhoud.operatorID = e.ID
left outer join leveranciers as l on mconderhoud.leverancierID = l.ID
Where a.status = 1 AND a.nextdate=@datumBegin AND mconderhoud.operatorID >=
(case @Operator
when -1 then -1
else @Operator
end)
and mconderhoud.operatorID <=
(case @Operator
when -1 then (select MAX(id) from properator)
else
@Operator
end)
order by a.nextdate Asc, b.gebouw, b.verdieping

SET @datumBegin = DATEADD(day,@freq,@datumBegin)

CONTINUE
END

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-09-04 : 04:51:02

Yuk. Not sure about your table structure and can't see the wood for the trees.
If I understand correctly you want some periods in the future from now to munge in with your current query.
I can't give you exactly that, but in general terms use this to get some numbers:

declare @frequency int;
select @frequency=17;

select number from master..spt_values where type = 'p' and number % @frequency = 0 and number<=366

using that you can make some dates:

select CAST(FLOOR( CAST( GETDATE()+number AS FLOAT ) ) AS DATETIME) from master..spt_values where type = 'p' and number % @frequency = 0 and number<=366

If you package this up as an inline view, you can join onto the rest of your query as you need to.

select * from
( select * from ....) x
inner join
( my bit ) y
on (whatever x v y criteria meet the condition you are after between/after/before/whatever)

Hope that helps and makes sense.

hansvantendeloo
Starting Member

3 Posts

Posted - 2012-09-04 : 06:10:10

quote:
Originally posted by LoztInSpace

If I understand correctly you want some periods in the future

I would like to have the periods from the already active maintenances counted forward. So i need to calculate each active maintenance with it's frequency until i reach the end date in the stored procedure. (and then only select the dates that are the same as my query results)

And i use this query to fill the report data so i need al lot of joins (each maintenance has a machine, each machine has parts, choosen type of maintenance, operator can be choosen and so on). And its partially in Dutch so sorry for the query

so in a nutshell

: for each row in table[mconderhoudplan] that has status 1 get plannedDate + get frequency from table[mconderhoud] and count forward (=plannedDate + frequency till endDate)
So i generate per row multiple dates till enddate.

Now check if any of these date fall togheter with dates from the new maintenance (rows from the stored procedure)

Hope this makes more sense and sorry for any english language errors

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-09-04 : 20:34:04

Sorry I'm still not getting it. Maybe some input, output and explanation of how you got there. Don't worry about all the joins and stuff - that's irrelevant. Just pick the main table, the schedule, stuff like that.
Cheers.

hansvantendeloo
Starting Member

3 Posts

Posted - 2012-09-05 : 02:21:48

-- insert main data in .net
INSERT INTO MConderhoud
(Datum, MachineID, Frequentie, Uitvoerder, OperatorID, LeverancierID, Doorgegeven, ProjectID, Omschrijving, Status, tijd)
VALUES (@Datum, @MachineID, @Frequentie, @Uitvoerder, @OperatorID, @LeverancierID, @Doorgegeven, @ProjectID, @Omschrijving, @Status, @tijd)

--1 row gets added to this planning table with status 1 (and the ID of the above insert, "OnderhoudID")
INSERT INTO MConderhoudplan
(OnderhoudID, soort, NextDate, Status)
VALUES (@OnderhoudID, @Soort, @NextDate, @Status)

A user can finish a row in the planning. What I do is set the status to 10 for that row and then i add a new row in the planning table (a copy of the first row but now status = 1 AND NEXTDATE = NEXTDATE + FREQUENCY(from main data table)

I would like to know all the dates in the next year for all the rows that have status 1 in the planning table (ps: only one row in planning table can have status = 1 PER maintenance,but i have like 500 maintenances with different frequencies)

Does this help?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources