use my_db
-- check the table names first
exec sp_msforeachtable 'print ''?'''
-- uncomment the next line to truncate
--exec sp_msforeachtable 'truncate table ?'
Wednesday, February 7, 2007
MS SQL server: Truncate All Tables
JavaScript: custom sort function
<html>
<head>
<script language="JavaScript" type="text/JavaScript">
var myarray = ["AY","Aa","Ae","Ealing","EDF"];
function mysortfn(a,b) {
if (a.toLowerCase() < b.toLowerCase()) return -1;
if (a.toLowerCase() > b.toLowerCase()) return 1;
return 0;
}
</script>
</head>
<body>
<p><a href="#" onclick="document.getElementById('test').innerHTML=myarray.sort(mysortfn);return false;">Test</a></p>
<p id="test"></p>
</body>
</html>
MS SQL server: Optional Search Parameters
create proc myproc (
val_1 int, -- required
val_2 varchar(32), -- optional
val_3 int -- optional
) as
select *
from mytable
where
col_1 = val_1
and col_2 like case
when val_2 = '' then col_2
else '%' + val_2 + '%'
end
and col_3 = case
when val_3 = 0 then col_3
else val_3
end
go
MS SQL server: Deleting Duplicate Records
I have a table tmPunchtimeSummary which contains a sum of employee's hours per day. The table contains some duplicates.
How can I write a delete statement to only delete the duplicates which in this case would be the 1st and 5th records?
CREATE TABLE [tmPunchtimeSummary]
(
[iTmPunchTimeSummaryId] [int] IDENTITY (1, 1) NOT NULL ,
[sCalldate] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sEmployeeId] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dTotalHrs] [decimal](18, 4) NULL
) ON [PRIMARY]
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1234', 4.5)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1234', 4.5)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2468', 8.0)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1357', 9.0)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2345', 8.5)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2345', 8.5
How can I write a delete statement to only delete the duplicates which in this case would be the 1st and 5th records?
CREATE TABLE [tmPunchtimeSummary]
(
[iTmPunchTimeSummaryId] [int] IDENTITY (1, 1) NOT NULL ,
[sCalldate] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sEmployeeId] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dTotalHrs] [decimal](18, 4) NULL
) ON [PRIMARY]
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1234', 4.5)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1234', 4.5)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2468', 8.0)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1357', 9.0)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2345', 8.5)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2345', 8.5)
delete t1
from tmPunchtimeSummary as t1 inner join tmPunchtimeSummary as t2
on t1.sCalldate = t2.sCalldate and t1.sEmployeeId = t2.sEmployeeId and t1.dTotalHrs = t2.dTotalHrs
and t1.iTmPunchTimeSummaryId < t2.iTmPunchTimeSummaryId
select * from tmPunchtimeSummary
iTmPunchTimeSummaryId sCalldate sEmployeeId dTotalHrs
--------------------- -------------------- -------------------- --------------------
2 20060610 1234 4.5000
3 20060610 2468 8.0000
4 20060610 1357 9.0000
6 20060610 2345 8.5000
MS SQL server: greatest value
How to get the greatest value from 3 or more different columns?
-- create a test table (just for example)
create table #t (a int, b int, c int)
-- insert sample data
insert #t
select 1, 2, 3
union all select 4, 5, 6
union all select 7, 8, 9
-- select a max value
select max(mx) from (
select mx = max(a)
from #t
union
select max(b)
from #t
union
select max(c)
from #t
) as t
drop table #t
-----------
9
Subscribe to:
Posts (Atom)