Best Way To Handle Excluding Holidays From Report
May 18, 2015I inherited a report that counts patient visits per month. Holidays are hard coded into the query. What is the best way to handle holidays without hardcoding?
View 4 Replies
ADVERTISEMENT
Calculate Date Difference Excluding Bank Holidays
Aug 28, 2013I have a Bank holiday table (ID column, and Date column), how to exclude bank holidays in the datediff returned.
Create FUNCTION [dbo].days_diff ( @date1 datetime,@date2 datetime )
RETURNS int
AS
BEGIN
declare @i int
Declare @count int
[Code] .....
Report Model: How Do I Handle Table Joins?
Jan 23, 2008Hi,
I'm only new to Reporting services and have just started working on a report model for ad hoc reporting.
I have around 50 tables, many of them related through FK's. How can I handle this when designing the model?
E.G
I have a Table called Posts, which has a Hospital ID which is an FK to the Hospitals table. A relationship exists between the two in my Data Source View, however I cannot access the Hospital Name (stored in the Hospital table) using the report builder.
I'd greatly appreciate any help you can give.
Thanks,
John
How To Handle Optional Parameters When Called A Report Via URL
Jul 5, 2007Please i have lots of reports, i would like to pass parameters, i may pass or i will not pass.
but all of my reports are using stored procs from sql server database.
please guys can some one give me an idea how can i have optional parameters defined in the stored procedure.
in the reports layout collections property where i have to specify the parameters need to run the report there how can i define. wether it is optional of required parameter.
i see the parameters collections object there are hidden, pass null or pass a default value or pass the info via query etc.
please help thank you very much for the information.
Arranging Holidays
Feb 2, 2008Hello
In my ASP 2.0 website, I wish to arrange holidays and specific days from database with using Sql Server. What SQL query I must add to this following code? If you can post the required codes, I'd be most thankful. Regards.private DateTime _siparis;public DateTime Siparis
{
get { return _siparis; }set { _siparis = value; }
}protected void Button1_Click(object sender, EventArgs e)
{Timer1.Enabled = true;
DateTime sonTarih = DateTime.Today.AddDays(1).AddHours(13);Session["siparis"] = sonTarih;
TimeSpan aradakiFark = sonTarih - DateTime.Now;StringBuilder sb = new StringBuilder();
sb.Append("Remaining time is ");if (aradakiFark.Days > 0)
{
sb.Append(aradakiFark.Days.ToString());sb.Append(" days ");
}if (aradakiFark.Hours > 0)
{
sb.Append(aradakiFark.Hours.ToString());sb.Append(" hours ");
}if (aradakiFark.Minutes > 0)
{
sb.Append(aradakiFark.Minutes.ToString());sb.Append(" minutes ");
}
lblSure.Text = sb.ToString();
}protected void Timer1_Tick(object sender, EventArgs e)
{DateTime sonTarih = DateTime.Today.AddDays(1).AddHours(13);
TimeSpan aradakiFark = (DateTime)Session["siparis"] - DateTime.Now;StringBuilder sb = new StringBuilder();
sb.Append("Remaining time is ");if (aradakiFark.Days > 0)
{
sb.Append(aradakiFark.Days.ToString());sb.Append(" days ");
}if (aradakiFark.Hours > 0)
{
sb.Append(aradakiFark.Hours.ToString());sb.Append(" hours");
}if (aradakiFark.Minutes > 0)
{
sb.Append(aradakiFark.Minutes.ToString());sb.Append(" minutes ");
}
lblSure.Text = sb.ToString();
}
}
I've tried a select query that uses getdate and returns me to the manually arranged holidays in the Holidays table, but I've failed. I wish to do that, this query arranges holidays and adds +1 day on that holiday, if this one day added day is a holiday again, than adds one day again. How can I write this code? Thanks.
Holidays In SQL Server
Jul 20, 2005Hi!I have a large table in SQL Server 2000 with a datetime-column 'dt'. I wantto select all rows from that table, excluding days which fall on holidays orweekends. What is the best way to accomplish this? I considered creating anew table called "holidays" and then selecting all rows (sort of "where notin (select * from holidays)") , but I was looking for a better solutionsince that implies that I have to populate the "holidays" table.Suggestions are welcome!Sincerely,Nils Magnus Englund
View 2 Replies View RelatedArranging Holidays
Feb 1, 2008Hello
In my ASP 2.0 website, I wish to arrange holidays and specific days from database with using Sql Server. What SQL query I must add to this following code? If you can post the required codes, I'd be most thankful. Regards.
private DateTime _siparis;
public DateTime Siparis
{
get { return _siparis; }
set { _siparis = value; }
}
protected void Button1_Click(object sender, EventArgs e)
{
Timer1.Enabled = true;
DateTime sonTarih = DateTime.Today.AddDays(1).AddHours(13);
Session["siparis"] = sonTarih;
TimeSpan aradakiFark = sonTarih - DateTime.Now;
StringBuilder sb = new StringBuilder();
sb.Append("Remaining time is ");
if (aradakiFark.Days > 0)
{
sb.Append(aradakiFark.Days.ToString());
sb.Append(" days ");
}
if (aradakiFark.Hours > 0)
{
sb.Append(aradakiFark.Hours.ToString());
sb.Append(" hours ");
}
if (aradakiFark.Minutes > 0)
{
sb.Append(aradakiFark.Minutes.ToString());
sb.Append(" minutes ");
}
lblSure.Text = sb.ToString();
}
protected void Timer1_Tick(object sender, EventArgs e)
{
DateTime sonTarih = DateTime.Today.AddDays(1).AddHours(13);
TimeSpan aradakiFark = (DateTime)Session["siparis"] - DateTime.Now;
StringBuilder sb = new StringBuilder();
sb.Append("Remaining time is ");
if (aradakiFark.Days > 0)
{
sb.Append(aradakiFark.Days.ToString());
sb.Append(" days ");
}
if (aradakiFark.Hours > 0)
{
sb.Append(aradakiFark.Hours.ToString());
sb.Append(" hours");
}
if (aradakiFark.Minutes > 0)
{
sb.Append(aradakiFark.Minutes.ToString());
sb.Append(" minutes ");
}
lblSure.Text = sb.ToString();
}
}
Query To Count Holidays
Aug 9, 2004Hi,
I'm working on a helpdesk project and I require the calculation of the holidays.
I need to get the time difference of the assigned date and the solved date of the helpdesk tickets considering the week-end holidays and statutory holidays. Is there any possible way to do this. I need something similar to the NetworkDays function in excel.
Thanks.
Madhavi.
Function To Calculate Holidays
May 31, 2006Hello,
First things first - I have a table which holds employee information (tbl_EmployeeDetails), and another table which holds information about the holidays they have booked (tbl_Holidays).
If an employee books 5 days off, 5 rows will appear in the tbl_Holidays table, each line showing 1 under the day field and 7 under the hours field.
I'm trying to produce a function which will do the following :
1) Pass in the employee number
2) Establish whether the employee works full time or part time by looking up to tbl_employeedetails, and checking the fulltime flag
3) If full time, look up to tbl_Holidays and count number of days
4) If part time, look up to tbl_Holidays and count number of hours
5) After this, return the number of holidays booked
My code is as follows :
=============
CREATE FUNCTION [dbo].[fn_Get_Booked_Holidays_Current_Year] ( @EmpNo int )
RETURNS Float
AS
BEGIN
-- Declare fields
DECLARE @FullTime int
-- Determine if Part Time or Full Time
SET @FullTime = SELECT FullTime FROM tbl_EmployeeDetails
IF @FullTime = 1
SELECT COUNT(NumberOfDays) AS TotalHolidays, EmployeeNumber AS EERef
FROM dbo.tbl_Holidays
GROUP BY EmployeeNumber
HAVING (EmployeeNumber = @EmpNo)
IF @FullTime = 0
SELECT COUNT(NumberOfDays) AS TotalHolidays, EmployeeNumber AS EERef
FROM dbo.tbl_Holidays
GROUP BY EmployeeNumber
HAVING (EmployeeNumber = @EmpNo)
END
==========
Can someone please let me know where I'm going wrong, or what I need to do to get this function done?
Thanks,
J
SQL Help - How To Figure Out Consecutive Workdays With Holidays
Jul 18, 2006Hi everyone,
I'm hoping someone can help me with some sql statements. I have a temp table that contains 30 dates that a student has missed in the last year. I also have a holiday table of when training was not available. I want to find out if there are 6 consecutive days missed excluding weekends and holidays (from the holiday table). I know this is some nasty looping statement but I can't get my brain around it.
I would like do this in a stored proc but I could use C# if necessary.
Thanks in advance, Jessica
'NETWORKDAYS(start_date,end_date,holidays)' In SQL ?
Jul 23, 2005Is there an equivalent function in SQL to the'NETWORKDAYS(start_date,end_date,holidays)' function in Excel ?NetworkDays in Excel returns the number of whole working days betweenstart_date and end_date. Working days exclude weekends and any datesidentified in holidays. This part isn't vital but would be nice tohave. The weekend stuff is more important.I realise this will most likely need to be some method I write myself(possibly based on the name of the day - Mon-Fri), but any pointerswould be appreciated if anyone has done this before.ThanksRyan
View 2 Replies View RelatedCalc DateDiff In Workdays Only, No Holidays Either
Mar 26, 2008I use the NETWORKDAYS(start_date, end_date,holidays) function in Excel and Access regularly. I'm trying to find a similar function in TSQL to use on Server2005. NetWorkDays allows me to calculate the difference between my ticket open and close dates while excluding weekends and holidays, (we are nice to our staff and let them off).
I'm trying to find an easy way to do this in TSQL without writing a large amount of code.
I will greatly appreciate any help.
Date Difference Measurement And Weekends / Holidays
Jun 4, 2008The below code works fine to measure the difference in days between two dates.
However, there is an additional business requirement to subtract week-ends, and holidays, from the equation.
Any ideas on how to accomplish this task, and leverage the below, existing code? Thanks in advance!
(SELECT ABS((TO_DATE(TO_CHAR(" & ToFieldDate & "),'yyyymmdd') - TO_DATE(TO_CHAR(" & FromFieldDate & "),'yyyymmdd'))) FROM DUAL) AS Measurement "
Create Date Table With UK & Easter Bank Holidays
May 12, 2005Also includes ISOWeek & Weekends
Dont know whether this is of any use to anyone or it has been done before but there are a lot of posts on here regarding date calculation issues & usually the most straight forward answer is to compare against a table of dates.
So while looking at Bretts blog and another post on here, i thought i'd post this on here
http://weblogs.sqlteam.com/brettk/archive/2005/05/12/5139.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49698
Special thanks to rockmoose & BOL (as always)
Edit: It also moves bank holidays to the following Monday (and Tuesday - xmas) if the bank holiday(s) falls on the weekend
SET DATEFIRST 1
SET NOCOUNT ON
GO
--Create ISO week Function (thanks BOL)
CREATE FUNCTION ISOweek (@DATE datetime)
RETURNS int
AS
BEGIN
DECLARE @ISOweek int
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1
RETURN(@ISOweek)
END
GO
--END ISOweek
--CREATE Easter algorithm function
--Thanks to Rockmoose (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45689)
CREATE FUNCTION fnDLA_GetEasterdate(@year INT)
RETURNS CHAR (8)
AS
BEGIN
-- Easter date algorithm of Delambre
DECLARE @A INT,@B INT,@C INT,@D INT,@E INT,@F INT,@G INT,
@H INT,@I INT,@K INT,@L INT,@M INT,@O INT,@R INT
SET @A = @YEAR%19
SET @B = @YEAR / 100
SET @C = @YEAR%100
SET @D = @B / 4
SET @E = @B%4
SET @F = (@B + 8) / 25
SET @G = (@B - @F + 1) / 3
SET @H = ( 19 * @A + @B - @D - @G + 15)%30
SET @I = @C / 4
SET @K = @C%4
SET @L = (32 + 2 * @E + 2 * @I - @H - @K)%7
SET @M = (@A + 11 * @H + 22 * @L) / 451
SET @O = 22 + @H + @L - 7 * @M
IF @O > 31
BEGIN
SET @R = @O - 31 + 400 + @YEAR * 10000
END
ELSE
BEGIN
SET @R = @O + 300 + @YEAR * 10000
END
RETURN @R
END
GO
--END fnDLA_GetEasterdate
--Create the table
CREATE TABLE MyDateTable
(
FullDate datetime NOT NULL CONSTRAINT PK_FullDate PRIMARY KEY CLUSTERED,
Period int,
ISOWeek int,
WorkingDay varchar(1) CONSTRAINT DF_MyDateTable_WorkDay DEFAULT 'Y'
)
GO
--End table create
--Populate table with required dates
DECLARE @DateFrom datetime, @DateTo datetime, @Period int
SET @DateFrom = CONVERT(datetime,'20000101') --yyyymmdd (1st Jan 2000) amend as required
SET @DateTo = CONVERT(datetime,'20991231') --yyyymmdd (31st Dec 2099) amend as required
WHILE @DateFrom <= @DateTo
BEGIN
SET @Period = CONVERT(int,LEFT(CONVERT(varchar(10),@DateFrom,112),6))
INSERT MyDateTable(FullDate, Period, ISOWeek)
SELECT @DateFrom, @Period, dbo.ISOweek(@DateFrom)
SET @DateFrom = DATEADD(dd,+1,@DateFrom)
END
GO
--End population
/* Start of WorkingDays UPDATE */
UPDATE MyDateTable
SET WorkingDay = 'B' --B = Bank Holiday
--------------------------------EASTER---------------------------------------------
WHERE FullDate = DATEADD(dd,-2,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate)))) --Good Friday
OR FullDate = DATEADD(dd,+1,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate)))) --Easter Monday
GO
UPDATE MyDateTable
SET WorkingDay = 'B'
--------------------------------NEW YEAR-------------------------------------------
WHERE FullDate IN (SELECT MIN(FullDate) FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 1 AND DATEPART(dw,FullDate) NOT IN (6,7)
GROUP BY DATEPART(yy,FullDate))
---------------------MAY BANK HOLIDAYS(Always Monday)------------------------------
OR FullDate IN (SELECT MIN(FullDate) FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1
GROUP BY DATEPART(yy,FullDate))
OR FullDate IN (SELECT MAX(FullDate) FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1
GROUP BY DATEPART(yy,FullDate))
--------------------AUGUST BANK HOLIDAY(Always Monday)------------------------------
OR FullDate IN (SELECT MAX(FullDate) FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 8 AND DATEPART(dw,FullDate) = 1
GROUP BY DATEPART(yy,FullDate))
--------------------XMAS(Move to next working day if on Sat/Sun)--------------------
OR FullDate IN (SELECT CASE WHEN DATEPART(dw,FullDate) IN (6,7) THEN
DATEADD(dd,+2,FullDate) ELSE FullDate END
FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (25,26))
GO
---------------------------------------WEEKENDS--------------------------------------
UPDATE MyDateTable
SET WorkingDay = 'N'
WHERE DATEPART(dw,FullDate) IN (6,7)
GO
/* End of WorkingDays UPDATE */
--SELECT * FROM MyDateTable ORDER BY 1
DROP FUNCTION fnDLA_GetEasterdate
DROP FUNCTION ISOweek
--DROP TABLE MyDateTable
SET NOCOUNT OFF
Andy
Beauty is in the eyes of the beerholder
Error Log Peppered With --&&> 'The Conversation Handle Is Missing. Specify A Conversation Handle.'
Dec 3, 2007Hi
I'm using service broker and keep getting errors in the log even though everythig is working as expected
SQL Server 2005
Two databases
Two end points - 1 in each database
Two stored procedures:
SP1 is activated when a message enters the sending queue. it insert a new row in a table
SP2 is activated when a response is sent from the receiving queue. it cleans up the sending queue.
I have a table with an update trigger
In that trigger, if the updted row meets a certain condition a dialogue is created and a message is sent to the sending queue.
I know that SP1 and SP2 are behaving properly because i get the expected result.
Sp1 is inserteding the expected data in the table
SP2 is cleaning up the sending queue.
In the Sql Server log however i'm getting errors on both of the stored procs.
error #1
The activated proc <SP 1 Name> running on queue Applications.dbo.ffreceiverQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'
error #2
The activated proc <SP 2 Name> running on queue ADAPT_APP.dbo.ffsenderQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'
I would appreceiate anybody's help into why i'm getting this. have i set up the stored procs in correctly?
i can provide code of the stored procs if that helps.
thanks.
SQL Server 2012 :: Calculate Number Of Days From A Date - Exclude Weekends And Holidays
Feb 2, 2014I have already created a table name 'tblHolidays' and populated with 2014 Holidays. What I would like is be able to calculate (subtract or add) number of days from a date. For example subtract 2 days from 07/08/2014 and function should return 07/03/2014.
CREATE FUNCTION [dbo].[ElapsedBDays] (@Start smalldatetime, @End smalldatetime)
RETURNS int
AS
BEGIN
/*
Description:
Function designed to calculate the number of business days (In hours) between two dates.
[Code] ......
Conversation Handle Reuse And Conversation Handle XXX Not Found
Jan 18, 2008
We have implemented our service broker architecture using conversation handle reuse per MS/Remus's recommendations. We have all of the sudden started receiving the conversation handle not found errors in the sql log every hour or so (which makes perfect sense considering the dialog timer is set for 1 hour). My question is...is this expected behavior when you have employed conversation recycling? Should you expect to see these messages pop up every hour, but the logic in the queuing proc says to retry after deleting from your conversation handle table so the messages is enqueued as expected?
Second question...i think i know why we were not receiving these errors before and wanted to confirm this theory as well. In the queuing proc I was not initializing the variable @Counter to 0 so when it came down to the retry logic it could not add 1 to null so was never entering that part of the code...I am guessing with this set up it would actually output the error to the application calling the queueing proc and NOT into the SQL error logs...is this a correct assumption?
I have attached an example of one of the queuing procs below:
Code Block
DECLARE @conversationHandle UNIQUEIDENTIFIER,
@err int,
@counter int,
@DialogTimeOut int,
@Message nvarchar(max),
@SendType int,
@ConversationID uniqueidentifier
select @Counter = 0 -- THIS PART VERY IMPORTANT LOL :)
select @DialogTimeOut = Value
from dbo.tConfiguration with (nolock)
where keyvalue = 'ConversationEndpoints' and subvalue = 'DeleteAfterSec'
WHILE (1=1)
BEGIN
-- Lookup the current SPIDs handle
SELECT @conversationHandle = [handle] FROM tConversationSPID with (nolock)
WHERE spid = @@SPID and messagetype = 'TestQueueMsg';
IF @conversationHandle IS NULL
BEGIN
BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE [InitiatorQueue_SER]
TO SERVICE 'ReceiveTestQueue_SER'
ON CONTRACT [TestQueueMsg_CON]
WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER ( @conversationHandle )
TIMEOUT = @DialogTimeOut
-- insert the conversation in the association table
INSERT INTO tConversationSPID
([spid], MessageType,[handle])
VALUES
(@@SPID, 'TestQueueMsg', @conversationHandle);
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)
END
ELSE IF @conversationHandle IS NOT NULL
BEGIN
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)
END
SELECT @err = @@ERROR;
-- if succeeded, exit the loop now
IF (@err = 0)
BREAK;
SELECT @counter = @counter + 1;
IF @counter > 10
BEGIN
-- Refer to http://msdn2.microsoft.com/en-us/library/ms164086.aspx for severity levels
EXEC spLogMessageQueue 20002, 8, 'Failed to SEND on a conversation for more than 10 times. Error %i.'
BREAK;
END
-- We tried on the said conversation, but failed
-- remove the record from the association table, then
-- let the loop try again
DELETE FROM tConversationSPID
WHERE [spid] = @@SPID;
SELECT @conversationHandle = NULL;
END;
Excluding Records
Apr 24, 2008Hello,
I'm trying to figure out the best way to approach this seemingly simple task in Reporting Services 2005, but have come up with nothing but frustration. Any help would be appreciated!
Here's the basic idea:
SELECT Server.Database1.Employee, Server.Database1.HomeDept, Server.Database2.WorkedDept
FROM Server.Database1, Server.Database2
I need to exclude certain combinations of HomeDept/WorkedDept (about 11)...such as 1015/2223, 1015/2226, 2002/4422, 2002/8742, 2002/2342, etc. I want any other combinations of HomeDept 1015 & 2002 to be selected (which would be hundreds).
I've tried:
WHERE (Server.Database1.HomeDept <> '1015' AND Server.Database2.WorkedDept NOT IN ('2223','2226')) AND Server.Database1.HomeDept <> '2002' AND Server.Database2.WorkedDept NOT IN ('4422','8742','2342'))
(and all combinations of NOT IN, <>, and parenthesis placement...I even tried to do each exception separately)
What happens is that all instances of HomeDept 1015 and 2002 and all instances of WorkedDept 2223, 2226, 4422, 8742, and 2342 are excluded.
The SQL Query Designer will remove parenthesis so that the HomeDept exclusion is processed separately from the WorkedDept.
I've tried to do the SQL in the Generic Query Designer, so the SQL Query Designer doesn't reformat it, and it still returns the same results.
This behavior only seems to happen when using <> for both, NOT IN for both, or a combo of NOT and <>. If I used = for the HomeDept, the exclusion of the WorkedDept worked fine.
Any suggestions?
Excluding Value From Insert
Jun 17, 2014The script basically maps ALL products to customer levels. The Delete statement at the start is needed to make sure that any deleted products or temporary mapped products are removed once a week. However I want to map some products to CustomerID's 9,10 and 14 only. But when I run this statement it maps all products to all customer levels. What I want is thisAll products mapped to customer levels 9,10 and 14Then every other product mapped to customer levels 0,5,7, and 8 but not those extra ones in 9,10 and 14.
DELETE FROM ProductCustomerLevel
WHERE CustomerLevelID IN (0, 5, 7, 8, 9, 10, 14)
INSERT ProductCustomerLevel
(
ProductID,
CustomerLevelID
[code]....
Excluding Duplicates
Nov 20, 2007I have to exclude some records from the data flow.
If there is more than one record with equal col1 and col2, I need to exclude both records.
I can't do this operation in the OLE DB Source because I have to run some transformations before I run the col1-col2-equality check.
ANy ideas? Is aggregation transformation way to go? How do I tell to the aggr. transf to do a having count() > 1?
Excluding Empty Parameters
Apr 17, 2005I would like to exclude any parameter that is empty from the SELECT
command? How do I do this? This is part of a stored
procedure.
SELECT PersonID FROM Persons WHERE
(FirstName = @firstname) AND
(LastName = @lastname) AND
(SSN = @ssn) AND
(AddressID = @addressid) AND
(DOB = @dob) AND
(Middle = @middle)
THanks
# Of Weekdays Between Dates Excluding Sat/Sun
May 14, 2002Does anyone know how to write code to tell the amount of days between 2 dates excluding Saturday and Sunday.
Datediff will tell me the total amount of days and I guess I need some logic to look at the startdate and manipulate the total after the fact.
Any thoughts or solutions out there?
Excluding Duplicate Records
Apr 18, 2008and generating a report from an SQL table, and need to know how to exclude records that are "duplicates". Not duplicates in a sense that every field is identical, but duplicates in a sense where everything except the unique identifier is identical. Is there a quick and easy way to do this?
View 5 Replies View RelatedExcluding Data For Certain Criteria Only
Feb 6, 2012I'm having a problem writing a SQL query that excludes certain data. This is for a pay stub application to display current and previous paycheck stubs. To calculate certain data such as YTD figures and time off, we SUM on other tables. However, to display correctly, I can't SUM bonus checks for the current payperiod ONLY - but for previous pay periods, I must SUM bonus checks.
Here's an example of my data:
No code has to be inserted here.
No code has to be inserted here.
No code has to be inserted here.
No code has to be inserted here.
Right now my SQL is this:
Code:
SELECT PR04PTF.PayCheckNo, SUM(PR11ERF_History.PayCheckAmt) AS [TotalSum]
FROM PR04PTF
INNER JOIN PR11ERF_History ON
PR11ERF_History.EmployeeID = PR04PTF.EmployeeID
AND PR11ERF_History.PayPeriodEnd <= PR04PTF.PayPeriodEnd
WHERE PR04PTF.EmployeeID=441
Order By Excluding First X Characters
Jul 6, 2012I have a column where records are either 6 or 7 characters long.
for example:
111.111
OR
111.01
I am trying to order by the characters after the period. In other words I would like to exclude the first 4 characters (the 111.)
Duplicate Row Excluding Primary Key
Jul 6, 2007I am trying to duplicate a row in a table and this is the solution I came up with:
CREATE PROC duplicate_row(@my_primary_key)
AS
DECLARE @sql_stmt varchar(MAX)
SET @sql_stmt = 'INSERT INTO My_Table('
SELECT@sql_stmt = @sql_stmt + COLUMN_NAME + ','
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_NAME = 'My_Table'
ANDCOLUMN_NAME NOT IN ('my_primary_key','title')
SET @sql_stmt = @sql_stmt + 'title) SELECT '
SELECT@sql_stmt = @sql_stmt + COLUMN_NAME + ','
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_NAME = 'My_Table'
ANDCOLUMN_NAME NOT IN ('my_primary_key','title')
SET @sql_stmt = @sql_stmt +' title+''_copy'' AS title FROM My_Table WHERE my_primary_key = '+LTRIM(STR(@my_primary_key))
EXEC(@sql_stmt)
However this stored proc is horrifically slow. I was wondering if anyone had any suggestions on a better way to accomplish this.
-thanks
Group By By Excluding Prefix
Apr 12, 2006Suppose there is a table containing these recodes.country-------CON_CHNCON_JAPJAPCON_CHNWhen I use the following sql:select country, count(*) as num from table group by countrythe normal result will be:country num---------------CON_CHN2CON_JAP 1JAP1However, my desired result is as follows:country num-----------------CON_CHN2CON_JAP 2How can I re-write my SQL? Or any other methods to do that?
View 2 Replies View RelatedExcluding Repeated Values In A Sum
May 21, 2007The scenario is as follows:
I have rows coming from the db including:
Contract Number, Contract Name, owner and Actions associated with each contract.
SQL statement brings back:
Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
1234 123453 $50 Bob y
534232 5464634211 $30 Harry z
The problem is that each contract can have multiple actions associated with it...
There ideal output would be:
Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
Bob y
534232 5464634211 $30 Harry z
Total: $80
Basically I need to hide and not include repeated items based on a contract number... one idea I had was creating a group based on contract number and then display info in the header and then only owner and actions in the detail section.. The problem is Totals... how can I can it to avoid count the duplicated values..
Any help would be greatly appreciated.
Thanks,
Neil
Excluding Duplicates After Multicast
Sep 25, 2007I have to do various controls on a dataset - I created a multicast. After performing controls (one control per copy), I merge my (7) multicasted datasets using a Union All transformation. The problem I'm having are the duplicate rows created by merging the multicast copies.
How do I get rid of the duplicates? Is the Sort Transformation the solution by setting the option Remove rows with duplicate sort values to True? I have a unique key by which I'm able to discard the duplicates correctly. Are there any other ways (at a Union All level)? Is there sth like Union and Union All like in SQL?
I'm working on my 1st integration serv. project and it seems that more I work more questions I have. Shoudn't be the opposite? Thank you for the help.
Date Comparison Excluding Time
Jan 6, 2005I have a stored procedure that is suppose to get all records that have an expiration date of today. My where clause is as follows:
Round2ExpDate <= getdate()
This is not working because of the time factore. What is the best way to truncate the time for each date? All recommendations are greatly appreciated.
Simple Excluding Of Character Query
Feb 25, 2005i have a field with 2 characters(many of them ) and spaced inbetween them
which are codes for something
ie XA OX YY BY CY DY XC XD OE In any or all combination
i want to make a count of rows which contains BOTH XA
BUT DOES NOT CONTAIN any one or all of XC,XD & OE
SO I write the query
select count(colname)
from tablename
where colname LIKE ('%XA%')
AND colname NOT LIKE ('%XC%')
OR colname NOT LIKE ('%XD%')
OR colname NOT LIKE ('%OE%')
')
iS this correct
why then it does not exclude the XC,XD,OE
is there a better way ?
Data From Previous Day Excluding Weekends
Jan 23, 2014One of our departments once to automate a query that they have to pull data from the previous day. We are going to set this up as a job. How can we do this without using the Saturday and Sunday dates? So what we want to do is Pull the data from Friday on Monday. Is this possible? This is what they have. I know this pull the data from the day before.
select distinct
clm_id1, clm_rcvd, clm_6a, clm_6b, clm_dout, clm_cc1, clm_clir, clm_65a, clm_5, clm_1a, clm_1a1, clm_1a2, clm_1b, clm_1d, clm_1e, clm_1f, clm_tchg, clm_nego, clm_sppo, clm_att1, clm_att2, clm_att3, clm_att4, clm_att5, clm_chast, clme_fild
from
impact.dbo.clm
left join impact.dbo.clme on clm_id1 = clme_id
where
clm_dout = getdate()-1
Excluding Time Period From Results
Jul 12, 2006Hello, I would like to exclude the time period after 5.30pm and before 8.30am in my results. The time is in a 13 digit timestamp format which is the same as a standard unix timestamp with 3 digits which are microseconds.
I used:
dataadd(ss, TTIME/1000, '1970-01-01')AS time
to create a column with a readable time in it.
Here is a screenshot: http://www.abtecnet.com/timescreenshot.jpg
Can anyone help me with this. Thanks very much.
Andrew