October 4, 2007 at 3:27 am
I working on a mobile/cell phone database. Unlike in the original db I will use english column names here for better understanding (I'm native german)
There's one table, holding the SIM cards with the following (amongst others) columns:
SIMCards
TelNumber
ID_User
IMEI
I also have 2 history tables, where data gets inserted, when the SIM-cards table gets updated:
DeviceHistory
ID_DeviceHistory
TelNumber
ChangeDate
IMEIold
IMEInew
and
UserHistory
ID_UserHistory
TelNumber
ChangeDate
UserOld
UserNew
The device is linked to the SIM card, but it would of course be nice to know, when a certain user had a device.
I somehow have to query, when a user had a number and which device this number was assigned to in the same date range :blink:
I thought about it the whole morning, but I just didnt wangle it. The data is there, and it certainly can be done ... but how?
October 4, 2007 at 5:33 am
Without some sample data it is difficult to tell what you want.
Maybe something like:
SELECT U1.UserNew
    ,D.TelNumber
    ,D.ChangeDate AS DeviceDate
    ,D.IMEInew
FROM DeviceHistory D
    JOIN (
            UserHistory U1
            LEFT JOIN UserHistory U2
                ON U1.TelNumber = U2.TelNumber
                    AND U1.UserNew = U2.UserOld
                    AND U2.ChangeDate = (
                            SELECT MIN(U3.ChangeDate)
                            FROM UserHistory U3
                            WHERE U1.TelNumber = U3.TelNumber
                                AND U1.UserNew = U3.UserOld
                                AND U1.ChangeDate < U3.ChangeDate
                        )
        )
        ON D.TelNumber = U1.TelNumber
            AND D.ChangeDate >= U1.ChangeDate
            AND (D.ChangeDate < U2.ChangeDate OR U2.ChangeDate IS NULL)
October 4, 2007 at 6:07 am
Michael,
Please keep in mind to help your fellow SQLServerCentral members by posting table structure and sample data, so that we can be at our best in helping you.
Please try out what Ken has provided and let us know if it works or not.
Regards,
Wameng Vang
MCTS
October 4, 2007 at 6:59 am
mengus (10/4/2007)
Michael,Please keep in mind to help your fellow SQLServerCentral members by posting table structure and sample data, so that we can be at our best in helping you.
Please try out what Ken has provided and let us know if it works or not.
Regards,
Wameng Vang
Oh, sorry. Of course.
I will do that, as soon as I'm at work again!
Thanks for the help so far!
October 9, 2007 at 4:26 am
Hello Ken,
I tested the query you posted and it works just the way I wanted it to. Great, thanks a lot! :w00t:
For future issues:
I dont completely get how you did this. (I didn't even know that its possible to join via /> or /< )
I find this very complicated and wondered how one can figure out such solution ... do you make a sketch, or is it just the experience??
October 10, 2007 at 1:24 pm
SELECT U1.UserNew
    ,D.TelNumber
    ,D.ChangeDate AS DeviceDate
    ,D.IMEInew
Ken,
How do you format the code in your post? I can't get this new forum tool to work for me.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 11, 2007 at 6:07 am
I too would like to know how you keep your formatting!!!:D
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 11, 2007 at 9:03 pm
Just doing some formatting tests... please ignore the following post...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 11, 2007 at 9:05 pm
Just a test... please ignore...
FIGURE 1: Tally Table Code
[font="Courier New"]
drop table tally
--===== Create and populate the Tally table on the fly
 SELECT TOP 11000 --equates to more than 30 years of dates
        IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
  GRANT SELECT ON dbo.Tally TO PUBLIC[/font]
Test complete... regard all further alarms 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
October 11, 2007 at 9:34 pm
Looks like any decent HTML editor or a Word macro to change spaces to the literal " " (without the quotes) will do the trick.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 12, 2007 at 5:52 am
OK, My turn to test. ...
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 12, 2007 at 7:29 am
Jeff Moden (10/11/2007)
Looks like any decent HTML editor or a Word macro to change spaces to the literal " " (without the quotes) will do the trick.
Sorry folks... I didn't check my last post...
The thing in quotes is supposed to be an ampersand {Shift-7} as an escape character followed by "nbsp" (without the quotes, of course).
--Jeff Moden
Change is inevitable... Change for the better is not.
October 12, 2007 at 11:03 am
I tried using "& nbsp;", as well as html tags such as <pre> (what I had been using). They are ignored.
Wait. Notice the space before the "nbsp;" above. The ampersand I have to generate using "& amp" (without the space), but if I butt the "nbsp;" against it, a non-breaking space actually appears in the post. So what we enter into the text box goes through (at least) two levels of processing.
If you enter the code for non-breaking space and click "Preview" you will see the space in the preview. Click "Preview" again, without making any changes and the space disappears. Go back a page and you will see that the non-breaking space has been removed from the text box.
Weird.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply