Showing posts with label query. Show all posts
Showing posts with label query. Show all posts

Friday, March 30, 2012

Finding most recent date?

Hi,
Using query analyzer on a SQL 2000 server. I'm having trouble even
describing what I want here... Let's try this, here's the query:
select distinct pt.patient_id, payer_org_id as 'Payer ID', org.name as
'Payer Description',
pp.plan_name as 'Plan Name', pt_policy.policy_number as 'Policy Number',
Rank, claim_number, posted_transaction_date
from patient as pt
left join pt_policy on pt_policy.patient_id = pt.patient_id
left join organizations as org on org.org_id = pt_policy.payer_org_id
left join payer_plan as pp on pp.sys_id = pt_policy.payer_plan_sys_id
left join chg_item on chg_item.pt_policy_sys_id = pt_policy.sys_id
left join charge_on_claim as coc on coc.chg_item_sys_id = chg_item.sys_id
right join claim on claim.sys_id = coc.claim_sys_id
where pt_policy.discontinued = 'F' and pt.patient_id = '100561'
order by claim_number, pt.patient_id, rank
For each claim_number there are multiple chg_items but I would like to
return just the most recent chg_item based on the posted_transaction_date.
I can't quite figure it out though, I tried a subquery on the where clause
but that reduced it to one item being returned. This query should actually
return 13 records but there are three repeats. Basically I want one record
for each claim_number.
I hope this makes sense and is enough information. Any help is greatly
appreciated.
Thanks in advance,
LinnPlease post a simplified version of table structures, sample data & expected
results. For details refer to: www.aspfaq.com/5006
In general, you can have an approach similar to:
( based on guesswork )
SELECT
FROM claim_tbl t1
WHERE t1._col = ( SELECT TOP 1 t2._col
FROM change_item_tbl t2
WHERE t2.claim_nbr = t1.claim_nbr
ORDER BY posted_transaction_date DESC ) ;
Anith|||you have to use a correlated subquery that has a where clause joining it bac
k
to the main query.
select id, patient, org, a_date
from patient a
where a_date=(select max(a_date) from patient b where b.patient=a.patient)
--
If it aint broke don't brake it.
"Linn Kubler" wrote:

> Hi,
> Using query analyzer on a SQL 2000 server. I'm having trouble even
> describing what I want here... Let's try this, here's the query:
> select distinct pt.patient_id, payer_org_id as 'Payer ID', org.name as
> 'Payer Description',
> pp.plan_name as 'Plan Name', pt_policy.policy_number as 'Policy Number',
> Rank, claim_number, posted_transaction_date
> from patient as pt
> left join pt_policy on pt_policy.patient_id = pt.patient_id
> left join organizations as org on org.org_id = pt_policy.payer_org_id
> left join payer_plan as pp on pp.sys_id = pt_policy.payer_plan_sys_id
> left join chg_item on chg_item.pt_policy_sys_id = pt_policy.sys_id
> left join charge_on_claim as coc on coc.chg_item_sys_id = chg_item.sys_id
> right join claim on claim.sys_id = coc.claim_sys_id
> where pt_policy.discontinued = 'F' and pt.patient_id = '100561'
> order by claim_number, pt.patient_id, rank
> For each claim_number there are multiple chg_items but I would like to
> return just the most recent chg_item based on the posted_transaction_date.
> I can't quite figure it out though, I tried a subquery on the where clause
> but that reduced it to one item being returned. This query should actuall
y
> return 13 records but there are three repeats. Basically I want one recor
d
> for each claim_number.
> I hope this makes sense and is enough information. Any help is greatly
> appreciated.
> Thanks in advance,
> Linn
>
>|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eCsxw%23DWGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Please post a simplified version of table structures, sample data &
> expected results. For details refer to: www.aspfaq.com/5006
> In general, you can have an approach similar to:
> ( based on guesswork )
> SELECT
> FROM claim_tbl t1
> WHERE t1._col = ( SELECT TOP 1 t2._col
> FROM change_item_tbl t2
> WHERE t2.claim_nbr = t1.claim_nbr
> ORDER BY posted_transaction_date DESC ) ;
> --
> Anith
>
Thanks much Anith and Joe, both solutions worked once I figured out the
from/join clauses I needed.
For those of you playing along at home, here's what I have at this point:
select distinct claim_number as 'Claim',
RTRIM(pt.last_name) + ', ' + ISNULL(RTRIM(pt.first_name), ' ') AS 'Patient',
payer_org_id as 'Payer ID', org.name as 'Payer Description',
pp.plan_name as 'Plan Name', pt_policy.policy_number as 'Policy Number',
( select min(start_date)
from claim as c1
left join charge_on_claim as coc on coc.claim_sys_id = c1.sys_id
left join chg_item on chg_item.sys_id = coc.chg_item_sys_id
where c1.claim_number = claim.claim_number ) as 'Start Date',
( select max(end_date)
from claim as c1
left join charge_on_claim as coc on coc.claim_sys_id = c1.sys_id
left join chg_item on chg_item.sys_id = coc.chg_item_sys_id
where c1.claim_number = claim.claim_number ) as 'End Date',
( select max(posted_transaction_date)
from claim as c1
left join charge_on_claim as coc on coc.claim_sys_id = c1.sys_id
left join chg_item on chg_item.sys_id = coc.chg_item_sys_id
where c1.claim_number = claim.claim_number ) as 'Transaction Date'
from patient as pt
left join pt_policy on pt_policy.patient_id = pt.patient_id
left join organizations as org on org.org_id = pt_policy.payer_org_id
left join payer_plan as pp on pp.sys_id = pt_policy.payer_plan_sys_id
left join chg_item on chg_item.pt_policy_sys_id = pt_policy.sys_id
left join charge_on_claim as coc on coc.chg_item_sys_id = chg_item.sys_id
right join claim on claim.sys_id = coc.claim_sys_id
where pt_policy.discontinued = 'F'
Once I figured out the posted_transaction_date it was easy to apply it to
two other date fields.
There is only one more wrinkle in my query and that is to total all the
amounts related to each claim. I have successfully compiled another query:
select claim.claim_number, sum(amount) as 'Claim Balance'
from claim
left join ar_detail on ar_detail.claim_sys_id = claim.sys_id
group by claim.claim_number
order by claim.claim_number
But my attempts to introduce this directly into the above query have so far
been unsuccessfull. If I make these queries into views I can easily combine
them but I'm suspecting there is a more elegant solution. My problem is
that this is a purchased database product and I'm not allowed to add my own
views or tables to the DB. So I'm using either Visual FoxPro or Excel to
build these reports.
I tried following the etiquette rules you referenced Anith but I prefer
making timely responses to follow up posts and with all the one-to-many
relations in this query I'll be assembling adequate sample data for a w.
I'll start working on it but if you have any suggestions based on what I've
already provided I'm willing to give them a try.
I thought it was possible to do a subquery in a join statement but I can't
figure out the syntax. Now that I write that I don't think that's the
correct approach anyways. When I added it to the fields list, similar to
the date fields, it simply totalled up all of the amounts instead of total
by claim number. Any suggestions are welcome.
Thanks again,
Linn

finding missing number

Hi Guys,
I am using sql server 2000 and i want to find missing
number between 1 and 1000 in a table.
what is the query for that?
pls advice me.
RGDS
BijuAssuming you have another table called Numbers that contains all the
required numbers:
SELECT num
FROM Numbers
WHERE NOT EXISTS
(SELECT *
FROM YourTable
WHERE num = Numbers.num)
AND num BETWEEN 1 AND 1000
David Portas
SQL Server MVP
--|||If there's only one Num Missing, this will work...
Select Num - 1
From <TableName> T
Where Num Between 1 And 1001
And Not Exists
(Select * From <TableName>
Where Num = T.Num - 1)
If there's a possibility of multiple Sequential numbers missing,
asin
1
2
3
6
7
...
then Use David's solution
"bijupg" wrote:

> Hi Guys,
> I am using sql server 2000 and i want to find missing
> number between 1 and 1000 in a table.
> what is the query for that?
> pls advice me.
> RGDS
> Biju
>|||Or use SQL Server 2000's nice TABLE variable to create a control table:
-- Use the Edit menu's 'Replace Template Parameters...' command to replace
the your_table/your_field values
DECLARE @.control TABLE ( control_no INT PRIMARY KEY )
DECLARE @.i INT
SET NOCOUNT ON
SET @.i = 1
-- Add control numbers to temp table
WHILE @.i Between 1 And 1000
BEGIN
INSERT @.control VALUES( @.i )
SET @.i = @.i + 1
END
SET NOCOUNT OFF
-- List missing values
SELECT t.*
FROM @.control t
LEFT JOIN <your_table, SYSNAME, > c ON t.control_no = c.<your_field,
SYSNAME, >
WHERE c.control_no Is Null|||If you want the starting of each gap, you can do:
SELECT nbr + 1
FROM tbl
WHERE NOT EXISTS( SELECT *
FROM tbl t1
WHERE t1.nbr = tbl.nbr + 1 )
AND nbr <= 1000 ;
If you want the start & end of each set of missing numbers:
SELECT t1.Nbr + 1 AS "start",
MIN( t2.Nbr ) - 1 AS "end"
FROM tbl t1
INNER JOIN tbl t2
ON t1.Nbr < t2.Nbr
GROUP BY t1.Nbr
HAVING MIN( t2.Nbr ) - t1.Nbr > 1;
If you want to list all the missing numbers, following the suggestions to
use a table of sequential numbers.
Anith

Finding Max Length of ntext column in SP

I have upsized an access database and am trying to find all the maximum
lengths of the columns, so I can restructure the DB. I have the query that
will give me the max length of the column, but since I have a bunch of
columns, I would like to write a SP to do it for all the columns.
Here is my query
SELECT TOP 1 DATALENGTH([Members Present]) AS MembersLenMax
FROM tblIPPCore2
ORDER BY MembersLenMax DESC;
How can I automate this query?
Thanks,
DrewI have modified my query to the following,
SELECT MAX(DATALENGTH([Members Present])) AS MembersPresentLenMax
FROM tblIPPCore2
Thanks,
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:OP4nSJvVFHA.1508@.tk2msftngp13.phx.gbl...
>I have upsized an access database and am trying to find all the maximum
>lengths of the columns, so I can restructure the DB. I have the query that
>will give me the max length of the column, but since I have a bunch of
>columns, I would like to write a SP to do it for all the columns.
> Here is my query
> SELECT TOP 1 DATALENGTH([Members Present]) AS MembersLenMax
> FROM tblIPPCore2
> ORDER BY MembersLenMax DESC;
> How can I automate this query?
> Thanks,
> Drew
>|||Here is the answer...
This Procedure takes @.TableName, and returns the maximum lengths for each
column in the table. There is no error correction, so column names must not
have spaces or weird characters.
CREATE PROCEDURE spMaxLen
@.TableName varchar(20)
AS
declare @.v_tempTable TABLE (myid bigint identity(1,1), sSQL varchar(1000))
DECLARE @.vMaxCount int, @.iCount int, @.sSQL varchar(1000)
INSERT into @.v_tempTable (sSQL)
SELECT 'SELECT MAX(DATALENGTH([' + column_name + '])) AS ' +
replace(column_name, ' ', '') + 'MaxLen FROM ' + @.TableName
FROM information_schema.columns
where table_name = @.TableName
SET @.vMaxCount =@.@.ROWCOUNT
set @.iCount = 1
WHILE @.iCount < @.vMaxCount + 1
BEGIN
SELECT @.sSQL = sSQL from @.v_tempTable where myid = @.iCount
exec (@.ssql)
SET @.iCount = @.iCount + 1
END
GO
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:OP4nSJvVFHA.1508@.tk2msftngp13.phx.gbl...
>I have upsized an access database and am trying to find all the maximum
>lengths of the columns, so I can restructure the DB. I have the query that
>will give me the max length of the column, but since I have a bunch of
>columns, I would like to write a SP to do it for all the columns.
> Here is my query
> SELECT TOP 1 DATALENGTH([Members Present]) AS MembersLenMax
> FROM tblIPPCore2
> ORDER BY MembersLenMax DESC;
> How can I automate this query?
> Thanks,
> Drew
>

finding max date

I am not sure how to phrase this query...if i could have MAX in my
where clause it would be:
SELECT jj.id, jj.lastname, jj.firstname, jj.StartDt, jj.TotalDays,
sum(jpm.NbrDaysPaidFor) AS DaysPaidFor, sum(jpm.Amount) AS TotalPaid
FROM table1 jj
inner join table2 jp on jj.id = jp.id
inner join table3 jpm on jp.payeeid = jpm.payeeid
inner join table4 jpc on jpm.paychecknbr = jpc.paychecknbr
inner join table5 jt on jpc.trid = jt.trid
where (jj.enddt BETWEEN @.StartDt AND @.EndDt)
AND (max(jt.verifieddt) BETWEEN @.StartDt AND @.EndDt)
I need to get the MAX verifieddt and then check to see if it is between
the startdt and enddt passed. Can someone help?
Thank you!*untested*
SELECT jj.id, jj.lastname, jj.firstname, jj.StartDt, jj.TotalDays,
sum(jpm.NbrDaysPaidFor) AS DaysPaidFor, sum(jpm.Amount) AS TotalPaid
FROM table1 jj
inner join table2 jp on jj.id = jp.id
inner join table3 jpm on jp.payeeid = jpm.payeeid
inner join table4 jpc on jpm.paychecknbr = jpc.paychecknbr
inner join table5 jt on jpc.trid = jt.trid
where (jj.enddt BETWEEN @.StartDt AND @.EndDt)
group by jj.id, jj.lastname, jj.firstname, jj.StartDt, jj.TotalDays
having (max(jt.verifieddt) BETWEEN @.StartDt AND @.EndDt)|||I did forget my group by statement b/c I left out the rest of my where
for ease of reading. I probably should have just put the whole thing
up here...when I tried 'having', I got an error that the column was
invalid in the having b/c it is not contained in either an aggregate
function or the group by clause.
so, here's the whole hairy mess:
SELECT t1.id, t1.lastname, t1.firstname, t1.startdt, t1.totaldays,
sum(t3.NbrDaysPaidFor) AS DaysPaidFor, sum(t3.Amount) AS TotalPaid
FROM table1 t1
inner join table2 t2 on t1.id = t2.id
inner join table3 t3 on t2.payeeid = t3.payeeid
inner join table4 t4 on t3.paychecknbr = t4.paychecknbr
inner join table5 t5 on t4.trid = t5.trid
where t1.totaldays > 1
AND t1.statuscode = 'PF'
AND t3.StatusCode = 'VE'
GROUP BY t1.id, t1.lastname, t1.firstname, t1.startdt, t1.totaldays
having (((((t1.enddt BETWEEN @.StartDt AND @.EndDt) AND
(max(t5.verifieddt) BETWEEN @.StartDt AND @.EndDt))
OR (t1.enddt < @.StartDt AND max(t5.verifieddt) BETWEEN @.StartDt AND
@.EndDt))
AND t4.statuscode IN (SELECT code FROM table6 WHERE codetype =
'status'
AND (code = 'GO' OR code = 'GP')))
OR (max(t5.verifieddt) < @.StartDt AND (t4.VoidDt BETWEEN @.StartDt AND
@.EndDt)))|||When I run your query I get the 'column invalid in HAVING clause' error on
t1.enddt, t4.statuscode and t4.voiddt. Since you are already grouping on
t1.id, I would say that it is safe to also group on t1.enddt. Throw that
into your GROUP BY clause and now you just have t4 to worry about.
I'm not sure about this bit:
max(t5.verifieddt) < @.StartDt AND t4.VoidDt BETWEEN @.StartDt AND @.EndDt
Which t4.VoidDt value are you talking about here? Are you expecting several
t4 rows for each t1 row? (it looks like it).
In English, is this line trying to say "where 'the maximum value of
t5.verifieddt on those rows whose corresponding t4.voiddt is between
@.startDt and @.endDt' is less than @.startDt"?
"Confused" <cschanz@.gmail.com> wrote in message
news:1138386931.324271.157020@.f14g2000cwb.googlegroups.com...
>I did forget my group by statement b/c I left out the rest of my where
> for ease of reading. I probably should have just put the whole thing
> up here...when I tried 'having', I got an error that the column was
> invalid in the having b/c it is not contained in either an aggregate
> function or the group by clause.
> so, here's the whole hairy mess:
> SELECT t1.id, t1.lastname, t1.firstname, t1.startdt, t1.totaldays,
> sum(t3.NbrDaysPaidFor) AS DaysPaidFor, sum(t3.Amount) AS TotalPaid
> FROM table1 t1
> inner join table2 t2 on t1.id = t2.id
> inner join table3 t3 on t2.payeeid = t3.payeeid
> inner join table4 t4 on t3.paychecknbr = t4.paychecknbr
> inner join table5 t5 on t4.trid = t5.trid
> where t1.totaldays > 1
> AND t1.statuscode = 'PF'
> AND t3.StatusCode = 'VE'
> GROUP BY t1.id, t1.lastname, t1.firstname, t1.startdt, t1.totaldays
> having (((((t1.enddt BETWEEN @.StartDt AND @.EndDt) AND
> (max(t5.verifieddt) BETWEEN @.StartDt AND @.EndDt))
> OR (t1.enddt < @.StartDt AND max(t5.verifieddt) BETWEEN @.StartDt AND
> @.EndDt))
> AND t4.statuscode IN (SELECT code FROM table6 WHERE codetype =
> 'status'
> AND (code = 'GO' OR code = 'GP')))
> OR (max(t5.verifieddt) < @.StartDt AND (t4.VoidDt BETWEEN @.StartDt AND
> @.EndDt)))
>|||> In English, is this line trying to say "where 'the maximum value of
> t5.verifieddt on those rows whose corresponding t4.voiddt is between
> @.startDt and @.endDt' is less than @.startDt"?
Eek, word problems, which I always detested.
"Confused", please see http://www.aspfaq.com/5006 ... this way, you can
provide us proper specs, we can give a tested and working solution, and end
this madness.
A

Thursday, March 29, 2012

Finding jobs that wont run due to Daylight Savings Time?

Since this weekend is Daylight Savings, we want to make sure of any
jobs that won't run due to the "missing hour".

I have written a small query against sysjobschedules that tells me
which ones have a schedule that runs between 2 and 3. However, it
doesn't include any jobs that run every X minutes/hours, and so might
be run during that time. (i.e. a job that starts at midnight and runs
until 6, running every 2 hours)

Is there any (easy) way to determine this? I might be able to build a
function that uses the other fields in sysjobschedules to give a list
of times that the job will run, but I haven't gotten to that point yet.
Figured someone might have something already, rather than reinvent the
wheel.

Thanks,
MichaelI don't believe there's any easy way, but sysjobhistory might be a good
starting point - if a job ran between 2 and 3 in the past, it will
probably do so again. You could join on sysjobschedules to narrow it
down to daily schedules, schedules on a certain weekday or date etc.
And you can check the next_run_date column as well, of course.

Simon

finding hidden chars in sql query


I am working on a login system in flex and asp. I am encrypting the password before it is inserted it into the SQL database. But then when i do SELECT statement with username and encrypted password it returns 0 users found.
I outputted the sql statement and they passwords look excatly the same. But the SQL Select count(*) returns a 0.

link to the encrypting is used:
http://www.4guysfromrolla.com/webtech/110599-1.2.shtml

other information:
script language: asp + flex
database: sql server 2005

So i am thinking that their are hidden chars in the password. Is their a way to check this or even convert/exclude them ?
Any links or tips would be very helpfull

Use the sample to solve your issue...

Alter Function dbo.En_De_Crypt(@.Input varchar(max), @.Key int ) Returns Varchar(Max) as

Begin

Declare @.Len as Int;

Declare @.I as Int;

Declare @.Output as Varchar(max)

Select @.Len = Len(@.Input), @.I =1, @.Output=''

Declare @.Number Table (N int);

While(@.I<=@.Len)

Begin

Insert into @.Number Values(@.I);

Set @.I = @.I + 1;

End

Select @.Output = @.Output + Char(Ascii(Substring(@.Input,N,1)) ^ @.Key)

From @.Number

return @.Output

End

Go

Create Table #Passwords(

Password varchar(20)

);

Insert Into #Passwords Values(dbo.En_De_Crypt('One1234$$',100));

Insert Into #Passwords Values(dbo.En_De_Crypt('M1cr0$0ft',100));

Insert Into #Passwords Values(dbo.En_De_Crypt('Or@.c1e',100));

Insert Into #Passwords Values(dbo.En_De_Crypt('@.pp1e',100));

Select dbo.En_De_Crypt(Password,100) ,Password From #Passwords

/*

Orginal value Decrypted Value

One1234$$ + _VWP@.@.

M1cr0$0ft )Up'16T@.T '10

Or@.c1e +_$p_par

@.pp1e $__U_par */

--None of the query will return the data here..

Select Count(*) From #Passwords Where Password = '+

_VWP@.@.'

Select Count(*) From #Passwords Where Password = ')Up'16T@.T '10'

Select Count(*) From #Passwords Where Password = '?_p$'

Select Count(*) From #Passwords Where Password = '$__U_par'

--Use the following query to get the result..

Select Count(*) From #Passwords Where dbo.En_De_Crypt(Password,100)='M1cr0$0ft'

|||thnx Manivannan for the reply.
i will try it out.

Finding Duplicates

I am trying to complete an insert from query but the problem is I have duplicates, so I'm getting an error message. So to correct it I am creating a Find Duplicates statement in the Query analyzer but Its not working can someone tell me whats wrong with this statement (by the way I'm in SQL 2000 Server)

thank you

SELECT EmployeeGamingLicense [TM#]AS [TM# Field], Count([TM#])AS NumberOfDups
FROM TERMINATION
GROUP BY [TM#]
HAVING Count([TM#])>1;
GOHey Desiree...been a while...

What's this:

EmployeeGamingLicense [TM#]AS [TM# Field],

Doesn't look like one column...how about the DDL for the TERMINATION table...

Or how about

SELECT [TM#], Count([TM#]) AS NumberOfDups
FROM TERMINATION
GROUP BY [TM#]
HAVING Count([TM#])>1|||HI BRETT!!! :) HOw ya Been?

Your right totally over looked that, thank you. Works perfect now. Got another question though, what if I wanted to find duplicate records from one table to another. I would like to be able to see if I can find matching records in two tables One is the TERMINATION.tbl the other EmployeeGamingLicense.tbl. Can I make this into a Stored procedure?

SELECT EmployeeGamingLicense.[TM#], EmployeeGamingLicense.LASTNAME, EmployeeGamingLicense.FIRSTNAME, EmployeeGamingLicense.[SSN#]
FROM EmployeeGamingLicense LEFT JOIN TERMINATION ON EmployeeGamingLicense.[TM#] = TERMINATION.[TM #]
WHERE (((TERMINATION.[TM #]) Is Not Null));|||what if I wanted to find duplicate records from one table to another. I would like to be able to see if I can find matching records in two tables...

I'd have to ask you what that means...

For example...in your previous query, you found dups...

Do you want to see the rows in the License table where you have dups in TERMINATION?

Do you want to see where you have dups because of a join between the 2?

I'm not sure...|||I'm good my statement worked I found the duplicates I needed to find. The thing is I'm Inheriting databases and I need to find the errors with them before I create the Gui for the client. Thank you Brett :)|||So you have to audit the entire db that has a poorly defined schema...

good luck

Any dates or numerics in varchar fields?

Once you clean up the dups...do you plan to put unique constarints on?|||No the dates are in (datetype: smalledatetime) and the Numbers like SSN# is in (datetype: Int) :)|||A little off topic but what do you do with SSN's that begin with 0?|||Anything that does not get math applied to it, is not a number in my book...

You want to constrain it to be numeric...that's fine...otherwise it's char or varchar|||is that ok though for them to be Int and smalldatetime, or do you suggest char or nvarchar|||I agree, we use char(9) in our db's but I've had to fight off developers that want SSN and TIN fields as int datatypes. I hate to have to determine if I need to add leading zeros back to SSN's that are int datatypes or if the SSN is truly corrupt/bad data.|||Ok this is defintely one of those learning processes I desperatley need. I love the feed back forces me to analyze the datatypes more deeply especially for future purposes. Ok so you would reccommend char instead.|||Ok Interger data consist of negative and positive whole numbers such as -15, 0, 5, and 2,509. Ok so INT base data type is a Number in the range from -2,147,483,648 through 2,147483.647 only requires 4 bytes of storage per value). Ok and char can consist of numberics, and can consist of up to 8 KB|||Dates should defenitley stay as dates...why small though?

Is space an issue?

Just a rule of thumb....

Does math need to applied? No? Then it's char or varchar...

Now here's another one...

Are you're dates nullable or not nullable?|||A little off topic but what do you do with SSN's that begin with 0?Worse yet, what do you do for the wierdo's among us with letters in their SSN?

-PatP|||What is that suppose to mean?|||No good question didnt even think about that No space is not an issue so it doesnt have to be smalldatetime can just be datetime datatype instead.

THANK YOU :)

I Love this really makes you think and plan ahead, and with database you need to plan ahead eheheh|||Is anyone as in love with BOOKS ONLINE as I am. :)
and this FORUM OF COURSE|||Just make everything nvarchar(4000) especially since space isn't an issue. That way every column is the same and easier to manage. Better yet text works well too.|||Just make everything nvarchar(4000) especially since space isn't an issue. That way every column is the same and easier to manage. Better yet text works well too.

Now Peter...|||SSNs should be char(9). Listen to Der Kaisermann.

(OK, char(11) would be a second choice...)|||SSNs should be char(9). Listen to Der Kaisermann.

(OK, char(11) would be a second choice...)If you are only storing the kind of SSN/TIN numbers used today, that might be true. If you have to store all of the SSN values, you'll need a VARCHAR(12).

For most purposes, you can get by with only the 9 digit SSN values used today, and an indicator for non-standard ones that you can then describe in more detail for the exceptions in some kind of "notes" column.

-PatP|||What is that suppose to mean?

OK, examples?...but my spider sense says you're right...

Only because peoplesoft has National Id as varchar(12)|||The commonly used SSN values today are 999-99-9999

Immigrant families, circa 1948-1952 are formatted like 999-99-9999#A9, with the first nine digits being the same for all of them.

Families of business owners, circa 1963-1965 were formatted 999-99-9999C99, with the first nine digits being the business owner's SSN.

Although rare, tax paying non-residents are formatted as either aa9-000-99-9999, 999-00-9999AA9, or a few variants. These don't follow much of any rules that I know of, although there might be some rhyme or reason under the surface.

There aren't a bunch of these "problem child" values, but there are enough that they can cause massive headaches.

Then there are the real pain-in-the-patoot types that have earned significant amounts under one of those "oddball" formats, then were given another one of the commonly used variety (999-99-9999) for "administrative convenience" and established enough earnings/etc that neither could be rescinded. Those folks have the distinction of having two active, legitimate SSNs, and may even be paying taxes on both of them at the same time! They can ball up just about anything, since even the packages that support the nonstandard formats have no real way to establish a relationship between them!

-PatP|||Wow, I had to deal with SSN's, but not in the case where a person has more than 1 at any given time... In my case every newborn is given a fake SSN (usually mother's SSN-1) until a name is given to a child (boggles my mind when a mother is carrying a baby for 9 months and at the time of birth has no idea how to call it!!!!!!)|||Pat, I do NOT want to play Trivial Pursuit with you.|||Pat, I do NOT want to play Trivial Pursuit with you.It seems like nobody wants to play with me!

I've got a bunch of great Trivial Pursuit stories. I once won a game where I went 5 for 5, then spent something like 15 turns trying to get the sports one (in a room of sports-aholics).

-PatP|||It seems like nobody wants to play with me!
-PatP

Not even the girlfreind?|||Brett, you don't play Trivial Pursuit with your girlfriend. You play Twister.|||The commonly used SSN values today are 999-99-9999

Immigrant families, circa 1948-1952 are formatted like 999-99-9999#A9, with the first nine digits being the same for all of them.

-PatP

...and here I thought it was something Canadien?!?|||No, Canadian SSNs use the metric system. Everybody knows that.|||If you are only storing the kind of SSN/TIN numbers used today, that might be true. If you have to store all of the SSN values, you'll need a VARCHAR(12).

For most purposes, you can get by with only the 9 digit SSN values used today, and an indicator for non-standard ones that you can then describe in more detail for the exceptions in some kind of "notes" column.

-PatP
I'm a bit confused. My table contains SSNs (all unique) and i've indexed the column, and is often used in WHERE clauses. It's said of that indexes shall not be created on FREE-FORM text columns (varchar usually). Some bad examples in this context are Flag column and SSN column. But since SSN's unique surely one can index it.

CHAR(12) would be better or VARCHAR(12)?

Thanx!|||What? You still here? What were we talking about, again?

Oh yeah. I remember.

"Thou Shalt Not Create Indexes On Character Fields"? I know of no such commandment. They aren't as efficient as indexes on, say, integer columns, but its still much better than not having an index on your character field.

But I'd use varchar unless you want to enforce the length of the string. That's why I originally suggested char(9) or char(11), but as Pat pointed out not ALL social security numbers are the same length.|||WHAT?

I thought you used IDENTITY Columns in every table?|||sorry to for the stupid question but what do you mean By Free-Form what is exactly is that?

Inquisitive by nature :)|||I cant believe we are still on this you guys are so funny

I love it :)|||What? Me use IDENTITY?

I NEVER use IDENTITY columns.

I'm the GUID GUY, remember?

...and by Free-form, he means that users can put whatever text they want in the field. It's not populated from a drop-down list, for instance.|||There is a hard limit to the size of an index key of just over 900 bytes. So you can not index a varchar(1000) field, if you expect to be storing values that are 1000 characters long. If all of the values happen to be less than the limit (I do not know it off hand. I know, bad DBA), then you can get away with it. Of course, the first 1000 character value to come along will hurt you bad.|||What? You still here? What were we talking about, again?

Oh yeah. I remember.

"Thou Shalt Not Create Indexes On Character Fields"? I know of no such commandment. They aren't as efficient as indexes on, say, integer columns, but its still much better than not having an index on your character field.

But I'd use varchar unless you want to enforce the length of the string. That's why I originally suggested char(9) or char(11), but as Pat pointed out not ALL social security numbers are the same length.
Sorry, bear with me plz!
Shouldn't one use an index on a column even if it's unique. SSNs are usually used in the WHERE clauses in applications(in my application just like emp.id). Users in app. access individual records on the basis on SSN. If a table has millions of records, Index Scan or Table Scan?
Stupid question, it is. but doesn't bother me. Your answer may help in clearing the concepts. :cool:|||You may get an Index Seek. But even Indes Scan is better than Table Scan in your situation, because there are much fewer index pages for SSN index of char(9) vs. data pages for your table.|||Sorry, bear with me plz!
Shouldn't one use an index on a column even if it's unique. SSNs are usually used in the WHERE clauses in applications(in my application just like emp.id). Users in app. access individual records on the basis on SSN. If a table has millions of records, Index Scan or Table Scan?
Stupid question, it is. but doesn't bother me. Your answer may help in clearing the concepts. :cool:
Well, if it's truly unique it should have a unique index or unique constraint(which creates a unique index) on the column. Check out this article about index rules in general http://www.sql-server-performance.com/optimizing_indexes.asp|||You may get an Index Seek. But even Indes Scan is better than Table Scan in your situation, because there are much fewer index pages for SSN index of char(9) vs. data pages for your table.

Getting off the floor and into my barstool...umm office chair

What?

You're not suggesting a scan is a good thing? Are you?|||You're not suggesting a scan is a good thing? Are you?An index scan versus a table scan... It could be a good thing, in relative terms.

-PatP

Finding duplicates

Here is what I have so far. Would anyone know if this query picks up all id
numbers that have the same amount on a certain settlment batch number? They
are also both credit sales that were approved. Thanks for any info.
Use WinPayment
GO
SELECT
pos_condition_code,
convert(char(11), retrieval_reference_number) RR,
message_type,
authorization_identification,
convert(char(8), card_acceptor_identification) SN,
convert(char(25), transaction_name) TransactionName,
isnull(convert(char(2), id_code_1), ' ') ID,
convert (char (20), id_number_1)CardNumber,
convert(char(20), time_stamp)POSTime,
convert (char (20), transmission_date_and_time)TransmissionT
ime,
convert(char(2), response_code) RC,
isnull(convert(char(2), host_response_code), '') HRC,
convert(char(20), host_response_string)Message,
convert(char(7), stan) STAN,
convert(char(12), transaction_amount) Amount,
settlement_data
FROM
financial_message as Sale (NOLOCK)
Where
settlement_batch_number = '773'
AND
transaction_name = 'Credit Sale'
And
host_response_string = 'Successful Approval'
AND EXISTS
(Select *
FROM financial_message AS Rev
WHERE Rev.id_number_1 = Sale.id_number_1
AND Rev.transaction_amount = Sale.transaction_amount
AND Rev.settlement_batch_number = sale.settlement_batch_number
AND Rev.transaction_name = sale.transaction_name
And Rev.host_response_string = sale.host_response_string)
order by
time_stampYour filter is based on this
Rev.id_number_1 = Sale.id_number_1
AND Rev.transaction_amount = Sale.transaction_amount
AND Rev.settlement_batch_number = sale.settlement_batch_number
AND Rev.transaction_name = sale.transaction_name
And Rev.host_response_string = sale.host_response_string
Thus, only row(s) that satisfy the requirements would be returned.
-oj
"tarheels4025" <tarheels4025@.discussions.microsoft.com> wrote in message
news:2EF69A8A-0019-4FF7-89BD-A31499D3C8EC@.microsoft.com...
> Here is what I have so far. Would anyone know if this query picks up all
> id
> numbers that have the same amount on a certain settlment batch number?
> They
> are also both credit sales that were approved. Thanks for any info.
> Use WinPayment
> GO
> SELECT
> pos_condition_code,
> convert(char(11), retrieval_reference_number) RR,
> message_type,
> authorization_identification,
> convert(char(8), card_acceptor_identification) SN,
> convert(char(25), transaction_name) TransactionName,
> isnull(convert(char(2), id_code_1), ' ') ID,
> convert (char (20), id_number_1)CardNumber,
> convert(char(20), time_stamp)POSTime,
> convert (char (20), transmission_date_and_time)TransmissionT
ime,
> convert(char(2), response_code) RC,
> isnull(convert(char(2), host_response_code), '') HRC,
> convert(char(20), host_response_string)Message,
> convert(char(7), stan) STAN,
> convert(char(12), transaction_amount) Amount,
> settlement_data
> FROM
> financial_message as Sale (NOLOCK)
> Where
> settlement_batch_number = '773'
> AND
> transaction_name = 'Credit Sale'
> And
> host_response_string = 'Successful Approval'
> AND EXISTS
> (Select *
> FROM financial_message AS Rev
> WHERE Rev.id_number_1 = Sale.id_number_1
> AND Rev.transaction_amount = Sale.transaction_amount
> AND Rev.settlement_batch_number = sale.settlement_batch_number
> AND Rev.transaction_name = sale.transaction_name
> And Rev.host_response_string = sale.host_response_string)
> order by
> time_stamp

Wednesday, March 28, 2012

Finding Duplicates

I have a company table and I would like to write a query that will return to
me any duplicate companies. However, it is a little more complicated then
just matching on exact company names. I would like it to give me duplicates
where x number of letters at the beginning of the company name match AND x
number of letters of the address match AND x number of letters of the city
match. I will be doing this in batches based on the first letter of the
company name. So for example I will first process all companies that start
with the letter "A".

So for all "A" companies I want to find companies where the first 5 letters
in the company name match and the first 5 characters of the address field
match and the first 5 characters of the city match. THANKS!!!Can you post simplified DDLs, some sample data & expected results? For
detail refer to : www.aspfaq.com/5006

--
- Anith
( Please reply to newsgroups only )|||"Erich" <erich93063@.hotmail.com> wrote in message news:<102gbomj7gc84f7@.corp.supernews.com>...
> I have a company table and I would like to write a query that will return to
> me any duplicate companies. However, it is a little more complicated then
> just matching on exact company names. I would like it to give me duplicates
> where x number of letters at the beginning of the company name match AND x
> number of letters of the address match AND x number of letters of the city
> match. I will be doing this in batches based on the first letter of the
> company name. So for example I will first process all companies that start
> with the letter "A".
> So for all "A" companies I want to find companies where the first 5 letters
> in the company name match and the first 5 characters of the address field
> match and the first 5 characters of the city match. THANKS!!!

Something like this may work:

select t.*
from dbo.MyTable t
join
(
select
left(CompanyName, 5) as 'CompName',
left(Address, 5) as 'Addr',
left(City, 5) as 'City',
count(*) as 'Dupes'
from
dbo.MyTable
where
left(CompanyName, 1) = 'A'
group by
left(CompanyName, 5),
left(Address, 5),
left(City, 5)
having
count(*) > 1
) dt
on dt.CompName = left(t.CompanyName, 5)
and dt.Addr = left(t.Address, 5)
and dt.City = left(t.City, 5)

If this doesn't work as you expect, then please consider posting your
table DDL, as well as some sample data.

Simon|||This may work as well, assuming a parameter is passed into the stored proc
or function for the first letter:

SELECT C1.CompanyID, C2.CompanyID,
C1.CompanyName, C2.CompanyName,
C1.Address, C2.Address, C1.City, C2.City
FROM Company C1 JOIN Company C2 ON
LEFT(C1.CompanyName, 5) = LEFT(C2.CompanyName, 5) AND
LEFT(C1.Address, 5) = LEFT(C2.Address, 5) AND
LEFT(C1.City, 5) = LEFT(C2.City, 5) AND
C1.CompanyID != C2.CompanyID
WHERE LEFT(C1.CompanyName, 1) = @.FirstLetter

You could also use a variable parameter instead of hard-coding "5" to allow
for more specific or more general matches.
ie, ... LEFT(C1.Company, @.MatchLength) = LEFT(C2.CompanyName, @.MatchLength)

"Erich" <erich93063@.hotmail.com> wrote in message
news:102gbomj7gc84f7@.corp.supernews.com...
> I have a company table and I would like to write a query that will return
to
> me any duplicate companies. However, it is a little more complicated then
> just matching on exact company names. I would like it to give me
duplicates
> where x number of letters at the beginning of the company name match AND x
> number of letters of the address match AND x number of letters of the city
> match. I will be doing this in batches based on the first letter of the
> company name. So for example I will first process all companies that start
> with the letter "A".
> So for all "A" companies I want to find companies where the first 5
letters
> in the company name match and the first 5 characters of the address field
> match and the first 5 characters of the city match. THANKS!!!

Finding duplicate records

I want to clean out our database. We have alot of duplicate records. I want
to run a query on email address to find all the remove all but one of the
duplicate records. I am only working with one table. Can someone help?
TIA
TRD
Check the following articles - they will walk you through
the process:
How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/?id=139444
How do I remove duplicates from a table?
http://databases.aspfaq.com/database...m-a-table.html
-Sue
On Wed, 27 Sep 2006 17:02:28 -0400, "TRD"
<tdejohnx2@.hotmail.com> wrote:

>I want to clean out our database. We have alot of duplicate records. I want
>to run a query on email address to find all the remove all but one of the
>duplicate records. I am only working with one table. Can someone help?
>TIA
>TRD
>
sql

Finding duplicate records

I want to clean out our database. We have alot of duplicate records. I want
to run a query on email address to find all the remove all but one of the
duplicate records. I am only working with one table. Can someone help?
TIA
TRDCheck the following articles - they will walk you through
the process:
How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/?id=139444
How do I remove duplicates from a table?
[url]http://databases.aspfaq.com/database/how-do-i-remove-duplicates-from-a-table.html[
/url]
-Sue
On Wed, 27 Sep 2006 17:02:28 -0400, "TRD"
<tdejohnx2@.hotmail.com> wrote:

>I want to clean out our database. We have alot of duplicate records. I want
>to run a query on email address to find all the remove all but one of the
>duplicate records. I am only working with one table. Can someone help?
>TIA
>TRD
>

Finding duplicate records

I want to clean out our database. We have alot of duplicate records. I want
to run a query on email address to find all the remove all but one of the
duplicate records. I am only working with one table. Can someone help?
TIA
TRDCheck the following articles - they will walk you through
the process:
How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/?id=139444
How do I remove duplicates from a table?
http://databases.aspfaq.com/database/how-do-i-remove-duplicates-from-a-table.html
-Sue
On Wed, 27 Sep 2006 17:02:28 -0400, "TRD"
<tdejohnx2@.hotmail.com> wrote:
>I want to clean out our database. We have alot of duplicate records. I want
>to run a query on email address to find all the remove all but one of the
>duplicate records. I am only working with one table. Can someone help?
>TIA
>TRD
>

Finding Duplicate Foreign Keys

Hi

i tried the following query and able to get the list of foreign keys with column names as well as referred tables and referenced column

select parent_column_id as 'child Column',object_name(constraint_object_id)as 'FK Name',object_name(parent_object_id) as 'parent table',name,object_name(referenced_object_id)as 'referenced table',referenced_column_id
from sys.foreign_key_columns inner join sys.columns on (parent_column_id = column_id and parent_object_id=object_id)
Order by object_name(parent_object_id) asc

but i am not able to get the fks created more than once on same column refering to same pk

Thanks in AdvanceIs this helpful?

Field1 has the following values;
1, 2, 3, 3, 4, 5, 6, 6, 6, 7, 8, 9
duplicates highlighted in bold

The following SQL will pick out duplicates only

SELECT Field1
FROM MyTable
GROUP BY Field1
HAVING Count(*) > 1

Field1
--
3
6

:cool:|||I am not sure what is the exact problem, but I think 'Information_Schema' might help. It contains number of views that can be used to retrive meta data.

E.g.
Select * from information_Schema.TABLE_CONSTRAINTS|||here's one that I use in SqlSpec, it may be more than you need though:

select
quotename(su2.name) collate Latin1_General_CI_AS + N'.' collate Latin1_General_CI_AS + quotename(so2.name) collate Latin1_General_CI_AS as parent
,quotename(su1.name) collate Latin1_General_CI_AS + N'.' collate Latin1_General_CI_AS + quotename(so1.name) collate Latin1_General_CI_AS as name
,so1.name as shortname ,sc2.name as colName
,'foreign key from ' + quotename(su2.name) + N'.' + quotename(so2.name) + ' to ' + quotename(su3.name) + N'.' + quotename(so3.name) as description
,quotename(su3.name) collate Latin1_General_CI_AS + N'.' collate Latin1_General_CI_AS + quotename(so3.name) collate Latin1_General_CI_AS as refTable
,sc3.name as refColumn
,ObjectProperty(sf.constid, 'CnstIsDisabled') as isDisabled
,ObjectProperty(sf.constid, 'CnstIsNotRepl') as isNotForReplication
,ObjectProperty(sf.constid, 'CnstIsDeleteCascade') as isDeleteCascade
,ObjectProperty(sf.constid, 'CnstIsUpdateCascade') as isUpdateCascade
,so1.object_id as id from sysforeignkeys sf
join sys.objects so1 on sf.constid=so1.object_id
join sys.objects so2 on sf.fkeyid=so2.object_id
join sys.objects so3 on sf.rkeyid=so3.object_id
join sys.schemas su1 on su1.schema_id=so1.schema_id
join sys.schemas su2 on su2.schema_id=so2.schema_id
join sys.schemas su3 on su3.schema_id=so3.schema_id
join syscolumns sc2 on sc2.colid=sf.fkey and sc2.id=so2.object_id
join syscolumns sc3 on sc3.colid=sf.rkey and sc3.id=so3.object_id|||Thanks You ALL for your reply.

i got that using
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc and INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

with CTEsql

Finding duplicate entries in a "smart" way - by comparing first two words

What is the best way to compare two entries in a single table where
the two fields are "almost" the same?

For example, I would like to write a query that would compare the
first two words in a "company" field. If they are the same, I would
like to output them.

For example, "20th Century" and "20th Century Fox" in the company
field would be the same.

How do I do this? Do I need to use a cursor? Is it as simple as using
"Like?"Here's an example:

CREATE TABLE Sometable (company VARCHAR(20) PRIMARY KEY)

INSERT INTO Sometable VALUES ('20th Century')
INSERT INTO Sometable VALUES ('20th Century Fox')
INSERT INTO Sometable VALUES ('Something Else')

SELECT company
FROM Sometable AS S
JOIN
(SELECT first_two_words
FROM
(SELECT company,
LEFT(company,
CHARINDEX(' ',company+' ',CHARINDEX(' ',company)+1))
AS first_two_words
FROM Sometable) AS X
GROUP BY first_two_words
HAVING COUNT(*)>1) AS T
ON T.first_two_words =
LEFT(company,CHARINDEX(' ',company+' ',CHARINDEX(' ',company)+1))

--
David Portas
----
Please reply only to the newsgroup
--|||>> a query that would compare the first two words in a "company"
field [sic]. If they are the same, I would like to output them. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

CREATE TABLE Foobar
(foo_key INTEGER NOT NULL PRIMARY KEY,
company CHAR(35) NOT NULL,
...);

SELECT F1.foo_key, F2.foo_key
FROM Foobar AS F1, Foobar AS F2
WHERE CHARINDEX(F1.company, F2.company) = 1
AND F1.foo_key < F2.foo_key;

Monday, March 26, 2012

Finding common joins

At the risk of asking a stupid question -
Is anyone familiar with either a query against the systables or maybe
an outside tool that will provide a list of the most common joins that
have been made in user created views? I'm not talking about table
relationships that are established at the database level, but rather,
I'm referring to the ability to find which joins have been utilized in
poorly constructed databases where no relationships were established
in the first place.
Thanks in advance -
Cindy T.Do you mean listing all poor running queries? You could list all in Profiler

--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL
"Cindy" <ckspot-temp@.yahoo.comwrote in message
news:1191263723.914683.228640@.r29g2000hsg.googlegr oups.com...

Quote:

Originally Posted by

At the risk of asking a stupid question -
Is anyone familiar with either a query against the systables or maybe
an outside tool that will provide a list of the most common joins that
have been made in user created views? I'm not talking about table
relationships that are established at the database level, but rather,
I'm referring to the ability to find which joins have been utilized in
poorly constructed databases where no relationships were established
in the first place.
Thanks in advance -
Cindy T.
>

|||>I'm referring to the ability to find which joins have been utilized in poorly constructed databases where no relationships were established in the first place. <<

I am not sure that this would even be possible with an AI tool. You
would have to find VIEWs that restore split attributes, are used to
fix denormalized tables, etc.

Finding changes

Hoping one of you experts can help me - im writing an application and need to run a query that returns only the difference between 2 rows...

Example:

----------------
SELECT *
FROM summary, inventory
WHERE summary.fkInventory = inventory.ID
AND inventory.ComputerName = '[SOME_NAME_HERE]'
----------------

Now - this will return 2 rows with all columns from my tables. I need to know which columns that are different - and return only those to my application.

I am a bit new to SQL ( getting there fast ) but i have no clue how to do this - or if it can be done. Otherwise i will have to compare the data in my application but i'd much prefer to let the database do the work if possible...see
http://www.nigelrivett.net/Triggers_2_Creating_Audit_Trails.html

It has code for logging changes to a column that has changed in a trigger.

You will have to loop through each column checking the values.|||Do you have exactly two rows (summeries) per inventory (computer)?

Do you realize that your query if created as you required returns everytime different columns? You can beter consider to let the query return your columnname, the original value and the changed value.

First you have to specify your different values:

SELECT 'YourFirstColumnName' AS ColName,
cast(O.YourFirstColumnName AS VARCHAR(255)) AS OriginalValue,
cast(N.YourFirstColumnName AS VARCHAR(255)) AS NewValue,
fkInventory
FROM (SELECT min(S.ID) MinID, max(S.ID) MaxID, S.fkInventory
FROM Summary S
GROUP BY S.fkInventory ) OldNew
INNER JOIN Summary O ON OldNew.MinID = O.ID INNER JOIN Summary N ON OldNew.MaxID = N.ID
WHERE O.YourFirstColumnName <> N.YourFirstColumnName

UNION

SELECT 'YourSecondColumnName' AS ColName,
cast(O.YourSecondColumnName AS VARCHAR(255)) AS OriginalValue, cast(N.YourSecondColumnName AS VARCHAR(255)) AS NewValue,
fkInventory
FROM (SELECT min(S.ID) MinID, max(S.ID) MaxID, S.fkInventory
FROM Summary S
GROUP BY S.fkInventory ) OldNew
INNER JOIN Summary O ON OldNew.MinID = O.ID INNER JOIN Summary N ON OldNew.MaxID = N.ID
WHERE O.YourSecondColumnName <> N.YourSecondColumnName

UNION

....

You got the idea to repeat the query for every column you want to compare? It's not very elegant, but under the assumptions above, you get what you want.

Then, you may use this query Q in your query like:

SELECT ColName, OriginalValue, NewValue
FROM Q
WHERE Q.fkInventory = (SELECT ID
FROM inventory WHERE ComputerName = '[SOME_NAME_HERE]')

finding average earning over period of employment

It has been a while since I have had to write a really advanced query and I was wondering if I could get a little bit of help from someone

find the average monthly earning while you have been an affiliate

affiliate_id, transaction_dt, earnings are the only fields that you have to worry about. I can obviously find the average earnings for a period of a year but, I would like something like

spavgmonthlyearning affiliate_id

any help would be greatly appreciate even a point in the right direction would be fantastic.so this will do it.
is this pure sql or will it only run on sqlserver
thank you for your help

DECLARE @.maxdate as datetime
DECLARE @.mindate as datetime
set @.maxdate = (select max(TRANS_DT) from dbo.affiliate_log)
set @.mindate = (select min(trans_dt) from dbo.affiliate_log)
select sum(earning)/datediff(M,@.mindate,@.maxdate)
from dbo.affiliate_log where affiliate_id = 1|||select DATEPART(m,date_field), avg(sales)
from table
group by DATEPART(m, date_field)|||It depends on what you mean by "average".

The simple average monthly earning would just be the total earnings divided by the total months.

select affiliate_id,
sum(earnings)/datediff(month, min(transaction_dt), max(transaction_dt))
from yourtable
group by affiliate_id

Or if they skip some months:

select affiliate_id,
sum(earnings)/count(distinct convert(varchar(7), transaction_dt, 120))
from yourtable
group by affiliate_id

Finding and Deleting Table

How we can find out by Query that wether a particular table exists?

Quote:

Originally Posted by sajithamol

How we can find out by Query that wether a particular table exists?


Hi,
you can find the table in sys.tables
if exists(select name from sys.tables where name='table name')
drop table tablename|||you can find tables by using this query

Select name from sysobjects where xtype = 'u' and name like 'tblname%'

and for deleting the table

drop table tblName

Finding a users is adult or minor using a simple select query

my problem is that i need to find if a user is adult or minor using a
simple select query .I have the date of birth of the user and a column
named isadult as well in the table but the isadult column also updates
only when the user enters or updates his/her details.
I even tried using a sql function bt it wont allow me to use getdate
function.

Any solution are welcome unless it makes me fetch records using a
select query in a stored procedure.

Thanx in AdvanceUdit Handa wrote:
> my problem is that i need to find if a user is adult or minor using a
> simple select query .I have the date of birth of the user and a
column
> named isadult as well in the table but the isadult column also
updates
> only when the user enters or updates his/her details.
> I even tried using a sql function bt it wont allow me to use getdate
> function.

Can you post the exact syntax for the SQL function you used?|||On 23 Mar 2005 03:58:56 -0800, Udit Handa wrote:

>my problem is that i need to find if a user is adult or minor using a
>simple select query .I have the date of birth of the user and a column
>named isadult as well in the table but the isadult column also updates
>only when the user enters or updates his/her details.
>I even tried using a sql function bt it wont allow me to use getdate
>function.
>Any solution are welcome unless it makes me fetch records using a
>select query in a stored procedure.
>Thanx in Advance

Hi Udit,

The best solution is to remove the IsAdult column, drop the function and
create a view instead:

CREATE VIEW MyView
AS
SELECT Col1, Col2, ..., ColN, BirthDate,
CASE WHEN BirthDate > DATEADD (year, -18, CURRENT_TIMESTAMP)
THEN 'Underage'
ELSE 'Adult'
END AS IsAdult
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> I need to find if a user is adult or minor using a simple select
query .I have the date of birth of the user and a column named isadult
as well in the table but the isadult column also updates only when the
user enters or updates his/her details. <<

Now you have learned one of the many reasons that we do not use flags
in SQL, like we did in punch card systems. Drop the "isadult" flag; it
is a redundant computed column and create a VIEW that uses the date of
birth with the CURRENT-TIMESTAMP to determine this fact.

I also hope that you are not using the proprietary, low-level BIT data
type for this incorrect design.

Friday, March 23, 2012

Finding a query submitted by software

I have a retail software that has a reporting function for its sales. It has
a SQL Server 2000 database. The table links are extremely hard to define, and
I want to be able to write custom reports. If I can see the query that it
submits I can figure out how to make my own reports for it. Is there a way in
SQL Server to locate or capture a query that is submitted by a third party
software package?
Scott Ford
Information Services
Starlite Entertainment
Scott Ford wrote:
> I have a retail software that has a reporting function for its sales. It has
> a SQL Server 2000 database. The table links are extremely hard to define, and
> I want to be able to write custom reports. If I can see the query that it
> submits I can figure out how to make my own reports for it. Is there a way in
> SQL Server to locate or capture a query that is submitted by a third party
> software package?
I'd use SQL Profiler - it captures all queries.
Start profiler with recording the profiler data into a table. Use the
software then stop the profiler. Then analyze the profiler log.
|||Thanks Yuras .. I had just discovered the Profiler minutes before your post,
but it helps to have verification that this is the tool I need.
Scott Ford
Information Services
Starlite Entertainment
"Yuras" wrote:

> Scott Ford wrote:
> I'd use SQL Profiler - it captures all queries.
> Start profiler with recording the profiler data into a table. Use the
> software then stop the profiler. Then analyze the profiler log.
>