Hi folks,I'm trying to write a simple SELECT statement that will execute inquery analyser but will just have the data with no column names, or thedotted line between them and the data. I also want to avoid thestatement at the end which says nnn rows affected. any ideas? I want todo this because I intend to write the results to a flat file.Thanks for your helpDanny....
Does anyone have an example of an SQL query which returns rows for theyear-to-date, but where the "year" commences on August 1st?e.g. select * from mytable where datefield > last august 1stTIA for any helpIsabel
I have a question regarding ROLLUP and how to use the operator.
I've been asked to retrieve a list showing the total repair cost per year displaying Transport Type, Manufacturer and Transport. The query I used is:
SELECT ty.Description AS TransportDescription, t.SerialNumber AS Transport, ty.Manufacturer, SUM (r.RepairCost) AS RepairCost, YEAR(r.EndWorkDate) AS [Year]
[Code] .....
How could I use the rollup function to display subtotals per Transport Type, Manufacturer and Transport?
how to retrieve names of the users between start time and end time using stored procedure in sql server??
i have a table which stores user names along with other details. can anybody tell me how to get the names of the users between start time and end time??
hi, i am a nubie, and struggling with the where clause in my stored procedure. here is what i need to do: i have a gridview that displays records of monthly view of data. i want the user to be able to page through any selected month to view its corresponding data. the way i wanted to do this was to set up three link buttons above my gridview: [<<Prev] [Selected Month] [Next>>] the text for 'selected month' would change to correspond to which month of data was currently being displayed in the gridview (and default to the current month when the application first loads). i am having trouble writing the 'where' clause in my stored procedure to retrieve the selected month and year. i am using sql server 2000. i read this article (http://forums.asp.net/thread/1538777.aspx), but was not able to adapt it to what i am doing. i am open to other suggestions of how to do this if you know of a cleaner or more efficient way. thanks!
I added the subquery and now this thing consistently takes more than five minutes to return 7100+ rows. Any suggestions? Thanks again, you guys are the best.
ddave ---------------------------- SET @StrQry1 = '(SELECT 1 AS Counter, Q1.SubsidyLevel, VEL.* FROM dbo.ViewEligibilityPHC VEL LEFT OUTER JOIN (SELECT * FROM dbo.MEMB_LISHISTS l WHERE l.LISThruDate is null AND l.Deleted = ''0'') AS Q1 ON VEL.MEMBID = Q1.MemberID WHERE VEL.OPTHRUDT is null AND VEL.OPT LIKE ''' + @HPlan + ''' AND (VEL.PCP IS NULL OR VEL.PCP LIKE ''' + @Prvdr + ''') AND VEL.HCC LIKE ''' + @HCC + ''' AND (VEL.CaseMgrID IS NULL OR VEL.CaseMgrID LIKE ''' + @CaseMngr + ''') AND VEL.OPFROMDT <= CAST(''' + @SDate + ''' AS datetime) AND ISNULL(VEL.OPTHRUDT, CAST(''' + @TDate + ''' AS datetime)) >= CAST(''' + @EDate + ''' AS datetime)) '
i want to be able to get the columns from my table except for its pk. the long method is to selecting all columns by listing them one by one right? but what if i got 100 columns..
so.. i wanted to use the information_schema..
i have:
use dbase; select column_name from information_schema.columns where table_name = 'table1' and column_name != 'pkid'
so there i'm listing the columns, but how am i suppose to select those columns from my real table?
Hi,I have a table with two columns Task and Employee. It lists all thetasks and the assigned employees. The same task will have multipleroles with an employeename or 'manager' as the data.If I have the following data'sales', 'john''sales', 'manager''dev', 'manager''make_coffee', 'manager''browse', 'jane''browse', 'manager'I need to get the rows wherever an employee is named (sales and browsefor example) and get manager for the rest.I can make it in two queries. Look for not manager in one and then formanager. Is there anyway to get them in a single query?If I need to look for 'sales', I need to get 'john' and not 'manager'.How to do that in a single query?Another need is to list all tasks with assigned. So for the above, Ishould get the following list'sales', 'john''dev', 'manager''make_coffee', 'manager''browse', 'jane'with two queries, I will get'sales', 'john''browse', 'jane''dev', 'manager''make_coffee', 'manager'which is ok. Order/sequence is not important.TIA,Sreelatha
HelloThe problem is need to find out the querry that has updated or insertedinto the table and in turn 'Triggered the Trigger'. I have the username, the machine name, Application name, but not the query. The updateis not desired and the application is doing it but the applicationbeing so large we are unable to pin-point the code which is doing thedammage.Pls help!RegardsAnubhav
I have a dropdown list with checkbox and when I select multiple options and search, its returning only the last selected value in the grid. Here is the code I use it for search. Designation is the column where I bind its values to the checkbox.
I have the following script that calculates Sales by month and current year.
We run a Fiscal year from April 1st thru March 31st.
So April 2012 sales are considered Fiscal Year 2013.
Is there a way I can alter this script to get Fiscal Year Totals?
select ClassificationId, YEAR(inv_dt) as Year, cus_no, isnull(sum(case when month(inv_dt) = 4 then salesamt end),0) as 'Apr', isnull(sum(case when month(inv_dt) = 5 then salesamt end),0) as 'May', isnull(sum(case when month(inv_dt) = 6 then salesamt end),0) as 'Jun', isnull(sum(case when month(inv_dt) = 7 then salesamt end),0) as 'Jul',
[Code] ....
Data returned looks like the following.
ClassificationID Year Cus_no Apr May June .... 100 2012 100 $23 $30 $400 100 2013 100 $40 $45 $600
What I would need is anything greater than or equal to April to show in the next years row.
I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:
And the resultant string is: dd/mm/yyyy, for example 30/08/2004. But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?
I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?
I have two queries that give me the total sales amount for the current year, and the last year.
SELECT SUM([Sales (LCY)]) FROM [$Cust_ Ledger Entry] cle LEFT OUTER JOIN dw.dim.FiscalDate fd ON fd.CalendarDate = cle.[Posting Date] WHERE [Customer No_] = '10135' AND fd.CalendarYear = '2013'
[Code] ....
I would like to learn how to be able to make this a single query and end up with two columns and their summed up totals. Like it shows on the attached image.
This is my query without the columns I need:
SELECT c.CustomerNumber ,c.Name ,c.ChainName ,c.PaymentTermsCode ,cle.CreditLimit AS 'CreditLimit' ,SUM(cle.Amount) AS 'Amount'
I am writing a usage query, I need to determine what the current month and year is. If the current month is 1, then I need Year-1. I keep getting this error Line 5: Incorrect syntax near '='. If I comment out the case statement just evaluate the (YEAR(User_Date) = YEAR(DATEADD(yy, - 1, GETDATE())))or (YEAR(User_Date) = YEAR(GETDATE())) by itself, the query works. What I am missing? Anybody?1 SELECT CONVERT(char(10), User_Date, 101) AS userdate, COUNT(*) AS CNT, User_Name2 FROM Users3 WHERE (User_Name = 'Joe Bob') AND (MONTH(User_Date) = MONTH(DATEADD(mm, - 1, GETDATE()))) AND 4 case MONTH(User_Date) when 1 then 5 (YEAR(User_Date) = YEAR(DATEADD(yy, - 1, GETDATE())))6 else (YEAR(User_Date) = YEAR(GETDATE()))7 END8 GROUP BY CONVERT(char(10), User_Date, 101), User_Name9 ORDER BY CONVERT(char(10), User_Date, 101), User_Name
I am using the query below and it is working great to give me montly averages. I need to find the fiscal year to date averages for the same data. I think all I should need to change is the Select and Group by sections but I am having no sucess.
SELECT Format([CallDate],"yyyy mmm") AS [Month], Count(DailyCallStats.CallDate) AS CountOfCallDate, DailyCallStats.Agent, Avg(DailyCallStats.Inbound) AS AvgOfInbound, Avg(DailyCallStats.Outbound) AS AvgOfOutbound, Avg(DailyCallStats.TotalCalls) AS AvgOfTotalCalls, Avg(DailyCallStats.AvgWrap) AS AvgOfAvgWrap, Avg(DailyCallStats.ParkTime) AS AvgOfParkTime FROM DailyCallStats GROUP BY Format([CallDate],"yyyy mmm"), DailyCallStats.Agent, Format([CallDate],"yyyy mm") ORDER BY Format([CallDate],"yyyy mm");
Hi I need to write something in T-SQL which will return a record set from a table of IT hardware. The record set should comprise all items that are older than 3 years from the current date. Each hardware item has a purchase date against it so it should not be too difficult. I am considering the DATEDIFF function but I am not sure how best to write it.
I'm using PHP with SQLServer2k to create a page containing monthly counts of episodes at a facility occurring between two user selected month/year combinations. For instance, the user could select 10/2003 and 2/2004 and facility X and get a line for each month showing the count of episodes occuring in that month.
The problem is that the episode date is stored in three integer fields (epiday, epimonth, epiyear) and I'm having a terrible time getting them into a format where I can use them in a between statement.
I've tried evaluating the parts of the episode date seperately like:
where (epimonth>=10 and epiyear=2003) or (epimonth<=2 and epiyear=2004)
and that works, but what happens when someone wants to see from 10/2002 to 2/2004?
We have customers who are new to this year (2014) and there same customers in last year (2013). also there are customers we have not received business this year but only last year. so there are 4 conditions.
1) New customer (2014) - Customer(B) 2) Old Regular customer (2013 and 2014) - Customer(A) 3) Last Year (Lost) customer (2013) - Customer(C), no business received in year(2014)
For example we have a transaction table:
TransactionId, ReceivedDate, Customer 1, 2-Dec-2013, A 2, 3-Jan-2014, A 3, 2-Mar-2014, B 4, 25-Nov-2013, C
I want results like
Customer, Business (this year activity/last year activity) A, 1/1 B, 1/0 C, 0/1
How can i show this for each year? I used to separate it month wise as below but it does not return applying year with each customer anyhow...
select t.customerId, YEAR(Receiveddate), sum(case month(ReceivedDate) when 1 then 1 else 0 end )as Jan, sum(case month(ReceivedDate) when 2 then 1 else 0 end )as Feb, sum(case month(ReceivedDate) when 3 then 1 else 0 end )as Mar, sum(case month(ReceivedDate) when 4 then 1 else 0 end )as Apr,
HiI want to write a function that can return a sum for a given daterange. The same function should be able to return the sum for the sameperiod year before.Let me give an example:The Table LedgerTrans consist among other of the follwing fieldsAccountNum (Varchar)TransdateAmountMST (Real)The sample data could be1111, 01-01-2005, 100 USD1111, 18-01-2005, 125 USD1111, 15-03-2005, 50 USD1111,27-06-2005, 500 USD1111,02-01-2006, 250 USD1111,23-02-2006,12 USDIf the current day is 16. march 2006 I would like to have a functionwhich called twice could retrive the values.Previus period (for TransDate >= 01-01-2005 AND TransDate <=16-03-2005) = 275 USDCurrent period (for TransDate >= 01-01-2006 AND TransDate <=16-03-2006) = 262 USDThe function should be called with the AccountNum and current date(GetDate() ?) and f.ex. 0 or 1 for this year / previous year.How can I create a function that dynamically can do this ?I have tried f.ex. calling the function with@ThisYear as GetDate()SET @DateStart = datepart(d,0) + '-' + datepart(m,0) +'-'+datepart(y,@ThisYear)But the value for @dateStart is something like 12-07-1905 so thisdon't work.I Would appreciate any help on this.BR / Jan
What I am trying to do is create a DAX formula to rank each event in the order that it happened for the year. So I want to end up with something like this. This way I can compare the events year over year as the events don't happen on any regular time schedule.
I have tried to do this by creating a formula that will give me the day number of the year:
Day of Year =(YEARFRAC(CONCATENATE("Jan 1 ", YEAR([Event Date])),[Event Date])*360)+1
Then using rankX on this table, but I cant seem to get the proper result. Perhaps I am not understanding the use of rankX or going about this the right way.
=RANKX(FILTER(Event,EARLIER(Event[Event Year])=Event[Event Year]),Event[Day of Year])
or
=RANKX(All(Event[Event Year]),[Day of Year],,1,Dense)
Hello, I need to find the percentage of a a given contract start and end date for the year given.For example, the contract_start date is 05/08/2000 and the contract_end date is 04/30/2010, of course this will be 100% if you want to find the percentage for year 2008 but if you wanted to find the percentage for year 2010, then the percentage would be something like 42% (appr. 5 months) (b/c it would for year 2010, the contract would be from 01/01/2010 thru 04/30/2010)I need to find the percent from the beginning of the year, to the end.a few examples: start: 01/01/2007 end: 05/01/2007 if for year: 2007; this is 4 months @33.33% start: 05/01/ 2006 end: 06/01/2007 if for year: 2007, then 6 months @ 50% (01/01/2007 - 06/01/2007); but if it was for year 2006 then it would be 7 months @ 58% (05/01/2006 thru 12/31/2006) start: 01/01/2000 end: 03/01/2010 for year: 2008 then 12 months @ 100% Any help would be valued. Thank you!
It is all contained in one table. I have got the earliest years by using the YEAR() function and grouping by it. The only problem is that I am having a problem joining the table back onto itself with the subselect because of it's an aggregate.
Here is the first join I have....
SELECT DT.ClientID, Year1 FROM ( SELECT ClientID, YEAR(MyDate) as Year1 FROM MyTable ) AS DT GROUP BY ClientID, Year1 ORDER BY ClientID, Year1
I want to use that as my derived table and then join back to it... but it's not working...
I want to display records from Jun-2006 To Mar-2008.Month and Year are two different fields.I got the expected result using Union as follows.But i want it within one qury only.Is it possible? if yes please guide me.
(Select SUM(Amt), R.Month AS Month ,R.Year AS Year FROM Receipt R WHERE (R.Month >= 6 AND R.Year = 2006) Group By R.Year , R.Month ) Union (Select SUM(Amt), R.Month AS Month ,R.Year AS Year FROM Receipt R WHERE (R.Month <=3 AND R.Year = 2008) Group By R.Year , R.Month )
I am in seach of a query where in I can provide month, year and client name and fetch last available comments from the table.
Client,Month,Year and Comments are columns in that table.
For Ex: If i pass client as A, month as 7 and year as 2015, I should get comments for client A, month July and year 2015 if available.
If data not available, it must go to June month and so on until it finds comments.Also when month is Jan, if query is going back, year also should get changed.
