SELECT a.FIRMA,
a.PSNR,
a.FELDNR,
a.PFLFDNR,
a.INHALT AS Cardnr,
a.PFGLTAB,
[Code] .....
But it deliver me also rows which are not in valid. I need only rows from last month from 01.10 until 31.10 and this rows are over the time. How can i select dynamically all row between 01.10 and 31.10 ?
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select Jan 1- 23rd feb 1-23rd march 1-23rd april 1-23rd ,value from table
I've seen several posts that begin to address this problem, but havenot found a simple, elegant solution that will accomplish this goal.The important part of this solution is that it must be completelydynamic - I have over 40 different categories of devices, each withdifferent fields, and each search will return only one category. Ihave no knowledge of the number or datatype of these field namesbeforehand and must use the sp to dynamically create the table and thentranspose the data.Here is an exampleI have normalized data in this format (this is a simplification)deviceId fieldName fieldValue1 color red1 shape square1 weight(kg) 2.02 shape round2 weight(kg) 1.53 color blue3 shape oval3 weight(kg) 1.0I would like to convert this to the format: (note that it must handlenulls - deviceId 2)deviceId color shape weight(kg)1 red square 2.02 round 1.53 blue oval 1.0Anyone with any thoughts on how best to accomplish this?thanks,Matt
Hi All, I have a column in the table of type datetime.I need to get all the rows in the table but month wise.For Ex:
Jan 2003 (Rows whose date is in Jan 2003) Feb 2003 (Rows whose date is in Feb2003) . . . Jan 2004 (Rows whose date is in Jan 2004) Feb 2004 (Rows whose date is in Feb2004) . . so on...
Can any body give my SQL query to get the desired results. Thanks a lot, Kumar.
I am trying to get the number of rows for each month.
table name:sitez ID Name crDate access =========================== 1 Bob .. 2014-01-11 .. 1 2 Jerry .. 2014-01-22 .. 2 3 Jim .. 2014-05-06 .. 1 4 Jason .. 2014-12-11 .. 1 5 Jen .. 2014-11-21 .. 3
I am using the results to make a bar graph so I am querying the database for a given year and expecting 12 results back (running SQL 2012).
Select count(*) as ttl FROM sitez WHERE year(crdate) = 2014 and access = 1 group by all month(crdate)
This should return: 1 0 0 0 1 0 0 0 0 0 0 1
However when testing the script I was only getting back: 1 1 1
which didn't knowing which months were 0
By changing the GROUP BY to GROUP BY ALL, it now puts in the zeroes. However I'm still having issues with incorrect results.
When I query the database, the results for December 2014 shows '13' when I execute:
Select count(*) as ttl FROM sitez WHERE year(crdate) = 2014 and access =3 group by all month(crdate)
There are ZERO rows made with a year of 2014 and an access of 3.I verified this by going a straightforward select * from sitez where crdate = 2014 and access = 3
Why I'm seeing ghost results?
All I need is 12 results, ordered by crdate month.
i have a datatable with Part , DateSold,ItemsSold i want to show the sales for the last 12 months by month. if there were no sales for a given month i want the table to show 0.
here was my first attemp that only gives me data if all twelve months had sales.
SELECT MONTH(DateSold) AS Month, SUM(ItemsSold) AS ThisYear FROM inv_Monthly_Sales WHERE (YEAR(DateSold) = @Yr - 1) AND (Part = @Part) GROUP BY MONTH(DateSold) ORDER BY Month
i tried using isnull with the sum but that didn't work. how can i force the table to always generate a dummy table of 12 months and then left join to it and in that way force it to give 12 moths of sales even when it does'n exist.
I'm using the row sampling Transform to perform an A/B split on some data.
Is there anyway that I can dynamically populate the number of rows box ?
I currenlty have to find out the count, then divide it by 2 and manually enter this in to the box. It would be nice if there was a property associated with this that so that I could populate a variable with a count from a SQL query then associate this with the box using an expression.
Has anyone have any ideas how I can do this?
Also is there any other way of getting an A/B split of my data using SSIS or taking it one step further getting an A/B/C split..
In my EventHandler "cmdStop_Click" I am trying to perform some action but on that particular row's data.
My page is also reloading after every 5 secs.
After clicking a button in a row, when page refreshes, I am getting this message in popup error message. also that entry is ommited(as per code in EventHandler)
Hello what I'd like to display the following in a matrix report:
Parameter selected: 3 (March), 2008 (Year)
Monthly TO Summed up ArtNo March <=March 1210 20,500 50,900 1220 21,200 64,000 1230 15,400 40,300 ... ... ...
So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.
I need to generate a csv file from another csv file, seems to be simple but let's go the trick thing:
Needs to have maximum 1000 lines, if I reach to this, I need to create another csv and fill that new one.
Exemplifying:
I have a csv file called fileA and this has 2000 lines and another csv called fileB with 1500 lines.
I need to loop a folder and get the fileA, create an output called FileAOutput and start to fill that, if I reach to 1000 lines, I need to create a FileAOutput_2 and fill the other 1000 lines...so I'll go to fileB and do the same thing, but in the second case, I'll have 500 lines in the second output.
Given any datetime value, I need SQL to derive the last day (numeric) for the month in the datetime value..
eg. "2001-06-22 14:24:56.563" --> Last numeric day of June 2001 is 30 eg. "2000-02-06 23:11:05.323" --> Last numeric day of Feb 2000 is 29 eg. "1999-02-07 08:34:10.037" --> Last numeric day of Feb 1999 is 28
I'm sure I'm missing something. I am returning the TOP X number of customers by revenue and I'd like to change the number of records returned by passing a parameter but I keep getting an error. @TopX int ( or varchar) SELECT @TopX CompanyName, Amount FROM Sales Where..... Why will this not work?
select * from tbl where nmonth between datepart(mm,DATEADD(month, -2, getdate())) and datepart(mm,getdate()) this query is returning my data between 1(jan) and 3 (mar) if i change my query to get all datas between nov to mar select * from tbl where nmonth between datepart(mm,DATEADD(month, -4, getdate())) and datepart(mm,getdate()) this query is not returning data since the month between 11 to 3 Guys,Help me out to correct this syntax.
The below code is ASP 1.0 to dynamically search a database and I want to use the same concept for a ASP.Net 2.0 solution. Do I do this in the code behind or on the aspx page and if on the aspx page what controls do I use for the array split? Basically where do I start. It took me a long time to get this old code working, I am hoping it is simpler in 2.0. Thank you OLD ASP 1.0 code to dynamically build a Sql Select statement for searching a database using one or more search words entered by user. If Request.Querystring("kwdSearch") <> "" ThenDim kwdString, ArrKwdString, iCountiCount = 0 kwdString = Replace(Request.Querystring("kwdSearch"), "'", "''")ArrKwdString = Trim(kwdString)ArrKwdString = Split(kwdString, " ",-1,1) For iCount = 0 to UBound(ArrKwdString) If iCount < UBound(ArrKwdString) Then Criteria = Criteria & "tblLinkInfo.L_Keywords LIKE '%" & ArrKwdString(iCount) & "%' AND " Else Criteria = Criteria & "tblLinkInfo.L_Keywords LIKE '%" & ArrKwdString(iCount) & "%' " End ifNext RS.Open "SELECT * FROM tblLinkInfo Where (" & Criteria & ") AND L_Enabled = 1 ORDER BY " & SortBy & "L_Rank", CNN, 3 If RS.EOF Then If Rs.State Then RS.Close RS.Open "SELECT * FROM tblLinkInfo WHERE L_Description LIKE '%" & Replace(Request.Querystring("kwdSearch"),"''","'") & "%' AND L_Enabled = 1 ORDER BY " & SortBy & "L_Rank", CNN, 3 End If RESULTS --- Display results with Repeater1.DataBind(); etc Exit SubEnd If
How can i assign select statement to a sqldatasource I mean if the user clicks button 1 sqldatasource=-"select * from customers where status='ClOSED' " or else if the user clicks button2 sqldatasource="Select * from Customers Where Status='OPEN' " I want to bind the sqldatasource to a gridview finally . Is this possible ? I tried sqldatasource filters But am reallly confused Any suggestions/solutions are appreciated thankyou
I m writing a stored procedure to query a table Population that has the following fields - CityId, CityName, Plus0, Plus10, Plus20, Plus30, Plus40, Plus50, Plus60, Plus70, Plus80. The field Plus0 contains the number of people of age > 0 living in the city, Plus10 contains the number of people of age > 10 living in the city and so on. Given the city id and age groups like 20To40, 50To60, 40Plus, etc., I should be able to query the number of people in the city corresponding to the requested age group. Note that if the requested age group was 20To60, I need to make use of only 2 fields Plus20 and Plus60 in the table to compute this value. And if the requested age group was 40Plus, then I need only the value in the field Plus40. The problem is that a wide variety of age groups can be requested like 0Plus, 10Plus, ... , 80Plus, 0To10, 0To20, 0To30, .... 70To80.
Which is the most effecient way to handle this ?
1. Have a stored procedure that returns all the fields even though only 1 or 2 of them would be actually used ?
In this case, if I returned data for a large number of cities then there would be a lot of unnecessary information that was returned by the query. Lots of data would be passed through the network though most of it would not be used.
2. Have a stored procedure that takes in parameters @Plus0, @Plus10, @Plus20, .. @Plus80 that are bits indicating whether the field was required or not and then using a CASE statement to return values for a field only if the corresponding bit parameter was set, and returning NULL if the corresponding bit paramter was not set ?
In this case, I would be returning NULL for all those fields that were not required for a particular age group. This would save some network bandwidth, wouldn't it ?
3. Pass in the age group itself (ex: 0To20) as a parameter to the stored procedure and have lots of IF statements, one for each age group, that return only the fields that are needed for that age group.
This leads to a lot of code repitition.
4. Use a similar approach as above but use dynamic SQL queries to avoid code repitition.
But using dynamic SQL queries can affect the performance of the stored procedure as they need to be compiled each time.
I have a remote report ... I need to call one of two stored procedures depending on which parameters were passed to the report. (Both stored procedures return the same fields.) Does SQL Reporting Services allow you to switch the dataset or stored procedure name dynamically? If so, where do I put the logic? Right now I have it working with one stored procedure. I'm thinking that I should just make one stored procedure that takes all parameters and calls one of the other two stored procedures. Do I have any options besides this?
I posted this question last night and thought I had an answer. I have a date field. I want to be able to filter records by the month of the date. To do this, I pass the integer of the month. But I also want to be able to return all the records if no month integer is passed. So functionally,select * from table where (MONTH([AD ENDS]) = @month)will return all records where [Ad Ends] is in January if @month = 1 and all records if @month is empty. The solution I got last night was to use select * from table where (MONTH([AD ENDS]) = ISNULL(@month, MONTH([AD ENDS]))) If @month is null, all records are returned. I was focused on another aspect of the page when this was posted. It worked in the designer, so I thought I was set. This morning I realized I don't know how to pass a null variable in a querystring. Since a querystring is a string, it probably can't be done. Another suggestions was to change this programmatically. But is there a way to do this in the dataset? I'm using SQL Server 2005, a strongly typed dataset and the designer.Diane The answer i got last night was to
Hi, Support, I have a table order and the field are orderid,customerid,orderdate. What i need to do is that I want customer order list as how many order per week in a month. Can anyone help me please. Regards
Year    Month    Day    RELH    ------   --------    ------   ------- 1973    4       1       50 ...... etc 1973    4       30      20 .....etc 1974    5       1        50 ....etc 1974     5       30       99
I need to get years where average of RELH  of  months within year meetÂ
I tried to find it on the Internet, but was unsuccessful. If someone can help me with a select statement that will return JUST the current month and year or day and month. I would really appreciate it.
Is there a way I can write a query to dynamically select a database table. That is, instead of having a variable for a certain column like customerId which would be €œcustomerID = @customerID€? I want to use the variable to select the table. I assume I may have to use a stored procedure but I am unclear as to how this would be coded.
