Determining the differences in names from day to day??? 1

[brianhayes]

TIA, and I have tried to do this muliple ways, and always end up with the same thing... I do not know how to do it and nothing I have read has helped.. what am I missing?

What I am tring to do? Determine when a name exists, that did not exist in a prior date and/or previous date. Reporting the date of change only.

Data:

ID (int) Date_time (datetime) Name(varchar150)
110 2002-10-25 17:28:31.350 Backup Exec
110 2002-10-25 17:28:31.350 NetSupport
110 2002-10-25 17:28:32.450 Backup Exec
110 2002-10-25 17:28:32.450 NetSupport
110 2002-10-26 17:28:31.350 Backup Exec
110 2002-10-26 17:28:32.450 Backup Exec
110 2002-10-26 17:28:32.450 NetSupport
110 2002-10-28 17:28:31.350 Backup Exec
110 2002-10-28 17:28:31.350 NetSupport
110 2002-10-28 17:28:32.450 Backup Exec
110 2002-10-28 17:28:32.450 NetSupport
110 2002-10-28 17:28:32.450 Net Solution

In the data above you can see that on the 25th two names exist, and on the 26th, one of the names is missing, and then on the 28th, the two original names are now back, plus one.

What does this represent? That I had a change on the 26th for one name (NetSupport was removed) and two changes on the 28th (NetSupport was added back and Net Solution was added as well). So in short, NetSupport was remove on X and was installed in X, then Net Solution was added on X.

Can anyone help???? Im going on three weeks now and its not looking good....

Thank YOU
Brian


Brian Hayes
brian.hayes@eallowed.com

http://www.eallowed.com

 

[ClaireHCM]

I am confused by this sentance " So in short, NetSupport was remove on X and was installed in X, then Net Solution was added on X."

What is X ?
Do you want NetSupport to be back ?
Do you want Net solusion to be added?
How the names in 28th related to the names in 26th?

 

[brianhayes]

Sorry,

I am tring to track changes that can be made to the data based on what is installed on a machine. The data is collected programaticaly throught the day, and the differences from day to day are the result of someone making a machine change.

So, the report should show that on the 26 (this is X), someone uninstalled NetSupport (because it exists on the 25th), and then readded it back to the machine on the 28th, to include the addition of one other "Net Support" (because it never existed and/or did not exist on the prior date, or start date (MIN).

Does this make any sense? Because I feel I am confusing my self... I never know how to explain what I am strugling with.

Thank You for replying.
Brian

Normally there are about twenty or so for each machine, and depending on how many time we run our code, we could have about 15 entry per day for each.


Brian Hayes
brian.hayes@eallowed.com

http://www.eallowed.com

 

[brianhayes]

It seems that this has even stump some of my co-workers... Please let me know if some one needs more explanation and I will provide it..

Thanks

Brian Hyaes


Brian Hayes
brian.hayes@eallowed.com

http://www.eallowed.com

 

[ClaireHCM]

Could you demonstrate the desired reult?
For the data you provide,what kind of result you want to get?Could you express it as a form or table or view?Or maybe you want to express in some other way?

 

[brianhayes]

Got It....

ID (int) Date_time (datetime) Name(varchar150)
110 2002-10-25 17:28:31.350 Backup Exec
110 2002-10-25 17:28:31.350 NetSupport
110 2002-10-25 17:28:32.450 Backup Exec
110 2002-10-25 17:28:32.450 NetSupport
110 2002-10-26 17:28:31.350 Backup Exec
110 2002-10-26 17:28:32.450 Backup Exec
110 2002-10-26 17:28:32.450 NetSupport
110 2002-10-28 17:28:31.350 Backup Exec
110 2002-10-28 17:28:31.350 NetSupport
110 2002-10-28 17:28:32.450 Backup Exec
110 2002-10-28 17:28:32.450 NetSupport
110 2002-10-28 17:28:32.450 Net Solution


ID AsofDate Name Status
110 2002-10-26 NetSupport Removed
110 2002-10-28 NetSupport Installed
110 2002-10-28 Net Solution Installed


Being the Both NetSupport and Backup Exec existed on the 25th, and now on the 26th, we are missing an entry for NetSupport so it is assumed "Removed", now on the 28th, we see that the data now show "NetSupport" as being back in the data, thus installed... and a new entry "Net Solution", thus installed.

So my mental logic, states that if it existed on day one of data collection then it is considered removed and added if the entry in the data are not consistant (Not in the data between captured dates)...and if something shows up that did not show up in the past then is considered to be installed on that date, and if it ever is not in the data after the installed date, then it is considered to be removed.

TIA....

Brian


Brian Hayes
brian.hayes@eallowed.com

http://www.eallowed.com

 

[brianhayes]

Is there another Forum that I anyone knows about that I could post this information to? I figure the more people who know about this the greater the chances are of getting it resolved... Then I could post the resolution to them all so that anyone who ever has this type of issue will know the answer...

TIA
Brian Hayes


Brian Hayes
brian.hayes@eallowed.com

http://www.eallowed.com

 

[zhavic]

Do you still want this ?
I can write some query, which can do this, but now I have some work to do, I can post it tomorow

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[brianhayes]

That would me great, I look forward to work tomorow now...

Brian Hayes


Brian Hayes
brian.hayes@eallowed.com

http://www.eallowed.com

 

[Mr3Putt]

Here's an over-simplified sample that (sort of) does what you're after.

For the following query, my table name is "tServices", it contains 3 fields:

ID (integer)
DATE (datetime)
NAME (varchar)

SELECT a.ID, a.DATE,  a.NAME,  'DROPPED' AS TYPE
FROM tServices a  LEFT JOIN tServices  b ON (a.NAME = b.NAME) AND (a.ID = b.ID) AND (a.Date = DATEADD('d',b.DATE,-1))
WHERE b.ID IS NULL

UNION

SELECT a.ID, a.DATE, a.NAME, 'ADDED' AS TYPE 
FROM tServices a  LEFT JOIN tServices  b ON (a.NAME = b.NAME) AND (a.ID = b.ID) AND (a.Date = DATEADD('d',b.DATE,1))
WHERE b.ID IS NULL;

This should do something similar to what you're after...

 

[Mr3Putt]

D'Oh! This fixes a couple problems with the previous code... namely, the dates were wrong, and it was reporting an additional day's worth of data, with EVERYTHING being 'DROPPED'... this code seems to work.

SELECT a.ID, a.DATE,  a.NAME,  'ADDED' AS TYPE
FROM tServices a  LEFT JOIN tServices  b ON (a.NAME =b.NAME) AND (a.ID = b.ID) AND (a.Date = DATEADD('d',b.DATE,1))
WHERE b.ID IS NULL

UNION

SELECT a.ID,DATEADD('d',a.DATE,1), a.NAME, 'DROPPED' AS TYPE 
FROM tServices a  LEFT JOIN tServices  b ON (a.NAME =b.NAME) AND (a.ID = b.ID) AND (a.Date = DATEADD('d',b.DATE,-1))
WHERE b.ID IS NULL AND (a.DATE < (SELECT MAX(DATE) FROM tServices));

 

[brianhayes]

Awsome!!!! That makes alot of sence now that I can see the results.... Then I noticed that my data has dates missing.. Meaning, that I could have data on the 26th then I could be missing data on the 27th, 28th, then have data on the 29th. Not to mention, with datetime, you can have multiple entrys for the same day as well...

Which leads me to my next question, how can you determine the next avalable date based on the dates in the db?

At the bottom is your code with some alterations, using convert(convert()), to make sure I only get todays absolute datetime, when I have multiple runs for the same day. But, if there is a way to use the next highest datetime in the db, that would be more acurate..

Such As:
2003-06-05 02:53:58.000 APP1
2003-06-05 02:53:58.000 APP2
2003-06-05 02:53:58.000 APP3
2003-06-05 04:53:58.000 APP1
2003-06-05 04:53:58.000 APP2
2003-06-05 04:53:58.000 APP3
2003-06-05 06:53:58.000 APP1
2003-06-05 06:53:58.000 APP2
2003-06-05 06:53:58.000 APP3
2003-06-05 06:53:58.000 APP4 <APP4 ADDED IN THIS HOUR>
2003-06-05 08:53:58.000 APP1
2003-06-05 08:53:58.000 APP2
2003-06-05 08:53:58.000 APP3
2003-06-05 08:53:58.000 APP4
2003-06-05 08:53:58.000 APP5 <APP5 ADDED IN THIS HOUR>
2003-06-05 09:53:58.000 APP2 | <APP1 REMOVED IN THIS HOUR>
2003-06-05 09:53:58.000 APP3 | <APP1 REMOVED IN THIS HOUR>
2003-06-05 09:53:58.000 APP4 | <APP1 REMOVED IN THIS HOUR>
2003-06-05 09:53:58.000 APP5 | <APP1 REMOVED IN THIS HOUR>
2003-06-05 12:53:58.000 APP2 | <APP 3 REMOVED,APP6,7 ADDED>
2003-06-05 12:53:58.000 APP4 | <APP 3 REMOVED,APP6,7 ADDED>
2003-06-05 12:53:58.000 APP5 | <APP 3 REMOVED,APP6,7 ADDED>
2003-06-05 12:53:58.000 APP6 | <APP 3 REMOVED,APP6,7 ADDED>
2003-06-05 12:53:58.000 APP7 | <APP 3 REMOVED,APP6,7 ADDED>

The above is all for one day, then the results of this would need to be compared to the next day (Mainly the next day in which we have a record for), but if there is some way to find out the next datetime value, then the problem would be solved..I think...So, the below works, but based on the above, is still not acurate.

But, You have gotten me a long way forward....


SELECT
TS.SERVERID,
CONVERT(DATETIME,CONVERT(CHAR(8),TS.DATE_TIME)),
TS.NAME,
'ADDED' AS TYPE
FROM
TBLSERVERSOFTWARE TS
LEFT JOIN TBLSERVERSOFTWARE TS1 ON
(TS.NAME = TS1.NAME) AND
(TS.SERVERID = TS1.SERVERID) AND
(CONVERT(DATETIME,CONVERT(CHAR(8),TS.DATE_TIME)) =
DATEADD(day, 1, CONVERT(DATETIME,CONVERT(CHAR(8),TS1.DATE_TIME))))
WHERE

TS1.SERVERID IS NULL

UNION

SELECT
TS.SERVERID,
CONVERT(DATETIME,CONVERT(CHAR(8),DATEADD(day,1,TS.DATE_TIME))),
TS.NAME,
'DROPPED' AS TYPE
FROM
TBLSERVERSOFTWARE TS
LEFT JOIN TBLSERVERSOFTWARE TS1 ON
(TS.NAME = TS1.NAME) AND
(TS.SERVERID = TS1.SERVERID) AND
(CONVERT(DATETIME,CONVERT(CHAR(8),TS.DATE_TIME)) =
DATEADD(day, -1, CONVERT(DATETIME,CONVERT(CHAR(8),TS1.DATE_TIME))))
WHERE
TS1.SERVERID IS NULL AND
CONVERT(DATETIME,CONVERT(CHAR(8),TS.DATE_TIME)) <
(SELECT MAX(DATE_TIME) FROM TBLSERVERSOFTWARE WHERE SERVERID = TS.SERVERID)
[/CODE]


Brian Hayes
brian.hayes@eallowed.com

http://www.eallowed.com

 

[Mr3Putt]

Yeh, I knew the DateTime would make a mess of things for you... Couldn't come up with a quick solution for it, tho.

If you want to compare multiple intra-day runs, or runs from non-concurrent dates, I think you'll need a &quot;RUN&quot; table, with an incrementing ID#, and the RUN-DATE-TIME value... then in your TBLSERVERSOFTWARE table, you'd have the RUN_ID, rather than the DATE-TIME (or, you could have both).

As long as the ID's increment properly, you could replace the DATEADD(day,DATE,-1) stuff with just a.RUN_ID = (b.RUN_ID - 1)...

That's about all I can think of right now. Good luck.