db time conversion: based

Showing posts with label based. Show all posts

Wednesday, March 28, 2012

Query

Greetings,

I have three queries that I have to put together and they are based on the SELECT and GROUP BY.

1st Query requirement:
group the Employees by Job Code. Use a SELECT query.and modify the SQL

My answer is (and I'm not able to see the actual names and job codes):
SELECT 'EmployeeID' AS EmployeeID, 'FirstName' AS FirstName, 'LastName' AS LastName, 'JobTitleCode' AS JobTitleCode
FROM Employees_Table
GROUP BY EmployeeID, FirstName, LastName, JobTitleCode
ORDER BY JobTitleCode;

2nd Query requirement:
group the Employees by Salary. Use a SELECT Query and modify the SQL

My answer is (and very similar to the above, I'm not seeing the information):
SELECT 'Employee ID' AS EmployeeID, 'FirstName' AS FirstName, 'LastName' AS LastName, 'Salary' AS Salary
FROM Employees_Table
GROUP BY EmployeeID, FirstName, LastName, [Salary]
ORDER BY [Salary];

Last Query requirement:
group the Employees by Salary within their Job Code. Use a SELECT Query and modify the SQL

My rough draft answer (not really sure at this point :-( is:
SELECT 'First Name' AS FirstName, 'Last Name' AS LastName, 'Salary' AS Salary
FROM Employees_Table
GROUP BY 'Salary', 'Job Title Code';

Any words of wisdom would be greatly appreciated.

Thanks for your time!Looks like a homework...Are you sure the fieldnames are spelled correctly? I don't think your queries will run...

1st - ...will give an error on invalid field name
2nd - ...will do the same
3rd - ...same difference

But conceptually ... I don't think you are answering the questions. You need to loose GROUP BY and reverse the order of the fields in ORDER BY clause.|||Yes, the field names in my database are spelled the same exact way as I've listed them in the SQL statements?

Any other words of advice?

Thanks for your time.|||Yes, the field names in my database are spelled the same exact way as I've listed them in the SQL statements?

Any other words of advice?

Thanks for your time.
So, you're saying that 'EmployeeID' and 'Employee ID' are the same field or 2 different fields?|||You are correct and I've modified to the new statements below:

Query 1:
SELECT EmployeeID, FirstName, JobTitleCode
FROM Employees_Table
GROUP BY EmployeeID, FirstName, JobTitleCode
ORDER BY JobTitleCode;

Query 2:
SELECT EmployeeID, FirstName, [Salary]
FROM Employees_Table
WHERE Salary>45000
GROUP BY EmployeeID, FirstName, [Salary]
ORDER BY [Salary];

I hope I'm going in the correct direction with my answers?

Thanks for your responses!|||See my first post (loose GROUP BY, because you're not doing any aggregation)sql

Monday, March 26, 2012

Query

I have duplicate records in a table based on say
OrderNum, I would like to copy 1st record for each
OrderNum from this table into a new table and then update
the OrderNum record in the 2nd table with values from
rest of the records in the 1st table, can someone help me
in doing this?
Ex.
Frist table may have -
ORder1 AreaField1 description ...
ORder1 AreaField1 Description ...
Order1 AreaField2 Description ...
ORder2 AreadField Description ...
Order2 AreaField Description ...
ORder3 AreaField Description ...
ORder4 AreaField Description ...
I would like the 2nd table to look lik -
ORder1 AreaField1+AreaField2 Description ...
ORder2 AreaField Description ...
Order3 AreaField Description ...
ORder4 AreaField Description...
Thank you very much,
-Kim
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.mseq:7748
On Thu, 12 Aug 2004 11:56:32 -0700, Kim wrote:
(snip)
Hi Kim,
I just replied to an equal post in .programming.
Please don't multi-post!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Tuesday, March 20, 2012

Queried parameters

Hello,

I need to be able to set the date parameters of a report dynamically when it is run based on system time. The problem I am having is being able to compare the dates (StartDate & EndDate) against [Service Date 1]. Essentially this report will only pull the current month's data.

The date fields being created with the GETDATE, DATEADD & DATEDIFF functions are working correctly. Do I need to create a separate dataset to be able to run the parameters automatically in the actual report?

Any help would be greatly appreciated!

SELECT TodaysDate =GetDate()-2,dbo.[Billing Detail].[Service Date 1], DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0) AS StartDate, DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0)) AS EndDate, dbo.[Billing Detail].Billing, dbo.[Billing Detail].Chart, dbo.[Billing Detail].Item,
dbo.[Billing Detail].[Sub Item], dbo.Patient.[Patient Code], dbo.Patient.[Patient Type], dbo.[Billing Header].Charges, dbo.Practice.Name
FROM dbo.[Billing Detail] INNER JOIN
dbo.Patient ON dbo.[Billing Detail].Chart = dbo.Patient.[Chart Number] INNER JOIN
dbo.[Billing Header] ON dbo.[Billing Detail].Billing = dbo.[Billing Header].Billing CROSS JOIN
dbo.Practice
WHERE (dbo.[Billing Detail].Item = 0) AND (dbo.[Billing Detail].[Sub Item] = 0) AND (dbo.[Billing Detail].[Service Date 1] Between StartDate AND EndDate

Phorest,

You should be able to add the parameters to your query. If you are going against SQL Server, you can replace your parameters with @.StartDate AND @.EndDate. Then in the properies of the dataset, you can assign those parameters to Parameters!StartDate.Value and Parameters!EndDate.Value, respectively.

Jessica

|||

Thanks for your reply!

OK,

I think what I need to do is write the expression as a non-queried default value. However when I paste in what I know works in SQL Management Studio it returns an error "Name 'mm' is not declared"

<@.StartDate> =DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0)

<@.EndDate> =DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0))

I tried putting an integer after DATEADD(mm, X , 102 DATEDIFF... but i can't get beyond intellisense. How can I fix my expression to work with Reporting Services?

What I need is to have expressions to choose the first day of the month to the last day of the same month compared to NOW()

|||

Apparently that is the trick to use non-queried default values as an expression, However what I posted yesterday will not work as an expression due to the expressions limitations in SSRS:

<@.StartDate> =DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0)

<@.EndDate> =DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0))

Now I am using:

<@.StartDate> =DATEADD("D", -30, NOW())

<@.EndDate> =DATEADD("D", 1, NOW())

After much searching and experimentation I can get this to work well, but it isn't exactly what I want. Does any one have any tips as to being able to write the expression to select the first day of the current month and last day of the month?

It seems to be just beyond my grasp at this time...

Thanks!

|||

Phorest,

I'm afraid I misunderstood what you're trying to do. If you want a query that returns rows where the [Billing Detail].[Service Date 1] is between the start and the end of the current month, you can do that all in SQL.

It would look something similar to:

WHERE dbo.[Billing Detail].[Service Date 1]

BETWEEN dateadd(mm, datediff(mm,0,getdate()), 0)

AND dateadd(ms,-3,dateadd(mm, datediff(m,0,getdate() ) + 1, 0))

Does that work for you?

Jessica

|||

I'll have to try that in the SQL, though I was more after an expression more as a datetime datatype so it picks all the dates in the current month only and the user can then adjust the parameter manually after the initial running of the report if they so choose.

Thanks!

|||

I found what I was looking for here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1581230&SiteID=1

In the Report Parameters properties I set the DataType to DateTime and using the Default Values, Non-Queried radio button set the expressions like the following:

@.StartDate =DateSerial(Year(NOW()), Month(NOW)) +0,1) gives me the first date of the current month.

@.EndDate =DateSerial(Year(NOW()), Month(NOW)) +1,0) gives me the last date of the current month.

All is wellnow!

quarter date

How can I calculate the first day of the quarter based on the current date?
Ex. Today's date is 5/30/06. I'm trying to get 4/1/06.
Thanks!maybe something like this..
declare @.a datetime
set @.a = getdate()
select @.a
select cast(cast(year(@.a) as varchar)+ '0' + cast((datepart(qq,@.a)-1)*3 + 1
as varchar) + '01' as datetime)|||Like this...
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+0, 0)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Boy.. excellent piece of code :)
"SQL Menace" wrote:

> Like this...
> SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+0, 0)
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||Juz one nagging doubt. Why yuu need that +0
wouldn't this work good enuf'
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)|||Perfect, thanks so much!
"SQL Menace" wrote:

> Like this...
> SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+0, 0)
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||Thanks
Denis the SQL Menace
http://sqlservercode.blogspot.com/

Wednesday, March 7, 2012

q; radius search

I have latitude and longitude in my database, can anyone give me an sql
query how I can make radius search based on that?Not sure about radius but this function will return the distance between two
lat and lons. I am not really sure what you are speaking of in terms of
radius search...Perhaps this will lead you in the right direction...
Create FUNCTION [dbo].[fn_distance_fl](@.sLat int, @.sLon int, @.dLat int,
@.dLon int)
RETURNS float
AS
BEGIN
DECLARE @.fn_distance_fl float
SET @.fn_distance_fl = SQRT( ((0.006917 * (@.sLat - @.dLat) ) * (0.006917 *
(@.sLat - @.dLat)) + ( 0.005756 * (@.sLon - @.dLon) ) * ( 0.005756 * (@.sLon -
@.dLon))))
RETURN(@.fn_distance_fl)
END
thanks,
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:DEB1B553-ECF2-4A26-B09D-BDF622779463@.microsoft.com...
> I have latitude and longitude in my database, can anyone give me an sql
> query how I can make radius search based on that?
>|||Thanks for the reply. Ok. Here is an example, :
http://www.trucktraderonline.com/dealersearch.html there is a zip and there
is a distance search feature in this site, how are they performing this
search, I need to do the same thing, ge a zip code and do distance search, it
does not seem it involves two lat, long values.
"Warren Brunk" wrote:
> Not sure about radius but this function will return the distance between two
> lat and lons. I am not really sure what you are speaking of in terms of
> radius search...Perhaps this will lead you in the right direction...
> Create FUNCTION [dbo].[fn_distance_fl](@.sLat int, @.sLon int, @.dLat int,
> @.dLon int)
> RETURNS float
> AS
> BEGIN
> DECLARE @.fn_distance_fl float
> SET @.fn_distance_fl = SQRT( ((0.006917 * (@.sLat - @.dLat) ) * (0.006917 *
> (@.sLat - @.dLat)) + ( 0.005756 * (@.sLon - @.dLon) ) * ( 0.005756 * (@.sLon -
> @.dLon))))
> RETURN(@.fn_distance_fl)
> END
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:DEB1B553-ECF2-4A26-B09D-BDF622779463@.microsoft.com...
> >
> > I have latitude and longitude in my database, can anyone give me an sql
> > query how I can make radius search based on that?
> >
>
>|||Thanks for your reply. Can anybody write me that stored procedure,
MyTable: has fields: ID, Zip,Lat,Long
Assuming user entered UserZip and UserRadius=5miles. If these are parameters
for my stored procedure, how should I write my stored procedure to return all
the IDs that meet this criteria.
"Kent Tegels" wrote:
> Hello JIM.H.,
> Actually, it *probably* does. They know table that has the lat/long for some
> number of zip codes, and a table that knows that dealers are in what zip
> codes. So they take your input zip code, get the lat/lon for it, run a query
> against their lat/lon table ordering by the result the distance ascending,
> then join that back to dealer zip codes.
> Its unlike that match found this way is *exactly* 25 miles away from the
> customer, but "as the bird flies," the dealership should be within a 25 mile
> radius "give or take."
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
>|||Jim,
Do you have a database of Zip Codes that map a Code to a LAT LON. You will
need to start there. Then you could use my function inside of a stored proc
doing something like.
Select * from fn_Distance(Lat1,Lon1,Lat2,Lon2)
where [Value] < 5
Lat 1 and Lon1 should be the starting zip code and lat lon 2 should be the
zip code of the dealership.
Hope that helps...
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:276E2AA0-B200-4F48-B8AE-D6F2009F5DAD@.microsoft.com...
> Thanks for your reply. Can anybody write me that stored procedure,
> MyTable: has fields: ID, Zip,Lat,Long
> Assuming user entered UserZip and UserRadius=5miles. If these are
> parameters
> for my stored procedure, how should I write my stored procedure to return
> all
> the IDs that meet this criteria.
>
> "Kent Tegels" wrote:
>> Hello JIM.H.,
>> Actually, it *probably* does. They know table that has the lat/long for
>> some
>> number of zip codes, and a table that knows that dealers are in what zip
>> codes. So they take your input zip code, get the lat/lon for it, run a
>> query
>> against their lat/lon table ordering by the result the distance
>> ascending,
>> then join that back to dealer zip codes.
>> Its unlike that match found this way is *exactly* 25 miles away from the
>> customer, but "as the bird flies," the dealership should be within a 25
>> mile
>> radius "give or take."
>> Thanks,
>> Kent Tegels
>> http://staff.develop.com/ktegels/
>>|||Hi,
Thanks for your help, I have two problems,
1.Your lat, long value are int, mine are float
2. Your function returns only one value I need to find IDs that mach the
criteria
"Warren Brunk" wrote:
> Jim,
> Do you have a database of Zip Codes that map a Code to a LAT LON. You will
> need to start there. Then you could use my function inside of a stored proc
> doing something like.
> Select * from fn_Distance(Lat1,Lon1,Lat2,Lon2)
> where [Value] < 5
> Lat 1 and Lon1 should be the starting zip code and lat lon 2 should be the
> zip code of the dealership.
> Hope that helps...
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:276E2AA0-B200-4F48-B8AE-D6F2009F5DAD@.microsoft.com...
> > Thanks for your reply. Can anybody write me that stored procedure,
> > MyTable: has fields: ID, Zip,Lat,Long
> >
> > Assuming user entered UserZip and UserRadius=5miles. If these are
> > parameters
> > for my stored procedure, how should I write my stored procedure to return
> > all
> > the IDs that meet this criteria.
> >
> >
> > "Kent Tegels" wrote:
> >
> >> Hello JIM.H.,
> >>
> >> Actually, it *probably* does. They know table that has the lat/long for
> >> some
> >> number of zip codes, and a table that knows that dealers are in what zip
> >> codes. So they take your input zip code, get the lat/lon for it, run a
> >> query
> >> against their lat/lon table ordering by the result the distance
> >> ascending,
> >> then join that back to dealer zip codes.
> >>
> >> Its unlike that match found this way is *exactly* 25 miles away from the
> >> customer, but "as the bird flies," the dealership should be within a 25
> >> mile
> >> radius "give or take."
> >>
> >> Thanks,
> >> Kent Tegels
> >> http://staff.develop.com/ktegels/
> >>
> >>
> >>
>
>|||1. You may change the values to a FLOAT with no ramifications.
2. When you are selecting from a table you can use the function to return
distance for a number of rows...When you use the scalar function on a select
statement it will give you the distance for each row in the select.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A17B835D-E718-409E-BC2F-754A93F77732@.microsoft.com...
> Hi,
> Thanks for your help, I have two problems,
> 1.Your lat, long value are int, mine are float
> 2. Your function returns only one value I need to find IDs that mach the
> criteria
>
> "Warren Brunk" wrote:
>> Jim,
>> Do you have a database of Zip Codes that map a Code to a LAT LON. You
>> will
>> need to start there. Then you could use my function inside of a stored
>> proc
>> doing something like.
>> Select * from fn_Distance(Lat1,Lon1,Lat2,Lon2)
>> where [Value] < 5
>> Lat 1 and Lon1 should be the starting zip code and lat lon 2 should be
>> the
>> zip code of the dealership.
>> Hope that helps...
>> --
>> /*
>> Warren Brunk - MCITP - SQL 2005, MCDBA
>> www.techintsolutions.com
>> */
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:276E2AA0-B200-4F48-B8AE-D6F2009F5DAD@.microsoft.com...
>> > Thanks for your reply. Can anybody write me that stored procedure,
>> > MyTable: has fields: ID, Zip,Lat,Long
>> >
>> > Assuming user entered UserZip and UserRadius=5miles. If these are
>> > parameters
>> > for my stored procedure, how should I write my stored procedure to
>> > return
>> > all
>> > the IDs that meet this criteria.
>> >
>> >
>> > "Kent Tegels" wrote:
>> >
>> >> Hello JIM.H.,
>> >>
>> >> Actually, it *probably* does. They know table that has the lat/long
>> >> for
>> >> some
>> >> number of zip codes, and a table that knows that dealers are in what
>> >> zip
>> >> codes. So they take your input zip code, get the lat/lon for it, run a
>> >> query
>> >> against their lat/lon table ordering by the result the distance
>> >> ascending,
>> >> then join that back to dealer zip codes.
>> >>
>> >> Its unlike that match found this way is *exactly* 25 miles away from
>> >> the
>> >> customer, but "as the bird flies," the dealership should be within a
>> >> 25
>> >> mile
>> >> radius "give or take."
>> >>
>> >> Thanks,
>> >> Kent Tegels
>> >> http://staff.develop.com/ktegels/
>> >>
>> >>
>> >>
>>|||Thanks Warren,
I tested your function, my test show this always returns numbers close to 0,
what is the unit in here? I need to is the miles.
Thanks for your help.
"Warren Brunk" wrote:
> 1. You may change the values to a FLOAT with no ramifications.
> 2. When you are selecting from a table you can use the function to return
> distance for a number of rows...When you use the scalar function on a select
> statement it will give you the distance for each row in the select.
> thanks,
>
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:A17B835D-E718-409E-BC2F-754A93F77732@.microsoft.com...
> > Hi,
> > Thanks for your help, I have two problems,
> > 1.Your lat, long value are int, mine are float
> > 2. Your function returns only one value I need to find IDs that mach the
> > criteria
> >
> >
> > "Warren Brunk" wrote:
> >
> >> Jim,
> >> Do you have a database of Zip Codes that map a Code to a LAT LON. You
> >> will
> >> need to start there. Then you could use my function inside of a stored
> >> proc
> >> doing something like.
> >>
> >> Select * from fn_Distance(Lat1,Lon1,Lat2,Lon2)
> >> where [Value] < 5
> >>
> >> Lat 1 and Lon1 should be the starting zip code and lat lon 2 should be
> >> the
> >> zip code of the dealership.
> >>
> >> Hope that helps...
> >>
> >> --
> >> /*
> >> Warren Brunk - MCITP - SQL 2005, MCDBA
> >> www.techintsolutions.com
> >> */
> >>
> >>
> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> news:276E2AA0-B200-4F48-B8AE-D6F2009F5DAD@.microsoft.com...
> >> > Thanks for your reply. Can anybody write me that stored procedure,
> >> > MyTable: has fields: ID, Zip,Lat,Long
> >> >
> >> > Assuming user entered UserZip and UserRadius=5miles. If these are
> >> > parameters
> >> > for my stored procedure, how should I write my stored procedure to
> >> > return
> >> > all
> >> > the IDs that meet this criteria.
> >> >
> >> >
> >> > "Kent Tegels" wrote:
> >> >
> >> >> Hello JIM.H.,
> >> >>
> >> >> Actually, it *probably* does. They know table that has the lat/long
> >> >> for
> >> >> some
> >> >> number of zip codes, and a table that knows that dealers are in what
> >> >> zip
> >> >> codes. So they take your input zip code, get the lat/lon for it, run a
> >> >> query
> >> >> against their lat/lon table ordering by the result the distance
> >> >> ascending,
> >> >> then join that back to dealer zip codes.
> >> >>
> >> >> Its unlike that match found this way is *exactly* 25 miles away from
> >> >> the
> >> >> customer, but "as the bird flies," the dealership should be within a
> >> >> 25
> >> >> mile
> >> >> radius "give or take."
> >> >>
> >> >> Thanks,
> >> >> Kent Tegels
> >> >> http://staff.develop.com/ktegels/
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

q; radius search

Saturday, February 25, 2012

q; a trigger code

I have table T1 and I am trying to extract some date from T1 table based on
inserts, updates and deletes. The destination table T2 has three fields F1,
F2, F3, so I need something like
Insert into T2 (F1, F2, F3)
Select (F1, F2,Type)
From T1
Type should be defined based on Insert, Update, or Delete. This will be my
first trigger, Can anyone write this trigger for me?I would NEVER write any code that had table names like T1 or T2, and columns
named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
that you can post?
Give us more to work with, and you may get some good assistance here.
For example, the the code snipplet, is TYPE a column name, a literal, a
varable? Where did it come from?
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>I have table T1 and I am trying to extract some date from T1 table based on
> inserts, updates and deletes. The destination table T2 has three fields
> F1,
> F2, F3, so I need something like
> Insert into T2 (F1, F2, F3)
> Select (F1, F2,Type)
> From T1
> Type should be defined based on Insert, Update, or Delete. This will be my
> first trigger, Can anyone write this trigger for me?
>
>|||Hi Arnie,
That was just to simplify the case. I do not have actual table definitions
yet. Type is what I am trying to determine in the trigger. This will be
INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
"Arnie Rowland" wrote:
> I would NEVER write any code that had table names like T1 or T2, and columns
> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
> that you can post?
> Give us more to work with, and you may get some good assistance here.
> For example, the the code snipplet, is TYPE a column name, a literal, a
> varable? Where did it come from?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
> >I have table T1 and I am trying to extract some date from T1 table based on
> > inserts, updates and deletes. The destination table T2 has three fields
> > F1,
> > F2, F3, so I need something like
> >
> > Insert into T2 (F1, F2, F3)
> > Select (F1, F2,Type)
> > From T1
> >
> > Type should be defined based on Insert, Update, or Delete. This will be my
> > first trigger, Can anyone write this trigger for me?
> >
> >
> >
> >
>
>|||JIM.H. wrote:
> I have table T1 and I am trying to extract some date from T1 table based on
> inserts, updates and deletes. The destination table T2 has three fields F1,
> F2, F3, so I need something like
> Insert into T2 (F1, F2, F3)
> Select (F1, F2,Type)
> From T1
> Type should be defined based on Insert, Update, or Delete. This will be my
> first trigger, Can anyone write this trigger for me?
>
>
I'm guessing this is some sort of audit mechanism? If so, lots of info
available online regarding auditing, my friend Google found this one for me:
http://www.nigelrivett.net/AuditTrailTrigger.html
Incidentally, this type of activity, if done poorly, can cause BLOCKING
on updates and inserts. Just tossing that out there, since you've been
complaining all week about deadlocks.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, inside the Trigger you will be able to determine if it is executing
because of INSERT, UPDATE, DELETE.
If you are exploring some form of auditing, the archive table 'should' have
additional columns for the user and current date/time. Those are also
available internal to the Trigger.
Let us know how to help you when you're closer to the need.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
> Hi Arnie,
> That was just to simplify the case. I do not have actual table definitions
> yet. Type is what I am trying to determine in the trigger. This will be
> INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
>
> "Arnie Rowland" wrote:
>> I would NEVER write any code that had table names like T1 or T2, and
>> columns
>> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
>> that you can post?
>> Give us more to work with, and you may get some good assistance here.
>> For example, the the code snipplet, is TYPE a column name, a literal, a
>> varable? Where did it come from?
>> --
>> Arnie Rowland*
>> "To be successful, your heart must accompany your knowledge."
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>> >I have table T1 and I am trying to extract some date from T1 table based
>> >on
>> > inserts, updates and deletes. The destination table T2 has three fields
>> > F1,
>> > F2, F3, so I need something like
>> >
>> > Insert into T2 (F1, F2, F3)
>> > Select (F1, F2,Type)
>> > From T1
>> >
>> > Type should be defined based on Insert, Update, or Delete. This will be
>> > my
>> > first trigger, Can anyone write this trigger for me?
>> >
>> >
>> >
>> >
>>|||Thansk Arnie. Does this do update and insert safely? how can I do deleted?
CREATE TRIGGER trMyTrigger ON T1
FOR INSERT, UPDATE
AS
if exists (select * from inserted)
Insert Into T2(ID, Name,TrType)
Select ID, Name,'Insert'
From inserted
Where Name='TestData'
else
Update t
Set t.Name=i.Name, t.TrType='Update'
From inserted i INNER JOIN T2 t on i.ID=t.ID
GO
"Arnie Rowland" wrote:
> Yes, inside the Trigger you will be able to determine if it is executing
> because of INSERT, UPDATE, DELETE.
> If you are exploring some form of auditing, the archive table 'should' have
> additional columns for the user and current date/time. Those are also
> available internal to the Trigger.
> Let us know how to help you when you're closer to the need.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
> > Hi Arnie,
> >
> > That was just to simplify the case. I do not have actual table definitions
> > yet. Type is what I am trying to determine in the trigger. This will be
> > INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
> >
> >
> >
> > "Arnie Rowland" wrote:
> >
> >> I would NEVER write any code that had table names like T1 or T2, and
> >> columns
> >> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
> >> that you can post?
> >>
> >> Give us more to work with, and you may get some good assistance here.
> >>
> >> For example, the the code snipplet, is TYPE a column name, a literal, a
> >> varable? Where did it come from?
> >>
> >> --
> >> Arnie Rowland*
> >> "To be successful, your heart must accompany your knowledge."
> >>
> >>
> >>
> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
> >> >I have table T1 and I am trying to extract some date from T1 table based
> >> >on
> >> > inserts, updates and deletes. The destination table T2 has three fields
> >> > F1,
> >> > F2, F3, so I need something like
> >> >
> >> > Insert into T2 (F1, F2, F3)
> >> > Select (F1, F2,Type)
> >> > From T1
> >> >
> >> > Type should be defined based on Insert, Update, or Delete. This will be
> >> > my
> >> > first trigger, Can anyone write this trigger for me?
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>|||Triggers have access to 2 virtual tables, [inserted] and [deleted]. They are
identical in schema to the action table.
If there is an INSERT, [inserted] will have row(s) and [deleted] will be
empty.
If there is an UPDATE, both will have rows,
[deleted] has the state of the data before the UPDATE, and
[inserted] has the state of data after the UPDATE
If there is a DELETE, [deleted] will have row(s) and [inserted] will be
empty.
So you check both tables to determine what action fired the Trigger.
You probably should refer to Books on Line for additional information.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:EF507C01-C12B-439B-B9A9-856113703E38@.microsoft.com...
> Thansk Arnie. Does this do update and insert safely? how can I do deleted?
> CREATE TRIGGER trMyTrigger ON T1
> FOR INSERT, UPDATE
> AS
> if exists (select * from inserted)
> Insert Into T2(ID, Name,TrType)
> Select ID, Name,'Insert'
> From inserted
> Where Name='TestData'
> else
> Update t
> Set t.Name=i.Name, t.TrType='Update'
> From inserted i INNER JOIN T2 t on i.ID=t.ID
> GO
>
> "Arnie Rowland" wrote:
>> Yes, inside the Trigger you will be able to determine if it is executing
>> because of INSERT, UPDATE, DELETE.
>> If you are exploring some form of auditing, the archive table 'should'
>> have
>> additional columns for the user and current date/time. Those are also
>> available internal to the Trigger.
>> Let us know how to help you when you're closer to the need.
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
>> > Hi Arnie,
>> >
>> > That was just to simplify the case. I do not have actual table
>> > definitions
>> > yet. Type is what I am trying to determine in the trigger. This will be
>> > INSER,UPDATE,DELETE trigger, can I somehow figure it out in the
>> > trigger?
>> >
>> >
>> >
>> > "Arnie Rowland" wrote:
>> >
>> >> I would NEVER write any code that had table names like T1 or T2, and
>> >> columns
>> >> named F1, F2, F3. Is this real (and I hope not), or do you have table
>> >> DDL
>> >> that you can post?
>> >>
>> >> Give us more to work with, and you may get some good assistance here.
>> >>
>> >> For example, the the code snipplet, is TYPE a column name, a literal,
>> >> a
>> >> varable? Where did it come from?
>> >>
>> >> --
>> >> Arnie Rowland*
>> >> "To be successful, your heart must accompany your knowledge."
>> >>
>> >>
>> >>
>> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> >> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>> >> >I have table T1 and I am trying to extract some date from T1 table
>> >> >based
>> >> >on
>> >> > inserts, updates and deletes. The destination table T2 has three
>> >> > fields
>> >> > F1,
>> >> > F2, F3, so I need something like
>> >> >
>> >> > Insert into T2 (F1, F2, F3)
>> >> > Select (F1, F2,Type)
>> >> > From T1
>> >> >
>> >> > Type should be defined based on Insert, Update, or Delete. This will
>> >> > be
>> >> > my
>> >> > first trigger, Can anyone write this trigger for me?
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||Thanks Tracy, this helps a lot.
"Tracy McKibben" wrote:
> JIM.H. wrote:
> > I have table T1 and I am trying to extract some date from T1 table based on
> > inserts, updates and deletes. The destination table T2 has three fields F1,
> > F2, F3, so I need something like
> >
> > Insert into T2 (F1, F2, F3)
> > Select (F1, F2,Type)
> > From T1
> >
> > Type should be defined based on Insert, Update, or Delete. This will be my
> > first trigger, Can anyone write this trigger for me?
> >
> >
> >
> >
> I'm guessing this is some sort of audit mechanism? If so, lots of info
> available online regarding auditing, my friend Google found this one for me:
> http://www.nigelrivett.net/AuditTrailTrigger.html
> Incidentally, this type of activity, if done poorly, can cause BLOCKING
> on updates and inserts. Just tossing that out there, since you've been
> complaining all week about deadlocks.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

q; a trigger code

Monday, February 20, 2012

Q: this strange problem

I am having this strange problem, when I run my query in Query Analyzer, I
see the result is ok. But my report based on the same query shows first filed
repeats itself instead of showing right values. What might the problem be?Is this in development? In development the report data is cached (look for a
file with an extension called .data). If you change the paramter it will
requery. If no parameter then you can manually delete this file if you care.
The designer does this so when previewing you are not waiting for the query
to run.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:9E917150-64CD-43FA-8E80-84E716FC6AFE@.microsoft.com...
>I am having this strange problem, when I run my query in Query Analyzer, I
> see the result is ok. But my report based on the same query shows first
> filed
> repeats itself instead of showing right values. What might the problem be?

Q: sub-report visible/invisible

Hello,
I have a parameter and may sub-reports. I want to make one sub-report be
visible or invisible based one of the parameter true or false. Can I do this?answered in your other post, but answer is yes.
from other thread -
Yes that should work fine, right click on the greyed out subreport on your
master report, go to properties and visibility and stick the expression in
the box.
=iif(parameters!name.value = "true",false, true)
basically says "if parameter is true then show else don't show."
Greg
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:DB8F757A-0E83-4665-8205-04A51AF539BB@.microsoft.com...
> Hello,
> I have a parameter and may sub-reports. I want to make one sub-report be
> visible or invisible based one of the parameter true or false. Can I do
> this?
>

Wednesday, March 28, 2012

Query

Monday, March 26, 2012

Query

Tuesday, March 20, 2012

Queried parameters

quarter date

Wednesday, March 7, 2012

q; radius search

q; radius search

q; radius search

Saturday, February 25, 2012

q; a trigger code

q; a trigger code

Monday, February 20, 2012

Q: this strange problem

Q: sub-report visible/invisible

db time conversion

Blog Archive

About Me