HAPPY NEW YEAR Everyone
Dec 31, 2003Just got cut loose...
have a safe and happy new year!
anyone seen my salt shaker?
damn what a time to misplace it....
ADVERTISEMENT
Insert Value Not Happy, Why?
Aug 8, 2007How am I doing so far? I am trying to Insert the user_id if it does not allready exist in the table. But it does not like the value I am giving it in the INSERT statement.
What can I do?
ALTER PROCEDURE dbo.ap_Update_Usr_Type_Data
-- define parameters
--@passUserId int
AS
--Set @passUserId = UserId
SELECT [user_id] As UserId
FROM CC_host.dbo.usr_table As t1
Where(email LIKE N'%@%' And email LIKE N'%usa.com') Order By UserId
If Not Exists (Select [user_id] As UserId2 From Usr_Type_Data WHERE UserId2 = UserId)
INSERT INTO Usr_Type_Data (user_id, CustomerTypeId) VALUES(t1.UserId, 7)
GO
The error I am getting in Query Analyzer (SQL SERVER 2000) is:
The name 'UserId' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
Trigger Happy ... Not
Aug 29, 2007Hello Everyone, I am still learning Sql and need some help with a query. On the master table, I have an audit log. I have a trigger set up to run after an update. I only want to perform an update if the value is not true or one record updated will result on 1800 records in you audit log. I tried to disable and enable the trigger from within the trigger unsuccessfully. So now I am trying an If statement, in a trigger Maybe this is where you can help ?
I get the following error
Msg 512, Level 16, State 1, Line 1Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Extract from Trigger.If (Select Discprency from Master_Tablewhere ((Pro1 <> Pro2 and Pro1 <> Pro3) or (Pro2 <> Pro1 and Pro2 <> Pro3) or (Pro3 <> Pro1 and Pro3 <> Pro2) or(Pro1 = Pro2 and Pro1 <> Pro3 and Pro2 <> Pro3) or(Pro1 = Pro3 and Pro1 <> Pro2 and Pro2 <> Pro3) or(Pro2 = Pro3 and Pro1 <> Pro2 and Pro3 <> Pro1))) = 0Begin Update Master_Tableset Discprency = '1'where Discprency = 0EndElseBeginUpdate Master_Tableset Discprency = '0'where Discprency = 1End
Thanks for your time.
Trigger Happy
Mar 29, 2004Hi Folks,
Just me looking for some advice again on my complicated logon / registration process with DNN.
I ask my users to enter their league membership number as a password - this is then checked in a standalone table of data imported from FoxPro to test whether the membership no. i.e. password is current or not .... this all works fine. I do have a bit of a problem with the initial registration on the site however ...
All membership numbers acutally consist of a league division followed by the membership number so e.g. the member number 6789767 actually consists of two pieces of information - the first character is the league division and 789767 is actually the foxpro member number. So what I do is strip the first character away on sign in and registration when I am testing to see whether the user is a current league member e.g. ...
Dim MemberID As String = objUser.IsMember(Server.HtmlEncode(Mid(txtpassword.Text,2,10)))
This allows me to grab the real membership number out of the password and also helps to stop password guessing as no one will realise the first character is being stripped away. Fine except with the AddUser code in register.ascx.vb .... I don't know why but when I try to strip away the first character in a similar way in this line
Dim UserId As Integer
UserId = objUser.AddUser(PortalId, Server.HtmlEncode(txtFirstName.Text), Server.HtmlEncode(txtLastName.Text), Server.HtmlEncode(Address1.Unit), Server.HtmlEncode(Address1.Street), Server.HtmlEncode(Address1.City), Server.HtmlEncode(Address1.Region), Server.HtmlEncode(Address1.Postal), Server.HtmlEncode(Address1.Country), Server.HtmlEncode(Address1.Telephone), Server.HtmlEncode(txtEmail.Text), Server.HtmlEncode(txtUsername.Text), objSecurity.Encrypt(_portalSettings.HostSettings("EncryptionKey"), Server.HtmlEncode(Mid(txtPassword.Text,2,10))), IIf(_portalSettings.UserRegistration = 1, CStr(False), CStr(True)), UserId)
well it does the right thing and only adds the password minus the first character to the users table however when the user is pointed back at desktopdefault the users name is not displayed, the additional tabs available to registered users do not appear however the logoff link is active ... strange why this should be so but I can't work out what the problem is. So I was thinking that another way to approach this would be a trigger on the users table so that when a new record is inserted the trigger runs and strips the first character off the password before the password is committed to the table.
Can anyone show me a simple trigger that will do this / comment on why the problem with AddUser occurs .... not au fait with triggers but a trigger could make me happy in this instance I think.
Look forward to a reply
Laura D
Who Is Happy With Their Backups?
Jan 29, 2001If you are please let me know what software you are using. We currently are using Legato to back up our SQL Servers and it has
proven to be a real nightmare. Backing up to disk is not a solution for me since we have too many servers(16) and medium to large
Dbs. I would love any ones input into your current backup solution. Are you happy with backup time and restore time and is it
reliable?
Happy New Years
Dec 30, 2004I just wanted to wish everyone a Happy New Years and I wanted to thank all submitters to this forum for breaking up my code monkey workday and cutting into my productivity and I have found many of these discussions helpful in my preperation for my long procrastinated MCDBA exams.
Get hammered and do not drive.
Trigger-Happy
Jul 20, 2005I am trying to create a very minimal auditing system for a series ofdatabases.I am in the process of writing Update triggers for 5 Tablse. I willwrite a trigger for each table-the trigger's function will be toINSERT a row into my MasterChanges table everytime ANY data is changedin each of the 5 tables.I have set up MasterChanges to capture the following:what Table the change was made inwhat Field the change was made inwhat the Field was changed towhen this change occured.I have used the Columns_Update() function to enable the trigger tosurvey all 22 fields in the first table (my first attempt at this). Ican hardcode in the table's name, as there will be a separate triggerfor each table.The problem is how to identify the NAME of the COLUMN that was updatedand have the trigger write that NAME into MasterChanges, as well asthe new value of that column.My first thought is a very unacceptable hardcoding of multiple IFSTATEMENTS for each of the 22 fields.ie:IF(colA) then...elseif(colB) then...elseif(colC) then...etc.There must be a better way to do itI assume this would be done using Columns_Update() in some way, butcannot figure it out.Can anyone help?Thanks,Trevor Fairchild
View 2 Replies View RelatedHappy New Years Dbforums
Dec 30, 2005remember if you drink too much, get a taxi and charge it to your company.
View 4 Replies View RelatedFiscal Year Totals - Calculating Sales By Month And Current Year
Sep 18, 2013I 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.
Reporting Services :: Calculate Sales Percentage Difference Between Selected Year And Previous Year In A Matrix
Mar 27, 2015I'm trying to generate a report using matrix like this
Month
Product PreviousYearSalesAmount SelectedYearSalesAmount %SalesDifference
I can populate year sales amount, but i cant calculate the percentage.
Note: Month and Year are passed as parameters.
To Send The Date Format If The User Has Specified Only Month And Year, Or Only The Year
Aug 30, 2004I 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:
CmdInsert.Parameters("@Date").Value = day.SelectedItem.Value + "/" + month.SelectedItem.Value + "/" + year.Text()
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?
Thank you,
Cesar
SQL Server 2012 :: How To Get This Year And Last Year Totals In Two Separate Columns
Jun 19, 2014I 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'
[Code] ....
Select Month/year When Range Spans Year
Feb 25, 2004I'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?
Any suggestions on the best way to do this?
How To Find Year And Past Year Compare In Business
May 5, 2014We 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,
[Code] ....
Compare Given Period In Current Year / Previous Year
Mar 15, 2006HiI 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
View 3 Replies View RelatedHow To Do A Year-to-date SQL Query Where Year Commences In August?
Jul 20, 2005Does 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
View 2 Replies View RelatedPower Pivot :: DAX Ranking Events Year Over Year?
Jul 14, 2015I have a table of data that has a format similar to the following:
EventID | Event Date
--------------------
1 | 1/1/2014
2 | 2/8/2014
3 | 10/1/2014
4 | 2/5/2014
5 | 4/1/2014
6 | 9/1/2014
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.
Event Date | Year | Rank
------------------------
1/1/2014 | 2014 | 1
2/8/2014 | 2014 | 2
10/1/2014 | 2014 | 3
2/5/2015 | 2015 | 1
4/1/2015 | 2015 | 2
9/1/2015 | 2015 | 3
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)
How To Add The Total Balances Of Previous Year Into The Next Year?
Apr 13, 2006I'm working with a table called SALFLDGAMS where it has two columns that I need to work with. One of the columns is Period which has years from 2000 to 2005 and the other column is called amount which has the balance for that year. Let me explain in a little more detail. There are account codes associated with the years as well so there will be many places where for example the year 2000 will show up with a given value. What I'm trying to do is to create a view which has a BeginBalance column which adds lets say all the values for 2000 and sticks them to 2001. So what I'm saying is all the values from the previous year I want them in the current year. All in one column. Thanks for the help guys.
View 8 Replies View RelatedParameter Year And Previous Year (Beginner)
Dec 14, 2006I have one matrix that shows the CrashCount (measure) by month. Looks like this:
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Dec
25 90 100 55 52 55 22 55 22 35 65
The user selects a Year as a parameter. I want to put another matrix in that displays the previous year, just as the first year is displayed. How do i edit the second matrix? Do i put the parameter as
=(Parameters!CrashStatisticalYear.Value)-1 or is there some other way this can be done. Without having the user put 2 years. I just want them to pick one. And the previous year shows up in the matrix below this one. Can anyone help me with this...what should i do?
Sales For Current Year To Previous Year
Oct 20, 2007Guys,
I wanted to find the ratio: (sales made for current year 2007 - sales made for previous year 2006)/sales made for previous year 2006.
so, the result should be something like this:
Year: Sales: %change in sales:
2005 100 10%
2006 200 20%
2007 300 30%
How do I write a query for this...??? so that i can plot this in a chart in SSRS.
somebody help me.
Fiscal Year Date Help??? I Need The First Day Of The Year To Be 01/27.
Feb 4, 2008
Greetings All,
I have a SQL question that maybe someone out there can help me with. Our fiscal year starts on 01/27. I want to write a query that I can pass a date to and it will return the week number (DATEPART("ww", someDate)) of the year using the Fiscal Year Start Date as the base. Datepart works great except it figures the first day of the year as 1/1. Does anyone know how I can make it work with a first day of the year equal to the fiscal year date 01/27. Any help would be appreciated.
TIA,
~ck
SQL Server 2012 :: Compare Current Year Data With Last Year Data - Day Adjusted
May 25, 2015Our business get orders through the week with the weekends (Fri & Sat) orders being higher than weekdays. Im wanting to graph this years data with last years and possible the years before but to compare days in such a way that the all the weekdays line up. so comparing 2015 week 1 with 2014 week 1 but with 03/01/2015 (Sat) lining up with 04/01/2014 (Sat) etc.
I'm looking for alternatives to adding or removing days from the dates to solve this issue, i have a date dimension table for the past 5 years that i can use to compare calendar week 201401 with calendar week 201501 but I am finding it a bit inflexable.
How Can I Retrieving Records By Past One Week,past Month, Past Quarter Of Year And Then Half Year.
Apr 30, 2007Hi every one,
I have a database table and currently users may retrieve records for a specified date range by providing the start and end dates and then records between those dates provided are retrieved. For example if users wanted to view all records entered in april, they would have to select 04/01/2007 as the start date and then 04/30/2007 as the end date. The records for april would then be displayed in a gridview.
How can configure my sql query such that instead the user selectes a month from a dropdownlist of 12 months. I would love a user to just select the desired month from a list instead of selecting start and end dates. Eg if they are intrested in a report for june, then they should just select june from the list instead of specifying the start and stop dates. HOW can i achieve this.
Totals For This Year AND Last Year
Feb 25, 2008I want to write a query which returns count values sold of each product for last three months... which I can write...the hard bit is, I also want to return what the count value was last year for this month
i.e.
Feb Jan Dec
2007 2006 2007 2006 2007 2006
Soap 2 9 1 2 9 9
Kitchen Towel 1 34 3 34 23 45
Washing Up Liquid 33 3 5 6 7 33
How can I write a query which will show count totals for both years??????
Thanks
Grouping My Month/year Desending By Month/year
Dec 11, 2006i 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 )
Sql For Financial Reporting Periods This Month, Last Month, This Quarter, Last Quarter, This Year, Last Year
Oct 26, 2006Does 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?
View 8 Replies View RelatedLeap Year Dates Causing Error When You Pull Dates For Non Leap Year
Feb 28, 2008
I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error
Here is what I'm trying to generate
InnCodeID
Quarterly
Monthly
Days
Period
Year
BARmRev
AARmRev
PYRmRev
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
ADDIS
Q1
Jan
1
1
2008
3462.14
5107.65
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
Here is the error that I'm getting:
Code Snippet
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
(4834 row(s) affected)
Here is my Transact-SQL Syntax (summarized because I Couldn't post it):
SELECT
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'
AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'
THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT Trans1.TR_Amount
FROM dbo.Trans Trans1
WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)
AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)
AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,
--AA Script Here AS AARmRev,
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)
= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT SUM(Trans1.TR_Amount)
FROM dbo.Trans Trans1
WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND
trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))
+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND
trans1.datatype = '0') ELSE NULL END) * - 1 AS PYRmRev
FROM dbo.EntityDef INNER JOIN
dbo.Trans ON dbo.EntityDef.ED_Property_ID = dbo.Trans.TR_Entity INNER JOIN
dbo.CurrentDate INNER JOIN
dbo.DimTime ON YEAR(dbo.DimTime.TimeDate) = YEAR(dbo.CurrentDate.CurrDate) ON dbo.Trans.TR_Date = dbo.DimTime.TimeDate
WHERE (dbo.EntityDef.ED_Property_ID = 'ADDIS')
ORDER BY dbo.EntityDef.ED_Property_ID, dbo.DimTime.TimeDate
I appreciate all your help
Thanks
Getting Date In Day/month/year Instead Of Month/day/year
Feb 2, 2004I am trying to get my db to return a date in the format day/month/year but its returning the american version month/day/year.
I'm using a DatePart function that converts my date:
CREATE FUNCTION dbo.DatePart
( @fDate datetime )
RETURNS varchar(10)
AS
BEGIN
RETURN ( CONVERT(varchar(10),@fDate,101) )
END
This returns te american version, can anyone help me to get this to convert the UK way.
Thanks
Last Year
May 12, 2008Hello
I have a SQL server model with Bill and BillLine, where the second references the first
Bill(CodBill, Date, ...)
BillLine(CodBillLine,CodBill,Description, ..., Total)
What I want is a list of sum(total) grouped by month and description and also a list of last year sum(total).
Example:
Month | Description | Total this year |Total last year
1 | Water | 5.00 | 3.00
1 | Blood | 0.00 | 4.50
1 |Milk | 2.00 | 0.00
2 |Blood | 1.00 | 3.50
2 |Milk | 7.80 | 2.30
3 |... |... |...
....
12 |... |... |...
This is easy to do without the 4 th column using GROUP BY, but this column complicates all the thing. I was trying to do things with the PIVOT operator, but it was a useless effort.
Please help.
Max Of Sum By Year
Oct 9, 2013I'm new to sql. I have the below data. I need to produce a report that shows customer and total sales who had the max sales by year.
Order ID Cust ID Year Sales
O1 C1 2000 100
O2 C1 2000 150
O1 C2 2000 50
O1 C1 2001 150
O2 C3 2001 200
Report:
Cust ID Year Sales
C1 2000 250
C3 2001 200
Max By Year
Feb 21, 2008
I am trying to total a sub table by year. I have a table with multiple entries per position with compensation for the positions these are tied to employees and that is how I have to reference them the employee fills a postion and is compensated $xxx.00 per year. if the employee in the position changes then the compensation is displayed correctly otherwise it only currently shows the Highest amount payed on all lines. Although, it does display multiple lines for the employee if they have held the position for multiple years.
Please ask if you need clarification or example data.
jag
How Do I Get The Last Year And This Year
Oct 26, 2007
I want to display the values for this year and last year, my stored proc shows just this year. How can i get last year as well. Maybe with a range of DateDimension_Year=LastYear-This year
here's my stored proc:
Code Block
SELECT
C.[Date_Dimension_Year]
,C.[Date_Dimension_Period]
,C.[Date_Dimension_Fiscal_Week]
,[Consolidated_Sales_Tables_Id]
,C.[Customer_Code]
,C.[Report_Level_Id]
,[Sales_Amount]
, SUM(Sales_Amount)"Gross Turnover"
,[Sales_Quantity]
,TERRITORY_CODE
,Territory.Name as Territory_Name
FROM [RC_DWDB_INSTANCE_1].[dbo].[tbl_Customer_Sales_Summary_Fiscal_Week]AS C
INNER JOIN [RC_DWDB_INSTANCE_1].[dbo].TBL_TERRITORY_IN_CUSTOMER AS T
ON T.Customer_Code=C.Customer_Code
INNER JOIN RC_STAT.DBO.QRY_REPORT_LEVEL_BRAND AS B
ON B.REPORT_LEVEL_ID=C.REPORT_LEVEL_ID
INNER JOIN [RC_DWDB_INSTANCE_1].[dbo].[Territory] as Territory
ON Territory.Code=T.Territory_code
INNER JOIN
(
SELECT distinct [Date_Dimension_Year]
,[Date_Dimension_Period]
FROM [RC_DWDB_INSTANCE_1].[dbo].[Tbl_Date_Dimension]
WHERE [Date_Dimension_Date] = Convert(varchar,GETDATE() - 4,110)
) Current_fiscal
ON (C.[Date_Dimension_Year] = current_Fiscal.[Date_Dimension_Year]
AND C.[Date_Dimension_Period] = Current_fiscal.[Date_Dimension_Period])
WHERE [Brand_Id]=5
AND [Consolidated_Sales_Tables_Id]IN (15,14,18)
Group by
C.[Date_Dimension_Year]
,C.[Date_Dimension_Period]
,C.[Date_Dimension_Fiscal_Week]
,[Consolidated_Sales_Tables_Id]
,C.[Customer_Code]
,C.[Report_Level_Id]
,[Sales_Amount]
,[Sales_Quantity]
,TERRITORY_CODE
,Territory.Name
How To Get Months And Year
Nov 20, 2006Hi All,
I provided the client with the dropdownlist which lists all the months (january - december). This input will be used to filter in the invoiceDT.
How can I write the query where clause to compare the invoiceDT (mm/dd/yyyy) into this input which is only number 1 through 12?
TIA