Time Spans, Tricky SQL, My Head Hurts
Apr 8, 2006
Data apx (5 million rows):
Span example:
-------------M---------------
___________-------------Rx-------------
Needs to b converted to this (ignore the underscore, used for spacing):
---M-------|-------M & Rx----|---Rx--
The time spans can slide either way.
Data example:
MemberID Eff_Date Term_Date Med_COB Rx_COB
1 20050101 20050912 Y N
1 20050310 20051120 N Y
1 20060101 <null> Y N
1 20060101 <null> N Y
Resulting Records need to be in this format:
MemberID Eff_Date Term_Date Med_COB Rx_COB
1 20050101 20050310 Y N
1 20050311 20050912 Y Y
1 20050913 20051120 N Y
1 20060101 <null> Y Y
Any help with this problem would be greatly appreciated. We are running SQL2K. I like most people,would like to stay away from cursors and loops if possible.
Thanks,
nemesis01
View 8 Replies
ADVERTISEMENT
Jul 20, 2005
Hi all,I'm working on the schema for a database that must represent data about stock& bond funds over time. My connundrum is that, for any of several dimensionfields, including the fund name itself, the dimension may be represented indifferent ways over time, and may split or combine from one period to thenext.When querying from the database for an arbitrary time period, I need the datato be rolled up to the smallest extent possible so that apples can be comparedto apples. For instance, if the North America region becomes 2 regions, USAand Canada, and I query a time period that spans the period in which thissplit occurred, I should roll up USA and Canada and for records in the periodthat has both, and I should call the result something like "(NorthAmerica)(USA/Canada)" in the output. The client specifies that the dimensionoutput must represent all dimensions that went into the input.Of course, I have to account for more complex possibilities as well, e.g.Fund-A splits into Fund-B and Fund-C, then Fund-C merges into Fund-D producing(Fund-A/Fund-D)(Fund-B/Fund-C/Fund-D)(Fund-B/Fund-D)I can think of several ways to handle this issue, and they're allextraordinarily complex and ugly. Any suggestions?Thanks,- Steve Jorgensen
View 9 Replies
View Related
Nov 10, 2006
I'm trying to figure out how to this do with TSQL and preferablythrough joins rather than by using cursors or temp tables. I havemultiple time spans for multiple individuals that I would like torollup together where they don't actually have a gap in coverage.ID StartDate EndDateZ000001 01/01/1996 9/30/1996Z000001 10/01/1996 12/31/1998Z000001 01/01/1999 04/30/1999Z000001 01/01/2000 12/31/2000Z000001 01/01/2001 12/31/2001I would like to be able to "roll these up" in order to reflect theactual time frames so that it would like this:ID StartDate EndDateZ000001 01/01/1996 4/30/1999Z000001 01/01/2000 12/31/20014/30/1999 to 01/01/2000 is, of course, a legitimate break where therewas a date difference of more than one day.Thanks in advance for any ideas.
View 7 Replies
View Related
Oct 4, 2001
Challenging Date Spans Problem:
A health insurance plan stores information about covered members in the following table:
CREATE TABLE enrollment
(
ssn CHAR(9) NOT NULL,
startdate DATETIME NOT NULL,
enddate DATETIME NOT NULL,
policy INTEGER NOT NULL,
CONSTRAINT pk_enrollment PRIMARY KEY CLUSTERED (ssn, startdate) ,
CONSTRAINT ck_noreversedate CHECK (enddate>=startdate))
INSERT enrollment
VALUES (111111111, 2000-01-01, 2000-04-30, 1)
INSERT enrollment
VALUES (111111111, 2000-06-01, 2001-12-31, 1)
INSERT enrollment
VALUES (222222222, 2000-01-01, 2000-06-30, 1)
INSERT enrollment
VALUES (222222222, 2000-07-01, 2001-12-31, 2)
INSERT enrollment
VALUES (333333333, 2000-01-01, 2000-06-30, 1)
INSERT enrollment
VALUES (333333333, 2000-07-01, 2001-12-31, 1)
INSERT enrollment
VALUES (444444444, 2000-01-01, 2000-06-30, 1)
INSERT enrollment
VALUES (444444444, 2000-07-01, 2000-11-30, 1)
INSERT enrollment
VALUES (444444444, 2001-03-01, 2001-06-30, 1)
INSERT enrollment
VALUES (444444444, 2001-07-01, 2001-12-31, 1)
Assume that there are no overlapping enrollments. We are able to enforce this at the time the records are inserted (e.g., through the front-end application).
Members can have multiple enrollments. These enrollments can be adjacent (e.g, member 222-22-2222 has one enrollment which terminates on 06/30/2000 and another that begins on 07/01/2000) or there can be gaps (e.g., member 111-11-1111 has one enrollment which terminates on 04/30/2000, the subsequent enrollment begins 06/01/2000).
Our task is to write a SELECT statement that will coalesce all adjacent enrollments where the policy is the same. E.g., member 333-33-3333 has two records in the source table, these records would be combined into a single enrollment with start date = 01/01/2000 and end date = 12/31/2001.
SELECT e.ssn, e.policy, MIN(e.startdate) AS startdate, MAX(e.enddate) AS enddate FROM enrollment e GROUP BY e.ssn, e.policy WHERE EXISTS (SELECT * FROM enrollment e2 WHERE e.ssn = e2.ssn AND e.policy = e2.policy AND e.startdate = DATEADD(y,1,e2.enddate)) OR EXISTS (SELECT * FROM enrollment e3 WHERE e.ssn = e3.ssn AND e.policy = e3.policy AND e.enddate = DATEADD(y,-1,e3.startdate))
UNION
SELECT e4.ssn, e4.policy, e4.startdate AS startdate, e4.enddate FROM enrollment e4 WHERE NOT EXISTS (SELECT * FROM enrollment e5 WHERE e4.ssn = e5.ssn AND e4.policy = e5.policy AND e4.startdate = DATEADD(y,1,e5.enddate)) AND NOT EXISTS (SELECT * FROM enrollment e6 WHERE e4.ssn = e6.ssn AND e4.policy = e6.policy AND e4.enddate = DATEADD(y,-1,e6.startdate))
This works for member 333-33-3333 who has only one set of adjacent enrollments.
However it fails for member 444-44-4444 who has two sets of adjacent enrollments. Can anyone suggest an approach that would return two rows for 444-44-4444, one with start date 01/01/2000 and end date 11/30/2000 and another with start date 03/01/2001 and end date 12/31/2001?
View 2 Replies
View Related
Oct 23, 2007
I need to identify time spans where members identified as having a condition have NOT had any of 5 specified services in the past 12 months. I have a table (DiabStrata) that identifies time frames for which my data shows a member as having the condition, and I have 5 separate tables with the dates of the relevant services.
I can easily identify when a member hasn't had the service at all, or is lacking it at the start or end of the time frame for which they have the condition, but I'm hitting a wall on how to deal with gaps between the minimum and maximum identification dates.
Code Block
create table dbo.DiabStrata(memberid char(11),Strat tinyint, StratStart datetime, StratEnd datetime)
create table dbo.hba1c(memberid char(11),dos datetime)
insert DiabStrata(
select '1',1,'20060101','20070302'
union
select '1',1,'20070803','20080804'
union
select '2',1,'20020101','20080503')
insert hba1c(
select '1','20060301'
union
select '1','20070301'
union
select '2','20050101')
--Missed Service
Begin
select * into #eval from DiabStrata where strat=1
delete #eval
from #eval left join hba1c on #eval.memberid=hba1c.memberid where hba1c.memberid is null
--repeat for other indicators
update e
set stratstart=min(dos)
from #eval e join hba1c on e.memberid=hba1c.memberid
having min(dos)>stratstart
update e
set stratend=max(dos)+365
from #eval e join hba1c on e.memberid=hba1c.memberid
having max(dos)+365<stratend
delete from #eval where stratstart>stratend
--repeat for other indicators
Desired output is into DiabStrata with a strat of 2 for the time frame for which they have strat 1 but do not have all 5 services within the prior 365 days.
MID Strat StartStrat EndStrat
1 2 1/1/06 - 2/28/06
1 2 3/2/08 - 8/4/08
2 2 1/1/02 - 12/31/04
2 2 1/2/06 - 5/3/08
View 1 Replies
View Related
Mar 7, 2006
Hi,
I need to import and transform data from an Excel spread sheet where the information spans two rows. The file layout is something like:
Row1Product1 QTY Store1 Store2 Store3 ...
Row2Product1 AMT Store1 Store2 Store3
Row3Product2 QTY Store1 Store2 Store3
Row4Product2 AMT Store1 Store2 Store3
The output would look like
Product1 Store1 QTY AMT
Product1 Store2 QTY AMT
...
Product2 Store1 QTY AMT
Product2 Store2 QTY AMT
...
We currently use a VB6 program using Office Tools to handle this. Is there a way to handle this with the out-of-the-box SSIS?
Thanks in advance,
John
View 6 Replies
View Related
Jul 5, 2007
If a single row of data spans a page then the column header is not repeated until the next row on the next page.
Is there a way to overcome this?
View 1 Replies
View Related
Jan 25, 2008
Hello!
I try to execute the below statement on a column, I've been trying on a row where subType3 contains this string ",31,32,34,23,55,54,39,44,51,52," without the ",. The column is declared as varchar(MAX)
SELECT subType3 FROM aTable WHERE CONTAINS(subType3, '32')
The above statement executes and returns the result as expected, BUT if I execute this:
SELECT subType3 FROM aTable WHERE CONTAINS(subType3, '55')
then nothing is returned. I can't see any real pattern, 32, 34 and 54 returns the row. If I try with any of the others nothing is returned....Why is this?
I sort of need to use contains, if I dont wan't to start chopping up the string and patindex or something like that. Patindex works by the way, but when I have many values then it gets a bit cumbersome using anything else than contains.
Thanks for any and all help,
Cheers!
/Eskil
View 2 Replies
View Related
Apr 25, 2000
Hi all !
I have a table where there are a number of columns with varchar(20) and varchar(255).
I am looking at a table with 4 varchar(255) and 10 varchar(20) 's.
I have to estimates the size of the table.I can do that given the rowsize and the number of rows in the table.
My problem is what is the kind of over head that I need to take into account when I am dealing with a table with soo many variable length columns when calculating the row size?
What is the over head difference between char datatye and varchar datatype?
I can't go for char datatypes now.
Help and info regarding this is very much appreciated.
regards
Sush.
View 1 Replies
View Related
Feb 13, 2007
Hello
Is there a way to get the column head in query output.
Thanks
View 3 Replies
View Related
Dec 8, 2005
Question:
How do I return a list of items that matches one or more criteria that I pass in?
Background:
A user enters a sales lead (a company is looking for a place to have their event). That lead has a number of
criteria elements (start date, end date, city, region, maximum room rate, one or more amenities, etc. - more
details below) that should be used when trying to find Hotels that match that criteria. Obviously, some
criteria is more important than others (city, start and end date are more important than the maximum room
rate) - and it's unlikely that many (if any) of the Hotels will match *all* of the criteria entered by the
user. So, I'm looking to return a list of Hotels that match at least one of the criteria - if possible,
ordered by how many criteria elements match.
What makes this query particularly difficult, is that some of the criteria to match are stored in multiple
tables. For example, each Hotel has "Amenities" (Golf, Spa, etc.) - that are stored in a seperate table.
When a user enters a lead, they select which amenities they want to match. Also, a lead specifies a number
of rooms to block for each day between the Arrival and Departure date - these numbers can change from day
to day - but for this query - I think it's acceptible to get the largest number of rooms needed from any of
the days and compare that one number against the "MaxDailyRoomBlock" field of a Hotel (represented by the
"Property" table). Also, since a Hotel has different rates defined for each season, the query will have to
match the "MaxRate" against the rate of the correct season based on the Arrival and Departure dates. Also,
the rate can be within 20% of the stated "MaxRate".
Here are the following variables that will be passed into the query as criteria items:
RequestCity, RegionINDEID, ArrivalDate, DepartureDate, MaxRate, MaxTheaterSeating, MaxBanquetSeating,
MaxSchoolSeating, MaxBreakoutRooms, MaxRoomBlock
I know this is a huge post - and I sincerly appreciate any help you can provide.
DDL for Tables:
*In the DDL.txt attachment
Sample Data:
* In the data.txt attachment
Previous Attempts:
Unfortunately, I don't even know where to begin, so I haven't tried anything yet.
Expected Results:
PropertyIDNameNumOfMatches
-----------------------------------------------
1Marriot San Diego5
2Hilton San Diego3
3Hilton San Diego Downtown2
Thanks in advance, again...
View 3 Replies
View Related
Aug 24, 2007
I am the IS manager for a medium business. I am also the DBA and the programmer and help desk and trainer etc etc.
In 2003 I embarked on a project which went live in 2005. We contracted to have our order processing system rewritten into SQL. I went from a character based 4GL language called Progress running in a UNIX enviroment to a knock down, drag out, full blown microsoft solution. I have been operating this new system for two years making modest enhancements and improvements as my skills with microsoft are increasing and improving (retraining my brain to think objects)and hiring contractors to do the really cool fun stuff.
I have to learn Server 2003, SQL Server 2005, SQL Express, T-SQL, Replication, Reporting Services, SSIS, IIS, DTS, Visual Source Safe, Visual Studio, VB, C#, ADO.NET and the .NET Framework fits in here too although I don't really know where.
I hope you are starting to understand my feeling of being 'in over my head'. Right now everything is running perfect. I do backups, restore them and create new reports. Basic stuff.
On top of this our company has merged with a larger one and this bigger company does not have a microsoft solution. They have an AS400 home grown application that I do not want to learn. I will not go backwards!!!!! I will loose my job/position in the next couple of years. I want certifications (to get that interview).
I just don't have the skills to back up the certification (I don't want to be one of those exam cram people) I do a plethora of unrelated taks all day long. Not because I have to do them I choose to do them. I like both DBA and Programming.
So, if you were me, and could get certifications and have experience to back them up what would you do first?
View 4 Replies
View Related
Dec 18, 2007
I have a table that contains categories, users, modifydates . All three of those fields may be repeated many times.
I need to build a table that contains user, modifydate, oldcategory, newcategory, duration (number of days between modifydates when there is a category change)
I can use the min(modifydate) to get the 1st occurrence of each new category, but I am having trouble populating the oldvalue and then also getting the duration.
Adding to the complexity, I have to take the last entry of a null category prior to category being populated the first time and populate it with the first occurrence of category, Likewise, I have to populate the first occurrence of null after the last occurrence of a popoulated category. In other words, this simulates populated categories prior to first occurrence and after the last occurrence.
Any help would be great.
View 6 Replies
View Related
May 1, 2002
I'm trying to write a script that will go against sysprocesses (I think) and find the head of a blocking chain, and how many SPIDs it is blocking on down the line.
I found a reference to a script called head_blockers.sql in the Swynk Scripts database (posted March 2000), but the script appears to no longer be available for download.
Any help would be appreciated!
Thank you,
Susan Jones
View 2 Replies
View Related
Jan 19, 2014
I have an existing function and need to alter function to give result of the parent-description until its parent is reached.
--CREATE TABLE
CREATE TABLE [dbo].[CityData](
[Id] [int] NULL,
[ParentID] [int] NULL,
[City] [nchar](20) NULL,
[Location] [nchar](50) NULL,
[Amt] [int] NULL
) ON [PRIMARY]
[Code] ....
View 3 Replies
View Related
Jan 24, 2008
Hi
I have a huge matrix with 8 columns and then all the data that's generated when choosing a date span. These 8 columns are Topic, Salesperson, Company and so on and then the right part of the matrix grows with values for each month.
My problem is that there are no column headers for the first 8 columns!! How come?
What I did to resolve this was to put in a table in the top left cell and then write in the corresponding column header. This works ¦ in IE but not in PDF or Excel. And I really need it when I export it to Excel.
Any ideas?
Kind regards.
View 5 Replies
View Related
Aug 21, 2007
Here's My scenario,
I am using SQL2k5 with sp2. In have three branch stores in my site each with SQL 2k5 and a master server at headoffice also with SQL 2k5. The sales transactions for each store goes to a table called sales transaction. What I need is that the info from the sales transaction of each branch populate the sales transaction table at the head office, but not vice versa. That is to say the sales transaction table at each branch must have their information alone and no information from any other branch. Only the head office must have sales transactions from all branches in it's table. What type of replication do i use and how do i implement it?
Muchas muchas gracias.
View 3 Replies
View Related
Jan 1, 2008
Hi
Just started using Visual Studio Express 2005 (Web) with Server 2005 Express and I am trying to extract the data from a SQL but cannot find how to connect to and open a SQL recordset so as to use the values of a single record dotted around a page. I would be using the values in the <Head> and <body> areas of the page. The body of the page would utilise standard data grids for sub and sub data, this being no problem.
Below is how I would have retrieved the "EndDate" value from an Access databse under asp.
Set Conn=Server.CreateObject("ADODB.Connection") Set rs=Server.CreateObject("ADODB.RecordSet") Conn.Open "DBQ=" & Server.MapPath("../private/Drapers.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};" rs.open "SELECT * FROM Products where ID = 44", Conn, 3, 3 rs("Metta1") rs("Metta2")
The following is from the Web.config file
<connectionStrings><add name="ConnectCB" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|CBBasic.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/></connectionStrings>
I have tried lots of things that were either code that was superceeded or just plainly didn't work.
Any help would be appreciated.
John
View 3 Replies
View Related
Jun 6, 2000
On our datawarehouse server we are regularly having a 1203 error, causing the sql-server to hang. We get this message in the errorlog: Failed Assertion = 'm_activeSdesList.Head () == NULL'. In the knowledgebase I found a bug description that is very lookalike to our problem.
Article: Q240853 FIX: Lock Escalation With Parallel Query May Cause 1203 Error And Server Shutdown
*** part of the article ***
SYMPTOMS
If a lock escalation occurs while running a parallel query, it is possible to encounter error message 1203 as follows:
spid7 Process 7 unlocking unowned resource: KEY: 13:117575457:2 (35010560ebcd)
spid7 Process 7 unlocking unowned resource: KEY: 13:117575457:2 (35010560ebcd)
spid7 Error: 1203, Severity: 20, State: 1
spid7 Process ID 7 attempting to unlock unowned resource KEY: 13:117575457:2 (35010560ebcd).
spid7 Error: 1203, Severity: 20, State: 1
spid7 Process ID 7 attempting to unlock unowned resource KEY: 13:117575457:2 (35010560ebcd).
The error message included in the error log probably mentions the same lock resource in several of the error messages.
Once the error is printed, an assertion message similar to the following is also printed:
1999-08-09 13:15:26.79 kernel SQL Server Assertion: File: <proc.c>, line=1866 Failed Assertion = 'm_activeSdesList.Head () == NULL'.
After a dump of the stack for all threads, the server initiates a shutdown of the SQL Server process.
...
*** end ***
You can find the complete article on: http://support.microsoft.com/support/kb/articles/q240/8/53.asp?LN=EN-US&SD=gn&FR=0
We can't use the workaround, because that would shut out parallelism, which is necessary for the project.
There is a fix, but in the article Microsoft says: "A supported fix that corrects this problem is now available from Microsoft, but it has not been fully regression tested and should be applied only to systems experiencing this specific problem.".
You understan,d this is not one of my favorite type of fixes...
Does anyone have already installed the fix mentioned? Had any problems with it, or did it cause some other troubles?
Thx,
Kurt De Cauwsemaecker
Database Administrator
Telepolis Antwerpen
View 1 Replies
View Related
Feb 25, 2004
I'm using PHP with SQLServer2k to create a page containing monthly counts of episodes at a facility occurring between two user selected month/year combinations. For instance, the user could select 10/2003 and 2/2004 and facility X and get a line for each month showing the count of episodes occuring in that month.
The problem is that the episode date is stored in three integer fields (epiday, epimonth, epiyear) and I'm having a terrible time getting them into a format where I can use them in a between statement.
I've tried evaluating the parts of the episode date seperately like:
where
(epimonth>=10 and epiyear=2003)
or
(epimonth<=2 and epiyear=2004)
and that works, but what happens when someone wants to see from 10/2002 to 2/2004?
Any suggestions on the best way to do this?
View 5 Replies
View Related
Nov 8, 2004
I have 2 tables joined together by the IDs, People and the pets they
own
PEOPLE
ID NAME
1 JohnSMith
2 JaneDoe
PETS
ID PET
1 Dog
2 Cat
2 Hamster
2 Hamster
2 Fish
I have create another where the PETS are in one column separated by
semi-colons and removing the dups
NEW TABLE
ID NAME ALLPETS
1 JohnSmith Dog
2 JaneDoe Cat;Hamster;Fish
What is the best way to do it? The only way I can think of is to run
an update where it checks to see if the value already exists
THanks!
View 1 Replies
View Related
Mar 18, 2008
Hello,
I have four diffrent tabels:
bo_ Class, bo_Competition, bo_Result, bo_Licence
And list all Licence where bo_Class.classRankingNbr >0
ResultLicNbr FirstName SurName ClassName ResultPlace ClassRankingNbr ClassRankingFemaleMale
------------ --------- --------------- ------------- ----------- --------------- ----------
M70891DEN03 Dennis Vrabac U23 SM 2007 - Herrar 1 1 H
M050887PON01 Pontus Svensson U23 SM 2007 - Herrar 2 1 H
M181188MAR01 Marcus Edlund U23 SM 2007 - Herrar 3 1 H
M190291JOH01 Johan Helldén U23 SM 2007 - Herrar 4 1 H
M180360GER01 Gert Lindholm Herrar klass A 1 2 H
M041062MIC01 Micael Hamberg Herrar klass A 2 2 H
K191286SOP01 Sophia Bergvall U23 SM 2007- Damer 1 1 D
K030889REB01 Rebecka Larsen U23 SM 2007- Damer 2 1 D
K050785CAR01 Carin Johansson U23 SM 2007- Damer 3 1 D
If bo_Class.ClassRowNbr for an Class is 1 get out place 1 from that competition,
and if an ClassRowNbr is 2 get out top2 from that competition.
And so on.
From that list i want an SubQuery list where all licens order by where
bo_Result.ResultPlace = bo_Class.ClassRowNbr.
In text form:
ResultLicNbr FirstName SurName ClassName ResultPlace ClassRankingNbr ClassRankingFemaleMale
------------ --------- --------------- ------------- ----------- --------------- ----------------------
M170891DEN03 Dennis Vrabac U23 SM 2007 - Herrar 1 1 H
M180360GER01 Gert Lindholm Herrar klass A 1 2 H
M041062MIC01 Micael Hamberg Herrar klass A 2 2 H
K191286SOP01 Sophia Bergvall U23 SM 2007- Damer 1 1 D
K030889REB01 Rebecka Larsen U23 SM 2007- Damer 2 1 D
K180793LIN01 Linnéa Hamberg Damer Klass A 1 1 D
Hope someone can take the time and help me out.
Best regards
Gert Lindholm
View 6 Replies
View Related
Oct 18, 2005
One statistic questiong from an db Resultat.
SELECT m.Namn + ' ' + m.ENamn AS Spelare, SUM(r.Serier) AS Ser, SUM(r.Poang) AS Po, ROUND(SUM(r.Resultat) / SUM(r.Serier * 1.0), 2, 1)AS Snitt, ROUND(SUM(r.Poang * 1.0) / SUM(r.Serier), 2) AS [P Snitt], ROUND(SUM(r.Miss * 1.0 / r.Serier), 2, 1) AS Miss, SUM(r.Miss) AS [Sa Miss], MAX(r.Resultat) AS BΓ€sta, MIN(r.Resultat) AS SΓ€msta
FROM Resultat r INNER JOIN
Medlemmar m ON r.Spelare = m.ID
WHERE (r.Omgang IN (SELECT DISTINCT TOP 3 Omgang
FROM Resultat
ORDER BY Omgang DESC))
GROUP BY m.Namn + ' ' + m.ENamn
ORDER BY 4 DESC
What i want to do is sort out Min(r.Resultat) where serier is mor than 3, Not WHERE (r.Resultat) >3. More like in Access "Min(IIf(r.serier=4,r.resultat,Null)) AS [SΓ€msta]" But that i cant do in SQL
Have also tryed with "WHERE (SELECT MIN(r.Serier) FROM Resultat
Resultat r INNER JOIN
medlemmar m ON r.Spelare = m.ID"
Get lowest result same on all players.
Best reg
Gerten
View 2 Replies
View Related
Feb 7, 2006
Hello allI've got this tricky situation that I would like to solve in SQL, butdon't know how to do. This is the table:Id = 3, VId = 2, Time1 = 10:00, Time2 = 14:00Id = 4, VId = 2, Time1 = 16:00, Time2 = 17:00Id = 5, VId = 2, Time1 = 18:00, Time2 = 19:00Id = 6, VId = 2, Time1 = 20:00, Time2 = 21:00Id = 7, VId = 3, Time1 = 11:00, Time2 = 13:00Id = 8, VId = 3, Time1 = 15:00, Time2 = 16:00Id = 9, VId = 3, Time1 = 18:00, Time2 = 20:00GetRows @Time='15:30' will return row with Id=4GetRows @Time='16:30' will return row with Id=4 and row=9Logic behind this:Return row n where Time2 of Id=(n-1) < @Time < Time 1 of Id=(n) and sameVId.Ie. if @Time = '15:30' then Time2 of Id = 3 is lower than @Time, andTime1 of Id = 4 is higher than @Time => return row with Id = 4.This got a bit messy but if someone could decipher this and possiblygive an answer I'd be very glad.regardsJohnny
View 5 Replies
View Related
Jan 28, 2008
I have an integer in the database that was saved in reverse byte order (BigEndian). Due to some backward compatibility issues (long story) I can't just convert the number to the normal format and save it that way in the database.
Instead, when I read the number in my program, I just reverse its bytes and display the proper value, and translate the number back when it has to be saved back to the database.
Now, the problem is that there are some views that pull this numbers directly from the database and display it.
My question is: can this number be converted from BigEndian to LittleEndian similarly to how I do in my program using T-SQL?
Thanks.
View 4 Replies
View Related
Jul 18, 2007
I have a LastName field which holds this dataLastNameJohnson|VasquesAdams|Fox|JohnsonVasques|Smith Now letβs say I have a SELECT Stored Procedure which takes 1 parameter @LastName.The @LastName can be something like this: βFox|Smithβ?.I would like to have my SP to return me all of the records where LastName field have any of those names (Fox or Smith).In this example it will be the last two records: Adams|Fox|Johnson and Vasques|Smith . Thank you.
View 14 Replies
View Related
Dec 13, 2007
This is more of a SQL question than a .NET question, but if you could indulge me, I'd appreciate it.
I have a table that has 2 columns of particular interest for the purposes of this question. One is a foreign key to another table (int), the other is a name (varchar(50). I want to sort the results set in a specific way. I want to sort it in such a way that all entries that have the foreign key = 0 come first (sorted ASC by name) then I'd like all the other results with foreign key column > 0 to be sorted ASC by name. I was trying to be cute and tried an order by statement like this: "ORDER BY (foreignKey > 0), name" but it's a syntax error (as I initially thought it might be). I know I could probably do a stored procedure that will create a temporary table and I could insert a new column to help put these in order, and I also know I could put all the results into an array, then sort the array in code, but I was just wondering if there was a simpler, slicker way (tricky SQL query perhaps).
View 2 Replies
View Related
Mar 21, 2004
I have a table as follows:
Fixtures(ID, HomeTeam, AwayTeam, WeekNumber)
Each team plays alternately at home then away throughout the course of a season.
I want perform 2 seperate queries on this table.
Query 1:
I want to select a particular teams opposition for the entire season.
Query 2:
I want to select a particular teams opposition for a particular weekNumber.
Thanks
View 3 Replies
View Related
Oct 6, 2004
I have a stored procedure called TC3_GetAllJobOrders which takes 8 parameters as filter values and dynamically builds a statement to filter the data. If you pass in null values for the filters, then the data is not filtered.
I want to write another SP called TC3_GetNewestJobOrders which takes 9 parameters. The first 8 parameters are the same as TC3_GetAllJobOrders and the 9th parameter is numRecs which defines the number of records to return. The procedure should call TC3_GetAllJobOrders, sort the data by date and then return the top numRecs. However, I can't figure out the best way to write this stored procedure because it references another one.
I am trying to stay away from dynamic SQL if at all possible. But I am thinking I will have to use dynamic SQL because I don't think the number of records to be returned (as defined using the TOP keyword) can be parameterized. However, I was trying to write a dynamic SQL statement so that the end statement looked something like:
SELECT TOP 10 * FROM ( EXECUTE TC3_GetAllJobOrders ... ) ORDER BY createdOn DESC
However, I guess having the EXECUTE in parens like that is no good and SQL Server doesn't like it. What is the best/correct way to do it?
View 4 Replies
View Related
Mar 10, 2005
Hello,
I have a SQL statement which is working OK:
SELECT diakod,diatexter,skada FROM (SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl = @Kod) UNION ALL SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl =@Kod2)) AS SQ GROUP BY diakod,diatexter,skada HAVING COUNT(*) > 1
I need to have a third selector, I have used the following code :
SELECT diakod,diatexter,skada FROM (SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl = @Kod) UNION ALL SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl =@Kod2) UNION ALL SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl =@Kod3)) AS SQ GROUP BY diakod,diatexter,skada HAVING COUNT(*) > 1
This is giving me to many answers, does anyone have any good suggestions to improve it?
View 1 Replies
View Related
Mar 7, 2000
I have 2 tables which are related to each other, each having a Foreign Keys of the other table . When I delete company table, it gives me an error that I'm violating a FK constraint of the table owner. When I try to delete employee, it gives the same error.
CREATE TABLE OWNER {
employee_id PK
company_id - this is a FK of COMPANY
}
CREATE TABLE COMPANY{
company_id PK
owner_id - this is a FK of OWNER
}
Must I drop the constraints before I can delete? I don't want to do that because I don't want so many other tables are dependent on those tables.
Joyce
View 2 Replies
View Related
Feb 9, 2001
I'm developing a c++application with connections to a database, and got a little problem with the construction of a specific SQL Query. I was hoping that some of you guys maybe could help me out...
the problem is:
The table, table1, has two cols: Key and Item wich contains numbers only. Both are set to primary keys.
I want to find the records where Keys values 1, 2 has the same Item value
(and if they don't I don't want to find any post at all, of course)
something like this:
SELECT * FROM table1
WHERE ???
ex of table1:
Key | Item
----------
1 | 1
1 | 2
2 | 2
3 | 1
please help...
View 3 Replies
View Related
May 13, 2003
Hi, I'm tring to break my head finding a solution of how to return the following result:
I have the source table -> S_TAB with some data like
COL1 COL2 COL3
ABC DTT COL
ANC DRT COL
ANC DRT COL
......
......
what I need is come up with a single select statement (a view) to get the following output:
select <something> as RID, COL1, COL2, COL3 from S_TAB
RID COL1 COL2 COL3
1 ABC DTT COL
2 ANC DRT COL
3 ANC DRT COL
4 .....
5 .....
Any Idea will be appreciated
Dim
View 2 Replies
View Related
