Select * From ZT_DailyRpt_Detail Where isNumeric(TenDayDate) = 1 And TenDayDate < Convert(varchar(10),DateAdd(Month,-CAST ((SELECT keepmonth FROM zt_databackup a, zt_biller b WHERE a.companycode = b.companycode AND zt_DailyRpt_Detail.biller_code = b.billercode) AS int),GetDate()),112)
the sub query �SELECT keepmonth FROM zt_databackup a, zt_biller b WHERE a.companycode = b.companycode AND zt_DailyRpt_Detail.biller_code = b.billercode) AS int】will return a value 6 or 12 for the reason, every month have differnet days, ( some have 31 days , some are 30 days ) I don't want to check the day of date , only to compare month if sub query return "6" and I do DTS on 2007/07/29 , will select date which TenDayDate< 2007/01 not TenDayDate<2007/01/29 ( don't want to check the day of date)
does my query correct? if not can you correct it for me? thank you very much
I am going to compare thie value 2007-9-11 (this value was retrived from the column(TxnDate) in my DataBase, type is DateTime) I write code select * from ZT_ModifyLog where Year(TXnDate) = Year(GetDate()) AND ( ( Month(TXnDate) < Month(GetDate()) ) and Month(TXnDate) >= Month(GetDate())-1) it will like select * from ZT_ModifyLog where Year(2007-9-11 ) = Year(GetDate()) AND ( ( Month(2007-9-11 ) < Month(GetDate()) ) and Month(2007-9-11 ) >= Month(GetDate())-1) → select * from ZT_ModifyLog where TxnDate(2007) = 2007 AND 10 < GetDate(10) and 9 >= 9 so return TXnDate between 2007/9/1~ 2007/9/30 but what if Month(GetDate())-13)?? when the -1 biger than 12... I guess the code will cause error ... but can't think out how to avoid and change my code
I have a SmallDateTime data type field. The SmallDataTime contains day/month/year hour:minuteeconds AM/PM. What I wanted to do is create a store procedure that will only take the month of the field and compare that to my variable. So for example, I wanted to something like the following:
hai friends, iam doing a project in .net and using sql server. i need to compare only month and year part in datetime type to retrive data. 1)retrive unique year and its months available in the database. like may 2008 apr 2008 mar 2007
i would like to see the 2014-06 matched results (3rd query), if the same ssn and acctno is exist in 2012-06 and 2013-06 and 2014-06 then eliminate from results, otherwise show it
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06' select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06' select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06'
i have written the below query but it shows only matched across three queries, but i want to display / delete from 2014-06 records if the ssn and acctno is exist in 2012-06 and 2013-06
select c.* from ( (select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06' ) a join (select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06' ) b on a.SSN = b.SSN and a.acctno = b.acctno join (select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06' ) C on a.SSN = c.SSN and a.acctno = c.acctno join )
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.
ID varchar (contains alphanumeric values,not unique) Territory (combined with ID unique) Total_Used int can be null Date_ date (date of the import of the data) ID Territory Total_Used Date_ ACASC CAL071287 2014-06-01 ACASC CAL071287 2014-08-01 ACASC CAL071288 2014-09-01
[Code] .....
Now the problem,per month I need the most recent value so I'm expecting
I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.
I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount
My Desired output
My current query
SELECT RG.Id AS Id, RG.Name AS Name, ISNULL(SUM(AC.Amount), 0) AS Amount, RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]
Does anyone know how I can get last day of month if I pass a function a given month and and given year. @Month = 2 @Year = 2004 The result I would need is 29 because there are 29 in the month of February in the 2004. Any help on this is greatly appreciated. Kellie
when I'm in MediaImportLog , I want use column ImportSource to compare with column ChainCode in table BillerChain ( so I get BillerInfoCode) and then use the BillerInfoCode I got to compare with column BillerCode in Table Bill ( I get CompanyCode) finally I use CompanyCode to compare with column CompanyCode in table DataBackup so I can get the company's keepmonth How can I get the keepmonth? can I use parameters ?
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
Hi I trying to find a way to determine the number of working days per month starting from the current date to the last day of the current month.And within the same store procedure determine the number of working days as normal (each month is independent from the next). For example: The store procedure is executed
September: @CurrentDate = 9/10/2007 @EndDate = last working day 9/30/2007
Total# of working days = 15
October: @CurrentDate = 10/1/2007 @EndDate = last working day 10/31/2007
Total# of working days = 23
November: @CurrentDate = 11/1/2007 @EndDate = last working day 11/30/2007
Total# of working days = 22 etc. Any ideas of how i can approch this?
i have some classes that I want to group by month/year (note:i dont need the day of the month) how do i wirte my sql so it only gives me the dictinct groups month/year of the classes I have so that it comes out like so.. 11/2006 12/2006 1/2007 3/2007 i try with my sql below but i cant get the groups th come out in order. i dont think it sees it as a date value. dbo.classgiven.classdate date of the class.thank you all SELECT DISTINCT { fn MONTH(dbo.classgiven.classdate) } " + "/" + "{ fn YEAR(dbo.classgiven.classdate) } AS monthyear,{ fn MONTH(dbo.classgiven.classdate) } AS monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL)", conNorthwind )
Does anyone know of a way to use a funtion for returning records based on fiscal reporting periods like Quickbooks uses for example "This Month", "Last Month", "This Quarter", "Last Quarter", "This Year", "Last Year". While I realize that I can create a very long date time parsing routine for this but it is not very elegant or useful. I thought there might be a way to do this already with an existing function.I have created a stored procedure that I pass a @ViewRange Parameter to and it returns the records that I want but I need this ability in several procedures and wanted to turn it into a stored procedure.IF @ViewRange = 'This Month' SELECT TOP 20 Customer.LastName AS Customer, SUM(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE (MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP)) AND (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY SUM(Sales.AmtCharge) DESC;IF @ViewRange = 'Last Month' SELECT TOP 20 Customer.LastName AS Customer, Sum(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE(MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP) - 1) And (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY Sum(Sales.AmtCharge) DESC; Any ideas?
I would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month], FROM RECalendar WHERE (EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101))) AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
In my database . Some to the dates has been stored wrongly . for example 03/04/2008 to be stored in database . But in my database it was stored as 04/03/2008 . like this i have more that 100 records . How can i change this to correct format using query . Guide me urgent .
I remember reading that you can compare the schema of 2 databases to see if there are any diferences. For the life of me, I can not remember where I read it or what it is called. Any help would be great!
If we use operator < <= > >= to compare NON nummeric, then how it is being compared..? what it returns? eg : .... WHERE someObj1.UniqueX <= someObj2.UniqueX
public string Compare(StoredProcedure storedTarget)
{ string storedScript = string.Empty; if (storedTarget.RoutineDefinition != this.RoutineDefinition) { // string storedScript = (@"ALTER PROCEDURE ); <--- How can i put new stored procedure ?????? What is sql code for this work ? } } Thank You all sql guru s
I have an application that writes records with a timestamp to an sql table.
I wish to compare the values in each field and see if they have changed from the previous record and then delete if they haven't. Can anyone offer a script or procedure to do this please?
hi i doing a project like "IMesh", whatever ,i will take a sentencefrom the user and want to retrieve all row from the table has similarwordsfor example the user enter "programming with c#" so i retrieve allfields that contain "programming" or "c#"some thing like what a search engine dothanx
I need to compare tow dates DateField1 and DateField2 and find number of hours between these two dates. Then I need to deduct non-business days and hours (Business days: Monday-Friday and Business Hours: 7:00am-7:00pm) from this and find net hours. How can I do this?
Hi,I need to compare Todays Date with a Date in the Table (DateExpired)to see if the membership expired. How do I do that? thanks SELECT DateExpired FROM Member
i am making a transaction function for my website, when people transfer an amount from account A to account B, it should check the amount if account A has enough money to transfer. here is my codes, should i add an if statement before the tra.update()? how to write this codes? SqlDataSource tra = new SqlDataSource(); tra.ConnectionString = ConfigurationManager.ConnectionStrings["shuliConnectionString"].ToString(); tra.UpdateCommand = "update account set balance=balance" + -Convert.ToDecimal(TextBox5.Text) + " where AccountNumber = '" + TextBox3.Text + "'"; tra.Update(); tra.UpdateCommand = "update account set balance=balance+" + Convert.ToDecimal(TextBox5.Text) + "where AccountNumber = '" + TextBox4.Text + "'"; tra.Update(); tra.InsertCommand = "insert into Transactions(TransactionType,AccountNumber,DestAccount,Amount,Comment,ModifyDate) values ('T','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "','" + TextBox2.Text + "','" + DateTime.Now.ToLocalTime() +"')"; int inertRowNum = tra.Insert(); Server.Transfer("deposit_withdraw_confirm.aspx");