Trying To Get - List Of Brokers With One Case Only In Last 9 Months

Feb 27, 2007

I have a problem where I need to return a list of Brokers that have had only one case and that case was in the last nine months, the script below was returning appropriate numbers until I amended it to add the date of that case (cdateReceived) and the related join.

There were 470 rows returned before the addition of the cdateReceived and 1780 rows after. Ordinarily you would expect the dataset to grow with the addition of a join on a one-to-many realtionship but the criteria should be limiting the resuilts to be brokers with only one case!!! It should not then increase the number of rows.

What am I mis-understanding?

SELECT TOP 100 PERCENT
dbo.brokerDetails.bFirstname + ' ' + dbo.brokerDetails.bSurname AS Broker,
dbo.brokerCompany.companyName,
dbo.brokerCompany.cTown,
dbo.brokerCompany.cPostCode,
ISNULL(dbo.brokerParent.pName, 'DIRECTLY AUTHORISED') AS Network,
dbo.cases.cDateReceived

FROM dbo.brokerDetails INNER JOIN
dbo.brokerCompany ON dbo.brokerDetails.bCompany = dbo.brokerCompany.companyID LEFT OUTER JOIN
dbo.brokerParent ON dbo.brokerCompany.cNetworkID = dbo.brokerParent.parentID INNER JOIN
dbo.cases ON dbo.brokerDetails.BrokerID = dbo.cases.BrokerID
WHERE (dbo.brokerDetails.brokerID IN
(SELECT TOP 100 PERCENT brokerID
FROM dbo.cases
WHERE (cDateReceived < DATEADD(mm, - 9, GETDATE())) AND (Spiked = 0) AND (IsDirect = 0)

GROUP BY brokerID
ORDER BY brokerID
)
)
AND (dbo.brokerDetails.brokerID IN
(SELECT TOP 100 PERCENT brokerID
FROM dbo.cases
WHERE (spiked <> 1) AND (IsDirect = 0)
GROUP BY brokerID
HAVING count(caseID) =1
ORDER BY brokerID
)
)
ORDER BY dbo.brokerDetails.bFirstname + ' ' + dbo.brokerDetails.bSurname

View 14 Replies


ADVERTISEMENT

Trying To Get - List Of Brokers With One Case Only In Last 9 Months

Feb 27, 2007

I have a problem where I need to return a list of Brokers that have had only one case and that case was in the last nine months, the script below was returning appropriate numbers until I amended it to add the date of that case (cdateReceived) and the related join.

There were 470 rows returned before the addition of the cdateReceived and 1780 rows after. Ordinarily you would expect the dataset to grow with the addition of a join on a one-to-many realtionship but the criteria should be limiting the resuilts to be brokers with only one case!!! It should not then increase the number of rows.

What am I mis-understanding?

SELECT TOP 100 PERCENT
dbo.brokerDetails.bFirstname + ' ' + dbo.brokerDetails.bSurname AS Broker,
dbo.brokerCompany.companyName,
dbo.brokerCompany.cTown,
dbo.brokerCompany.cPostCode,
ISNULL(dbo.brokerParent.pName, 'DIRECTLY AUTHORISED') AS Network,
dbo.cases.cDateReceived

FROM dbo.brokerDetails INNER JOIN
dbo.brokerCompany ON dbo.brokerDetails.bCompany = dbo.brokerCompany.companyID LEFT OUTER JOIN
dbo.brokerParent ON dbo.brokerCompany.cNetworkID = dbo.brokerParent.parentID INNER JOIN
dbo.cases ON dbo.brokerDetails.BrokerID = dbo.cases.BrokerID
WHERE (dbo.brokerDetails.brokerID IN
(SELECT TOP 100 PERCENT brokerID
FROM dbo.cases
WHERE (cDateReceived < DATEADD(mm, - 9, GETDATE())) AND (Spiked = 0) AND (IsDirect = 0)

GROUP BY brokerID
ORDER BY brokerID
)
)
AND (dbo.brokerDetails.brokerID IN
(SELECT TOP 100 PERCENT brokerID
FROM dbo.cases
WHERE (spiked <> 1) AND (IsDirect = 0)
GROUP BY brokerID
HAVING count(caseID) =1
ORDER BY brokerID
)
)
ORDER BY dbo.brokerDetails.bFirstname + ' ' + dbo.brokerDetails.bSurname

View 14 Replies View Related

Page 2 - Trying To Get - List Of Brokers With One Case Only In Last 9 Months

Feb 28, 2007

Well I agree with you about "Order By" having no value in the subquery, its a silly slip from clarity caused after ordering the subquery when it was run in isolation as an individual query to verify the dataset. However removing the ORDER BY from the subquery allows the removal of TOP from the subquery but not the highest level query as I need to keep some order in the madness.

Quote: Originally Posted by r937 ORDER BY makes absolutely no sense in a subquery

take it out and i'll bet EM doesn't stick TOP 100 PERCENT in there any more

what it's trying to do is overcome the silliness of ORDER BY in a subquery with its own silliness

kind of like acid/base, yin/yang, ...

View 1 Replies View Related

Power Pivot :: Date Dimension - Create A List Of Months

Jul 21, 2015

I need to create a date dimension where the lowest level is month. I've seen examples which use the list function such as

Source = List.Dates(#date(2000, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2000,1,1)), #duration(1,0,0,0)),

The above increments by 1 day which is defined in the 1st argument of the #duration. My question is how can I dynamically change the value of this 1st argument such that its the number of days in the current month hence it will increment to only return the 1st date in the Month e.g

1/1/2000
1/2/2000
1/3/2000
etc..

I prefer to use an elegant approach if possible, the alternative would be return all dates, create a custom column from these dates which returns the month date - delete the dates column - get a distinct list of the month dates.

View 2 Replies View Related

Communication Between Two Service Brokers

Aug 2, 2007

I am trying to setup an alert system for our new application. Idea is to create triggers on the tables that we need updates about. When a table changes trigger will be fired and that will send a change data message to a service on a
different sql server. That service will process the message and create an event in the notification services database. Notification service will later send an email or sms etc depeding on what is required.

Currently i got the message exchange working between two service in the same database. Now I am working on exchanging message between two SQL Servers with no luck. Can anyone please post an article on how to setup communications between two services on different servers.

I did try to expose service broker as an endpoint and create a route on the other server to call it? (This don't work)


Thanks,
Fahad

View 3 Replies View Related

Dynamic CASE Statement Based On List Of Dates

Oct 5, 2007

I have the following table of data.  I need to take a date from a large table and do the following case:CASEWhen date < date(0)     Then '0'When date between date(0) and date(1)      Then '1'When date between date(1) and date(2)     Then '2'When date >= date(3)      Then '3'What I need is to be able to read all the dates the the Date table, sort then chronologically, and build the dynamic CASE statement so that the first When statement is < Date(0) and the last When statement is >= Date(Last)I hope I am making sense.  Dates will be added to the table about once a year or so and I don't want to keep going back into the sql function and rewrite it with the latest date.  Any ideas how to manipulate these dates into a case statement?  Don't worry about the second table below.  I just wanted you to see why I need to return an int from the Case function.thanksMilton



Dates Table

Date

4/1/2003

1/1/2006

4/2/2007

Fee Table



Date
Period
Class
Fee

1
Daily
True
329

1
Half Day
True
178

1
OT
True
49

1
Hourly
True
41

1
Daily
False
156

1
Half Day
False
86

1
OT
False
27

1
Hourly
False
19

2
Daily
True
355

2
Half Day
True
192

2
OT
True
50

2
Hourly
True
44

2
Daily
False
171

2
Half Day
False
92

2
OT
False
28

2
Hourly
False
21

3
Daily
True
364

3
Half Day
True
197

3
OT
True
51

3
Hourly
True
45

3
Daily
False
175

3
Half Day
False
94

3
OT
False
29

3
Hourly
False
21

View 3 Replies View Related

Trying To Use The Results Of A Case Statement In My Select List In My WHERE Clause?

Aug 10, 2006

I am fairly new with SQL and still learning. I have used a case statemtent for a column in my select list and want to use the results of that statement's field in my WHERE clause but it is not working for me. Here is the code I have so far:

SELECT
l.loanid,
p.investorid,
l.duedate,
case when pc.duedate >= l.duedate then pc.duedate end as RateDueDate,
pc.interestrate
FROM loan l
inner join participation p on p.loanid = l.loanid
inner join paymentchange pc on pc.loanid = l.loanid
where p.investorid = '12345' and RateDueDate is not null
order by l.loanid, pc.duedate

I want to put the results of this case statment in my where clause like highlighted above but it is not working because RateDueDate is not an actual column in the table. Any help would be greatly appreciated.

Thanks!

View 6 Replies View Related

Problem With Group By When Using Case Statements In The Select List.

Oct 11, 2007



I am using SQL Server 2005 and fairly new at using SQL Server. I am having problems using a Case statements in the select list while have a group by line. The SQL will parse successfully but when I try to execute the statement I get the following error twice :

Column 'dbo.REDEMPTIONHISTORY.QUANTITY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.



Below is the my sql statement:

SELECT dbo.DateOnly(TH.TransactionDate) AS RptDate, RH.Item,

ItemRef =

Case

when RH.Quantity < 0 then Sum(RH.Quantity)

when RH.Quantity >= 0 then Sum(0)

end

FROM dbo.RHISTORY AS RH INNER JOIN
dbo.TRANSHISTORY AS TH ON RH.TRANSACTIONID = TH.TransactionID

WHERE (dbo.DateOnly(TH.TransactionDate) BETWEEN '10-1-2007' AND '10-5-2007')
AND (RH.TransactionCode IN (13, 14, 15, 16))

Group by dbo.DateOnly(TH.TransactionDate), RH.Item

The TransHistory table contains, primary key transactionid, TransactionDate and the RHistory contains all the details about the transaction, the RHistory table is joined to the TransHistory table by foreign key TransactionID. I am trying to get totals for same item on the same day.

Any help will be greatly appreciated. I am also having trouble using If..Then statements in a select list and can not fin the correct syntax to use for that.

View 12 Replies View Related

Selecting Data Within 1 Month, 6 Months, 12 Months

Mar 27, 2008



I have the following table



FeedBack Type Date

test2 positive 03/15/08

tes3 negative 03/01/08

.. ....



in my page i need to select the number of negative/positive comments within the last



1 month, 6 months, 12 months



How can I accomplish that?
thanks

View 5 Replies View Related

SQL Server 2008 :: Change Text Format From Case Sensitive To Case Insensitive?

Aug 31, 2015

How can I change my T-SQL text editor from text sensitive to text insensitive?

View 2 Replies View Related

Case Insensitivity Is On Server Wide: Tables Render Case Sensative...

Jan 6, 2005

Hello:

I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?

Any suggestions?

View 4 Replies View Related

Case Insensitive Searching In Sql Server 2000 When It's Case Sensitive

May 4, 2007

Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
 
thanks in advance.

View 3 Replies View Related

SQL 2012 :: List All Different Values That Go With Single CAS ID To Appear As Comma Separate List

Jun 15, 2015

So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function

So the output would look like :

CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]

SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0

GROUP BY
preop.Case_ID

View 8 Replies View Related

HELP! Case Insensitive Database On Case Sensitive Server

Aug 17, 2005

We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????

View 4 Replies View Related

Doing A Case-sensitive Query In A Case-insensitive Database

May 29, 2008

I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?

Jacob

View 5 Replies View Related

Transact SQL :: Upper Case To Lower Case Conversion

May 4, 2015

I have column with value of all upper case, for example, FIELD SERVICE, is there anyway, I can convert into Field Service?

View 7 Replies View Related

Report Designer: Need To List Fields From Multiple Result Rows As Comma Seperated List (like A JOIN On Parameters)

Apr 9, 2008



I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.

However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).

For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).

Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac


How can I accomplish this?

I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.

Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?

View 21 Replies View Related

Insert Value List Doest Not Match Column List

Apr 18, 2007

HI...



I need to do a simple task but it's difficult to a newbie on ssis..



i have two tables...



first one has an identity column and the second has fk to the first...



to each dataset row i need to do an insert on the first table, get the @@Identity and insert it on the second table !!



i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list"



here is the script



INSERT INTO Address(
CepID,
Street,
Number,
Location,
Complement,
Reference)Values
(
?,
?,
?,
?,
?,
?
)
INSERT INTO CustomerAddress(
AddressID,
CustomerID,
AddressTypeID,
TypeDescription) VALUES(
@@Identity,
?,
?,
?
)



what's the problem ??

View 7 Replies View Related

Can You Use Replication From A Case Sensitive Db To A Case Insensitive Db?

Aug 19, 2007

I am curious with using replication in sql server 2005 one way from db A (source) replicating to db B(destination) in which db A has a collation of CS and db B has a collation of CI.  Will there be any problems with this scenario? Thanks in advance! 

View 2 Replies View Related

Problem Using Result From CASE In Another CASE Statement

Nov 5, 2007

I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.

Previously, I had been duplicating the CASE logic for both columns, like so:




Code Block...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE
[logic for condition 1]
THEN 'Condition 1 text'
WHEN [logic for condition 2]
THEN 'Condition 2 text'
WHEN [logic for condition 3]
THEN 'Condition 3 text'
WHEN [logic for condition 4]
THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...






This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.

This is what I'd like to do:



Code Block
...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,


shipment_status_text =

CASE shipment_status

WHEN 1 THEN 'Condition 1 text'

WHEN 2 THEN 'Condition 2 text'

WHEN 3 THEN 'Condition 3 text'

WHEN 4 THEN 'Condition 4 text'

ELSE 'Error'

END,
...remainder of SQL view...


This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.

Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?

Thanks,

Jason

View 1 Replies View Related

Items In List A That Don't Appear In List B (was Simple Query...I Think)

Jan 20, 2005

Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true


In pseudo-SQL, I want to write a clause like this

Code:

IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True


Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.

I may have to achieve this with some kind of logical loop but I don't know how to do that.

Can anyone help?

View 3 Replies View Related

Select List Contains More Items Than Insert List

Mar 21, 2008

I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."
How can I do this?

Insert Query:
insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE)
SELECT DISTINCT
tri_IDENT.IDA AS PPID,
tri_Ldg_Tran.CLM_ID AS PTicketNum,
tri_ClaimChg.Line_No AS PLineNum,
tri_Ldg_Tran.Tran_Amount AS PAmount,

CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT'
THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier
ELSE 0 END AS PPatPay,

tri_Ldg_Tran.Create_Date AS PDepositDate,
tri_Ldg_Tran.Tran_Date AS PEntryDate,
tri_ClaimChg.Hsp_Code AS PHCPCCode,
tri_Ldg_Tran.Adj_Type,
tri_Ldg_Tran.PRS_ID,
tri_Ldg_Tran.Create_Time,
tri_Ldg_Tran.Adj_Group,
tri_Ldg_Tran.Payer_ID,
tri_Ldg_Tran.TRN_ID,
tri_ClaimChg.Primary_Claim,
tri_IDENT.Version
FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN
qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc =
qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN
tri_Ldg_Tran RIGHT OUTER JOIN
tri_IDENT LEFT OUTER JOIN
tri_ClaimChg ON tri_IDENT.Pat_Id1 =
tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID =
tri_ClaimChg.PRS_ID AND
tri_Ldg_Tran.Chg_TRN_ID =
tri_ClaimChg.Chg_TRN_ID
AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN
tri_Payer ON tri_Ldg_Tran.Payer_ID
= tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type
= tri_Ldg_Tran.Pay_Type AND
qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type
WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg')
AND (tln_PaymentTypeMappings.PTMClientCode = 'SR')
AND (tri_ClaimChg.Primary_Claim = 1)
AND (tri_IDENT.Version = 0)

View 2 Replies View Related

Insert Only New Items From A List But Get ID's For New And Existing In The List.

Feb 12, 2008

Hi,

I have a a table that holds a list of words, I am trying to add to the list, however I only want to add new words. But I wish to return from my proc the list of words with ID, whether it is new or old.

Here's a script. the creates the table,indexes, function and the storeproc. call the proc like procStoreAndUpdateTokenList 'word1,word2,word3'

My table is now 500000 rows and growing and I am inserting on average 300 words, some new some old.

performance is a not that great so I'm thinking that my code can be improved.

SQL Express 2005 SP2
Windows Server 2003
1GB Ram....(I know, I know)

TIA





Code Snippet
GO
CREATE TABLE [dbo].[Tokens](
[TokenID] [int] IDENTITY(1,1) NOT NULL,
[Token] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Tokens] PRIMARY KEY CLUSTERED
(
[TokenID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_Tokens] ON [dbo].[Tokens]
(
[Token] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE FUNCTION [dbo].[SplitTokenList]
(
@TokenList varchar(max)
)
RETURNS
@ParsedList table
(
Token varchar(255)
)
AS
BEGIN
DECLARE @Token varchar(50), @Pos int
SET @TokenList = LTRIM(RTRIM(@TokenList ))+ ','
SET @Pos = CHARINDEX(',', @TokenList , 1)
IF REPLACE(@TokenList , ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Token = LTRIM(RTRIM(LEFT(@TokenList, @Pos - 1)))
IF @Token <> ''
BEGIN
INSERT INTO @ParsedList (Token)
VALUES (@Token) --Use Appropriate conversion
END
SET @TokenList = RIGHT(@TokenList, LEN(@TokenList) - @Pos)
SET @Pos = CHARINDEX(',', @TokenList, 1)
END
END
RETURN
END
GO

CREATE PROCEDURE [dbo].[procStoreAndUpdateTokenList]
@TokenList varchar(max)
AS
BEGIN
SET NOCOUNT ON;
create table #Tokens (TokenID int default 0, Token varchar(50))
create clustered index Tind on #T (Token)
DECLARE @NewTokens table
(
TokenID int default 0,
Token varchar(50)
)

--Split ID's into a table
INSERT INTO #Tokens(Token)
SELECT Token FROM SplitTokenList(@TokenList)
BEGIN TRY
BEGIN TRANSACTION
--get ID's for any existing tokens
UPDATE #Tokens SET TokenID = ISNULL( t.TokenID ,0)
FROM #Tokens tl INNER JOIN Tokens t ON tl.Token = t.Token

INSERT INTO Tokens(Token)
OUTPUT INSERTED.TokenID, INSERTED.Token INTO @NewTokens
SELECT DISTINCT Token FROM #Tokens WHERE TokenID = 0

return the list with id for new and old
SELECT TokenID, Token FROM #Tokens
WHERE TokenID <> 0
UNION
SELECT TokenID, Token FROM @Tokens
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @er nvarchar(max)
SET @er = ERROR_MESSAGE();
RAISERROR(@er, 14,1);
ROLLBACK TRAN
END CATCH;
END
GO




View 5 Replies View Related

How To Get Months

Nov 20, 2006

I need to get all the data based on the user selection of the months. So, I will provide a dropdown will all list of the months. When user pick the month, for example January, then I will show all the invoice on January. How to get the month of the year in the where clause in T-SQL?
 TIA

View 7 Replies View Related

3 Months Lag

Mar 21, 2008

I have a question i am confused very much with i am doing on MTD Average , i really need some help with code.

the logic behind the code is



3 MTD should be the past two completed months as well as the dates in the current month as well. So taking the example above – if the reporting period for Feb was 01/30/2007 to 02/27/2007, and March was 02/28/2007 to 03/27/2007, the 3 MTD should then be the average from 01/30/2007 to 04/10/2007.



This code does not actually gives me the exact value, please advise on any correction to the code , or a new way to write the code.



Avg
(
[Time].[Month].CurrentMember.Lag(3) :
[Time].[Month].CurrentMember,
[Measures].[A1_Avg]
)

View 4 Replies View Related

Months Between

Jan 23, 2008

Hi, I am trying to determine the amount of months between today and adate stored in the database. But I cannot seem to figure out get thedifference between the two dates. Thanks in advance.

View 2 Replies View Related

Case Sensitivity When A User Enters Data Into The Database. How To Deal With Case Sensitivity.

Sep 6, 2007

I am working on a C#/asp.net web application. The application has a text box that allows a user to enter a name. The name is then saved to the database.
Before the name is saved to the database, I need to be able to check if the name already exists in the database. The problem  here is that what if the name is in the database as "JoE ScMedLap" and somoene enters the name as "Joe Schmedlap" which already exists in the database,but just differs in case.
In other words how do deal with case sensitiviy issues.

View 2 Replies View Related

Restore Of Case Insensitive Database To A Case Sensitive Database - SQL Server 2000

Jul 20, 2005

Yesterday I received a response to my CI/CS Collation problem and therecommendation was to try and restore a CI Collation database to a CSCollation database. After creating a blank CS database a full restore(Force restore over existing database) does change the Collation toCI. I'm unsure as to how I can restore without changing theCollation. Any suggestions?

View 2 Replies View Related

How To Get Date From 3 Months Ago

Oct 10, 2006

Hi all,How to get the date from the 3 months ago? So, if user enter a date (10/10/2006), how can I get to 07/10/2006.Thanks in advance...

View 3 Replies View Related

How To Get Months And Year

Nov 20, 2006

Hi All,
I provided the client with the dropdownlist which lists all the months (january - december). This input will be used to filter in the invoiceDT.
How can I write the query where clause to compare the invoiceDT (mm/dd/yyyy) into this input which is only number 1 through 12?
TIA

View 1 Replies View Related

How Do I Pass In # Of Months

Aug 3, 2007

I have a stored procedure that I want to pass in a number of months and use it in my where caluse. I need to minus the number of months that as passed in by @TaskMonths.
But it is minusing days not months.
@TaskMonths intWHERE (tblTasks.Caller = @Caller) AND (tblTasks.DueDate BETWEEN CONVERT(varchar, GETDATE() - @TaskMonths, 101) AND GETDATE()) I apprecaite any help.

View 2 Replies View Related

Retrieving By Months Only

Sep 16, 2004

I have records in a table and 1 column is in the smalldatetime format which stores the date in the format "2004-09-22",2004-09-20",2004-09-12",2004-08-04" etc etc.

Can anyone tell me how to craft an SQL statement so that i can retrieve records for a certain month.For example,if i want to retrieve records for the month of September,i would get "2004-09-22",2004-09-20",2004-09-12" in results.

View 1 Replies View Related

12 Months Back

Jan 18, 2005

SELECT K.TRANS_TYPE,K.COMPANY,K.DIVISION,K.SALES_FORCE,K.SALES_LINE,K.SALES_REGION,K.SALES_ZONE,K.FORECAST_ DATE,C.STATISTICS_PERIOD,K.PRODUCT_NUMBER,
K.CUSTOMER_CODE,K.WAREHOUSE,0,0,0,K.FORECAST_QTY
FROM KPI_SIGNOFF AS K inner join ROUNDED_CALENDAR as C on K.FORECAST_DATE
between C.START_DATE and C.END_DATE


Hi this is the code that I am using to populate a table let us call new_table.
However what I would like to do is get the max forecast date and get the statistics_period for that max forecast date.

Once I get that I would set that as my current statistics period.

Then I want to create a new column in the same new_table call stat_period-1, which would be the one period back.

Please note that the statistics period is like the months 1 – 12.
Does not go higher that 12 and does not go lower than 1.

So for stat_period-1 when statistics period is equal to 1 it would have to be set to 12.

This is what I want but do not know how to place this into the code, please help me with this.
THanks

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved