Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows. SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches FROM dbo.MAKES INNER JOIN dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>" SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC "> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" /> <asp:Parameter Name="ATT_ID2" /> <asp:Parameter Name="VAL2" /> <asp:Parameter Name="ATT_ID3" /> <asp:Parameter Name="VAL3" /> <asp:Parameter Name="ATT_ID4" /> <asp:Parameter Name="VAL4" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>" SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource> <br /> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID"> </asp:DropDownList> <asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br /> <br /> <br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False" ReadOnly="True" SortExpression="DERIVATIVE_ID" /> <asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" /> </Columns> </asp:GridView> </asp:Content> AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback). Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage. Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
mytable fld1 int primkey fld2 varchar(20), fld3 varchar(20)From the definition of the above table, how do i do i modify my below query to only select the rows with duplicates in fld2. I know a groupby with a having count will display the duplicates for a given field, but i want my query to see all the fields and rows that are duplicates. select fld1, fld2, fld3 from mytable
Hi,In the following query the calculator gives value 3.3 but the query returns 3.0 I need to get the decimal part also. i.e I need to get 3.3 as answer from the query. select cast(66/20 as decimal(6,2)) Need help.Thanks
This is a query that produces a table with garbage data, but (I think) will get the point across of what I need. SQL Server 2008
Create Table SanitationGarbage ( saleid int ,projectname varchar(200) ,typeofsale varchar(200)
[code]....
Now my select query below does not group the data like I need it to, nor does it show a total row like I need it to. How does this need to be written so the data is displayed in the proper formatting?
Select projectname ,Count(saleID) As [Total Sales] ,Count(case when typeofsale = 'Final' then saleID else null end) As [Final Sales] ,Count(case when typeofsale = 'Pending' then saleID else null end) As [Pending Sales] FROM SanitationGarbage GROUP BY projectname order by projectname asc
I have a column that is a varchar(1000). When I select that column the whole column doesn't show in the grid. To see the what's in there I right click the cell and "Copy". I then paste the results into notepad.
My problem is that I'm only getting 500 characters. I have several questions:
1. How do I verify that there are more than 500 characters in the column?
2. How do I select all characters in the column?
3. Is there anyway to see them in the Management Studio tool or do I still need to past them into notepad.
How to show a SQL search query in pages? for example 10 records in each page, like search engines. I'm using .NET Framework 2, SQL Server 2000 and Adobe Dreamweaver CS3. Thanx
I am trying to create a query that will show how much revenue that we have recieved from a customer After the first invoice and I'm having a difficult time creating a query to do it.. I have a customer table and a sales table joined by custno. SELECT Customer.LastName, Sales.InvDate, Sales.AmtChargeFROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo The output I'd like is CustNo, LastName, FirstInvoiceAmount, LifeCycleAmount Getting the first inv date seems straight forward SELECT Customer.CustNo, MIN(Sales.InvDate) AS FirstInv FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo GROUP BY Customer.CustNo However getting the amount of that first inv and then getting the sum of all invoices not including the first invoice has me scratching my head. Can anyone point me in the right direction?
My query produces accurate results just produces one instance for each sales region. I am needing it to only give me one instance of each sales region and give me total counts. What should I re-write?
Code: Select salesmanname, case when [state] IN ('GA', 'FL', 'AL', 'SC', 'NC', 'TN') Then 'South' when [state] IN ('CA', 'NV', 'WA', 'OR', 'TX') Then 'West' when [state IN ('NY', 'NJ', 'PA', 'DL', 'CT') Then 'NE' end As [Sales Region]
I would like to show employee work hours daily from Monday to Sunday. I have managed to write a query to get the total hours.
This is the query giving total hours from and to date.
(select sum(t.timespent)/60 from timeitems t where t.employee = e.code and t.project = p.code and t.ndate >= '2014-07-15' AND t.ndate <= '2014-07-15') as Hours from projemplink pl
Select position, SubsidiaryName from position Where SubsidiaryName in ('country1','country2') and position not like('testuser_%') and position not like ('Inactive_%') and position not like('olduser_%');
However when I run it, the result more accounts in country 1 than expected. I think it could be inactive users who have not logged into this system for 6 months or more. How can I build a query that incorporates my original query + shows only users who logged into the past 6 months ?
I have 2 TableAuthorsID Name1 Clint2 Voke BooksBookID ID BookName Price1 1 Book1 10 2 1 Boo21 12 3 2 Book3 6 4 1 Book4 13 5 1 Book5 2 Now I want to List All Authors and only show Most Expensive book Name of each Author.So I need this Fields :ID,Name,BookName,BookID,Price. How could I Write SQL query For It (I want to show results in DB Without Using SP).I want to Create NEw Views Which Shows my required Results. thanks,
I have an index that shows distribution statistics of 98.20%, which is very poor. I set show query plan and show statis I/O on. This table has 1113675 rows of data.
************* select orderID, custId, intertcsi from tblorders where intertcsi = '2815'
STEP 1 The type of query is SELECT FROM TABLE tblorders Nested iteration Index : indxInterTCSI orderID custId intertcsi ----------- ----------- --------- 1015245 1011313 2815 2556392 2556392 2815 ....
Table: tblOrders scan count 1, logical reads: 104, physical reads: 58, read ahead reads: 0 *************** Then I use the same select statement to force a table scan:
select orderID, custId, intertcsi from tblorders (index=0) where intertcsi = '2815'
STEP 1 The type of query is SELECT FROM TABLE tblorders Nested iteration Table Scan orderID custId intertcsi ----------- ----------- --------- 60472 61084 2815 102184 102333 2815 ... Table: tblOrders scan count 1, logical reads: 110795, physical reads: 6891, read ahead reads: 103980
When the index is not provided, the logical reads and physical reads increased dramatically. Does this tell me that I should keep that index though it is a poor selection? Is that because a huge table like this make the optimizer use the index. The query without using index takes longer time to run. Any idea or comment would be very appreciated.
Query should only return less than 3000 records but its returning over 4M. It needs to show all duplicates records.... All the info are on the same table VENDFIl, so I used a self join but it seems to be looping..
SELECT A.FEDTID, B.VENDOR, C.NPI_NUMBER FROM VENDFIL A, VENDFIL B, VENDFIL C GROUP BY A.FEDTID, B.VENDOR
If I skip those objects that were deleted, is there a query that I can run off Master/MSDB that will show all database objects and when they were last modified, or create date if it has not been modified?
select shop ,ltrim(str(datepart(hh,yourdatetimefield)))+':00 - '+ltrim(str(datepart(hh,yourdatetimefield)))+':59' as time_span ,sum(case when datediff(dd,yourdatetimefield,getdate())=0
Hi All,I'm a relative newbie to SQL Server, so please forgive me if this is adaft question...When I set "Show Execution Plan" on in Query Analyzer, and execute a(fairly complex) sproc, I note that a particular query is reported ashaving a query cost of "71% relative to the batch" - however, this isnowhere near the slowest executing query in the batch - other querieswhich take over twice as long are reported as having costs in theorder of a few percent each.Am I misreading the execution plan? Note that I'm looking at thegraphical plan, and am not reading the 'estimated' plan - I'm usingthe one generated from executing the sproc. My expectation was thatthis would be based on the execution times of the queries within thesproc, however, this does not appear to be the case. (Note - Idetermined execution times from PRINT statements, using GETDATE() todetermine the current time, down to milliseconds).Any feedback would be of great assistance... I may well have tochange the way I approach optimizing queries based on these findings.Thanks,LemonSmasher.
(isnull(Answer_description,' ') + ' ' + Answer_text) as Answer
from dbo.viewX where assessment_id ='xxxx'
Pleae Note: This query is assigned to a string and passed to the database layer. So, i need the modifications with in this query. Answer_description and Answer_text are the two different fields, i need to embed a ":" (colon) between the two data if Answer_text is having any data else display only answer_description.
If a button is never clicked in a given month, it never gets logged to the table. In this example, the 5th button was not clicked during the month of December, so it does not appear in the results. I want to modify my query so it displays the name of the button and a zero (in this case "5th Button 0") in the results for any buttons that were not clicked. For some reason I am drawing a blank on how to do this. Thanks in advance.
I am facing an issue in MDX Query. I have a custom MDX query, When I run the query I am getting results too. Now my requirement is to show the dimension names in the query result, So that I can get those header names in the cell set itself. Please see the below image.
In this image I need to show 'Fiscal Year' and 'Fiscal Quarter' in that highlighted area. Is there any custom query for this?
in my table i ve the column of item code which contains '1000' ,'2000' ,'3000' series i jus wanna display the output of item codes '1000','2000'series and some of ('3000019','3000020','3000077','3000078').
i tried in my join query
these code left(itemcode,4) in ('1000','2000') or itemcode in ('3000019','3000020','3000077','3000078')
I am trying to print Companies with less than 100 employees for all dates.Here's my table structure
Create table CompanyEmployeeArchive( Company varchar(100) not null, Employees int, Dateinserted date)
Insert into CompanyEmployeeArchive values('Microsoft',1001,'2015-01-01') Insert into CompanyEmployeeArchive values('Microsoft',1050,'2015-02-01') Insert into CompanyEmployeeArchive values('Microsoft',1600,'2015-03-01') Insert into CompanyEmployeeArchive values('IBM',10,'2015-01-01') Insert into CompanyEmployeeArchive values('IBM',80,'2015-02-01') Insert into CompanyEmployeeArchive values('Apple',90,'2015-01-01') Insert into CompanyEmployeeArchive values('Apple',900,'2015-02-01') Insert into CompanyEmployeeArchive values('Apple',1000,'2015-03-01')
I want companies that have employees less than 100 for all dates i.e. Only IBM. Apple has < 100 employees only on one month.Select Company, dateinserted, employees from CompanyEmployeeArchive group by company,dateinserted,employees having employees < 100 order by company, dateinserted this query lists Apple too. How can I change the query so Apple does not show up in the list.
SELECT id, (CONVERT(datetime, event_date)) AS event_date, xmlfilename, event_active FROM test WHERE (YEAR(event_date) >= YEAR(GETDATE())) AND (MONTH(event_date) >= MONTH(GETDATE())) AND (DAY(event_date) >= DAY(GETDATE())) ORDER BY event_date ASC
The above query shows me event only upcoming event from the current month.
I want to list all the events happening today and future. I do not want to show past event. How to do this?
