Twas The Friday Before Christmas...
Dec 21, 2007
'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...
View 3 Replies
ADVERTISEMENT
Oct 5, 2007
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
View 3 Replies
View Related
Sep 20, 2007
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 Related
Jun 11, 2004
Hi 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!
View 12 Replies
View Related
Dec 15, 2006
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
View 8 Replies
View Related
May 22, 2008
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)
View 3 Replies
View Related
Jul 23, 2005
Here is what I need to accomplishiif(len(sMemoText) > 15, left(sMemoText, 15) + '...', sMemoText)How to convert this into a case or anyother way to get this into aselect statement...Thanks MUCH!Jeff
View 3 Replies
View Related
Aug 30, 2006
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.
View 2 Replies
View Related
Jul 2, 2015
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
Dec 6, 2007
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?
View 7 Replies
View Related
Apr 27, 2006
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
View 1 Replies
View Related
Nov 3, 2015
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] ....
View 4 Replies
View Related
