Handy query to determine if an email subscription failed to send a report

 

SELECT C.Name, S.LastRunTime, S.LastStatus, S.Description

FROM Subscriptions AS S

LEFT OUTER JOIN [Catalog] AS C

ON C.ItemID = S.Report_OID

WHERE LEFT (S.LastStatus, 12) != 'Mail sent to'

AND LEFT (S.LastStatus, 12) != 'New Subscrip'

To check for more than one primary key to insert into a table.

For example table p1 and p2 are combined and inserted into table p3

INSERT INTO p1 VALUES (1000)

INSERT INTO p1 VALUES (2000)

INSERT INTO p1 VALUES (3000)

INSERT INTO p2 VALUES (1000,5)

INSERT INTO p2 VALUES (1000,2)

INSERT INTO p2 VALUES (2000,1)

INSERT INTO p2 VALUES (3000,1)

SELECT * FROM p3

INSERT INTO p3

select hdr.ponumber,ord

from p2 Det,

p1 Hdr

where Hdr.PONumber = Det.PONumber

and (det.ponumber not in (select ponumber from p3)

or det.ord not in (select ord from p3))

Converting rows into columns

drop table temp1
select td.salesordernoitem,
case when diskcomp = 'inlet' then material end as I into temp1
from tbltransassemblies ta
inner join tbltransdiskcomp td on td.salesordernoitem = ta.salesordernoitem
where diskcomp in ('inlet')
drop table temp2
select td.salesordernoitem,
case when diskcomp = 'outlet' then material end as O into temp2
from tbltransassemblies ta
inner join tbltransdiskcomp td on td.salesordernoitem = ta.salesordernoitem
where diskcomp in ('outlet')
select t1.salesordernoitem,I,o into temp3
from temp1 t1 inner join temp2 t2 on
t1.salesordernoitem = t2.salesordernoitem

select * from temp3
select * from tbltransdiskcomp where salesordernoitem = '11381068-1'

Temporary table dropping

IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(N'tempdb..#temp1')
)
BEGIN
drop table #temp1
END

access to linked server

Linked server syntax
insert into [xxx.xxx.x.xx].bmsus.dbo.bimsus_AccountsReceivable_Outbound_Data
select * from bimsus_AccountsReceivable_Outbound_Data

set date field null

to set the date field null
update table name set date = null

SQL Having clause....

--Having clause

select name,sum(dpgrossbookingpriceineuro) from vtgeneral
where bookdate >= '2008-07-01' and bookdate <= '2009-06-30'
group by name
having sum(dpgrossbookingpriceineuro) > 50000
order by sum(dpgrossbookingpriceineuro) desc

select name,sum(dpgrossbookingpriceineuro) from vtgeneral
where bookdate >= '2008-07-01' and bookdate <= '2009-06-30'
group by name
having sum(dpgrossbookingpriceineuro) < 5000
order by sum(dpgrossbookingpriceineuro) desc

Identify the No of days between job status

The above table represents the status of a jobnumber from order entery to completion. In this case for performance mprovement if we need to identify the number of days it has taken between each status

drop table #temp
delete from tbltulsaopkpi
create table #temp( id int identity , lotno varchar(30), jobDate datetime, status varchar(5) )
insert into #temp
select jobnumber, date,jobstatus
from tbltransjobstatus where jobnumber = 'A8000160-1'
--select * from #temp
declare @ctr int, @jobstatus varchar (3), @lotno varchar(20)
SELECT @ctr = count(id) FROM #temp
declare @olddate datetime, @newDate datetime
declare @i as int, @noDays as int
set @i= 1
set @noDays = 0
WHILE @i <= @ctr -- - 1 >= 0
BEGIN
if @i = 1 --or @i =@ctr
BEGIN
select @oldDate = jobdate, @lotNo = lotno, @jobStatus = status
from #temp
where id = @i
-- select @oldDate, @lotNo, @jobStatus, 0
insert into tbltulsaopkpi values
(@lotno,@jobstatus,0)
END
else
BEGIN
select @newDate = jobdate, @lotNo = lotno, @jobStatus = status
from #temp
where id = @i
set @noDays = datediff(day,@oldDate, @newDate)
- (datediff(ww,@oldDate, @newDate + case when datename(dw,@oldDate) = 'sunday' then 1 else 0 end))
- (datediff(ww,@oldDate, @newDate + case when datename(dw,@newDate) = 'saturday' then 1 else 0 end))
- (datediff(ww,@oldDate, @newDate + case when @newDate not in (select date from tblholiday where finyr = dbo.ufgetfinyr(@newDate)) then 1 else 0 end))
insert into tbltulsaopkpi values
(@lotno,@jobstatus,@noDays)
-- select @newDate, @lotNo, @jobStatus, @noDays
set @oldDate = @newDate
END
set @i = @i + 1
-- create table tbltulsaopkpi (
-- lotno varchar(20),
-- jobstatus varchar(5),
-- NoofDays int
-- )
END
-- select * from tbltulsaopkpi

--writter by marquerite

To Generate Account Activity Monthwise

select name,
isnull(sum(case when OM.month= 1 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as JulGross,

isnull(sum(case when OM.month= 1 and tp.Salesordernoitem not like '[%xzcd%]' then Quantity else 0 end),0) as JulPieces,

isnull(sum(case when OM.month= 2 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as AugGross,

isnull(sum(case when OM.month= 2 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as AugPieces,

isnull(sum(case when OM.month= 3 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as SepGross,

isnull(sum(case when OM.month= 3 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as SepPieces,

isnull(sum(case when OM.month= 4 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as OctGross,

isnull(sum(case when OM.month= 4 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as OctPieces,
isnull(sum(case when OM.month= 5 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as NovGross,
isnull(sum(case when OM.month= 5 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as NovPieces,
isnull(sum(case when OM.month= 6 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as DecGross,
isnull(sum(case when OM.month= 6 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as DecPieces,
isnull(sum(case when OM.month= 7 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as JanGross,
isnull(sum(case when OM.month= 7 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as JanPieces,
isnull(sum(case when OM.month= 8 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as FebGross,
isnull(sum(case when OM.month= 8 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as FebPieces,
isnull(sum(case when OM.month= 9 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as MarGross,
isnull(sum(case when OM.month= 9 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as MarPieces,
isnull(sum(case when OM.month= 10 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as AprGross,
isnull(sum(case when OM.month= 10 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as AprPieces,
isnull(sum(case when OM.month= 11 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as MayGross,
isnull(sum(case when OM.month= 11 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as MayPieces,
isnull(sum(case when OM.month= 12 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as JunGross,
isnull(sum(case when OM.month= 12 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as JunPieces
from vtgeneral tp
left outer join tblOpenMonth as OM on datediff(day,tp.bookdate,OpenDt) <=0
and datediff(day,tp.bookdate,CloseDt) >= 0
and bookdate >= '2008-07-01' and bookdate <= '2009-06-30'
group by name

Date function - remove saturday and sunday

Date Functions

--Finding the number of days between the job status date and today's date
datediff(day,fs.statusDate,getdate())

--Finding the number of weeks between the job status date and today's date
datediff(ww,fs.statusDate,getdate())

--Identifying sunday and removing from the no of days calculation
- (datediff(ww,fs.statusDate,getdate() + case when datename(dw,fs.statusDate) = 'sunday' then 1 else 0 end))

--Identifying saturday and removing from the no of days calculation
- (datediff(ww,fs.statusDate,getdate() + case when datename(dw,getdate()) = 'saturday' then 1 else 0 end))

--Identifying holidays based on the business and removing from the no of days calculation
--table tblholiday contains the dates where the business is on holidays
- (datediff(ww,fs.statusDate,getdate() + case when getdate() not in (select date from tblholiday where finyr = dbo.ufgetfinyr(getdate())) then 1 else 0 end))

SQL Temp tables, using temp tables

--- creating a dataset with temp table
select cm.customerid, cm.name, cm.Phone,
cm.Fax,cm.Email,cm.Contact,category as category,0 as x,rm.repid into #temp1
from customermaster cm left outer join repsmaster rm on cm.repid = rm.repid
where customerid not in
(select customerid from vtgeneral where bookdate >= getdate() - 730
and bookdate <= getdate() - 365 and customerid is not null) and category = 'Direct'

--using the above table and creating another output
select cm.customerid, cm.name, cm.Phone, cm.Fax,cm.Email,cm.Contact,cm.category as repcategory, sum(isnull(DPGrossBookingPriceInEuro,0)) as x from #temp1 cm inner join vtgeneral vtg on cm.customerid = vtg.customerid where bookdate is not null and (bookdate >= getdate() - 1065
and bookdate <= getdate() - 730 ) --and cm.category = 'Direct' group by cm.customerid, cm.name, cm.Phone, cm.Fax,cm.Email,cm.Contact ,cm.category

union

select cm.customerid, cm.name, cm.Phone, cm.Fax,cm.Email,cm.Contact,cm.category as repcategory, 0 as x from #temp1 cm where cm.customerid not in (select customerid from vtgeneral where bookdate is not null and (bookdate >= getdate() - 1065
and bookdate <= getdate() - 730 ) and customerid is not null)
group by cm.customerid, cm.name, cm.Phone,
cm.Fax,cm.Email,cm.Contact,cm.category
drop table #temp1

SQL Cursors

In cases where you would like to loop through a records and based on certain conditions you would like to insert, updates or delete cursors can be used.

But having said it is not recomended from performance point of view. Here is the script in case you would like to use where we cannot avoid.

Declare hello Cursor LOCAL For

Select id from table

Open hello

Fetch Next From hello into @Id

While (@@Fetch_Status=0)
begin

-- here you can write the query to loop through id and put in your update conditions


Fetch Next From hello into @Id

end

Close hello

DeAllocate hello

SQL error handling,begin Tran,Commit , rollback, try and catch

BEGIN TRAN
s--sql statment
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
END
COMMIT TRAN
GO

SQL DB Mail

--db mail
EXEC msdb.dbo.sp_send_dbmail @recipients = 'vijay@xxx.ie',
@copy_recipients = 'vijay@xxx.ie',
@subject = 'LMK GP Integration Failure - Receivables',
@body = 'LMK GP Integration Failure - Receivables';

Get Accperiod / Financial period

--Function to get account period for example if the financial month of the company is starting from 01st July to 30th June and you would like to know the financial period this funtion will provide the financial period based on the given date.

For example 01st July 2009 will 01/2009 and 01st Nov 2009 will be 05/2009

ALTER Function [dbo].[GetAccperiod](@transdate as datetime)
returns varchar(10)
as
begin
declare @AccPeriod varchar(10)
declare @Month smallint
declare @Year int
--declare @tod datetime
--select @tod=cast(@transdate as datetime)
begin
--Get Current Month
select @TransDate = convert(datetime,convert(varchar,@TransDate,101))
select @Month = month(Closedt),@Year = Year(CloseDt) from tblOpenMonth where opendt <= @TransDate and CloseDt >= @TransDate
-- Select @Year = year(@transdate)
if (@Month < 7)
begin
Select @Year = @Year -1
end
if (@Month >=7)
begin
Select @Month =@Month -6
--Append zero if month returns a single digit value
if @Month < 10
Select @AccPeriod='0' + cast((@Month) as varchar(2))
else
Select @AccPeriod=cast((@Month) as varchar(2))
end
else
begin
Select @Month = @Month + 6
--Append zero if month returns a single digit value
if @Month < 10
Select @AccPeriod='0' + cast((@Month) as varchar(2))
else
Select @AccPeriod=cast((@Month) as varchar(2))
end
Select @AccPeriod = @AccPeriod + '/' + cast(@Year as varchar(4))
return (@AccPeriod)
end
End

creating Index, rebuilding index

--drop a existing index
drop index idx_repid on tblrepsmaster
--create or rebuild the index, it is advised to rebuild once a month
Create unique index idx_repid on dbo.tblrepsmaster(repid)

Replace String Function

-- to replace characters in a field the following query can be userd
--in this example the quartor was spelt wrong so we use this query to update
quarter_name --- is the field name
quartor -- is the characters to be replaced
quarter -- is the one to be replace with
update tblbsbcalendar set quarter_name = replace(quarter_name,'Quartor','Quarter')

String Functions

--replace function --- replacing p in the invoiceno to blank
replace(invoiceno, 'p','')

Indexing

--Create index on table transdetails for the key field lotno

Create unique index idx_lotno on db.transdetails(lotno)

Case statements

--using case statements to convert the integer to varchar of a month
select case when month(getdate()) = '1' then 'jan'
when month(getdate()) = '2' then 'feb'
when month(getdate()) = '3' then 'mar'
when month(getdate()) = '4' then 'apr'
when month(getdate()) = '5' then 'may'
when month(getdate()) = '6' then 'jun'
when month(getdate()) = '7' then 'jul'
when month(getdate()) = '8' then 'aug'
when month(getdate()) = '9' then 'sep'
when month(getdate()) = '10' then 'oct'
when month(getdate()) = '11' then 'nov'
when month(getdate()) = '12' then 'dec'
end