Here is what I need to accomplish
iif(len(sMemoText) > 15, left(sMemoText, 15) + '...', sMemoText)
How to convert this into a case or anyother way to get this into a
select statement...
Thanks MUCH!
Jeff
Hi,
I need to run report that shows activity for Paediatrics Consultants who starts their audit week on Fri 5pm and finish next Fri at 5pm.
Set datefirst would work Fri to Thur but not with hours. And does not always seem to work either.
I need to be able to see historical data and do not want people to enter dates (from to), will group it by wek, consultant
Any clues? Cheers
Petr
Is there an easy way to find out what date was last friday to a given date? For example today is 20 Sep 2007, what would be the expression to find last friday to this date (14 Sep 2007) ?
View 9 Replies View RelatedHi all...I'm still trying to figure out a query related to the topic I posted yesterday about the table scan...got past that...it was smoke and mirrors...
Now...I have a table that looks like (essentially) this:
BegDate (smalldatetime)
EndDate (smalldatetime)
PortfolioID (int)
WklyPerPriceChg (float)
I am trying to come up with a query that returns a table that looks like this:
BegDate, EndDate, WklyPerPriceChg for PortfolioID1, WklyPerPriceChg for PortfolioID2, WklyPerPriceChg for PortfolioID3, WklyPerPriceChg for PortfolioID4
Here is my select:SELECT TOP 100 PERCENT
T.BegDate as StartDate,
T.EndDate as EndDate,
SP.WklyPerPriceChg as Sandp,
WR.WklyPerPriceChg as WeekRvw,
NA.WklyPerPriceChg as NewAm,
T.WklyPerPriceChg as T100
FROMdbo.WeeklyPortfolioIndex T (nolock) INNER JOIN
dbo.WeeklyPortfolioIndex WR (nolock) ON T.BegDate = WR.BegDate INNER JOIN
dbo.WeeklyPortfolioIndex NA (nolock) ON T.BegDate = NA.BegDate INNER JOIN
dbo.WeeklyPortfolioIndex SP (nolock) ON T.BegDate = SP.BegDate
WHERE((T.BegDate >= @FirstBegDate) AND
(T.EndDate <= @LastEndDate)) AND
SP.PortfolioID = 2 AND
WR.PortfolioID = 67 AND
NA.PortfolioID = 11 AND
T.PortfolioID = 90
ORDER BY T.BegDate DESC
I think you can disregard the date stuff that seems to be working.
What I am finding though, is that the self-join only returns rows for dates that have ALL FOUR of the target portfolioID's. What I want is a row for any day that has AT LEAST ONE of the portfolios, and would like NULL or zero returned in the columns for any portfolioID that does NOT have a row present in the table for that date(range).
I thought I would try a FULL OUTER join rather than the inner one (which I would expect to return an output row if ALL portfolio rows were present for that day)...but that returns more of a cartesian product type thang rather than what I am looking for.
I further thought I need something like a GROUP BY T.BegDate, for example, but then it complains that my select columns are not aggregates.
I know this should be fairly easy...I'm just missing the boat by mere inches, I think...
And missing by "mere inches" is still enough to leave me all wet ;)
Thoughts?
Thanks!
If I put this in crystal reports I can manipulate it ok, but actually translating it to a command isn't so easy for me. I'm terrible at cursors and need some help here, pretty pretty please.
I have a table...
CREATE TABLE [dbo].[StagePayment] (
[PaymentID] [uniqueidentifier] NOT NULL ,
[JobID] [uniqueidentifier] NOT NULL ,
[Paid] [bit] NOT NULL ,
[Sequence] [smallint] NOT NULL ,
[Amount] [decimal](10, 2) NOT NULL ,
[Activity] [varchar] (30) NULL ,
[ActivityID] [uniqueidentifier] NULL ,
[DueDate] [datetime] NULL ,
[InvoiceNumber] [varchar] (50) NULL ,
[DueNext] [bit] NOT NULL
) ON [PRIMARY]
GO
Simple example of the data I need to work with...
PayIDJobIDPaidSequenceAmountDueNext
609412110.000
6B1E 18110.000
297C 25110.000
E175 30114390.000
8CF9 30124390.000
916839114400.000
59F4 39126632.000
BCAF 5411417.500
59225402417.500
8DA0 68112988.000
76C5 68129958.000
0C13 68137566.000
F3F4 68142390.000
What I need to accomplish is flagging the DueNext field if the amount due has not been paid. I cannot rely on the Paid field as it's currently user controlled. I need to compare what's due with the total already paid (stored in another table).
So for instance,
Job #68 - the total amount due would be $22902.00
If the customer has paid $15000.00 then Sequence 3, PayID 0C13, should be flagged as DueNext.
Anyone have a very simple example of a running total cursor that I can reference or a better idea of how to work with this? I appreciate any and all suggestions!
Thanks!
abbra_doo
I want to make a function which returns the next Friday, given a day in the week. I can't find a solution which works. For example:
Input -> Output
4/22/08 -> 4/23/08
4/23/08 -> 4/23/08
4/24/08 -> 4/30/08
I can't find a solution which works. According to one website, this should do it:
DECLARE @Friday DATETIME
SET @Friday = '20000107'
SELECT DATEADD(day, ((DATEDIFF(day, @Friday, GETDATE()) / 7) * 7), @Friday)
'twas the friday before Christmas,
and all through the company,
everyone appeared to be gone,
except for lil' ol' me.
when I gander at Lotus Notes,
I can see that they are dialed in,
surely asleep on their couch,
but "available over IM".
To be there myself,
is all that I wish.
The highlight of the day
was a lunch of fried fish.
Back to the query analyzer,
dutifully I shall go,
just thought I would post,
to wish you all a merry ho ho...
I have a query and am trying to just return the difference between two dates but not include weekends.For instance, if I have 08/21/2006 - 08/28/2006, there are 6 weekdays. I tried this, but I am getting 7 as a result.SELECT DATEDIFF(weekday, request_start_date, request_end_date) AS days_off, request_id
FROM request Any help would be greatly appreciated.
I am having one requirement where I want to show only first Friday of every month of 2014,2015 and 2016 year.
View 9 Replies View Related
I have a weekly report that i want to display mon tues wed thur and fridays date from last week.
I know how to specifically pick one date:
=DateAdd(DateInterval.Day, -6,DateAdd(DateInterval.Day, 1-Weekday(today),Today))
that would set the parameter to monday. But how to do i make it select all the dates from monday-friday of the previous week?
Attendee Registration URL: http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032290575&Culture=en-US
I'll be talking about database mirroring starting @ 9:30 AM Pacific Daylight Time.
Thanks,
Mark
Here below is the perfect query i made which is working fine and giving me the sql output but just only need is how to convert to excel and automate the job scheduling so that it run on everyday and send the mail with attachment .
SELECT DN, cn, displayName, mail, objectClass, sAMAccountName, Company, givenName, sn
FROM
(
SELECT DN, cn, displayName, mail, objectClass, sAMAccountName, Company, givenName, sn, 1 [ordering] FROM alpha.dbo.DCADFeed
where sAMAccountName collate SQL_Latin1_General_CP1_CI_AS in
[Code] ....
found it
View 3 Replies View RelatedHey,
I have MS SQL database.
I have procedure:
code:--------------------------------------------------------------------------------
CREATE PROCEDURE dbo.Reg_DropTable
@ModuleId varchar(10)
AS
declare @TableName varchar, @kiek numeric
set @TableName = 'reg_'+@ModuleId
begin
DROP TABLE @TableName <- HERE I GOT ERROR
end
GO
--------------------------------------------------------------------------------
I got error when using variable with tables names.
How to do this?
Ps. Number is send to this function and it must drop table with name Reg_[That number]
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday)
1 1 a b c d e m
1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
Any help is appreciated.
Hi,
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
I have:
Master table Hotel
table AddressCrossRef (with: RefID = Hotel.ID, RefType = 'Hotel', AddrID)
joins
table Address (key = AddrID)
table MediaCrossRef (with RefID = Hotel.ID, RefType= 'Hotel', MediaID)
joins
table Media (with MediaID,mediaType = 'thumbnail')
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have
left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using
where (media.mediaTyple like 'thumbnail' or media.mediaType is null)
but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
any clue?
thanks
...when I started this endeavor.
I have a previously developed Lotus Notes App. The idea was simple; as
I am sefl taught on Lotus Script, I figured I'd be able to stumble my
way through VB.Well,
it started OK. I used VB Express to get familiar with the stuff, but
decided to go with a full version of VS 2005 and try and get this thing
properly developed as a web app. I purchased several reference books
etc., and have become relatively familiar with the forums here.First
issue I have is that I simply want to use code to update or add records
to an SQL DB. I know about datagridview etc., but I want to update the
DB using forms, not the tabular view those controls provide. I thought
it would be relatively straight forward, but found my ignorance runs
deeper than I thought. When I tried to do so I am finding I am not
really clear on where to make declarations etc. in the web app. If anyone could point me in the right direction that would be great. The
issue with searching these forums is most posts deal with datagridview
or something similar.I have spent a ton of time trying to find relevant
posts or articles, but have had no luck yet.Again, all Ireally
need is a nudge in the right direction. I am more than willing to plod
through reference materials or articles/posts to find what I need to
know, I just can't find where to even start on the info I need. Regards, Joe
Hi
I have a problem with my sql WHERE query, if i manually type ([Area] = 'The First Area') then it is okay but if i try and pass the variable 'The First Area' using the
([Area] = @Area) it doesnt work.
ALTER PROCEDURE dbo.StoredProcedure1(@oby nvarchar,@Area char,@Startrow INT,@Maxrow INT, @Minp INT,@Maxp INT,@Bed INT)ASSELECT * FROM(SELECT row_number() OVER (ORDER BY @oby DESC) AS rownum,Ref,Price,Area,Town,BedFROM [Houses] WHERE ([Price] >= @Minp) AND ([Price] <= @Maxp) AND ([Bed] >= @Bed) AND ([Area] = @Area)) AS AWHERE A.rownum BETWEEN (@Startrow) AND (@Startrow + @Maxrow) Please Help I know it must be something simple as the sql works but not when i pass the variable.... Thanks In Advance
How can i get an output like this from this sql??
HEM_PATIENT_ID is primary key...
Output:
First row: initial values of the fields
Second row: average of the same fields
Please help me...
select * from (
select HEM_LOKOSIT, HEM_NNS
from LPMS.HEMOGRAMS
where HEM_PATIENT_ID = 33
union
select AVG(HEM_LOKOSIT), AVG(HEM_NNS)
from LPMS.HEMOGRAMS
where HEM_PATIENT_ID = 33)
order by HEM_LOKOSIT desc nulls last;
Am new to SQL Server , can any1 tell me is there anyway to display date in this format month and year like Apr 2000 ,excluding the day.
eg: 01/28/2000 should b displayed like Jan 2000
Hi,
I'm relatively new to SQL7 but I did use 6.5 a fair bit.
I'm trying to test the restore of the Transaction log backup and having a bit of difficulty. The idea is that I make a complete database backup at 1am backup the transaction log every 30 minutes between 7am and 7pm. I need to be able to restore the database to a known state between 7am and 7pm with a max data loss of 30 minutes.
What I am trying to achieve is (as a test):
1)Create a small test database with a test table
2)Add some data to the test table
3)Back up the transaction log
4)Restore the transaction log to 'undo' the data added in step 2
Should be simple I think !!! The problem I am encountering is that in step 4 it won't let me restore only the transaction log (a tick automatically appears in the database backup as well). Bah !
Can someone please tell me what simple steps are required to get this to work. I need specifically on what options to chose during the backup and restore processes.
Thankyou,
Tim
Given one table with one column and two rows, one containing the string 'Bill', one containing the string 'Gates'.
Write a select statement which gives you the result 'Bill Gates'.
???+
Hi All,
I have a table with 2 columns which looks like the following.
IDText
-------------
1AAA
1BBB
1CCC
2DDD
2EEE
2FFF
3GGG
3HHH
3III
Each ID can have multiple texts associated with it. I want to write a query that gives me the following output.
IDText
-------------
1AAA; BBB; CCC
2DDD; EEE; FFF
3GGG; HHH; III
I appreciate your help
Thanks
Hi All,
I have a table with 2 columns which looks like the following.
IDText
-------------
1AAA
1BBB
1CCC
2DDD
2EEE
2FFF
3GGG
3HHH
3III
Each ID can have multiple texts associated with it. I want to write a query that gives me the following output.
IDText
-------------
1AAA; BBB; CCC
2DDD; EEE; FFF
3GGG; HHH; III
I appreciate your help
Thanks
Hi,
I have a table with two columns. I need to find distinct value of col1 and the correspondin repeated value of col2 for that col1 value with comma seperated list. Is there any function
for this in MS SQL?
I need somethgn like
a 1,2,3
b 4,5
c 7
d 5,55,5
I can do that with creating 2 cursors but looking for some easy way around.
Any suggestion and help highly appretiate.
Thanks
incorrect syntax near #
how do i fix this and did i make any other errors?
SELECT Master.CheckNum, Master.CheckDate, Master.ExpenseType, Deal.Alias, Detail.InvoiceDate, Detail.InvoiceAmount, Detail.Person, Detail.Deal, Detail.InvoiceNum, Detail.Reference, Detail.idDetial
FROM Master INNER JOIN (Deal INNER JOIN Detail ON Deal.Deal = Detail.Deal) ON Master.CheckNum = Detail.CheckNum
WHERE (((Master.CheckDate)>#12/5/2005#) AND ((Deal.Alias)="aic"));
Originally i had:
DELETE FROM #RptDetails WHERE StructureType <> @StructureType
AND #RptDetails WHERE #RptDetails.TraderId <> @TraderId OR #RptDetails.TraderId is null
But it didnt delete the structure types i changed to :
DELETE FROM #RptDetails WHERE StructureType <> @StructureType --AND
DELETE FROM #RptDetails WHERE #RptDetails.TraderId <> @TraderId OR #RptDetails.TraderId is null
and it did, how do i format the 2nd sql into 1 statement and what was i doing wrong?
Hi,
How do I sum all of the returned values into my output param ? This returns multiple rows, all data is oftype decimal.
Thanks
Bob
ALTER proc
spPSICalcA9
@iReturn int output,
@Contract varchar (8)
as
Select
sd.HoursLostRain,
sd.HoursLostMaxT,
sd.HoursLostMinT,
sd.HoursLostFrost,
sd.HoursLostWind,
sd.HoursLostVis
from
SiteDiary sd
where
sd.Contract = @Contract
"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com
Hi,
I need to return all records where ..
Contract = @Contract
AND
CrossReference is null or ""
I have this but I dont think its right..
Bob
where
a.Contract = @Contract AND a.crossreference is null OR a.crossreference = ""
"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com
I think it's simple, but I can't get it to work.In English its: find records in TableA where the field [Field1] hasmore than one unique value in Field2sample records in TableAField1 Field22241 123452241 123452242 123452242 99856desired return (2 records)2242 123452242 99856thank you for your helpPaul
View 3 Replies View RelatedI have a form that after being filled out has its contents written to adatabase and then goes to confirmation .asp page. I want the confirmationpage to be personalized but I am not sure how to pass the "name" parameterto the confirmation.asp page. I know I am going to kick myself whrn I seethis.Thanks, Houston
View 7 Replies View RelatedGreetings,
I'm trying to find out if a particular column in a table has more than 10 digits. for example: the TN column.
How can I write this query? The data type for this column is bigint. Also, would it be better to change this data type to char(10) instead as it's used primarily for phone # lookups? Thanks in advance!
ID TN
1 7132483234
Hi, Im trying to create a simple search page to return a list of products from a table in a sql database. Id like to be able to search a product description column in a table I have called products using the contents of a text box. I was wondering how i can go about getting the search to check for all words the user types in rather than just a single word or an exact phrase. Im currently using the following sql query
SELECT [product_title], [product_description] FROM [products] WHERE ([product_description] LIKE '%' + @product_search + '%')
this works fine for single words and exact phrases but if i had product called 'fred w bloggs' and i enter 'fred bloggs' it will not return anything.
Please could anyone suggest how i shoud go about this?
Im not sure if my web hosting company will enable full text search or will this be required?
Thanks for any help!
pete_ (very new to asp.net!)