SQL 2012 :: Data Not Filtering Properly
Apr 2, 2015
I have to filter my some of the data using below sql
select * from dbo.tmp_date
where issue_dt <'042015' but its not filtering out because of NVARCHAR an if suppose i used datatype to int its removing 0 (zero) from the begning
My data is in formate MMYYYY formate
CREATE TABLE dbo.tmp_date (issue_dt NVARCHAR(10));
INSERT INTO dbo.tmp_date (issue_dt)
SELECT '062012'
UNION
SELECT '062079'
UNION
SELECT '062015'
UNION
SELECT '062014'
GO
select * from dbo.tmp_date
where issue_dt <'042015'
go
View 2 Replies
ADVERTISEMENT
Jul 19, 2013
I've seen this error on several of my databases again and again this week:
Violation of PRIMARY KEY constraint 'PK_XXXX'. Cannot insert duplicate key in object 'dbo.MyTable'. The duplicate key value is (whatever number here). The statement has been terminated.
The thing is, the PK is unique, and the INSERT statement does not touch that column, it touches the other ones. So SQL2012 is the one that automatically generates the next and unique available value.
How can be possible that the value to be inserted (generated by SQL2012) is a duplicate one? By the way, this was not happening on the previous SQL2005 or SQL2008 server where the databases were being hosted.
Here's my SQL 2012 version: 11.0.3000.0 (SP1 applied). Runs on top of a Win2008R2 Cluster.
View 9 Replies
View Related
Aug 21, 2014
How I am using a CASE statement within a WHERE clause to filter data:
CREATE PROCEDURE dbo.GetSomeStuff
@filter1 varchar(100) = '',
@filter2 varchar(100) = ''
AS
BEGIN
SELECT
[Code] .
What I want, is to be able to pass in a single value to filter the table, or if I pass in (at the moment a blank) for no filter to be applied to the table.
Is this a good way to accomplish that, or is there a better way? Also, down the line I'm probably going to want to have multiple filter items for a single filter, what would be the best way to implement that?
View 5 Replies
View Related
Sep 1, 2015
I have a multi-tenant database where each row and each table has a 'TenantId' column. I have created a view which has joins on a CTE. The issue I'm having is that entity framework will do a SELECT * FROM MyView WHERE TenantId = 50 to limit the result set to the correct tenant. However it does not limit the CTE to the same TenantId so that result set is massive and makes my view extremely slow. In the included example you can see with the commented line what I need to filter on in the CTE but I am not sure how to get the sql plan executor to understand this or weather it's even possible.I have included a simplified view definition to demonstrate the issue...
ALTER VIEW MyView
AS
WITH ContactCTE AS(
SELECT Col1,
Col2,
TenantId
[code]....
View 4 Replies
View Related
Oct 10, 2006
Here is the scenerioI am trying to copy a database from a live sql server to a local server.I wanted to copy the tables, triggers, procedures, functions, usersSo I have "Copy tranfer of objects " in the DTS wizard.But the process fails informing that the users are not there in the local server and fails the process.Now what is my expectation is that why the user logins every thing is not copied to the local sever.Help would be more appreciated. Pretty urgent.
View 30 Replies
View Related
Dec 12, 2007
I have a column called "Image" in a table that stores all the user information. Image holds the data for the user's badge photo. I'm currently working on a project to move some of the data from the users table to more relevant tables. With the SQL script I wrote to copy the data into the new tables the image data appears to not have been transferred correctly. I had tried storing the image data into a variable of type varbinary(8000) before inserting it back in. Is there a certain datatype that must be used to store the data when reading from a column of data type "image" and then inserting it into another column of data type "image" without getting truncation or corruption of the data?
View 1 Replies
View Related
Oct 4, 2006
How to display money type data properly, with commas every 3 digits?
I've tried :
CONVERT (money, ProjectCost, 1) AS ProjectCost
but it gives me the type as 1234.56 no commas in it?
can anyone help me with that?
thanks!
View 3 Replies
View Related
Nov 9, 2004
Why is Select * from [Merchandise] where [Product Name] like '[ABCD]%'the same as Select * from [Merchandise] where [Product Name] between 'A' and 'D'I can run Select * from [Merchandise] where [Product Name] like 'A%'and get Products that start with the letter "A" but they don't show up when I try to get all "A","B","C","D" Products.
View 2 Replies
View Related
Nov 13, 2001
Hi,
Our current method of limiting what data a user can see is implemented solely through our Web based business intelligence tools. No filtering is enabled at the database level. This has become somewhat cumbersome as security is tied exclusively to these tools. The tools use one common logon to access the underlying database.
I would like to implement security at the database level (SQL Server 2000) and thereby produce a more flexible/portable solution. I was thinking of setting up individual database accounts for each user and then tying these into our company structure table by passing system_user result to a constraint.
For example System User name 'Store 2' would reference Store '2' in the structure table. Depending on the user, different columns will need to be referenced to filter the rows. A store user would be validated against the store column, an Area Manager user would be validated against the Area Manager column and Head Office users would not be valiadated at all i.e. they are not filtered.
1) What is the best method to implement such a look up. Can or should I use Check constraints for such a solution?
2) Would a UDF be useful?
Any ideas on the best approach to take would be greatly appereciated.
Thanks
Rob
View 1 Replies
View Related
Sep 24, 2007
Hi,
I have following query. I want to insert the value MBR_COV_EFF_DATE
to table fixed_MM if the function dbo.CheckTheDate2 returns correct date and if it returns NULL I want to insert it on error_MM table.
How can I do this?
select dbo.CheckTheDate2(MBR_COV_EFF_DATE,'MBR_COV_EFF_DATE') AS MBR_COV_EFF_DATE
from [unfixed_MM]
Thanks
Sanjeev
View 2 Replies
View Related
Feb 19, 2007
I need to filter data.
In dataset/ Filter tab, I have entered:
Expression: =Fields!PRACTICE_ID.Value
Operator: =
Value: 20
and get this error: The comparison failed. Check the data type returned by filter expression.
Practice_id has int data type. How else can I check the data type returned by filter expression?
I tried to filter on table level the same way, and get the same error.
Could anybody help, please, to make it work?
Thank you
gndsp
View 3 Replies
View Related
Oct 12, 2006
i HAVE TRIED THE FOLLOWING CODE BUT ITS NOT WORKING AS I WANT TO FILTER IT ACCORDING TO THE VALUE OF DROPDOWNLIST I HAVE TRIED CONFIGURE THE SQLDATASOURCE. DATASOURCE MODE PROPERTY IS SET TO THE DATASERSTILL IT IS NOT SHOWING ANY RESULTS <%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <h3>Search Jobs</h3> <table cellspacing="10"> <tr> <td valign="top" style="height: 162px"> <table border="0"> <tr> <td valign="top" style="width: 70px"> Location</td> <td><asp:DropDownList runat="server" id="CountryListBox" AppendDataBoundItems="True" DataSourceID="CountrySqlDataSource" DataTextField="location" DataValueField="location" AutoPostBack="True" > <asp:ListItem Selected="True" >(Show All)</asp:ListItem> </asp:DropDownList> </td> </tr> <tr> <td style="width: 70px"> Skills</td> <td><asp:TextBox runat="server" id="LastNameTextBox" Text="*" /></td> </tr> <tr> <td style="width: 70px"></td> <td><asp:Button runat="server" id="FilterButton" Text="Filter Results" /></td> </tr> </table> </td> <td valign="top" style="width: 587px; height: 162px;"> <asp:GridView ID="EmployeesGridView" DataSourceID="SqlDataSource2" DataKeyNames="employeeID" AutoGenerateColumns="False" AllowSort="True" RunAt="server" Height="143px"> <HeaderStyle backcolor="Navy" forecolor="White"/> <RowStyle backcolor="White"/> <AlternatingRowStyle backcolor="LightGray"/> <EditRowStyle backcolor="LightCyan"/> <Columns> <asp:BoundField DataField="employeeID" HeaderText="employeeID" ReadOnly="True" SortExpression="employeeID" /> <asp:BoundField DataField="employeeName" HeaderText="employeeName" SortExpression="employeeName" /> <asp:BoundField DataField="companyName" HeaderText="companyName" SortExpression="companyName" /> <asp:BoundField DataField="jobSkills" HeaderText="jobSkills" SortExpression="jobSkills" /> <asp:BoundField DataField="experiance" HeaderText="experiance" SortExpression="experiance" /> <asp:BoundField DataField="location" HeaderText="location" SortExpression="location" /> </Columns> </asp:GridView> </td> </tr> </table> <asp:SqlDataSource ID="CountrySqlDataSource" SelectCommand="SELECT DISTINCT location FROM tlbEmployee" EnableCaching="True" CacheDuration="60" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" RunAt="server" /> <asp:SqlDataSource ID="EmployeeDetailsSqlDataSource" SelectCommand="SELECT * FROM [tlbEmployee] WHERE (([location] LIKE '%' + @location + '%') AND ([jobSkills] LIKE '%' + @jobSkills + '%'))" EnableCaching="True" CacheDuration="60" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" FilterExpression="location LIKE '{0}' AND jobSkills LIKE '{1}'" RunAt="server"> <FilterParameters> <asp:ControlParameter ControlID="CountryListBox" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="LastNameTextBox" PropertyName="Text" /> </FilterParameters> <SelectParameters> <asp:ControlParameter ControlID="CountryListBox" Name="location" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="LastNameTextBox" Name="jobSkills" PropertyName="Text" Type="String" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT DISTINCT location FROM tlbEmployee"></asp:SqlDataSource> </div> <asp:SqlDataSource ID="SqlDataSource2" runat="server"></asp:SqlDataSource> </form></body></html>
View 1 Replies
View Related
Apr 4, 2007
Hi,
In my application datas are downloaded from "http://www.amfiindia.com/portal/upload/downloadnav.txt;" this website and written on a text file.
Downloadnav.txt contens looks like this
-------------
Scheme Code;Scheme Name;Net Asset Value;Repurchase Price;Sale Price;Date
Open Ended Schemes ( Income )
ABN AMRO Mutual Fund102652;ABN AMRO Monthly Income Plan-Regular Plan-Growth Option;12.8907;12.8907;12.8907;4-Apr-2007102653;ABN AMRO Monthly Income Plan-Regular Plan-Monthly Dividend Option;10.6359;10.6359;10.6359;4-Apr-2007102654;ABN AMRO Monthly Income Plan-Regular Plan-Quarterly Dividend Option;10.5377;10.5377;10.5377;4-Apr-2007102622;ABN AMRO Flexi Debt Fund-Regular Plan-Daily Dividend Option;10.0000;10.0000;10.0000;4-Apr-2007102619;ABN AMRO Flexi Debt Fund-Regular Plan-Growth Option;11.6182;11.6182;11.6182;4-Apr-2007
Birla Sun Life Mutual Fund101843;Birla Bond Index Fund-Dividend Plan (Plan A);10.1607;10.1353;10.1607;4-Apr-2007101844;Birla Bond Index Fund-Growth Plan (Plan B);11.6557;11.6266;11.6557;4-Apr-2007101315;Birla Bond Plus-Insititutional Dividend;10.4411;10.4150;10.4411;4-Apr-2007
----------------------------------------
I cut .copied datas alone in another text file datafile1.txt which looks like this
------------------------------------------
102652;ABN AMRO Monthly Income Plan-Regular Plan-Growth Option;12.8907;12.8907;12.8907;4-Apr-2007102653;ABN AMRO Monthly Income Plan-Regular Plan-Monthly Dividend Option;10.6359;10.6359;10.6359;4-Apr-2007102654;ABN AMRO Monthly Income Plan-Regular Plan-Quarterly Dividend Option;10.5377;10.5377;10.5377;4-Apr-2007102622;ABN AMRO Flexi Debt Fund-Regular Plan-Daily Dividend Option;10.0000;10.0000;10.0000;4-Apr-2007102619;ABN AMRO Flexi Debt Fund-Regular Plan-Growth Option;11.6182;11.6182;11.6182;4-Apr-2007101843;Birla Bond Index Fund-Dividend Plan (Plan A);10.1607;10.1353;10.1607;4-Apr-2007101844;Birla Bond Index Fund-Growth Plan (Plan B);11.6557;11.6266;11.6557;4-Apr-2007101315;Birla Bond Plus-Insititutional Dividend;10.4411;10.4150;10.4411;4-Apr-2007
I am bulkinserting this datafile1.txt datas in to the Sqlserver table "Indian stocks".Since Downloadnav.txt has extra description,title That file is not able to bulk insert.
Now i have to read/filter downloadnav.txt so that file will look like datafile1.txt.
Bulk insert command which i use :
Bulk insert INDIAN_STOCKS from 'C:Documents and SettingsAmrithaMy DocumentsKarthikaRobertRobertdatafile1.txt' with (FIELDTERMINATOR=';',ROWTERMINATOR='');
Can anyone tell me how to read txt file and filter it out(elimates titles,spaces).I am trying so hard for the past 1 week couldn't do.
Thanks,
Karthika
View 2 Replies
View Related
Nov 15, 2005
Hi,I have three dropdown lists branch, month & year. Based on these I need to retrieve data in my Datagrid.I have written the following SQL statement & code in my Refreshgrid(): SqlCmd.CommandText = " SELECT * " SqlCmd.CommandText += " FROM HSECData" SqlCmd.CommandText += " where branchno=" &Branchno SqlCmd.CommandText += " And yearno=" &YearNo SqlCmd.CommandText += " And monthno=" &monthNoThis is working fine, initially the datagrid is empty and then displays data when I select say, branch- Melbourne, Year-2005, Month-October and other exisiting combinations.The problem I am stuck at is when the page loads, initially I want all the data to be displayed in datagrid, i.e. for 'All' branches, 'All' years and 'All' months and then when I select combinations of these dropwdown lists as above the data should be filtered and displayed in those selected combinations. but as of now I get and empty datagrid when I select 'All' from these 3 drop down lists or when the page opens the first time.Can someone kindly guide me with the correct SQL cinstruct to achieve both the requirements?Thanks,Aartee.P.S. I am sorry if I have posted this question in a wrong category in the forum, I was unsure whether I should post it in Datagrid category or SQL server category, I thought this was my best bet.My aplogies.
View 1 Replies
View Related
Sep 28, 1999
Hi Nerds
I have a database table which is growing 100,000 recs per day. It has data from 06/01/1999 to current. I need to keep data only from 08/01/1999. I BCP'd out around more than 10 million rows(entire table) out into an OS flat file. Now I need to BCP in rows only after 08/01/1999. How do I filter those rows during BCP'ing in, or is there a way of doing it while BCP'ing out. The table has a field named proc_date smalldatetime(4) NOT NULL.
Anyone can please suggest a way of doing this.
Thanks
Pran.
View 1 Replies
View Related
Jan 14, 2006
Hello,I have a simple table containing adresses.A sample view of the table isid name city--------------------------------100 Meier New York101 Meier Tokyo110 Olson Amsterdam110 Olson Dublin111 Paul Berlin...The view is ordered by (name,city)Now my problem:I want to see only the second half of the viewstarting from "Olson" in "Dublin"Using the constraintwhere (name>="Olson") and (city>="Dublin")does not the right thing. It eliminates i.e. row 111.The only idea I have is to concatenate the fields tosimulate the compound index to be able to dowhere name+"~"+city>="Olson~Dublin"not to forget to struggle with null fields to getthe right results. This slows down the simple querydramatically.This seems to be a complicated solution for a simpleproblem, given the fact, that the index is alreadyavailable at the server.Does anyone has an idea or suggestion?-Hubert
View 5 Replies
View Related
Feb 11, 2008
Hi all,
I have a stored procedure, which returns a list of results/data.
Some of that data includes a count.
What I would like is to filter the data where we see all the count data that has 0 or all the count data greater than zero.
Now it is entirely possibly for me to go back to the stored procedure and amend it so that that is entirely possible, however i was wondering if it was also possible to produce this by filtering data at the dataset.
I provide a filter at the datset by clicking on dataset and then filters and then the expression for the field for the search count and having a parameter that provides the value.
Now it is straightforward if i just want to show all results where the searchcount = 0.
The problem lies where i want to show all results that have a searchcount > 0. The problem being it only allows me to choose one operator = or >, not a combination of both, meaning I cannot have the option to have either results.
Where am i going wrong?
View 6 Replies
View Related
Aug 31, 2015
I have a simple query
select distinct
 bl_rec_ref
 ,freq_cd
 from hgmprty1
where(bl_rec_ref is not null and bl_rec_ref not like '%SCH%')
That produces the following output (part shown)
However what I need to show is only bl_rec_ref values where there is no corresponding freq_cd of QTLY.
Looking at the example data above Rows 45 and 46 have the same bl_rec_ref value with a MTHLY and a QTLY value in freq_cd so therefore I don't want this to appear in my output. Similarly with Rows 47 and 48 were the bl_rec_ref has both QTLY and MTHLY values so again should be excluded.
However looking at Rows 51 to 55 which has a bl_rec_ref only showing once for each value of MTHLY and no value for QTLY so these Rows are required in the output.
View 4 Replies
View Related
Jun 12, 2007
I can't find any info on what the 'IN' operator does when filtering data. What's the syntax? I'm hoping this operator will behave similar to the IN operator in sql. I want to pass in 1 or more values that I'd like my data filtered by. If I say <field> IN <single value> it works, but if I say <field> IN <value1>,<value2> I get back nothing at all. Any claification on the use of this operator and the correct syntax for my value list would be greatly appreciated!!
View 1 Replies
View Related
May 2, 2008
Hello,
I have a dataset which I would like to remove data from, but I can't seem to find out how to do this.
The dataset contains the following columns:
SCode, NIn, SIn, AIn, NOut, SOut, AOut and TagNumber.
I would like to remove data from the dataset when the following occurs:
("SC123", "NIn123", "s-in-323", "a-in-342", "NOut43", "s-out-231", "a-out-45", "tagnumber12")
("SC123", "NIn123", "s-in-xyz", "a-in-xws", NULL, NULL, NULL, "tagnumber12")
This is when NIn occurs with the same value more than once, and I would like to remove (or ignore, filter) the row when NOut, SOut and AOut are null.
I am new to SSIS and can't see how I could do this (although I'm sure it's possible).
If anyone could show me how I would appreciate it.
Thanks.
View 11 Replies
View Related
May 17, 2006
I have a big presentation tomorrow and I have a query I'm trying to
make a little more presentable. query text itself formatted (filtering data)
Tables:
--tpeople
--tsynclog
The query:
select Firstname+' '+Lastname as [FirstName LastName],synctime as
[Nestle synctime],sum(DurationInMinutes)
from tpeople
join tsynclog on people_id = id
where synctime > '2006-05-05'
group by
tpeople.FirstName,tsynclog.people_id,tpeople.LastName,tsynclog.SyncTime
having sum(DurationInMinutes) > 100
order by synctime desc
As you can see I need some alias added and maybe the whole thing formatted a little
different. Can anyone help?
Thanks.
View 2 Replies
View Related
Mar 18, 2008
We are pulling data from a sharepoint list and we have created a report using business development studio in sql 2005 and have created a table with data that we want to display.
We are trying to filter this data so that we can get the report to show entries for the day or for a week, but im not sure how to achieve this?
Thanks in advance.
View 12 Replies
View Related
Aug 28, 2007
I have what at first site should be a simple reporting services issue but cannot resolve:-
I have a complex report comprising over 90 pages of various sections but when analysed in detail , 80% of it follows a simple pattern i.e. it comprises around 100 instances of the same sub-report bound to the same data source BUT grouped and filtered on different groupings and filter values.
The pattern is as follows:-
Each sub-report instance is bound to an SSAS cube which has fields L1, L2...L7
Each sub-report instance groups the data dynamically by one or more groups G1,G2..G7, the actual fields to be used being defined by the parent report (i.e. one instance may group by fields L7, L3, L4, another by L2,L3 etc)
Each sub-report instance has up to 7 filters defined (F1..F7) may filter the data i.e. the parent may pass a filter stipulating that L7="A", L3="B". To do this, the parent sets filter parameters F7="A" and F3="B"
The approach I have adopted is to set up parameters in the sub-report for dynamically grouping the data i.e. the
sub-report has 7 parameters defined G1..G7 and the parent report populates G1..G7 with the grouping breakdown required for any given instance. This works fine!!!!
However what I am trying to do is prevent each sub-report performing major round trips to the underlying cube for each sub-report instance by specifying the filter as part of the MDX query by defining the 7 filter parameters as query parameters. (I could of course not filter the query and place the filter in the report but this would almost certainly lead to performance issues). Instead what I am struggling to do is to create the MDX query in such a way that when a filter is not supplied by the parent, the MDX query does not filter the data.
Can anyone advise on this. I guess my question is more of an MDX question than a reporting services one.
View 1 Replies
View Related
Apr 24, 2008
Hello, I am trying to use a boolean parameter to filter data in a table, but there is something I am missing.
Basically I want something like this:
I have a boolean parameter, "EP", and I have a filter set on my table as:
Expression Operator Value
=Fields!REFERRAL_SOURCE.Value = IIF (Parameters!EP.Value, "1297", ????)
Using the filter tab, I can't specify an expression for the 'Operator' so I was trying to work it out using either '=' or 'like'. What needs to go into the ???? in order for the referral source to be "not 1297" (i.e. the inverse of the filter)?
Or, am I completely missing an easy way to do this?
Thanks.
View 4 Replies
View Related
Mar 13, 2007
Is there a way to dynamically slice / filter data when running a data mining query?
(Dynamically means - not deployed as part of the structure)
For example - I€™d like find associations to products sold in different geographic locations.
Does the answer differ if the structure is based on a cube or on a rational DB?
View 1 Replies
View Related
Mar 10, 2008
Is this level of security possible in RS 2005? if so how?
Any guidance would be appreciated.
Thanks.
View 1 Replies
View Related
Mar 16, 2008
Hello,
We have set up reporting in sql 2005 so that we can report from a MOSS 2007 list. At the moment the report is created with all the data from within the list. How could i create a filters so that the user can generate a report by name and date?
Thanks.
View 3 Replies
View Related
Jun 6, 2006
Hi,
I am using SQL 2005. I have a SELECT query in a stored proc with 3 parameters:
@subaccount,@numDaysCutoff,@numDaysPcts. The proc needs to be modified to return data when subaccount values are any of these:
FRRIJ
FRRIC
FRMM
ROBECO
FRJV
MAIL
FRUKV
FRICE
Currently I use a WHERE condition and am able to get data correctly. However, for a NULL value I should get everything including those not in the above list. Should I use CASE statement instead? How?
@subaccount VARCHAR(8) = NULL
, @numDaysCutoff INT = 1
, @numDaysPcts INT = 1
SELECT Subaccount = ISNULL(h.subaccount, lo.subaccount)
, SecurityID = ISNULL(h.security_id, lo.security_id)
, SecurityName = s.name
, QtyHeldAndPending = ISNULL(h.quantity, 0) +
(CASE WHEN lo.type = 1 THEN lo.resulting_quantity * (-1)
WHEN lo.type = 2 THEN lo.resulting_quantity
ELSE 0 END )
, L.AverageDailyVolume
, XDaysVol = L.AverageDailyVolume * @numDaysPcts
, CutoffVol = L.AverageDailyVolume * @numDaysCutoff
, DaysVolHeld = h.quantity / NULLIF(L.AverageDailyVolume, 0)
, HeldPctNDaysVol = h.quantity / NULLIF((L.AverageDailyVolume * @numDaysPcts), 0) * 100
, TargetedHoldingsUSD = tm.ApprovedPortfolioTarget * iv.value_usd
, CutoffVolUSD = L.AverageDailyVolume * @numDaysCutoff * s.price_usd
, TargetedPctNDaysVol = (tm.ApprovedPortfolioTarget * iv.value_usd) /
NULLIF((L.AverageDailyVolume * @numDaysPcts * s.price_usd), 0) * 100
, DaysVolTargeted = (tm.ApprovedPortfolioTarget * iv.value_usd) /
NULLIF((L.AverageDailyVolume * s.price_usd), 0)
, NDaysCutoff = @numDaysCutoff
, NDaysPcts = @numDaysPcts
FROM subaccount_positions_table h --vGlobalHoldings h
JOIN iv_subaccount_table iv ON iv.subaccount = h.subaccount
FULL OUTER JOIN LiveOrders lo ON lo.subaccount = h.subaccount AND lo.security_id = h.security_id
FULL OUTER JOIN TM_DerivedSecurityTargetDetail tm ON tm.Subaccount = h.subaccount AND tm.SecurityID = h.security_id
LEFT JOIN dbo.security_table s ON s.security_id = COALESCE(h.security_id, lo.security_id)
LEFT JOIN dbo.SecurityLiquidity L ON L.SecurityID = h.security_id AND SourceID = 99
WHERE (h.subaccount = ISNULL(@subaccount, h.subaccount)
OR lo.subaccount = ISNULL(@subaccount, h.subaccount) )
AND status = 1
AND ( h.quantity > (L.AverageDailyVolume * @numDaysCutoff) -- qtyHeld > XDaysVol
OR -- Targeted Vol exceeds cutoff
ISNULL((tm.ApprovedPortfolioTarget * iv.value_usd), 0) >
ISNULL((L.AverageDailyVolume * @numDaysCutoff * s.price_usd), 0) -- Target > XDaysVol
)
ORDER BY ISNULL(h.subaccount, lo.subaccount), ISNULL(h.security_id, lo.security_id)
Thanks in advance!!!
sqlnovice123
View 2 Replies
View Related
Mar 27, 2008
We are using xml to pull data from a custom sharepoint list into sql 2005. We have set a parameter that allows the user to filter the data by surname, however when the user tries to filter the list the drop down box brings up a list of every record, so there are duplicate entries for each surname.
Is there a way of filtering this so that there is only one instance of the users surname instead of it showing all the records?
Any help would be much appreciated.
View 2 Replies
View Related
May 8, 2008
Hi all!
I have a problem.. I have a report, with subreports, which does the job it was supposed to do. Unfortunately, subreports does not export to Excel very well, so I have to come up with a solution without the use of subreports.
The scenario (keeping it simple)
I have a list of departments. Each department can have multiple costs attached to it. The cost happens on a specific date. Fairly straightforward and simplified
I want to view each departments costs, sorted by month, like this:
Jan - Feb - March - April....
Dep SUM COST
Dep FOR EACH MONTH
Dep
Dep
...
...
I did this in the old report by passing the department ID and the date interval as parameters to a subreport and have the subreport return the sum of all the fields, which worked fine.
Now that the subreports can no longer be used, I'm struggling on how to sort my output on that date interval. I can group my result pr. department, but how to filter the result so that I get the right numbers in the right month... no luck yet.
As far as I know; I can't use different datasets in one table? I could have made a different dataset for each month...
Anyone know of a way to design this? I'm sure the problem isn't unique.
Does one have to aggregate the sums inside the SQL query instead?
Thanks for any replies!
Regards,
Mats
View 4 Replies
View Related
Jul 31, 2015
Data Scenario – Sitemaster entity has two attributes (Region and Country).
Region and Country are both domain-based attributes on separate entities [Region and . The Country entity has a domain based attribute on REGION.
Data relationship looks like this… REGION > COUNTRY
Customer Requirement
Customer would like the MDS UI to filter the Country drop-down options based on the Region selection.
To my knowledge – the only filtering that can be done is based on user security using Hierarchy Membership Permissions.
View 2 Replies
View Related
May 26, 2015
SQL Server 2012 Data Tools was working fine for me but something must've changed, now every time I try to create a new SSIS project I get:
The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT)).
When I try to open an existing project I get:
exception has been thrown by the target of an invocation
external component has thrown an exception (SSISUpgrade)
The issue seems to only arise with SSIS projects.I have already uninstalled SQL Server 2012 and reinstalled it and that didn't work.I tried to install Visual Studio 2012 Data Tools with BI and that also crashes when I try to create an SSIS project.Output of SQL Server SELECT @@VERSION is:
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
   Feb 10 2012 19:39:15
   Copyright (c) Microsoft Corporation
   Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
SQL Data Tools page info:
Microsoft SQL Server Integration Services Designer
Version 11.0.2100.60
Microsoft Visual Studio 2010
Version 10.0.40219.1 SP1Rel
Microsoft .NET Framework
Version 4.5.51641 SP1Rel
View 5 Replies
View Related
May 7, 2014
I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.
So here is the DDL, sample data, and the ideal return rows
CREATE TABLE #InvLogData
(
Id BIGINT, --is actually an identity column
Manifest_Id BIGINT,
Doc_Num BIGINT,
Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust
Compart_Id TINYINT,
[Code] ....
I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.
SELECT DISTINCT(column X)
FROM #InvLogData
GROUP BY X
HAVING COUNT(DISTINCT X) > 1
One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.
View 9 Replies
View Related