i have this FUNCTION and the FUNCTION work OK now how to add to this FUNCTION another mondy date ? "last month last monday"
Code Snippet CREATE FUNCTION [dbo].[Monday_List]( @arg_date datetime ) returns table as return ( select convert(varchar(10), (dateadd(mm, datediff (mm, 0, @arg_date), 0) + (8 - datepart(dw, dateadd(mm, datediff (mm, 0, @arg_date), 0))) % 7) + offset, 103) as Date, [Index], Number from ( select 1 as Number, 'First_Monday' as [Index], 0 as offset union all select 2, 'Second_Monday', 7 union all select 3, 'Third_Monday', 14 union all select 4, 'Fourth_Monday', 21 union all select 5, 'Fifth_Monday', 28 ) x where month(dateadd(mm, datediff (mm, 0, @arg_date), 0) + (8 - datepart(dw, dateadd(mm, datediff (mm, 0, @arg_date), 0))) % 7) = month((dateadd(mm, datediff (mm, 0, @arg_date), 0) + (8 - datepart(dw, dateadd(mm, datediff (mm, 0, @arg_date), 0))) % 7) + offset) )
this is the Output i get
select * from Monday_List('1/3/8')
Date Index Number ---------- ------------- ----------- 03/03/2008 First_Monday 1 10/03/2008 Second_Monday 2 17/03/2008 Third_Monday 3 24/03/2008 Fourth_Monday 4 31/03/2008 Fifth_Monday 5 */
now i need to add to the FUNCTION "last month last monday" like this
select * from Monday_List('1/3/8')
Date Index Number ---------- ------------- ----------- 04/02/2008 last monday 0 ---------------------- i need to add 03/03/2008 First_Monday 1 10/03/2008 Second_Monday 2 17/03/2008 Third_Monday 3 24/03/2008 Fourth_Monday 4 31/03/2008 Fifth_Monday 5 */
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]
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 am writing an sql query as follow:SELECT DATEPART(dw, CALLSTARTTIME) AS dayFROM TABLEGROUP BY DATEPART(dw, CALLSTARTTIME)ORDER BY DATEPART(dw, CALLSTARTTIME)it returnsday====1234567my question is, how can I return monday, tuesday, wednesday, etc... insteand of 1-7?
I'm using VB.Net and SQL Server. Here's the scenario I have that I can't figure out how to accomplish: (btw, this is all manual - I'm NOT using an ASP.Net Calendar control) I want to provide a week's worth of calendar data automatically. Let's say I have all the calendar dates and times in a table. But - I only want to show the data from the current week. So, no matter which day the end user loads the page (Monday, Tuesday, Saturday, etc), I want to be able to find the previous Sunday (or Monday?), and then provide data from the table for events that are happening from that Monday, through the next Sunday. Finding the last Monday (or Sunday?) is the first problem, and then getting only data between that date and the following Sunday is the second problem - how to create the code and hw to create the sql statement - - any ideas?
I am using SQL Server 2000. I need to query my database for all thecontracts that came in during a certain time frame (user is promptedfor reportingperiodid).Table - PeriodsFields - Reporting Period id intReporting Period desc varchar(30)Reporting Period Begin Date datetimeReporting Period End Date datetimeIf the user selects a 3 then the begin date is Jan. 1, 2004 and theend date is June 30, 2004.Now I need to calculate did any money come in for each week in thattime frame. I need to create a weekly list of all the weeks in thattime frame. Each time frame begins on a Monday. So my list wouldlook like1/5/20041/12/20041/19/20041/26/2004All the way to the end of that time period.How do I create this weekly list from a given time period using T-SQL?I would appreciate any and all help on this.Thanks,Tony
I have a stored procedure that grabs the Region key from the user, and the date. But i want to set up 5 reports, for day of the week. So i want to make the second parameter a defualt parameter set by me in each report. which is better then having to create 5 stored procedures with the same info just different dates. when i try to enter the same data i have in my stored procedure to get Mondays date, i get an error.
Here is what i entered in the report parameter default expression box for the date :
I have a query and am trying to just return the difference between two dates but not include weekends.For instance, if I have 08/21/2006 - 08/28/2006, there are 6 weekdays. I tried this, but I am getting 7 as a result.SELECT DATEDIFF(weekday, request_start_date, request_end_date) AS days_off, request_id FROM request Any help would be greatly appreciated.
In my below T-sql the shipment date is set to next Thursday, if the shipment is missed in current week. Now, I've to change the t-sql code to change the shipment dates to Monday instead of Thursday.
USE tempdb; GO DECLARE @Date DATE; SET @Date = GETDATE(); --SET @Date = '2014-07-25'; DECLARE @TEST_DATA TABLE
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
I need to generate the week ranges like this format :
Here from date and to date would be picked up from the table but just to make you understand i have hard coded it but this is the real date which is falling inside the table.Â
Note : Week should be generated from Monday to Sunday within desired date range
Hi,We are using Month-year tables to keep the history of long transactionof our application. For example:We capture the details of a certain action in table"TransDtls<CurrMonth><CurrYear>" (this month: TransDtls072005).This way tables keep growing. every month a new table gets created. Wehave done it because we estimated that every month year table willcarry around 2 - 3 Lac records and most of the time the operations willwork on current month year table.Avoiding this way and carrying on with single table instead of "Monthyear" table might lead us system performance issues.But now we are a bit confused on the way we are heading and also facingthe implementation issues like manipulating data from different"month-year" tables. Could anyone please help us to make our visionclear on this?Looking for your valuable comments.Thanks.
I have a table report that lists the prior 12 months of sales data. I'd like the report to display in reverse order from current month back. When I sort decedning by month it puts the months in reverse alphabetic order. How can I sort by numeric order?
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
how to order by month name query returns Dec13,Mar14,Jan14,Nov13 .. etc but i want to Nov13,Dec13,Jan14,Mar14 ... etc
declare @cols as varchar(max), @query as varchar(max) set @cols =STUFF((select ','+QUOTENAME(tb3.month) FROM ( select distinct (DATENAME(MONTH,dtDate)+''+CONVERT(varchar(5),YEAR(dtDate)) ) as month from tableA) tb3 FOR XML PATH(''), TYPE ).value('.','NVARCHAR(MAX)'),1,1,'') set @query ='SELECT '+@cols +' from (SELECT DATENAME(MONTH,dtDate) + CONVERT(varchar(5),YEAR(dtDate)) as month,Price FROM tableA ) tb pivot ( sum(Price) for monthin('+@Cols+')) p 'exec(@query)
How could I create a table that shows the month, each day in that month and the year and give me the option to set a start date & and end date? This is sql server 2005. I have used this before, but it doesn't allow me to see the days in the month. Essentially I want my output to be
Day Month Year 01-01-2012 January 2012 ....... 01-02-2015 January 2015 ..... 03-01-2015 March 2015 .... 08-02-2020 August 2020
Code:
CREATE TABLE #yourTempTable([MonthName] VARCHAR(9), [Year] INT); WITH CTE(N) AS (SELECT 1 FROM (SELECT 1 UNION ALL SELECT 1)a(N)), CTE2(N) AS (SELECT 1 FROM CTE x CROSS JOIN CTE y), CTE3(N) AS (SELECT 1 FROM CTE2 x CROSS JOIN CTE2 y), TALLY(N) AS (SELECT 0 UNION ALL SELECT TOP 11 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM CTE3), DATETALLY(N) AS (SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())+N, 0) FROM TALLY) INSERT INTO #yourTempTable SELECT DATENAME(month,N), YEAR(N) FROM DATETALLY; SELECT * FROM #yourTempTable;
I am trying to make a query on one table, an invoices table, and Iwant to see how many orders are in each monthYear Month Count---------------------------2006 01 802006 02 1102006 03 208....I cant just do Distinct MonthPart because we have years, so to getthese three columns, is just beyond my SQL skills.Any suggestions or pointer would be greatly appreciated!!!!
I'm trying to get my head around this, any help would be appreciated. I have a calendar table I've created for use with a billing report. It would be great to have a column that had the first day of the month for every row entry (so for every row representing a day in february, that row would have February 1st, in datetime format). I thought it would be easy to create and populate this column at first, but I'm finding it much more difficult. Does anyone know how to populate this column?
I do appreciat your help, I want to run a store procedure which will show 6 months . I do not know how to write the procedure, here in the notion in my mind, I want to be able to pass an input parameter (month) to the procedure which will then run a query to show 6 months prior the input parameter month, how can I do that, thanks for your help
I'm using CTEs to try and get the totals of two different criteria queries that I want to group by Month depending on the associated Inquiry Date. Here is what I have right now;
Code: ;With CTE(total, InitDate) as ( SELECT count(Inquiry.ID), Inquiry.Date from Inquiry Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk Left Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.ID WHERE (Inquiry.Date >= '3/1/2012' AND Inquiry.Date <= '9/26/2012' AND Inquiry.Date IS NOT NULL) AND (Inquirer.Program = 'Res. Referral Coord.')AND TransferInquiryID_fk IS NULL Group By Inquiry.Date
[code]...
I get 170 for InitCount, but for TransCount I only get 19, not 26. I assume it is with my left outer join statement grouping but I am not sure how I would change this to get the proper counts. All I want to do is group the values together depending on the month they were done in.
I have a table thats updated daily with monthly data totals:
Month, Total orders1, Total orders2, etc 12/01/2012, 5, 8, etc 11/01/2012, 6, 5, etc
How do I pull data from this table in SQL Server for ONLY the current month? I was thinking using the getdate() function to get the current month, but it doesn't match exactly so I get no results
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 )
need help how to check in table if all the employees have Full month
if it double days fix it
if the employees have less days ! > move to temp table
for eny problem with this employee (check continuity or error) move to temp table ! i have stored procedure that insert employees for next month "Full month" from start of the month until end of the month how to check continuity and if i don't give less days OR double days in month like if the month is 29 days the employee must have 29 not more
for eny problem with this employee move to temp table !
situation 1 employees have less days ! sn empid ShiftDate day ------------------------------------------------- 1 111111 2008-02-01 Tuesday 2 111111 2008-03-02 Wednesday 3 111111 2008-04-03 Thursday 4 111111 2008-03-04 Friday 5 111111 2008-03-05 Saturday 6 111111 2008-03-06 Sunday 7 111111 2008-03-07 Monday . ---------------------------------------------- 8 111111 2008-03-09 Wednesday 9 111111 2008-03-10 Thursday .......................................................................until end of the month 99 222222 2008-02-01 Tuesday 100 222222 2008-03-02 Wednesday 101 222222 2008-04-03 Thursday 102 222222 2008-03-04 Friday . ---------------------------------------------- 104 222222 2008-03-06 Sunday 105 222222 2008-03-07 Monday 106 22222 2008-03-09 Wednesday 108 22222 2008-03-09 Wednesday 109 22222 2008-03-10 Thursday .......................................................................until end of the month
I have a table with a datetime field 'TheDate'. Currently dates are stored as 'mm-dd-yyyy 00:00:00'. Is there a way to get just the month, day and year parts, '01/01/2008' into the field without changing the field data type to varchar? I'm asking because when I do this:
update TheTable set TheDate = substring(convert(varchar,TheDate,101),1,10)
I'm still getting a date in the format 'mm-dd-yyyy 00:00:00' stored in the table. I'd like to be able to lose the time portion, but I'd like to be able to keep the datetime datatype for date math purposes. Can it be done?
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