|
Hi,
I have an employee table with employee data like empid, empname, empcreateddate. Every data new employees will join, so data is inserted in this table. I want to need reports weekly, to know, In this weak how many new employees are joined?
How to do? can u give query for this?
I am not getting the result, when i am using this below query
Select * from Employees
where EmpCreatedDate between convert(varchar,getdate(),101) and convert(varchar,getdate()-7,101)
G. Satish
|
|
|
|
|
See date functions from msdn here[^]
DateAdd, DatePart and GetDate will be there to help you.
Regards,
Mehroz
|
|
|
|
|
Try the following query
Select * from Employees
where EmpCreatedDate between select DATEADD(DAY, -(DATEPART(dw,GETDATE())-1),GETDATE()) and getdate()
|
|
|
|
|
Select * from YourTable where datediff( day, YourColumn, Getdate()) < 8
Do good and have good.
|
|
|
|
|
Hi i have one doubt...,
I want to add a month from the given date...,
Mean suppose if i give a date(mm/dd/yyy)='01/01/2008' from that add 30 days and give '31/01/2008'
another example if i give '02/16/2008' add 30 days and give '03/16/2008'
like this,
how its possible?
Thanks & Regards,
NeW OnE,
please don't forget to vote on the post
|
|
|
|
|
You can use the DateAdd function. See msdn documentation
here[^]
Regard,
Mehroz
|
|
|
|
|
select dateadd(day,30,columnname) from tablename
Do good and have good.
|
|
|
|
|
thanks
Thanks & Regards,
NeW OnE,
please don't forget to vote on the post
|
|
|
|
|
Lets say that you get an ID from an external system.
Eg. PersonID
Lets also assume that you need to store local information related to that ID.
eg orders etc.
All those tables with related info would get an FK to PersonID
but there wouldnt be any "direct" data associated with the PersonID.
The result would be that we need a "person table" with only one field: PersonID wich is the PK in order to get true ref integrity between the id and the various related tables.
What does the DB normalization rules say about such cases?
Does it state anything at all?
It feels completely wrong in the sense that its stupid to have an ID that points to nothing.
But feels right in the sense that the child data can point to the root id and all the related tables would have valid FK's and cascades and all that crap would work as it should..
(yes this is very much a problem introduced by SOA)
Ideas?
Right or wrong? options?
|
|
|
|
|
Using SQL Server 2005...
I have a table (call it TableA) with a primary key column. I want to pull data from another table (call it TableB) and insert it into TableA using a stored procedure. There may be times when the record to be inserted into TableA will have a duplicate key value. I want the stored procedure to ignore duplicate key errors, and just move on to the next record to be inserted.
Is that possible? Does SQL already do it this way?
Alternatively, the stored procedure is creating tableB with new records that are to be added to tableA. What SQL statement will delete duplicates from table B before adding tableB's contents to tableA?
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
|
|
|
|
|
The following query will delete records from tableB that have a key value which is already present in tableA.
delete
from tableB
where exists
(select top 1 * from tableA
where keyvalue = tableB.keyvalue)
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Ohhh, that's much less troublesome than my solution (and doesn't require me to create a 2nd temp table). I'll 5 you if it works.
5'd! Thanks!
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
|
|
|
|
|
Alrightie, the following code works.
--declare and initialize variables
DECLARE @publisher varchar(25);
SET @publisher = 'mypublisher';
DECLARE @dateStart datetime;
DECLARE @dateEnd datetime;
SET @dateStart = '2008-02-27 00:00:00.000';
SET @dateEnd = '2008-03-01 23:59:59.999';
declare @byteChunk int
set @byteChunk = 2;
DECLARE @sizeDivider bigint;
SET @sizeDivider = CASE (@byteChunk)
WHEN 0 THEN 1024
WHEN 1 THEN 1048576
WHEN 2 THEN 1073741824
WHEN 3 THEN 1099511627776
WHEN 4 THEN 1125899906842624
END;
--create our temporary tables
CREATE TABLE #tempB
(
DownloadDate datetime,
Bytes int,
PublisherName varchar(50),
UniqueKey varchar(80)
)
CREATE TABLE #tempC
(
DownloadDate datetime,
Bytes int,
PublisherName varchar(50),
UniqueKey varchar(80)
)
-- gather all of the appropriate records from another table, and put
-- them into a temporary table
insert into #tempB (DownloadDate, Bytes, PublisherName, UniqueKey)
select CAST(FLOOR(CAST(EndTimeStamp as float)) as DateTime) as endtimestamp, TotalBytesReceived, PublisherName, Convert(varchar, CAST(FLOOR(CAST(EndTimeStamp as float)) as DateTime)) + '.' + @publisher as UniKey
from dbo.downloads
where LOWER(publishername) = LOWER(@publisher)
and EndTimeStamp between @dateStart and @dateEnd
order by endtimestamp;
-- transfer only unique records to a second temporary table (records that
-- don't already exist in exist in dailybandwidth) - we're trying to side-step
-- the error handling in SQL and effectively let the Stored proc insert what it
-- can and ignore the duplicate records
insert into #tempC (DownloadDate, Bytes, PublisherName, UniqueKey)
SELECT DownloadDate, Bytes, PublisherName, UniqueKey
FROM #TEMPB
where not exists
(
SELECT DownloadDate, Bytes, PublisherName, UniqueKey
FROM dailybandwidth
where #tempb.uniquekey = dailybandwidth.uniquekey
)
-- transfer records from 2nd temp table (guaranteed to be unique)
-- into destination table
insert into dbo.DailyBandwidth (DownloadDate, Bytes, PublisherName, UniqueKey)
select DownloadDate, SUM((Bytes*1.0) / @sizeDivider) as Bytes, PublisherName, UniqueKey
from #tempC
group by DownloadDate, Publishername, UniqueKey
order by DownloadDate asc
-- drop our temp tables
drop table #tempB
drop table #tempc
I know it seems cumbersome, but it's the only way I could figure out how to do it. The original table contains over 5 million records (and it grows by several hundred thousand records every day). The ultimate goal is to tally up all bytes for a given day/publisher, and store them into a much smaller table (representing 1 day of downloads per publisher). This data will ultimately be presented on a web page, so speed of retrieval is paramount.
If anyonw can suggest a better way, I'm most certainly all ears.
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
|
|
|
|
|
Hello, first time asking a question here, so please be gentle
I'm filling a dataset and using OleDbDataAdapter.Update() to insert it into a DBF table using Jet. The update is running painfully slow and I've read up a bit on how to speed up and saw something about starting the transaction before running the update. Doesn't really seem to do much for me. I have a table write that goes on for 24 minutes. Any help would be appreciated.
Code snippit:
If Not cnDBConn.State = ConnectionState.Open Then
cnDBConn.Open()
End If
Dim transID As System.Data.OleDb.OleDbTransaction = cnDBConn.BeginTransaction()
cmInsert.Transaction = transID
daAdapter.InsertCommand = cmInsert
daAdapter.DeleteCommand = Nothing
daAdapter.UpdateCommand = Nothing
daAdapter.ContinueUpdateOnError = False
daAdapter.Update(dsOut, sCurrentTable)
|
|
|
|
|
How do i get a package variable "varPath" from within the Data Flow script component in SSIS
modified on Thursday, March 13, 2008 10:04 AM
|
|
|
|
|
You'll have to define it as either a read or read/write variable in the script properties. Once you've done that you can access it from within the script.
|
|
|
|
|
|
Hi!!
I'm having a problem( again) while creating a .dbf file. First, I have this line to create the table:
Create Table Results.dbf (BuyDate Date)
And this worked well.
Now, I've this line to add a date in the table:
Insert Into Results.dbf (20070922)
But it gives me a "Data type mismacth" error. I've already saw that the date format is YYYYMMDD. So where is the error here??
|
|
|
|
|
Rafael Carmo wrote: Hi!!
I'm having a problem( again) while creating a .dbf file. First, I have this line to create the table:
Create Table Results.dbf (BuyDate Date)
And this worked well.
Now, I've this line to add a date in the table:
Insert Into Results.dbf (20070922)
But it gives me a "Data type mismacth" error. I've already saw that the date format is YYYYMMDD. So where is the error here??
AFAIK, a DBF file stores all data as ASCII text only - maybe this is the issue here...
|
|
|
|
|
Just only ASCII text?? So why is there a Date format for .dbf file...??? I tried to insert '20070922', "20070922" and this didn't work as well...
|
|
|
|
|
Snaider wrote: Just only ASCII text?? So why is there a Date format for .dbf file...??? I tried to insert '20070922', "20070922" and this didn't work as well...
It's been a while since I have seen a DBF file... I think the Date type in a DBF file is a 10-char storage. Maybe you could try '03/13/2008' (depending upon your system locale)
|
|
|
|
|
Try this
Insert Into Test2 values ('09/22/2007');
and check the data in database.
Do good and have good.
|
|
|
|
|
It didn't worked...but I've just find out how to do it:
Insert Into Results.dbf Values (CtoD([06/18/2005]))
Only by this way it worked.
Thx for everybody's help
|
|
|
|
|
Dang. I'm stuck doing DBF stuff again!
But to answer your question, you can use the "CTOD" function as you've seen. Or you can use {} as the field delimiters for an actual date field. So:
Insert into Results({09/22/2007})
|
|
|
|
|
hi! I have a problem with sql server. I want to find a record from a database, with like 30 tables in it, and lots of records. I don't know exactly where that record is, in which table. So, cause I am a beginner in SQL server, with the knowledge what I have, I did this:
SELECT * FROM dbTest WHERE VALUES ='some value';
and it gives me error, near VALUES, so I don't know how to find it, any help will be welcomed, so I can resolve this issue. Thanks ahead
|
|
|
|
|