I have a set of records that will always be the same. A template if you wanna call it that.
"SELECT ID, Field1, Field2, Field3, Field4, Field5, Field6, Field7 FROM TABLE WHERE Field2 = 000"
This returns a 20 record set. The user will click a button and create another set of 20 records, where they can make changes accordingly.
Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
Thanks Ross
Ok, I have two parameters - @StartDate and @EndDate. We only care about the date part of these paramters. What I would like to do is create a table with one record for each date between these two values. For example:
@StartDate = '01/01/2008'
@EndDate = '01/8/2008'
Should yield a table with 9 records in it for every day between @StartDate and @EndDate like so:
01/01/2008 <datacol1> <datacol2>
01/02/2008 <datacol1> <datacol2>
01/03/2008 <datacol1> <datacol2>
01/04/2008 <datacol1> <datacol2>
01/05/2008 <datacol1> <datacol2>
01/06/2008 <datacol1> <datacol2>
01/07/2008 <datacol1> <datacol2>
01/08/2008 <datacol1> <datacol2>
I know I could just do a WHILE (@StartDate <= @EndDate) loop and insert records into a temp table but I'm looking to see if there are any new methods/techniques to achieve this with a more simple statement.
I'm trying to select records that occur more than once. I'm trying to base this on the email column. So basically I want the query to look something like this:
select * from table where emailaddress count > 1
Can someone provide me with the correct syntax? :)
I have select to split FullName on LastName and FirstName columnselect
Substring(FullName, 1,Charindex(',', FullName)-1) LName
,Substring(FullName, Charindex(',', FullName)+1, LEN(FullName)) FName
from Table1
Is it possible to create function based on that select? If yes. How it to do?
I am trying to select records based on a year.
DECLARE @Date
SET @Date = 2012
DECLARE @Year int
SET @Year = (SELECT DATEPART(yyyy,@Date))
SELECT @Year AS Year
--SELECT *
-- FROM [Orders].[dbo].[Orders] od
-- WHERE .Orderl_Date < @Date;
----WHERE DATEPART(yyyy,@Date)= @Year
----WHERE DATEPART(yyyy,od.Order_Date)= @ArchiveYear
DECLARE @Date
SET @Date = 2012
DECLARE @Year int
SET @Year = (SELECT DATEPART(yyyy,@Date))
SELECT @Year AS Year
--SELECT *
-- FROM [Orders].[dbo].[Orders] od
-- WHERE .Orderl_Date < @Date;
----WHERE DATEPART(yyyy,@Date)= @Year
----WHERE DATEPART(yyyy,od.Order_Date)= @ArchiveYear
I have the below script to create directories based on my select statement.
create table students
(
f_name varchar(100),
l_name varchar(100)
);
insert into students values ('John','Smith'),
[Code] ...
The result I am getting is truncating the directory names as in the below:
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
View 1 Replies View RelatedI have a report that uses different datasets based on the year selected by a user.
I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.
I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.
How could I display data from the dataset a user selects via the year_id options?
Hello
Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:
This is a simple matrix
test a
text b
text c
text d
text e
text f
text g
This is a matrix with all the same row-height.
test a
text b
.
text c
.
.
text d
text e
text f
text g
.
.
Thx you a lot
Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables. I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key. Is there a way to automatically create new records in the foreign tables that will have the new id? Would this be a job for a trigger, stored procedure? I admit I haven't studied up on those yet--I am learning things as I need them. Thanks.
View 4 Replies View RelatedHi! I have a table Tbl1 has to columns:
A B
_________
Ibm Me
Sony Me
Me Bob
Me Frank
I'd like to select all rows where B=ME and A=Me Thanks for the help
I have two tables and they have a foreign key relationship. It will be a 1 to Many realationship.
Table1- the primary key table has the following columns
ContentID - int identity field
DateAdded- datetime
Table2 - The foreign key table, has the following relevant fields
FK - int foreign key
version - int
When an initial record is added there is a record added to each table. The ContentID in Table1 will match the FK field in Table2. Then a user will be able to "edit" the record in Table2, but instead of writing over the record that is already there, a new record will be added to Table2. I would like to calculate the version field where each time a record is added for the particular ContentID/FK it adds 1 to the last version that was added to that particular ContentID/FK.
Does that make sense. I am trying to implement some type of revision tracking for my CMS that I am building and this is the only way I can come up with because my client is worried that somebody will go in and make incorrect changes to their site and they want to be able to roll it back to a previous version.
I would appreciate any ideas.
Thanks,
laura
Hello
I want to delete my record after ten days of the Posted date(field)..how do I do it? I'm able to insert date(short date , long time) to the database. I'm using sql server and C#. this is what I have so far.I would appreciate your help..
Thanks!!!<%@ Page Language="C#"%>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat=server>
void Page_Load(Object sender , EventArgs e)
{
SqlConnection conPubs;
string strDelete;
SqlCommand cmdDelete;
conPubs = new SqlConnection( @"Server=localhost;Integrated Security=SSPI;database=Book" );
strDelete = "Delete tblbook Where Posted_Date =???????????";
cmdDelete = new SqlCommand( strDelete, conPubs );
conPubs.Open();
cmdDelete.ExecuteNonQuery();
conPubs.Close();
Response.Write("Records Deleted!");
}
</script>
//
</script>
<html>
<head>
</head>
<body>
<form runat="server">
<!-- Insert content here -->
</form>
</body>
</html>
I am trying to create a report where the rows of the database have fields containing customer names and dates associated with the names. I want to know for each customer how many records the customer has for specified date ranges.
SELECT [DL].[Customer], Count([DL].[Date Received]) AS [CountOfDate Received1]
FROM [DL]
WHERE ((([DL].[Date Received]) Between #12/31/2012# And #1/1/2014#))
GROUP BY [DL].[Customer];
The idea is a list of the number of records for each customer in 2013.
Ultimately my goal is to show the customer activity over multiple years.
So,
Customer Name 201120122013
Customer A123
Customer B246
Customer C543
I am trying to go down the path of a Union:
SELECT [DL].[Customer], Count([DL].[Date Received]) AS [CountOfDate 2013]
FROM [DL]
WHERE ((([DL].[Date Received]) Between #12/31/2012# And #1/1/2014#))
GROUP BY [DL].[Customer]
[Code] ....
This returns 2 columns only not the four I am looking for.
I have a table that has a DateTime column which uses a DataTimedatatype. How do I retrieve a range of records based on the month andyear using ms sql?Eugene Anthony*** Sent via Developersdex http://www.developersdex.com ***
View 4 Replies View RelatedNewbie alrt...
I am trying to create an asp page that will update an Access 2000 database. I need to update records if the user exists and create a new record if the user does not exist. Most of the variables are pulled from a separate "post" form.
I am using 2 pieces of info to find duplicates, as employee numbers can be assigned to multiple employees. Right now I have the page check for a duplicate employee id number then check for a duplicate last name. Unfortunately it is running each check separately, so if the last name is duplicated anywhere, it is sending a duplicated value.
here is the chunk of code in question... all RF_variables are request.form variables
cnt="SELECT COUNT(emp_id) AS Xnum FROM " & RF_course
cnt=cnt & " WHERE emp_id='" & RF_emp_id & "'"
set again=conn.Execute(cnt)
dup=again("Xnum")
if dup>=1 then
cnt="SELECT COUNT(lname) AS Xnum FROM " & RF_course
cnt=cnt & " WHERE lname='" & RF_lname & "'"
set again=conn.Execute(cnt)
dupl=again("Xnum")
if dupl=1 then
upd="UPDATE " & RF_course & " SET "
upd=upd & "section" & RF_section & "='" & RF_score & "'"
upd=upd & " WHERE emp_id='" & RF_emp_id & "'"
upd=upd & " AND lname='" & RF_lname & "'"
on error resume next
conn.Execute upd
else
ins="INSERT INTO " & RF_course
ins=ins & " (lname,fname,emp_id,cname,"
ins=ins & "section" & RF_section & ")"
ins=ins & " VALUES "
ins=ins & "('" & RF_lname & "',"
ins=ins & "'" & RF_fname & "',"
ins=ins & "'" & RF_emp_id & "',"
ins=ins & "'" & RF_cname & "',"
ins=ins & "'" & RF_score & "')"
on error resume next
conn.Execute ins
end if
else
ins="INSERT INTO " & RF_course
ins=ins & " (lname,fname,emp_id,cname,"
ins=ins & "section" & RF_section & ")"
ins=ins & " VALUES "
ins=ins & "('" & RF_lname & "',"
ins=ins & "'" & RF_fname & "',"
ins=ins & "'" & RF_emp_id & "',"
ins=ins & "'" & RF_cname & "',"
ins=ins & "'" & RF_score & "')"
on error resume next
conn.Execute ins
end if
Hopefully this is understandable.
If anyone can offer any help I would greatly appreciate it.
Thanks
I have a spreadsheet that we download from one of our customers that contains Sales Order Release information. The spreadsheets contains 1-n releases per part #.
I have a SQL 2000 view that groups this information into a smaller table (after importing into SQL).
I need to take this view and create 1-n release records based on our production run qty and put it in a new table. I have a field in my view that contains the desired # of records to create.
Since I never used TSQL to loop through table/view, how do I do this. Following is what the output should look like:
From SQL view
---------------------------------------
Part #: 11124A1
Qty Due:175
Run Qty: 50
# Release to Generate: 4
Output to Table
----------------------
Part # QtyDue RunQty
----------------------------------------------------
11124A1 50 50
11124A1 50 50
11124A1 50 50
11124A1 25 50
Note QtyDue is the production run qty, with the last record containing the remaining QtyDue from view's Qty Due
I have a table of Sales Order Releases. Following is what the table looks like:
SMIPartNo
QtyType
PO
POLine
QtyDue
UOM
DateDue
DateDueType
fsono
fcustno
finumber
frelease
fspq
JI-117933A1
Firm
N40136001
234
200
EA
7/2/2007 0:00
SH Ship Date
E00001
20
001
1
1800
JI-117933A1
Firm
N40136001
234
400
EA
7/9/2007 0:00
SH Ship Date
E00001
20
001
2
1800
JI-117933A1
Firm
N40136001
234
400
EA
7/30/2007 0:00
SH Ship Date
E00001
20
001
3
1800
JI-117933A1
Firm
N40136001
234
400
EA
8/6/2007 0:00
SH Ship Date
E00001
20
001
4
1800
JI-117933A1
Raw Material
N40136001
234
400
EA
8/13/2007 0:00
SH Ship Date
E00001
20
001
5
1800
JI-117933A1
Raw Material
N40136001
234
200
EA
8/20/2007 0:00
SH Ship Date
E00001
20
001
6
1800
JI-117933A1
Raw Material
N40136001
234
400
EA
8/24/2007 0:00
SH Ship Date
E00001
20
001
7
1800
JI-117933A1
Raw Material
N40136001
234
200
EA
9/3/2007 0:00
SH Ship Date
E00001
20
001
8
1800
JI-117933A1
Raw Material
N40136001
234
400
EA
9/10/2007 0:00
SH Ship Date
E00001
20
001
9
1800
JI-117933A1
Raw Material
N40136001
234
400
EA
9/17/2007 0:00
SH Ship Date
E00001
20
001
10
1800
See 2nd post for rest of question
My SQL is very basic. How do I create a query that will accept a parameter, an integer, and based on the integer, locate all the matches in a db?
SELECT COUNT(*) AS Expr1, tblArticle.ArticleIDFROM tblArticle INNER JOIN tblArticleCategory ON tblArticle.ArticleCatID = tblArticleCategory.ACategoryIDGROUP BY tblArticle.ArticleID
This isn't setting up the query to request a parameter.What am I doing wrong here? I"m trying to get the total number of articles for a particular category ID.
I am having a table where i have the following columns where the date format is dd/mm/yyyy
Purchase Description From_Date To_Date------------------------------- --------------- ----------------Desktop 2/2/2007 2/3/2007Mouse 2/1/2007 28/1/2007Laptop 5/1/2008 15/3/2008Speaker 4/1/2008 21/1/2008
My requirement is i need to create a stored procedure which will look for the from_date and to_date values. If the difference is more than 30 days that record should get deleted automatically. How to write the stored procedure?
Please provide me with full stored procedure
Thanx in advance
I'm storing records that contain a date/time data type. I am needing two links on a reports page (asp), the first should return all records for the current month and the second link should return all records for the last three months (including current month). I have no idea how to just sort by month.
I'm also not sure what to include here in this post to help you answer my question. On the form that is submitted initially the text field is named "txtSubmitDate" and in the database it's stored in a field called "submitdate" and is 8 characters in length.
I've tried:
'SELECT TODAY'S MONTH
SqlJunk = "SELECT * FROM eom WHERE MONTH(submitdate) = MONTH(GETDATE())-1"
'SELECT TODAY'S MONTH and the last 2 months
SqlJunk2 = "SELECT * FROM eom WHERE MONTH(submitdate) = MONTH(GETDATE()) OR MONTH(submitdate) = MONTH(GETDATE())-1 OR MONTH(submitdate) = MONTH(GETDATE())-2 ORDER BY submitdate ASC"
These are not working because it can't handle the change in year (going from january 2005 back to december 2004, etc).
Any ideas?
I have the following table
Code:
10012014-09-01 00:00:00.000BH1-Z-1280180
20012014-09-01 00:00:00.000BH1-Z-9990300
30012014-09-01 00:00:00.000CHO1-Z-1280180
40012014-09-01 00:00:00.000CHO1-Z-9990306
50012014-09-01 00:00:00.000OT11-Z-99906
60012014-09-01 00:00:00.000WRK1-Z-1280180
70012014-09-01 00:00:00.000WRK1-Z-9990306
80022014-09-01 00:00:00.000BH1-Z-0800480
90022014-09-01 00:00:00.000CHO1-Z-0800480
100022014-09-01 00:00:00.000WRK1-Z-0800480
110022014-09-02 00:00:00.000BH1-Z-0800480
120022014-09-02 00:00:00.000CHO1-Z-0800600
130022014-09-02 00:00:00.000OT11-Z-0800120
140022014-09-02 00:00:00.000WRK1-Z-0800600
150012014-09-02 00:00:00.000BH1-Z-1280480
160012014-09-02 00:00:00.000CHO1-Z-1280480
What I want to do is update the table so that it populates the PERCENTAGE column on an empref/hrscode/date basis based on the sum of the WRK hours per day and empref.
EG for 2014-09-01 for empref 001 the result would be
Code:
0012014-09-01 00:00:00.000BH1-Z-12837.037180
0012014-09-01 00:00:00.000BH1-Z-99961.728300
0012014-09-01 00:00:00.000CHO1-Z-12837.037180
0012014-09-01 00:00:00.000CHO1-Z-99962.963306
0012014-09-01 00:00:00.000OT11-Z-9991.2356
0012014-09-01 00:00:00.000WRK1-Z-12837.037180
0012014-09-01 00:00:00.000WRK1-Z-99962.963306
IE Sum WRK = 486 so 180 is 37.037 percentage. Each HRSCODE hours total should total 100% (37.037 + 61.728)
I can write a query to do this individually but how can I so this as a query for the full table.
Code:
declare @@total as float
set @@total=(select SUM(hours) from tmsuser.tmswrhrs where hrscode='worked' and empref='001' and '2014-09-01 00:00:00.000'=procdate)
update tmsuser.TMSWRHRS set PTAS1=(Str(((hours/@@TOTAL*100)),12,3)) where empref='001' and '2014-09-01 00:00:00.000'=procdate
How can I pull out duplicate records based on certain fields?
Table called Bank
I want to pull out records that have duplicate inv_no, cus_no, amount,ordernum
Not all the fields are the same in each record. But I want the records that have these fields that are the same.
I have the following table
10012014-09-01 00:00:00.000BH1-Z-1280180
20012014-09-01 00:00:00.000BH1-Z-9990300
30012014-09-01 00:00:00.000CHO1-Z-1280180
40012014-09-01 00:00:00.000CHO1-Z-9990306
50012014-09-01 00:00:00.000OT11-Z-99906
[Code] ....
What I want to do is update the table so that it populates the PERCENTAGE column on an empref/hrscode/date basis based on the sum of the WRK hours per day and empref.
EG for 2014-09-01 for empref 001 the result would be
0012014-09-01 00:00:00.000BH1-Z-12837.037180
0012014-09-01 00:00:00.000BH1-Z-99961.728300
0012014-09-01 00:00:00.000CHO1-Z-12837.037180
0012014-09-01 00:00:00.000CHO1-Z-99962.963306
0012014-09-01 00:00:00.000OT11-Z-9991.2356
0012014-09-01 00:00:00.000WRK1-Z-12837.037180
0012014-09-01 00:00:00.000WRK1-Z-99962.963306
IE Sum WRK = 486 so 180 is 37.037 percentage. Each HRSCODE hours total should total 100% (37.037 + 61.728)
I can write a query to do this individually but how can I so this as a query for the full table.
declare @@total as float
set @@total=(select SUM(hours) from tmsuser.tmswrhrs where hrscode='worked' and empref='001' and '2014-09-01 00:00:00.000'=procdate)
update tmsuser.TMSWRHRS set PTAS1=(Str(((hours/@@TOTAL*100)),12,3)) where empref='001' and '2014-09-01 00:00:00.000'=procdate
I NEED TO UPDATE THESES RECORDS BASED ON THIS QUERY
select * from property where
Lis_key in(select lis_key from property where substring(func_key,1,5)='GEOSS'
And substring(func_key,6,8) >=3)and
Func_key = 'PV000000'
and substring(Attrib_code,5,2)!=16
AND ACTIVE = 1
order by Lis_key
HELP ME
I am having a table where i have the following columns where the date format is dd/mm/yyyy
Purchase DescriptionFrom_DateTo_Date
------------------------- --------
Desktop2/2/20072/3/2007
Mouse2/1/200728/1/2007
Laptop5/1/200815/3/2008
Speaker4/1/200821/1/2008
My requirement is i need to create a stored procedure which will look for the from_date and to_date values. If the difference is more than 30 days that record should get deleted automatically. How to write the stored procedure?
Please provide me with full stored procedure
Thanx in advance
C.R.P RAJAN
I have a question about SQL Server.
Table patient:
create table patient (pn int,code int,date date,doctorcode int)
insert into patient (pn,code,date,doctorcode)
values
(1,10,'2015-02-19','100),
(1,10,'2015-02-19','101),
(1,10,'2015-02-19','102),
[Code] ...
Table Patientref:
create table patientref
(pn int,code int, sdate date,edate date,status int)
insert into patientref(pn,code,sdate,edate,status)
values
(1,10,'2015-02-13','2015-02-19',1),
(1,10,'2015-02-19','2015-03-24',2),
[Code] ...
Here we need consider patient dates that fall between sdate and edate of the patientrefs table, and then we need to consider the highest status values in order (for example, the highest values in order - 2 is first highest, 4 is second highest, 3 is third highest, and 1 is fourth highest value)
If the date falls between multiple different sdate and edate with the same status values, then we need to consider the latest sdate value and from that entire record we need to extract that value.
Examples: patient
pn | code | date | doctorcode
2 | 10 |2015-02-12 | 101
2 | 10 |2015-02-13 | 102
2 | 10 |2015-02-14 | 103
Table : Patientref:
pn | code | sdate | edate | Status
2 | 10 |2015-02-08 | 2015-02-19 | 4
2 | 10 |2015-02-09 | 2015-02-19 | 2
2 | 10 |2015-02-10 | 2015-02-19 | 2
2 | 10 |2015-02-11 | 2015-02-18 | 1
Here, pn=2 values have dates which fall between sdate and edate of patientref table. Then we give highest values status is 2, and status 2 values have two records, then we go for max sdate(latest sdate). Then this pn=2 latest sdates is 2015-02-10 and we need to retrieve the corresponding edate and status values.
pn = 4donot have sdate and edate and status values dut not fall conditon
Based on this, the desired output is below:
pn | code | date | doctorcode | sdate |edate |status
1 | 10 |2015-02-19 | 100 |2015-02-19 |2015-03-24 | 2
1 | 10 |2015-02-19 | 101 |2015-02-19 |2015-03-24 | 2
1 | 10 |2015-02-19 | 102 |2015-02-19 |2015-03-24 | 2
2 | 10 |2015-02-12 | 101 |2015-02-10 |2015-02-19 | 2
[Code] ...
I tried it like this:
select p.pn,p.code,p.[date],p.doctorcode,pr.sdate,pr.edate,pr.[status] from patient p
outer apply (select top 1 pr.pn,pr.code,pr.sdate,pr.edate,pr.[status] from patientref pr
where pr.pn=p.pn and pr.code=p.code and p.date between pr.sdate and pr.edate
order by case when pr.status=2
then 1 when pr.status=4 then 2
when pr.status=3 then 3
when pr.status=1 then 4 end ,pr.sdate
)pr
but this query not given expected result.here when dos not fall between sdate and edate that records not given in the above query. I required that records also.if not fall b/w condition then we need retrive that records empty values for that records.
There are two seperate jobs,Job A and Job B, which run and insert records in a table. Job A runs first and then Job B runs. The task is to overwrite Job B records if Job A and Job B have same
PID,EDate,CP,RelativePNum,XrefCode,CPD
PID,EDate,CP,RelativePNum,XrefCode,CPD
In the above screenshot, row 1 and row 2 have same
So i want to write a query which deletes row 2 records, and keep row 1,row 3 and row 4 records. This i want to do in the whole table.
create
table AB
(
PID
int,
tTimeStamp
datetime,
EDate
varchar(40),
[Code] ....
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC
I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.