How can I install ms sql client on my windows 7 machine ? Because without this driver error is "Provider cannot be found"
I am using mssql server 2005 on my Win XP, I am fail to install dot net framework 2 on windows 7 client.
vendredi 10 juin 2016
jeudi 9 juin 2016
report on daily attendance, door events database
I have table [acc_monitor_log]. Which has raw events stored for employees.
I convert raw logs to basic columns what are needed.
SELECT convert(char(10), al.[time], 105) [exactdate]
, convert(char(5), al.[time], 108) [exacttime]
, u.[Name]
, u.[lastname]
, u.[Badgenumber]
,[type of entry] = case when al.[state] = 1 then 'Exit' else 'Entry' end
,al.[event_point_name]
FROM [access].[dbo].[acc_monitor_log] al, [access].[dbo].[USERINFO] u
where
[time] >= '01-06-2016'
and al.[card_no] = u.[CardNo]
and u.[Name] <> 'Vizitator'
and al.[event_point_name] like '%AC-%'
So results are something like this.
exactdate exacttime Name lastname Badgenumber type of entry event_point_name
01-06-16 7:57 user1 user1 9 Entry Floor.5
01-06-16 12:04 user1 user1 9 Exit Floor.5
01-06-16 13:08 user1 user1 9 Entry Floor.4
01-06-16 17:27 user1 user1 9 Exit Floor.4
02-06-16 7:43 user2 user2 10 Entry Floor.5
02-06-16 12:02 user2 user2 10 Exit Floor.5
08-06-16 13:01 user2 user2 10 Entry Floor.5
08-06-16 17:29 user2 user2 10 Exit Floor.5
I need to find out employees who went out during lunch 12:00 - 13:00 but did not return on the same day. What troubles me that I need to have it covering whole month. If I needed that within a day, I think I wouldn't be stuck with this.
How can I exclude rows from the same day? Ideally I would need list of employees who went out on which day and did not return on that day.
Create Stored procedure with reference to old stored procedure
I have an OLD SP which is working fine as per the requirement. Here is what it looks like.
and the SP is below:-
ALTER PROCEDURE [dbo].[GET_RECORDS_FORDATE]
@From_date Datetime,
@To_date Datetime
AS
BEGIN
CREATE TABLE #temp(
date datetime, Total int,doc_From_To varchar(50),Inward int, First_Level_Transfer int,
Data_Entry_Transfer int,
Second_Level_Transfer int, Outward_Transfer int,
Closed int, Communication_Transfer int, Returned int
)
INSERT INTO #temp
(date, Total,doc_From_To, Inward, First_Level_Transfer,
Data_Entry_Transfer,
Second_Level_Transfer, Outward_Transfer,
Closed, Communication_Transfer, Returned)
SELECT
doc_date, COUNT(*),
(select kk.doc_no FROM inward_doc_tracking_hdr kk where mkey in (select min(mkey) FROM inward_doc_tracking_hdr jj
where jj.doc_date =convert(datetime,aa.doc_date,103) ) )
+ ' - '+
(select kk.doc_no FROM inward_doc_tracking_hdr kk where mkey in (select max(mkey) FROM inward_doc_tracking_hdr jj
where jj.doc_date =convert(datetime,aa.doc_date,103) ) )
,SUM(
CASE
WHEN status_flag in ('6','23') THEN 1 ELSE 0
END)
,SUM(
CASE
WHEN status_flag in ('4','26','24','19') THEN 1 ELSE 0
END)
,SUM(
CASE
WHEN status_flag in ('15','20') THEN 1 ELSE 0
END),
SUM(
CASE
WHEN status_flag in ('17','21') THEN 1 ELSE 0
END),
SUM(
CASE
WHEN status_flag in ('18','27') THEN 1 ELSE 0
END),
SUM(
CASE
WHEN status_flag='5' THEN 1 ELSE 0
END),
SUM(
CASE
WHEN status_flag='16' THEN 1 ELSE 0
END),
SUM(
CASE
WHEN status_flag='14' THEN 1 ELSE 0
END)
FROM inward_doc_tracking_hdr aa
WHERE doc_date between @From_date and @To_date
--AND status_flag <> '6'
GROUP BY doc_date
END
Select * from #temp
So, now what I want is, I want to create a another
SPwhich will have following column as my output and only with one parameter
I am using SQL-server-2005 Please help
How to replace a character in a string using T-SQL
In my table column, i have below sample data
Test1 145, Area 1
Test2 146,
Test3 145, Area 2, Plot 10
What i want to achieve is to replace "," in the string but only if it is the last character. If i have more characters after "," then the replace should leave the string as it is.
In the example above, the replace would only work in line 2.
The expected out put would be like below
Test1 145, Area 1
Test2 146
Test3 145, Area 2, Plot 10
In line 2 above, "," has been replaced with empty space.
I have tried this Replace(column1, ', ', '') AS ColName but this replaces the "," in Test1 and Test3.
mercredi 8 juin 2016
Thousand separator to large number in SQL Server 2005
We have a table where we are using a column to store the price of the item. The column size is 17.2 decimal. We need to have both thousand separator and decimal separator in the string. We are currently using this:
SELECT '$' + convert(varchar,cast('2123232322323.21' as money),-1) as Price
But it raises an error if the size exceed 15 digits.
SQL COALESCE modification
I have below code
SELECT @email = COALESCE(@email + ', ', '') + Email
FROM dbo.FTX_ALERTUSER WITH (NOLOCK)
WHERE AlertID = 9017 AND Email IS NOT NULL
--SELECT @email
--jlee_20160106b fixed bug from 20151028 change where Vendoremail not included.
SELECT @email2 = VENDOREMAIL FROM FTX_NCMR_VENDOR_REQUIREMENT WITH(NOLOCK) where VENDORCODE = @param1 and BU = @param2
SELECT @email = @email + ',' + ISNULL(@email2,''' ''')
which is creating string
[ftx_alert_list] 'updateLastRunDT','9017','FTX-Software, Karthik@XYZ.com, ray@XYZ.com, tony@XYZ.com, yen@XYZ.com,' '','FTX_F20160607004'
I want to correct this string like
exec [ftx_alert_list] 'updateLastRunDT','9017','FTX-Software, Karthik@XYZ.com, ray@XYZ.com, tony@XYZ.com, yen@XYZ.com','','FTX_F20160607004'`
The only difference is ,' '', i want ','', after yen@XYZ.com.
Please help.
exec stored procedure with flexible parameter for "select count(*)..." and sending an eMail
i want to write a SP to send an eMail when the select count(*) value is bigger then the value "Menge" This SP should be used fleixble due the Params for more Tables and/or "where criteria"
Unfortunately i get this erorr and iam not able to fix it :(
Msg 245, Level 16, State 1, Procedure sp_eMail_Test3, Line 23 Conversion failed when converting the varchar value 'select count(*) from test where not [sys_completed] is null' to data type int. Blockquote
Can you help me?
My SP:
create PROCEDURE [dbo].[sp_eMail_Test3]
@recordCount as int = 0,
@FileN as varchar(max) =null,
@DatSubj as varchar(20) =null,
@DatFile as varchar(20) =null,
@SQL_Count as varchar(max) =null,
@MySQL as varchar(max)=null,
@MyTable as varchar(max)=null,
@MyWhere as varchar(max)=null,
@Menge as int = 0,
@eMail_TO varchar(max) =null,
@eMail_Subject varchar(max) =null,
@eMail_Body varchar(max) =null
AS
BEGIN
SET NOCOUNT ON;
set @MySQL = 'select count(*) from ' +@MyTable + ' where ' + @MyWhere
set @SQL_Count = @MySQL
set @recordCount = convert(int, @SQL_Count ) -- <<--this is the error
IF (@recordCount > @Menge)
begin
-- variablen zuweisung
set @DatSubj = CONVERT(varCHAR(20), GETDATE() ,120) --datum fürs subject
set @DatFile = replace(convert(varchar, getdate(), 120), ':','_') --datum für filename
set @FileN ='Eska_Report_' + @DatFile +'.csv' --filename
EXEC msdb.dbo.sp_send_dbmail
@body_format = 'HTML',
@profile_name = 'testmailprofile',
@recipients = @eMail_TO,
@subject = @eMail_Subject ,
@Body = @eMail_Body ,
@query = 'SET NOCOUNT ON;
select * from Test where sys_gueltig = ''Y''
and not sys_completed is null ',
@attach_query_result_as_file = 1 ,
@query_attachment_filename= @FileN ,
@query_result_separator = ';' ,
@query_result_no_padding= 1,
@exclude_query_output =1,
@append_query_error = 1,
@query_result_header =1
end
END
I call the SP in this way
exec sp_eMail_Test3
@Menge = 0,
@eMail_TO = 'testuser@test.xx' ,
@eMail_Subject = 'test3 ',
@eMail_Body = 'Hallo, das ist ein Test',
@MyTable ='test' ,
@MyWhere = 'not [sys_completed] is null'
In the future i want to call the SP via ADO conenct in VBA
Inscription à :
Articles (Atom)