Thursday, May 23, 2013

[T-SQL] previous Friday till the end of the current Friday

[T-SQL] previous Friday till the end of the current Friday

previous Friday till the end of the current Friday

Posted: 22 May 2013 11:50 PM PDT

Hi Guys,I'm designing a report in SSRS,I need to pull that from DB from previous Friday till the end of the current Friday.this is what I have on WHERE clause dateadd (ss,c.open_date+7200,'1970-01-01') > Dateadd(day,-7,getdate())Please assistThe whole Query:select distinct Top 6 Count(c.ref_num) as Logged, p.sym AS [Category] -- into #TempCat from call_req c, ca_contact cn, ca_contact cn2, ca_contact cn3, cr_stat st, view_group v, prob_ctg p WITH (NOLOCK), act_log al where c.type = 'I' and c.assignee *= cn.contact_uuid and c.customer = cn2.contact_uuid and al.analyst = cn3.contact_uuid and c.status = st.code and c.group_id *= v.contact_uuid and c.persid = al.call_req_id and c.category = p.persid and dateadd (ss,c.open_date+7200,'1970-01-01') > Dateadd(day,-7,getdate()) Group by p.sym order by count(c.ref_num) desc

Pivot Table Help

Posted: 22 May 2013 11:50 PM PDT

DECLARE @EmpID nvarchar(max) = ' 'DECLARE @sql nvarchar(max)SELECT @EmpID = @EmpID + '['+Convert(nvarchar(max),EmpID) +']'FROM (SELECT DISTINCT EmpID FROM emp.teamassignments) AS StaffListSET @EmpID = STUFF(@EmpID,1,1,'')PRINT @EmpIDSET @sql = 'SELECT * FROM emp.teamassignments ' +'PIVOT TEAM FOR EmpID IN ''['+@EmpID+']'' AS P'EXECUTE sp_executesql @sqlI am trying to pivot at get a listing of my EmpID's going down and their respective teams across.Some belong to 1 team others may belong to 5 or more.I have this: EmpID TEAM1 Stripes1 Orange1 Green2 Blue3 Sales3 ExecI would like this: EmpID TEAM_1 TEAM_2 TEAM_31 Stripes Orange Green2 Blue None None3 Sales Exec None

concatenation breaks when using different datatype

Posted: 22 May 2013 04:42 PM PDT

HI,I have a table, SampleNums, containing a couple of int columns that I need to concatenate into one column. When the columns are stored as varchar datatype my below ddl works, but when I recreate the SampleNums table with num1 and num2 as int columns, the concatenation doesn't work.[code="plain"]create table SampleNums (SomeID int, num1 varchar(4) null, num2 varchar(4) null)insert into SampleNumsvalues(1, 858, 621),(2, 597, 425),(3, 683, 840),(4, NULL, 333),(5, 444, NULL);select * from SampleNums--drop table SampleNums;select SomeID, NewNum = case when cast(num1 as varchar(10)) is null then NUM2 when cast(num2 as varchar(10)) is null then NUM1else cast(num1 as varchar(10)) + '.' + cast(num2 as varchar(4)) endfrom SampleNums---DESIRED OUTPUTselect '1', '858.621' UNION ALLselect '2','597.425' UNION ALLselect '3', '683.840' UNION ALLselect '4', '333' UNION ALLselect '5', '444' [/code]but with everything the same, except that Num1 and Num2 have int datatypes (use drop ddl and run create sequence again with datatype int) I get this error:--with datatype as intMsg 245, Level 16, State 1, Line 2Conversion failed when converting the varchar value '858.621' to data type int.Also, if I recreate and change datatypes to numeric, I get the following output. It is strange that line 1 and 3 have increased in value by 1:[code="plain"]--with datatype as numericselect '1', '859' UNION ALLselect '2','597' UNION ALLselect '3', '684' UNION ALLselect '4', '333' UNION ALLselect '5', '444' [/code]I would like conditional statements to work regardless of int/numeric/varchar datatype. Why, when I am handling for the string concatenation using CAST is it breaking? How to fix? Thanks!!!!

creating procedure for Insertion?

Posted: 22 May 2013 05:15 PM PDT

Hai Friends , I m creating one web application in that input fileds are departuredate,from_place,To_place,travel mode.My condition for insertion when i choose to enter departuredate(input date) is always greater than already appeared date on database date.create table journey(departuredate datetime,from_place varchar(50),to_place varchar(50),travel mode nvarchar(50))insert into journey values ('20-05-2013','cdsfs','dhf','Train')insert into journey values ('21-05-2013','cds','dh','Car')insert into journey values ('22-05-2013','cfs','df','Bus')My procedurecode:create procedure jack(@departuredate datetime,@from_place varchar(50),@to_place varchar(50),@travelmode nvarchar(10))as begin if exists('select departuredate from journey where departuredate<@departuredate')print 'Must choose greater date of departuredate'endelsebegininsert into journey (departuredate,from_place,to_place,travel mode) values(@depaturedate,@from_place,@to_place ,@travelmode,)endend/these query shows exceuted successfully,but it was not woking any body suggest me

difference between IS NULL and = NULL

Posted: 22 May 2013 02:12 AM PDT

I altered a sql script the other day to handle for a column containing NULLs. Basically, in the case of a NULL I wanted the value 'Other' to take its' place. When I gave the condition = NULL, the NULLs remained. When I gave the condition IS NULL then the NULLS were replaced with the value 'Other' as was desirable.So far I have only come up with the following link when googling: http://www.sqlservercentral.com/articles/T-SQL/understandingthedifferencebetweenisnull/871/ and it refers to setting variables to NULL and memory allocation.Can someone explain difference between IS NULL and = NULL when using conditional statements?

Syntax on assigning result of EXEC to var

Posted: 22 May 2013 07:54 PM PDT

Morning folks,I'm a bit stumped here. The issue I've got is syntax related I'm sure.Essentially I want to run the results of an Stored Proc into a variableThe error that I'm getting is:Incorrect syntax near the keyword 'EXEC'[code]DECLARE @Cols as nvarchar(max)SET @Cols = 'SELECT @ColsOut = EXEC DB..sp_1 ''DB..Tmp_LkUp_Tbl'' , NULL , B, ''MOP'',''TTL_Cnt'' 'EXEC sp_Executesql @Cols, N'@ColsOut varchar(max) Out', @cols OUTPUTPRINT @Cols[/code]Any help would be greatly appreciated as this is starting to frustrate me somewhatThanks

join 2 rows to a column

Posted: 22 May 2013 07:52 PM PDT

create table dbo.#abc([name] varchar(100),id int)insert into dbo.#abc([name],id)select 'Animal',100unionselect 'beer',100unionselect 'Tree',200unionselect 'Olive',200I want to have resultset asanimal.beertree.Olive.

trouble joining 3 tables

Posted: 22 May 2013 04:22 AM PDT

I'm trying to join 3 tables. I can do either one of these lines individually but not both together. Any ideas what I'm missing? The 3 tables are examination, preliminary_exam and pathology. from ((examination as b inner join examination as pre on b.related_exam_id = pre.id) left join preliminary_exam on pre.procedure_id = preliminary_exam.id)((examination as b2 inner join examination as pat on b2.related_exam_id = pat.id) left join pathology on pat.procedure_id = pathology.id)Thanks very much.

Need some query help.

Posted: 22 May 2013 06:57 AM PDT

I need to figure out how to do a group by on this query by number of days between start and end date. Can someone point me in the right directions. Thanks!select plantfilenumber,(select min(cc.lasteventdate) from tbltitlevault_history c inner join tbltitlevault cc on c.tvid = cc.tvid where c.tvid = a.tvid and c.eventid = '1') as startDate,(select max(dd.lasteventdate) from tbltitlevault_history d inner join tbltitlevault dd on d.tvid = dd.tvid where d.tvid = a.tvid and d.eventid = '6') as endDatefrom tbltitlevault a inner join tbltitlevault_history b on a.tvid = b.tvid
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog