how would i take the query and show just the current months unique logins
SELECT DISTINCT YEAR(Login_Time) AS Year, MONTH(Login_Time) AS Month, COUNT(*) AS Login_Cnt
FROM Login
WHERE Login_Time >=#10/1/2005#
GROUP BY YEAR(Login_Time), MONTH(Login_Time)
ORDER BY YEAR(Login_Time), MONTH(Login_Time)
Here is my query
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?
How to show the CurrentMonthanddateandyear in my report header in ssrs?
1.How to show the currentdateandMonthyear exmple date format like June 29 2015 on my report header.
2.How to change the report rdl name with the same name like EmpUpdatedreportJune 29 2015.rdl ,it is possible to create and change the rdl file name with the current dateandmonth.
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]
[Code] ....
Down below is my tables.
If I want to add time stamp in log_old_val table.
what value in log_old_val and cone in trigger have to be modified?
thanks
************************************************** *****
--main table
> create table test (manufacturer varchar(500), score int)
> insert into test values('Toyota', 1 )
> insert into test values('Toyota', 2)
>
> --logging table
>
> create table log_old_val(manufacturer varchar(500), score int, operation
> varchar(10))
>
> --trigger to log old value into log_old_val table.
> create trigger tr_man on test
> for update,delete
> as
> if @@rowcount = 0
> return
> if exists (select * from inserted)
> if exists (select * from deleted)
> insert into log_old_val
> select manufacturer,score, 'update' from deleted
>
> if exists (select * from deleted)
> if not exists (select * from inserted)
> insert into log_old_val
> select manufacturer,score, 'delete' from deleted
> go
Need a script to capture current date modify table list(12 AM to 11:59 PM PST) in a database.
View 7 Replies View Relatedl want to run my procedure every month,but it must look at the 1st and the last day of the month.So that when l use my where clause l'll
say where 1st and last day between @startdate and @enddate. How do l do that?
collections_hexagon '2002-05-12','2002-05-12'
Alter Procedure Collections_Hexagon
(
@Startdate datetime,
@Enddate datetime
set @Startdate = 1stdayof currentMonth
set @Startdate = lastdayof currentMonth
)
Hi there everbody,
i am trying to get the last day of the Q + 1 month
the first part i have succeeded (dateadd(ms,-3,DATEADD(qq, DATEDIFF(qq,0,getdate() )+1, 0))) ,but how do i add 1 more month ?
would appreciate any suggestion
regards
Yoav
hi,
If today is the 22/4/08
how can I get the day last month,
eg. 22/3/08
?thank you
Hi,I have a simple table called events, which lists the start and end dates of events. I'm using a calendar control that queries the db for events, but at the moment it does a check for every day by passing in the day. (Very inefficient) What I'd like to do is pass in the current month, and get a set of rows that have an event which is in that month. It sounds easy, but I'm a little confused about what to do if the event starts current month -1 and ends currentmonth +1 I obviously need to return results like these also.
View 4 Replies View RelatedI 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 have table 'Open_Months' as shown below
Emp_IdMonthYear
00FBG 42013
I need current month and previous 2 months, for this i have concatinated month and year column into date formate, the query for this as shown below
SELECT cast(CONVERT(datetime, CONVERT(varchar(4), OM_Year) + RIGHT('0' + CONVERT(varchar(2), OM_Month), 2) + '01', 112) AS Datetime) from Open_Months where OM_Emp_ID = '00FBG'
Date
2013-04-01
From the above date I need to find current month and its previous 2 months.
I have a problem with a date in my sql view, I need the records from the 3 last months but without the current month, if I execute my view right now I have the records from January to april but I just need from January to march, I must have always the 3 previous month but I don't know how I can do it
(dbo.frhkrg.fakdat >= DATEADD(MM, - 3, GETDATE()))
Hi, I'm dabbling in trying to retrieve some info from an SQL database and publish it in Sharepoint data view. I'm trying to create a View in the SQL database that retrieves all records for the current month. So that in July, it automatically shows me July records, and when August ticks over, it automatically displays August's records. I can hard code the dates in to the query no probs (e.g. a statement like ENTRYDATE >= '1/07/2007') but was hoping there is a way to specify current month. So maybe the statement looks like ENTRYDATE >= "CURRENTMONTH".
Thanks in advance!
Hi I want to show only the month names in user selection criteria.
If we create a report parameter of type datetime it will let user to select day too.
I want only month and year to be selelected from dateteim control...
Thanks,
Sandeep
I am trying to get a query that subtracts a month and a day from current date in SQL Server.
Currently I have SELECT DATEADD("MM", -1,GETDATE()) which subtracts one month from the date but I want to subtract a month and a day from date.
I m creating P&L(profit and Loss ) Reports of accounts its consists of 2 levels
in level2:
my procedure display the output of (Actuals in lakhs)
RESPONSIBILITY DEPT CATEGORY CURRENT YTD ACTUALS
SALES Sales Net Sales 444.65
Sales Sales LESS TRD 22.55
SALES NET RETURNS NET RETURNS 422.10 (net sales - TRD)
Finance LESS ED LESS ED 40
Sales Totals Sales 382.10(RETURNS - ED)
(only calculation for above dept only remaining dept values display sum of relvenat accounts ,and if i click the category relvent account codes shown here)
Materials .... ... ..
production ..... ............ ........
i made a procedure for above
create procedure Pl_level2
@fmdate datetime,
@todate datetime,
@category varchar(200)
as
begin
create table #temp1
[code]....
like i m inserted so many accounts in temp tables .
IF (@category IS NULL or @catagory=' ' )
begin
select
responsibility,
dept,
category,
round(Max(Actuals)/100000,1,2) as Actuals from #temp
[code]....
here i can display only current YTD only how to display previous year (13-14) YTD in level1
How to do?
I am having one requirement where I want to show only first Friday of every month of 2014,2015 and 2016 year.
View 9 Replies View RelatedCan we show the data of a report for previous month? The report is supposed to run montly basis. But once it is run, it shows the data only for the previous month.
we can show it if its only for previous day. In this case it is like this;
cdtable.SubmittedDate = GETDATE () - 1
But I dont find function like GETMONTH() or smthing.
Thanks
hi,
Can we show the data of a report for previous month? The report is supposed to run montly basis. But once it is run, it shows the data only for the previous month.
we can show it if its only for previous day. In this case it is like this;
cdtable.SubmittedDate = GETDATE () - 1
But I dont find function like GETMONTH() or smthing.
Thanks
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 need to get previous month data in the table based on current date.
In case of execution of each month, the data for previous month should come with date as between
create table TestDate
(Sno Int,
Name varchar(100),
DateofJoin datetime)
insert into TestDate values (1,'Raj', '2/21/2014')
insert into TestDate values (1,'Britto', '6/12/2014')
insert into TestDate values (1,'Kumar', '5/14/2014')
insert into TestDate values (1,'Selva', '6/27/2014')
insert into TestDate values (1,'Ravi', '5/2/2014')
insert into TestDate values (1,'Gopu', '6/2/2014')
/*
if I execute in month July ( ie: today)
select * from TestDate where dateofjoin between 1-june-2014 and 30-june-2014
Result
5 Ravi 2014-05-02 00:00:00.000
3 Kumar 2014-05-14 00:00:00.000
if I execute in month June
select * from TestDate where dateofjoin between 1-may-2014 and 30-may-2014
Result
6Gopu2014-06-02 00:00:00.000
2Britto2014-06-12 00:00:00.000
4Selva2014-06-27 00:00:00.000
/*
I have the following code block
CREATE TABLE #tbl_1 (event_time DATETIME2, SID INT ,NAME VARCHAR(20) )
INSERT INTO #tbl_1 VALUES ('2015-08-27 13:47:24.123','150','abc')
INSERT INTO #tbl_1 VALUES ('2015-09-27 13:47:24.123','149','acb')
INSERT INTO #tbl_1 VALUES ('2015-10-27 13:47:24.123','148','cba')
CREATE TABLE #tbl_2 (event_time DATETIME2, SID INT ,NAME VARCHAR(20) )
INSERT INTO #tbl_2
SELECT * FROM #tbl_1 where ? SELECT * FROM #tbl_2
My requirement is to insert values into #tbl2 that are in current month which are event_time values '2015-08-27'
How I want to see the below dates via 6 different Select Statements but I am unsure how to get this?
01-May-2015
01-Apr-2015
01-Mar-2015
01-Feb-2015
01-Jan-2015
01-Dec-2014
I have this Select statement
select replace(convert(char(11),getdate(),113),' ','-')
But it is returning the 15-May-2015 and it should be 01-May-2015 for this select statement
I want to show this kind of output
UserID UserName 1 2 3 30
OR
UserID UserName 1 2 3 31
user data saved in db select distinct UserID,Name from Userss Where IsActive=1 and order by UserID and i want to just calculate no of days in month based on year and month name supplied by user. one way i can do it. first i will create a temporary table and in loop add many columns to that table and later dump user data to specific column.
I have a SSRS report using 2008 R2. It prompts the user for the start and end dates. This all works. But now I want the start date parm to default to the first day of the current month and the end date parm to default to the last day of the current month.In the new query window in SQL Server Management Studio, I can run this chunk of code to get the first day of current month:
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
And this code to get the last day of current month:
SELECT DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE()))),
DATEADD(MONTH, 1, GETDATE()))
But I don't know how to do this in SSRS 2008. How can I make my start / end parms to get these values.
I installed sql 2005 a while back. Then I recently found out my file system was fat32 (I don't understand why the hardware people did this...) and I had to convert to NTFS. Naturally the sql service no longer worked so I uninstalled inorder to reinstall now I can't reinstall it I keep getting this message
native_error=5039, msg=[Microsoft][SQL Native Client][SQL Server]MODIFY FILE failed. Specified size is less than current size.
I'll try to post the full log in a new post.
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
Ali
I have 12 month report and I need show volume and difference between current and prev month volume, what is the smart way to do this, do I need to put prev month value onto same row horizontally? I think should be some other smart way, I heard about LEAD function?
This what I think for now, It should be listed per ClientID also, in my example I have single ClientID for simplicity.
I tried to do LEAD but with not success..
/*
IF OBJECT_ID('tempdb..#t') is not null drop table #T;
WITH R(N) AS
(SELECT 1 UNION ALL SELECT N+1 FROM R WHERE N <= 12 )
SELECT N as Rn,
10001 ClientID,
DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())) AS [Month],
[code]....
I am having a problem while converting datetime to varchar with code 106. Here is the code and result I get:
TSQL Code:
SELECT CONVERT(VARCHAR(100), GETDATE(), 106)
Result:
"26 10 2007"
My expected result:
"26 Oct 2007" or "26 October 2007"
Have you encountered this problem before? Is it related to the SQL server setting? Please help and thank you in advance.
I need to calculate “NET_SALES” and “MARGIN_PERCENT” for each month of the current year … the following returns the same values for each month in the list, which are for the current month. Taking out the GROUP BY line works fine for an overall number.
SALES_MONTH, NET_SALES, MARGIN_PERCENT
January, 1246627.69, 24
February, 1246627.69, 24
March, 1246627.69, 24
-------------------------------------------------
DECLARE @NetSales DECIMAL(18,6)
DECLARE @Cost DECIMAL(18,6)
SELECT
@NetSales = sum(IL.MERCHANDISE+IL.TAX)
,@Cost = sum(IL.COST)
FROM INVOICELINE IL
[Code] .....
I'm trying to create a WHERE statement that will calculate values from our current fiscal year to the last complete month.I'm using code that was created for us that does the calculations for our entire fiscal years. I thought I had fixed the WHERE statement to work like we wanted last year, but it appears to be broken now after trying it again in January and February. I'm guessing my WHERE statement only works for March and up, but how to get it to work for every month. Most attempts I'm trying it's just returning very large and inaccurate values.
I included my WHERE statement below of what I originally had that worked last year. The @BeginYear/Month/etc are retrieved from a different table and @Month is just set to MONTH(GETDATE())-1.
WHERE
(YEAR(SA3.DocumentDate)=@BeginYear AND MONTH(SA3.DocumentDate)>=@BeginMonth AND MONTH(SA3.DocumentDate)<=@Month)
OR
(YEAR(SA3.DocumentDate)=@EndYear AND MONTH(SA3.DocumentDate)<=@EndMonth AND MONTH(SA3.DocumentDate)>=@Month)
I can't seem to upload images so here's the link: [URL] ....
I need to get the last value of # Orders week avg in the current context (the highlighted value). The calendar dimension here is a week level one so I don't think I can use LASTNONBLANK. The measure is from the facts_sales table which is linked to a calendar dimension via a date key.