Complex Date Computations
Mar 6, 2008
I have a table the looks like this :
IDVar1 Date1
-- --- -------
11018-Feb-02
11123-Dec-02
23027-Nov-02
35009-Oct-02
46025-Mar-02
46310-May-02
55230-May-02
66907-Feb-02
67426-Feb-02
68023-Sep-02
69009-Oct-02
712301-Feb-02
712910-Dec-02
85612-Jan-02
91004-Feb-02
91518-Feb-02
91930-Sep-02
92012-Oct-02
102103-Oct-02
102308-Oct-02
102430-Nov-02
I want to create a table like this :
IF the date (Date1) diffrence between any two dates for the same ID is less than 30 Days CNT1 = 1, ELSE CNT1 =0
IF the date (Date1) diffrence between any two dates for the same ID is less than 60 Days CNT2 = 1, ELSE CNT2 =0
IF the date (Date1) diffrence between any two dates for the same ID is less than 90 Days CNT3 = 1, ELSE CNT1 =0
Note : CNT1,CNT2 and CNT3 for ID =2,3,5 and 8 should be 0 because there are no multiple recors for the same ID
ID CNT1 CNT2 CNT3
-- ---- ---- ----
1
2
3
4
5
6
7
8
9
10
Any help will be welcomed.
View 6 Replies
ADVERTISEMENT
Apr 17, 2008
hi need help to solved date calculation for next month
i explain
i have table employee and the employee insert into table the holidays
the date start >>>> to date end
now i need to create a view only for next moth , in this view i need to see only the relative dates for the next month if the dates is not for the next month i don't need to see it
like this example 09/07/2008 > 09/08/2008 (is not for next month)
like this example 10/09/2008 > 12/09/2008 (is not for next month)
555
EEE
09/07/2008
09/08/2008
4
666
fff
10/09/2008
12/09/2008
1
in this example i need to see only the relative dates for next month only in the view
333
cccc
01/05/2008
15/05/2008
4
4
333
cccc
01/05/2008
31/05/2008
1
30
tb_all_holiday before
id
fname
Start_Date
EndDate
val_holiday
111
aaaa
15/03/2008
10/05/2008
1
222
bbbb
02/05/2008
31/05/2008
3
333
cccc
03/04/2008
15/05/2008
4
333
cccc
29/04/2008
07/07/2008
1
444
dddd
01/05/2008
02/05/2008
1
444
dddd
09/05/2008
19/08/2008
1
555
EEE
09/07/2008
09/08/2008
4
666
fff
10/09/2008
12/09/2008
1
VIEW_all_holiday after -next month only
id
fname
Start_Date
EndDate
val_holiday
sum_day_next_month
111
aaaa
01/05/2008
10/05/2008
1
4
222
bbbb
02/05/2008
31/05/2008
3
29
333
cccc
01/05/2008
15/05/2008
4
4
333
cccc
01/05/2008
31/05/2008
1
30
444
dddd
01/05/2008
02/05/2008
1
1
444
dddd
09/05/2008
31/05/2008
1
22
all the time i need to see only the relative dates for the next month only
tnx
View 10 Replies
View Related
Jan 20, 2004
I hope I explain myself clear enough. I have an integer field of date values: 20031231. Some of the values in the field are zero. I want to convert the integer to 12/31/2003. Right now I am doing it with 2 views. The first view takes the zeros and converts them to null by using case. The second view uses convert to make it into the date string I want. Is there some way I can do it all in one view?
Thanks
View 14 Replies
View Related
Jan 8, 2008
Hi,
Can someone please guide me how to extract this particular data. It is quite complex, the raw data is not in a good shape and I am not sure if it is even possible. For simplification, I am copying a sample of only 3 columns from my table.
ID FromDate ToDate
01 06/09/2006 06/09/2006
02 06/09/2006 06/12/2006
03 06/09/2006 06/12/2006
04 06/09/2006 06/12/2006
05 06/09/2006 06/12/2006
06 06/09/2006 06/12/2006
07 06/10/2006 06/10/2006
08 06/12/2006 06/12/2006
09 08/30/2006 09/05/2006
10 08/30/2006 09/05/2006
11 08/30/2006 09/05/2006
12 09/13/2006 09/17/2006
13 09/13/2006 09/26/2006
14 09/13/2006 09/26/2006
15 10/05/2006 10/10/2006
16 10/05/2006 10/10/2006
17 10/10/2006 10/15/2006
18 10/11/2006 10/11/2006
19 10/16/2006 10/16/2006
20 10/20/2006 10/22/2006
21 10/23/2006 10/25/2006
From and to date represent a duration of an event, but in some instances the data has multiple lines but they are all to be considered one event. For example all the from dates (row 1 to 8) in Oct 06 are covered by ID/row 05 because 6/9/06 to 6/12/06 is one event, all other rows of June 06 data are just redundant and not needed in the resultset. There isn't a unique event name either so comparing and picking row 05 just based on the above data is an issue.
Another problem is for example the dates in October (10/5 - 10-16) all represent 1 event but the date span is spread all over. Multiple events are also possible in one month, so gettign MIN and MAX based on the month will not work.
The desired result set need to have duration of each unique event for example (1 in June, 1 in August, 1 in Sept, and 1 in Oct) and also need to have the number of events after that date and the calculated from and to date of that event:
ID FromDate ToDate Events after this date NextEventFromDate NextEventToDate
05 06/09/2006 06/12/2006 4 08/30/2006 09/05/2006
09 08/30/2006 09/05/2006 3 0913/2006 09/26/2006
13 09/13/2006 09/26/2006 2 10/05/2006 10/16/2006
? 10/05/2006 10/16/2006 1 10/20/2006 10/25/2006
? 10/20/2006 10/25/2006 0
View 3 Replies
View Related
Sep 21, 2004
This concerns eligibility healthcare information. A member can have multiple rows in the table showing they are eligible for different date ranges with different health plans. eff_date and term_date are the fields in this table. Term_date can be NULL.
I need a WHERE statement that shows members
1) eligible between 1/1/2004 and 8/15/2004
(term_date is null or term_date >= '8/15/2004')
and eff_date <='1/1/2004'
2) members are allowed only one gap in this timeframe of up to 45 days. *NEED HELP
3) a gap of 1 day should not be counted as a gap in enrollment. *NEED HELP
Any help on #2 and #3 would be appreciated.
View 1 Replies
View Related
Mar 5, 2008
Hi,
I have a table with date ranges for activitys. I need to get the userid , firstdayidle, lastdayidle for when a user has been idle in the last 90 days.
it gets tricky since it also must include users that have no records in activity table or only one record so it has 2 idle periods, etc
I have a hard time wrapping my head around the logic, weither using a datetables or not, so any help would be appreaciated
--my first step is to get the activitys that enter the last 90 days
select * from Activity2
where (enddate > DateAdd(d, -90, getDate()) AND enddate < getDate()) OR (startdate > DateAdd(d, -90, getDate()) AND startdate < getDate())
CREATE TABLE [dbo].[InternalUser](
[userid] [int],-- IDENTITY(1,1) NOT NULL,
[fullname] [varchar](50)
}
insert into [InternalUser] values(1,'a')
insert into [InternalUser] values(2,'b')
insert into [InternalUser] values(3,'c')
insert into [InternalUser] values(4,'d');
insert into [InternalUser] values(5,'e');
insert into [InternalUser] values(6,'f');
CREATE TABLE [dbo].[Activity2](
[activityid] [int] NOT NULL, --IDENTITY(1,1)
[userid] [int] NOT NULL,
[startdate] [datetime] NULL,
[enddate] [datetime] NULL
)
insert into [Activity2] values(1,1,'2007-02-15 00:00:00.000','2008-03-15 00:00:00.000')
insert into [Activity2] values(2,2,'2007-01-01 00:00:00.000','2008-01-02 00:00:00.000')
insert into [Activity2] values(3,2,'2008-01-20 00:00:00.000','2008-04-10 00:00:00.000')
insert into [Activity2] values(4,3,'2008-02-20 00:00:00.000','2008-10-10 00:00:00.000')
insert into [Activity2] values(5,4,'2007-01-01 00:00:00.000','2008-01-16 00:00:00.000')
View 5 Replies
View Related
Dec 7, 2006
Hello all,
I am new to SSIS, so I am hoping there is an easier way to do this...
I need to evaluate a date in a field and determine if it is between the beginning and end of whatever the current month is... In Access, this was written as something like:
IIF(datevalue >= CDate(Format(Now(),"mm/01/yy")) AND datevalue < CDate(Format(DateAdd("m",1,Now()), "mm/01/yy)), value1, value2)
Trying to recreate this in SSIS using expressions during a derived transformation has been extremely difficult. Here is what I came up with:
(DUE_DATE >= (DT_DATE)( (DT_WSTR,2)MONTH(GETDATE())+"/01/"+ (DT_WSTR,2)YEAR(GETDATE()))) && (DUE_DATE<(DT_DATE)( (DT_WSTR,2)MONTH( DATEADD("m",1,GETDATE()) )+"/01/"+(DT_WSTR,2)YEAR( DATEADD("m",1,GETDATE() )))) ? value1 : value2
Any help you all could give would be appreciated.
Thanks!
Josh
View 7 Replies
View Related
Nov 12, 2003
I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.
Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.
I build clauses like this (i.e., 4 fields shown):
SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )
My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.
View 7 Replies
View Related
Sep 21, 2006
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
View 3 Replies
View Related
Apr 26, 2006
Hello,
Lets look at this table :
CREATE TABLE [dbo].[TableHisto](
[Id] [int] NOT NULL,
[Week] [nvarchar](50) COLLATE French_CI_AS NULL,
[Project] [int] NOT NULL
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identifiant d''enregistrement' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'TableHisto', @level2type=N'COLUMN', @level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date de l''enregistrement' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'TableHisto', @level2type=N'COLUMN', @level2name=N'Week'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Projet de référence' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'TableHisto', @level2type=N'COLUMN', @level2name=N'Project'
It is a table where i store projects week reports.
I want to make a request to display a table with project ID in Row, Weeks in columns and either TableHisto.id or Null value in cell.
I use SQL 2005. Thanks for any help
View 9 Replies
View Related
Sep 3, 2006
I need some help on how to structure a sql statement. I am creating a membership directory and I need the stored procedure to output the Last Name, First Name (and if married) Spouse First Name. Like this Flinstone, Fred & Wilma All members are in one directory linked by two fields. [Family ID] all the family members have the same family id and then there is a Family position id that shows if they are the Husband, Wife or Kids. I have no problem with this part select (LastName + ',' + FirstName) as Name, [Phone 1] as Phone, [Unit Name] as WD, [Street 1] as Street, SUBSTRING(City,1,3) as City, SUBSTRING(Postal,1,5) as Zipfrom Membership Where [HH Order]=1 Order By LastName ASC Could someone help me on how to display the " & Spouse FirstName " as part of the name field only if there is a spouse [HH Order]=2 for the current [Family ID]????
View 6 Replies
View Related
Nov 13, 2006
I need to get multiple values for each row in a database, then do a calculation and insert the calculation and the accountnumber related to the calculation the data, into a different column. I get an error trying it this way...there is no real identifier, it is jsut something that needs to get done per row...any ideas on how I can accomplish this?
Declare @NetCommission decimal
Declare @AccountNumber varchar(50)
Set @NetCommission = (select (CommissionRebate * Quantity)
from Account A
Join Trades T on A.AccountNumber = T.AccountNumber)
Set @AccountNumber = (select A.AccountNumber
from cmsAccount A
Join Trades T on A.AccountNumber = T.AccountNumber)
Insert into Transaction
(
Payee
,Deposit
,AccountNumber
)
Values
(
'Account Credit'
,@NetCommission
,@AccountNumber
)
View 13 Replies
View Related
Dec 1, 2006
Hello,
could someone help with this query in a stored proc.?
SET @SQL = 'SET ''' + @avgwgt + ''' = '
'(SELECT AVG(AverageWeight)
FROM CageFishHistory where CageID IN (' + @cagearray + ')
and ItemDate =''' + CONVERT(varchar(23),@startdate) + ''')'
EXEC @SQL
I'm trying to get an average value across dynamically selected rows. (I'm using a list array to deliver the selection to the stored proc). I need to re-use the average value within the procedure,so it's not enough to output it as a column of the resultset - EG. 'Select AVG(AverageWeight) as AvgWgt' . If I take out the @avgwgt line it works fine, but otherwise I'm getting this error:
"Incorrect syntax near '(SELECT AVG(AverageWeight)
FROM CageFishHistory where CageID IN ('."
It may be that I can access a column of the resultset in the rest of the procedure, and that would help avoid the use of pesky apostrophes, but I don't know how to do it.
View 3 Replies
View Related
Jan 6, 2007
I've written a lot of queries in the past, but I'm having a lot of trouble with this one.
View 4 Replies
View Related
Mar 19, 2007
I'm sure there is a way of cracking this, but I can't think of a good solution. Right now I am not happy with the solutions I come up with, one of which takes 4 minutes to run on SQL Server
The scenario: User is presented with search page where one or more search terms can be entered/selected. There are no required parameters. It can be any or all of the possibilities presented. Below is a model of the search parameters presented.
The user will either select to show more options under Profile ABC, or go down to Profile STU or Profile XYZ to show more options, or even select all Profiles and then select from Type 1 and either a. or. b. or. c. or ALL of the above.
I cannot predict what a user will make part of the search query so I have to have a stored procedure ready which can handle any or all of the parameters a user may select.
Am I biting off more than I can chew (it seems so)? Or is there an elegant way of handling the unknown combination of search parameters that a user might throw into my sql query?
I'm running this under ASP 1.0 and SQL Server 2000.
[check to show the options below] Profile ABC
[check to shore more options] Type 1
A. Contains fields for entering another data string and selecting from drop-down boxes
B. ditto
C. ditto
D. ditto
[check to show more options] Type 2
A. Contains fields for entering another data string and selecting from drop-down boxes
B. ditto
C. ditto
D. ditto
[check to show more options] Type 3
A. Contains fields for entering another data string and selecting from drop-down boxes
B. ditto
C. ditto
D. ditto
[check to select more options] Type 4
A. Contains fields for entering another data string and selecting from drop-down boxes
B. ditto
C. ditto
D. ditto
[check to show more options] Profile XYZ (as above)
[check to select more options] Profile STU (as above)
View 6 Replies
View Related
Sep 7, 2007
I'm working on a system that used to load control dynamically into a table structure based on "Row" and "Column" properties in the item object.
The system is now being revamped, and instead of a table structure, it's being loaded into a list, which will be controled by css. The new relevant variables are "Sequence" and "Width."
Since there are already thousands of existing items in the database, I have to write a script that can take a really good guess at legacy items' Row and Col, and input values for Sequence and Width.
Since all items exist on "tabs," I can query for all items on a given tabID, Ordered By Row, Col -- that will give me a sequence.
Width isn't literal, it has 6 presets: Whole, Half, Third, Quarter, Two Thirds, Three Quarters, represented in the table as 0,1,2,3,4,5 -- for our purposes, I'll assume that all items on a row are equal in width. We can determine width by figuring out the number of items within the same row, so if there is only one in the row, it's a Whole (0), if there are three in the row it'll be a Third (2), etc.
I'd like to create a query that gets all items by tab, assigns the appropriate sequence, and figures out how many items are in the row with a given item, to assign the correct width.... but I have no idea how to make t-sql do that. I don't mind multiple queries to get the whole process done, and it doesn't need to be efficient -- this is a one-off script to run to give legacy items values that we can work with.
Where would I start?
View 1 Replies
View Related
Oct 30, 2007
HI.
I have 3 tables
1- std with : stdID , programID.
2- Programs with :ProgramID , Cost
3 - Movements with : stdID , balance.
the first table contain the stdID and ProgramID , some times the std hasn't programID that mean he hasn't programID. then we return null.
if the std has programID there is to cases.
the first one he have a movement on his balance then we get the biggest balance for the std.
the second case he hasn't any moventen then we get his balance from Programs table by the ProgramID .
I need sql server function that return table like this
stdID , Balance
that means every std with his Balance.
Regards.
View 11 Replies
View Related
May 13, 2008
This is too complex anyone know how to make it less complex.
I am trying to get all the selected fields from contacts into a datagrid where the other fields contain a string in textbox1.
This works
SELECT [company], [contactname], [emailaddress], [secondemailaddress], [phonenumber], [webpage] FROM [contacts] WHERE (([AB] LIKE '%' + ? + '%') AND ([AL] LIKE '%' + ? + '%'))
When i add all the rest of the fields it says its too complex. Please Help
SELECT [company], [contactname], [emailaddress], [secondemailaddress], [phonenumber], [webpage] FROM [contacts] WHERE (([AB] LIKE '%' + ? + '%') AND ([AL] LIKE '%' + ? + '%') AND ([B] LIKE '%' + ? + '%') AND ([BB] LIKE '%' + ? + '%') AND ([BD] LIKE '%' + ? + '%') AND ([BA] LIKE '%' + ? + '%') AND ([BH] LIKE '%' + ? + '%') AND ([BL] LIKE '%' + ? + '%') AND ([BN] LIKE '%' + ? + '%') AND ([BR] LIKE '%' + ? + '%') AND ([BS] LIKE '%' + ? + '%') AND ([BT] LIKE '%' + ? + '%') AND ([CA] LIKE '%' + ? + '%') AND ([CB] LIKE '%' + ? + '%') AND ([CF] LIKE '%' + ? + '%') AND ([CH] LIKE '%' + ? + '%') AND ([CM] LIKE '%' + ? + '%') AND ( LIKE '%' + ? + '%') AND ([CR] LIKE '%' + ? + '%') AND ([CT] LIKE '%' + ? + '%') AND ([CV] LIKE '%' + ? + '%') AND ([CW] LIKE '%' + ? + '%') AND ([DA] LIKE '%' + ? + '%') AND ([DD] LIKE '%' + ? + '%') AND ([DE] LIKE '%' + ? + '%') AND ([DG] LIKE '%' + ? + '%') AND ([DH] LIKE '%' + ? + '%') AND ([DL] LIKE '%' + ? + '%') AND ([DN] LIKE '%' + ? + '%') AND ([DT] LIKE '%' + ? + '%') AND ([DY] LIKE '%' + ? + '%') AND ([E] LIKE '%' + ? + '%') AND ([EC] LIKE '%' + ? + '%') AND ([EH] LIKE '%' + ? + '%') AND ([EN] LIKE '%' + ? + '%') AND ([EX] LIKE '%' + ? + '%') AND ([FK] LIKE '%' + ? + '%') AND ([FY] LIKE '%' + ? + '%') AND ([G] LIKE '%' + ? + '%') AND ([GL] LIKE '%' + ? + '%') AND ([GU] LIKE '%' + ? + '%') AND ([GY] LIKE '%' + ? + '%') AND ([HA] LIKE '%' + ? + '%') AND ([HD] LIKE '%' + ? + '%') AND ([HG] LIKE '%' + ? + '%') AND ([HP] LIKE '%' + ? + '%') AND ( LIKE '%' + ? + '%') AND ([HU] LIKE '%' + ? + '%') AND ([HX] LIKE '%' + ? + '%') AND ([IM] LIKE '%' + ? + '%') AND ([IP] LIKE '%' + ? + '%') AND ([IV] LIKE '%' + ? + '%') AND ([JE] LIKE '%' + ? + '%') AND ([KA] LIKE '%' + ? + '%') AND ([KT] LIKE '%' + ? + '%') AND ([KW] LIKE '%' + ? + '%') AND ([KY] LIKE '%' + ? + '%') AND ([L] LIKE '%' + ? + '%') AND ([LA] LIKE '%' + ? + '%') AND ([LD] LIKE '%' + ? + '%') AND ([LE] LIKE '%' + ? + '%') AND ([LL] LIKE '%' + ? + '%') AND ([LN] LIKE '%' + ? + '%') AND ([LS] LIKE '%' + ? + '%') AND ([LU] LIKE '%' + ? + '%') AND ([M] LIKE '%' + ? + '%') AND ([ME] LIKE '%' + ? + '%') AND ([MK] LIKE '%' + ? + '%') AND ([ML] LIKE '%' + ? + '%') AND ([N] LIKE '%' + ? + '%') AND ([NE] LIKE '%' + ? + '%') AND ([NG] LIKE '%' + ? + '%') AND ([NN] LIKE '%' + ? + '%') AND ([NP] LIKE '%' + ? + '%') AND ([NR] LIKE '%' + ? + '%') AND ([NW] LIKE '%' + ? + '%') AND ([OL] LIKE '%' + ? + '%') AND ([OX] LIKE '%' + ? + '%') AND ([PA] LIKE '%' + ? + '%') AND ([PE] LIKE '%' + ? + '%') AND ([PH] LIKE '%' + ? + '%') AND ([PL] LIKE '%' + ? + '%') AND ([PO] LIKE '%' + ? + '%') AND ([PR] LIKE '%' + ? + '%') AND ([RG] LIKE '%' + ? + '%') AND ([RH] LIKE '%' + ? + '%') AND ([RM] LIKE '%' + ? + '%') AND ([S] LIKE '%' + ? + '%') AND ([SA] LIKE '%' + ? + '%') AND ([SE] LIKE '%' + ? + '%') AND ([SG] LIKE '%' + ? + '%') AND ([SK] LIKE '%' + ? + '%') AND ([SL] LIKE '%' + ? + '%') AND ([SM] LIKE '%' + ? + '%') AND ([SN] LIKE '%' + ? + '%') AND ([SO] LIKE '%' + ? + '%') AND ([SP] LIKE '%' + ? + '%') AND ([SR] LIKE '%' + ? + '%') AND ([SS] LIKE '%' + ? + '%') AND ([ST] LIKE '%' + ? + '%') AND ([SW] LIKE '%' + ? + '%') AND ([SY] LIKE '%' + ? + '%') AND ([TA] LIKE '%' + ? + '%') AND ([TF] LIKE '%' + ? + '%') AND ([TN] LIKE '%' + ? + '%') AND ([TQ] LIKE '%' + ? + '%') AND ([TR] LIKE '%' + ? + '%') AND ([TS] LIKE '%' + ? + '%') AND ([TW] LIKE '%' + ? + '%') AND ([UB] LIKE '%' + ? + '%') AND ([W] LIKE '%' + ? + '%') AND ([WA] LIKE '%' + ? + '%') AND ([WC] LIKE '%' + ? + '%') AND ([WD] LIKE '%' + ? + '%') AND ([WN] LIKE '%' + ? + '%') AND ([WR] LIKE '%' + ? + '%') AND ([WS] LIKE '%' + ? + '%') AND ([WV] LIKE '%' + ? + '%') AND ([YO] LIKE '%' + ? + '%'))
View 10 Replies
View Related
May 20, 2008
hi how are you please help me in my problem which i can't make it.
Now, i have a project in ASP.NET and SQL Server 2005. let's call the project an image gallery, in my project i have a table named "Category" in which all the categories are in this table. also while adding a new category a new table will be created automatically with the name of that category.
now, what i want is that to build a query that reads the contents of the tables that the tables name are the names of the each record in the "Category" table.
is that possible ?
please if any one help can me in my problem.
View 4 Replies
View Related
Feb 10, 2004
I have the following SQL 2000 database table:
NEWS (IDNews, Country, PublishDate, Title)
I have to get a dataset containing only one record for each country, having most recent publish date.
Any suggestions? Thanks.
View 3 Replies
View Related
Feb 8, 2005
I have 2 tables, say table1, and table2. There is a DocID (primary key) in table1. In table2, DocID is the foriegn key. There can be more than 1 DocID.
this is the table structure (sample)
Table 1:
DocID DocName OtherID etc
1 test 2
2 test2 3
Table2:
TblID DocID OtherID
1 1 10
2 1 13
3 1 25
how do I join these two tables, such that I get all the otherID's for each DocID.
ie.,
DocID OtherID
1 2 and 10 and 13 and 25
2 3
i am writing this query to display search results on a search page (with keyword search) and so, if I display the result in more than one row, then the user might think that there is more than document...whereas the case is that there is only one document with more than one other ID's.
is there any way I can do this? display...more than 1otherID in the same row for the same DociD?
Currently, I am using a left outer join of table1 and table2.
An suggestions on how to do this?
View 6 Replies
View Related
Oct 1, 2005
I cant get "order by" to work in this sql query..I use this query:
"SELECT DISTINCT TOP 12 name,total = COUNT(*) FROM products where kat = 'music' group by namn"and I want to add this some where to get 12 random records: "ORDER BY NewID()"I tried this: "SELECT DISTINCT TOP 12 name,total = COUNT(*) FROM products where kat = 'music' group by namn ORDER BY NewID()"" but get the error:"ORDER BY items must appear in the select list if SELECT DISTINCT is specified"I can´t figure out how I should write the query..Somebody have any ideas??/Radiwoi
View 2 Replies
View Related
Apr 11, 2003
Hi,
I need a way to retrieve records from a table with a 30 min interval between the records.
For e.g., Lets say I have the following data in a table :-
userid hitdt
1 4/1/2003 10:00 AM
1 4/1/2003 10:15 AM
1 4/1/2003 10:31 AM
1 4/1/2003 11:10 AM
1 4/1/2003 11:30 AM
1 4/1/2003 11:41 AM
I need a query which would return me the following recordset :-
userId hitId
1 4/1/2003 10:00 AM
1 4/1/2003 10:31 AM
1 4/1/2003 11:10 AM
1 4/1/2003 11:41 AM
Is there a way to do this without using a cursor ?
Thanks
View 7 Replies
View Related
Sep 7, 2003
table_a has patient_id, tran_id and other fields a,b,c
table_b has patient_id,tran_id, key_id
and other fileds d,e,f
table_a patien_id + tran_id is unique
table_b patient_id + tran_id is not unique, could be duplicated.
I have to create a query which will retrieve fields from table a a,b,c and fields d,e from table b where
table a. patient_id + tran_id =
tableb.patient_id + tran_id and table_b.key_id is the min key_id for that patient_id + tran_id.
I should retrieve just one record.
How would I be able to do that?
Please help!!
Thanks
View 2 Replies
View Related
Jun 13, 2001
I have a sp which requires a somewhat(at least for me) where clause. It needs a standard clause but then needs to differentiate the where based on whether a certain field is null or not. I didn't think an if would work but neither is my case. Below is the where clause. Thanks
where(OTHER_ORDER_DATES_.DF_RESTRICTION1 in
('Dental hold', 'Medical hold', 'Mental health hold')
AND
case when OTHER_ORDER_DATES_.DF_RES1TO is not null
then
OTHER_ORDER_DATES_.DF_RES1FROM <=@sdate AND
OTHER_ORDER_DATES_.DF_RES1TO >= @edate
when OTHER_ORDER_DATES_.DF_RES1TO is null
OTHER_ORDER_DATES_.DF_RES1FROM <=@edate
View 6 Replies
View Related
Jun 10, 2005
Code:
ID GroupID User
1 101 Tom
2 101 Mark
3 101 Clark
4 102 Tom
5 102 Mark
6 103 Tom
7 103 Clark
8 104 Tom
9 104 Clark
10 105 Tom
11 105 Bred
the users of Group 101 are Tom,Mark,Clark
the users of Group 102 are Tom,Mark
the users of Group 103 are Tom,Clark
the users of Group 104 are Tom,Clark
the users of Group 105 are Tom,Bred
I want to show Tom that
Both You and Clark are together in 3 groups
Both You and Mark are together in 2 groups
Both You and Bred are together in 1 group
View 5 Replies
View Related
Jan 27, 2007
I might have to redesign the tables for this, but I'll ask anyway. I have a table with the following fields:
Email - VarChar
Seminar - Int
PeckingOrder - Int.
As I add addresses to the table, each one has a Seminar, and then each Seminar has a Pecking Order Value. If an email address shows up for more than one seminar, it can have multiple records. IE:
email1 - 1523 - 424
email1 - 1526 - 124
email1 - 1524 - 235
email2 - 1526 - 124
email2 - 1524 - 235
for address, seminar, and pecking order would be sample entries into the table. Give or Take 1000 records in the table at any given time. What I want to pull out is:
Distinct Email Addresses
For each Email Address - The Max(PeckingOrder)
And the Seminar that's associated with Max(PeckingOrder)
For the sample data set above, I'd want to see these two records returned by the query:
email1 - 1523 - 424
email2 - 1524 - 235
I can't seem to get the Having / Where clause right to pull those two records properly. Anyone have any suggestions?
Thanks,
--Daniel
Edited - Didn't realize the BB removed email addresses.
View 1 Replies
View Related
Nov 23, 2006
Here is my data
------------------------------
BookName | Price
Book 1 | 75
Book 2 | 100
Book 3 | 45
Book 4 | 10
I want to make a report eg:
Category | Total
>= 75 | 2
25 - 74 | 1
< 25 | 1
View 1 Replies
View Related
Feb 15, 2007
okay;
i have a table called tblSlots
tblSlots is a list of Start datetimes and End datetimes.
every day has the same list of 10 slots.
tblSlots:
PKSlotINDEX
datStartTime
datEndTime
then i have a table called tblPersons
tblPersons:
PKPersonINDEX
txtLast
txtFirst
then i have a table called tblSchedule
tblSchedule:
PKScheduleINDEX
fkSlotINDEX
fkPersonINDEX
i want to write a query that takes any one specific person's schedule for an entire specific day, adds an arbitrary number of days to datStartTime, and finally inserts the PKSlotINDEX corresponding to the calcultated StartTime and fkPersonINDEX.
wow complicated isnt it...
the goal is to take the schedule of one day for one person and copy it to another day; i can scrap my current layout if necessary.
thanks!
View 1 Replies
View Related
Oct 10, 2007
Code:
SELECT
goto_last_name as 'Manager',
advisor_name,
advisor_ao_number as 'AO',
COUNT (CASE advisor_termination_date WHEN '<Null>' THEN 1 END) as '#Advisors',
SUM(Weekly_TOS_GDC) as 'TOS GDC'
FROM weekly_condensed_tb
WHERE advisor_platform_number = 1
AND report_date BETWEEN CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME) -28
AND CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME)
GROUP BY goto_last_name,advisor_ao_number,advisor_name
HAVING COUNT (CASE advisor_termination_date WHEN '<Null>' THEN 1 END) > 1
In the query above i select a range from the date - 28 days and do a sum on the TOS GDC column. What i am trying to do is have another query where the range will be - 56 and maybe - 86 so i get the SUMS for that as well. I need to display this in SQL Reporting Services and i can only have one DataSet returned otherwise i will not be able to bind it to one table.
How can i go about this so i return one set of data for 3 different date ranges.
Thanks
View 2 Replies
View Related
Aug 26, 2005
Hey all,
I have 3 tables: 1 table with Module information (1 quiz-like part of an Exam), 1 table with the relationships between Modules and Exams (ratios) and 1 table with User information, that contains the Exam number, Module number, and score. I am trying to get a full list of modules' names for 1 exam, and the user's score for those modules they taken. All this would pertain to 1 single Exam. I am only getting records returned for those modules that the user has taken; I want the full list with NULL values for the scores of modules the user has not taken. Hope that made sense, here is my attempt:
Code:
SELECT ExamModules.Name, ExamUsers.Score
FROM ExamToModule LEFT OUTER JOIN
ExamModules ON ExamToModule.ModuleID = ExamModules.ID LEFT OUTER JOIN
ExamUsers ON ExamToModule.ModuleID = ExamUsers.MID AND ExamToModule.ExamID = ExamUsers.EID
WHERE (ExamToModule.ExamID = 1) AND (ExamUsers.UserName = 'dizzle')
In this case the user is Dizzle and the Exam's ID (primary key) is 1. I've changed LEFT to RIGHT and FULL, they all return the same few records. Help?
View 4 Replies
View Related
Jun 2, 2004
I'm struggling with a syntax error in my sp. I'm hoping someone can give it a fresh look and straighten me out. Thanks.
Background:
1. The innermost query (using the dynamic statements) will run fine on its own.
2. I can wrap that in another query (with the innermost as a DERIVEDTBL) and it will run fine UNLESS I apply the GROUP BY. Then I get syntax errors.
3. Also, the entire query runs fine as a view (with hard coded vals for column and value).
4. I don't know what kind of formatting this message will apply, so just know that I've checked my line truncations and they all include a trailing '+. Also, I know that Occured is misspelled. That is the correct object name. Not all our DB admins are lit majors.
Here's the code:
CREATE PROCEDURE [dbo].[sp_ninetydayavgtotals]
@column nvarchar(100),
@value smallint
AS
DECLARE @SelectString nvarchar(500)
SET @SelectString = 'SELECT AVG(total_attacks) AS avg_attacks ' +
'FROM (SELECT TOP 100 PERCENT thedate AS [day], COUNT(total) AS total_attacks, SUM(enemyKIA) AS EKIA, SUM(enemyWIA) AS EWIA, SUM(coalitionKIA) AS CKIA, SUM(coalitionWIA) AS CWIA ' +
'FROM SELECT(TOP 100 PERCENT CONVERT(nvarchar, dateOccured, 11) AS thedate, txtCategory AS total, enemyKIA, enemyWIA, coalitionKIA, coalitionWIA ' +
'FROM dbo.v_maindata ' +
'WHERE (CONVERT(nvarchar, dateOccured, 11) > CONVERT(nvarchar, (getdate()-90), 11)) AND (CONVERT(nvarchar, dateOccured, 11) != CONVERT(nvarchar, getdate(), 11)) AND ' + @column + ' = @value ' +
'ORDER BY dateOccured DESC) DERIVEDTBL ' +
'GROUP BY thedate ' +
'ORDER BY thedate DESC) DERIVEDTBL'
EXEC sp_executesql @SelectString, N'@column nvarchar(100), @value smallint', @column, @value
GO
-------------------------------------------------------------------------
Owen Eustice
MNC-I Webmaster
Victory Base South
View 5 Replies
View Related
Aug 11, 2004
Result of the select from dbUsers is
db_name User Group
DB1 U1 db_datareader
DB1 U1 db_datawriter
DB1 U1 db_ddladmin
Db2 U1 db_dataReader
Db2 U1 db_datawriter
Db2 U2 db_dataReader
Db2 U2 db_datawriter
I somehow want it in a way so that the Db_name and USer_name are not repeated .
IS it possible ?
thanks
View 3 Replies
View Related
