T-SQL (SS2K8) :: How To Show Last Purchased Rate In All Months Of Output
Nov 12, 2014
I've the table like
create table test
(
product varchar(50),
QTY int,
rate float,
Bill_date datetime,
total_val float
)
insert into test values ('Milk','50','500','2014-04-20', '25000')
values ('Milk','20','560','2014-05-20','12000')
values ('Milk','30','450','2014-06-18','13500')
values ('Milk','30','700','2014-06-18','21000')
My expecting output:
Month product Qty Rate T_val
April Milk 50 700 25000
MAY MILK 20 700 12000
Like I've to show the price of latest price in all of months product billed. How to make Query?
View 9 Replies
ADVERTISEMENT
Jun 3, 2014
I am looking for a way to show how I can have a result set that shows a record with one item and the number of records where it was purchased or sold. Below is my sample data.
Use tempdb
go
create table #ItemLedgerEntry
(
ENTRYNO INT NOT NULL
, ITEMNO VARCHAR (50) NOT NULL
, POSTINGDATE DATETIME NOT NULL
, ENTRYTYPE INT NOT NULL
[code]....
I know something like this will give me results but I'd like to run one query.
select itemno, count(*) from #ItemLedgerEntry where entrytype = 0
group by itemno
order by count(*) desc
View 5 Replies
View Related
Mar 27, 2008
Hi All,
I have following problem
please help me to resolve it.
There is table for shipping
as follows:
City
Kg
Rate(rs)
Rajkot
1
25.00
Rajkot
2
30.00
Rajkot
3
42.50
Ahemedabad
1
42.50
Ahemedabad
2
55.00
Ahemedabad
3
67.50
Ahemedabad
4
80.00
Goa
1
90.00
Goa
2
105.00
Goa
3
120.00
Gondal
1
25.00
Gondal
2
30.00
Gondal
3
42.50
Morbi
1
25.00
Morbi
2
30.00
Morbi
3
42.50
Gandhinagar
1
42.50
Gandhinagar
2
55.00
Gandhinagar
3
67.50
Gandhinagar
4
80.00
We need to display
the table in html page using any Asp.Net language.
The result table should be:
City(s)
1
2
3
4
Rajkot,Gondal,Morbi
25.00
30.00
42.50
-
Ahemedabad,Gandhinagar
42.50
55.00
67.50
80.00
Goa
90.00
105.00
120.00
-
Baruch
70.25
80.00
92.00
120.50
Description:
- Display all the cities
together for which all the shipping charges for (1-4) kgs are same. The range
of kgs is not fixed.
Reply as soon as possible.
Thanks
View 3 Replies
View Related
Sep 9, 2014
When sizing products we use predefined size groups that the users can choose any or all of the sizes from. For example if i size group consisted of sizes (6,8,10) they could use all sizes (6,8,10) or just (6,8) or just (10) if required. Similarly, if a group consisted of (S,M,L,XL) they could choose to only buy (S,L). They cannot choose across groups, so would not be able to choose (6,S)
Once the required sizing is determined they then assign size mixes to the sizes to denote how much of the buy will be in that size. So for example if we had 3 sizes: (6,8,10) and they had the associated mixes (25%,25%,50%) that would mean we would buy 25% of size 6 and 50% of size 10. All size mixes must add up to 100% in total.
The users do analysis to determine what sizes they wish to buy and how much of it.
We also have a franchise portion of the business that have some predefined size mixes. They use the same base size groups as above, but the rule is that they can only use sizes that the particular product is being bought in.
So if the assigned franchise mix is S (50%), M (50%) and the main mix was S (100%) then the franchise mix would only be able to then have the S size.
We would then eliminate the sizes from the franchise mix and then to ensure that the franchise mix still adds to 100 we would then pro-rate up the franchise mix to give a new mix. To do this I divide one by the total the remaining size mixes to get a ratio and then multiple the mixes by this factor.
In the case above not be able to use the M size and would only use the S.This would be
-Total of remaining mixes, in this case only size S for simplicity
1 / 0.5 = 2
-multiple original mix by this factor
0.5 * 2 = 1
size S would now be 100% instead of 50%
The issue I'm having is that on occasion some of the totals are adding up to 100.01% because another one of the requirements is that it needs to be 4 decimal places (0.1015 would represent 10.15% in excel)
Here is a shortened version of the code with some test data:
DECLARE @SizeRange TABLE
(
SizeProfileIDINTNOT NULL,
CPCVARCHAR(3)NOT NULL,
SizeNameVARCHAR(5)NOT NULL,
SizeMixDECIMAL(14,4)NOT NULL
[Code] ...
How to get this to round to 4dp and still total 100%?
View 2 Replies
View Related
Aug 26, 2005
Hi,I need what would be similar to a cross tab query in Access.First Column down needs to show all the months, column headings wouldbe the day of the month....1 2 3 4 etc...JanFebMaretchow do i set this up in a stored procedure?any help to get me in the right direction would be greatlyappreciated!!thanks,paul
View 2 Replies
View Related
May 29, 2015
I have a line graph in my report.My report has no parameters on it.What I ideally want it to do is if I run the report today (May) it will only show me the last 12 months so May to May.At the moment it is including April from last year as well, so would want that excluded. If I run it in two months time in July I would want it to only show July to July.The chart has the categories of Finanical Year (in the 2014-2015 format) and the actual month name.
View 3 Replies
View Related
Feb 18, 2008
I´m creating a report in SSRS 2005 where you can filter after month and year. The result is the total sale for that particular month. What I want to do, is adding an extra column to compare with the result in the same month last year. F.eks. say that I choose january 2008 I would like to see the results for january 2007.
I thought of writing an expression to do that, but as that isn't my stronges side I would appreciate some help
View 3 Replies
View Related
Nov 3, 1999
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
View 2 Replies
View Related
Sep 22, 2014
I have the following query:
;WITH CTE_LastOrder (CustomerID, LastOrderDate) As
(
SELECT CustomerID, MAX(PaymentDate) LastOrderDate
FROM [Order]
GROUP By CustomerId
)
SELECT * from dbo.Customer C
JOIN CTE_LastOrder LO ON C.CustomerId = LO.CustomerId
WHERE LO.LastOrderDate > (dateAdd(YEAR,-1, GetDate()))
That should select all customers from the customers table where they haven't had a Paid Order (ascertained via PaymentDate not being null) in the last 12 months - this could also include customers who don't appear in the Order table at all.
The query is giving me results but shows a LastOrderDate for date ranges in 2014..
View 5 Replies
View Related
Jul 29, 2015
My case is I have customers with multiple ordering dates, I'll to run a query to pull only records within 2 months period based on the previous picking records. Ex is below:
create table #tmp_CusttInfo
(patID varchar(20),
enc_date datetime)
go
insert into #tmp_CustInfo
select '111','2015-01-01 09:25:05.000'
[Code] ...
I'd like to get:
CustID OrderDate
111 2015-01-01 09:25:05.000
111 2015-03-01 09:25:05.000
111 2015-05-01 09:25:05.000
222 2015-01-01 09:25:05.000
222 2015-03-01 09:25:05.000
222 2015-05-01 09:25:05.000
Is that possible.
View 6 Replies
View Related
Jul 9, 2014
I am working with SQL 2008
I have the following code which works and outputs the desired results, however I am attempting to figure out a way to get this output by week starting from January 5th to July 6th without having to manually change the date for each week and running again. Ideally I would like to be able to save the results to Excel separated by server by week.
Code:
SELECT AVG(cast(StatValue as float))as 'CPU Utilization', StatName, ServerName as 'Server Name', from dbo.ServerStats s
WHERE s.StatName IN ('MEMPTAGE-TOTAL',
'CPUPEAKP-TOTAL',
'DISKPERC-C',
'DISKPERC-F',
[Code] .....
View 1 Replies
View Related
Sep 29, 2014
I am working in sqlserver 2008 R2 and below is my sample research query. I am trying to get previous 6 months data.
WITH CutomMonths
AS (
SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0) startdate
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N + 1, 0) enddate
FROM (
VALUES (1)
[Code] ...
Current output what i am getting:
Expected Output:
I found why the April month i didn't get the $20 because the startdate of my perks CTE '2014-04-03'. If it is '2014-04-01' then i will get the expected output.
But i should not change the the date on perks. How to neglect this date issue and consider the month instead to get the expected output.
View 6 Replies
View Related
Mar 16, 2015
I've SSRS sales report to which I need to pass the dates for previous month's start date and end date which I am able to pass using below code. However, since the sales report has data from the past year(2014) I need to pass the dates for last year as well. The below code gives StartDate1 as 2015-02-01 and EndDate1 as 2015-02-28. I need to get the dates for past year like 2014-02-01 as StartDate2 and 2014-02-28 as EndDate2
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS StartDate1,
DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS EndDate1
View 1 Replies
View Related
Mar 10, 2014
My Table struct
create table cust
(
cust_id int ,
city varchar(20),
pincode int,
sales int,
latitude float,
longitude float
[code]....
like i wanna display each pincodes how to make a code?
View 6 Replies
View Related
Jun 28, 2014
I have a table history of Employee data.
id | EmpNo | EmpName | MobileNo | Email | EmpSSS | UpdateDate | UpdateUser
I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.
Result must be:
EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to
View 4 Replies
View Related
Nov 5, 2014
Detail:
create table detail
(
grn_no varchar(55),
po_no varchar(50),
vendor_no numeric(10,2),
Formul_id varchar(50),
Adjust_code varchar(50),
adjus_value float
)
insert into detail values
('Grn/0001/14-15','po/00011/14-15/','5000','ED12','ED12','500')
values ('Grn/0001/14-15','po/00011/14-15/','5000','CST','CST12','50')
values ('Grn/0001/14-15','po/00011/14-15/','5000','CES6','CES6','5')
values ('Grn/0001/14-15','po/00011/14-15/','5000','VAT','VAT','0')
values ('Grn/0001/14-15','po/00011/14-15/','5000','HCES2','HCES2','2.50')
i wanna display ED ,CESS ,HCESS,VAT in separate columns in output How to that?
View 4 Replies
View Related
Oct 7, 2014
I have a sql query that gets the count of exams held in each month.
Below is the code that I have used.
select Examid, count(*) as CumCount
from [dbo].[Exams] where ExamCategory in ('Major','Critical') and Month(EXOCCRDATE) = Month(getdate())
and Year(EXOCCRDATE) = Year(getdate()) group by Examid
The code works good when we have data for the current month. When we dont have any exams for the current month, the code outputs empty values. I want the code to be altered so that when there is no value returned in the output, i want a default value shown in the output.
I have attached the sample data that I am using. In the data we dont have dates for the month of October. So when I run the code it will display empty output. So what I need is I need a text like 'No Data' to be shown when no value is returned by the query.
I tried using case but it does not work.
View 6 Replies
View Related
Aug 7, 2014
The data we are getting from our shipping company has the customer name and customer number attached.
so we could have.. declare @String varchar(25) = 'asdf - 10'
but we also have.. declare @String varchar(25) = 'asdf - jeik - 10'
So how do I strip off the " - 10", when the ending number is not the same number of char's (1,11,111,1111, ect)
I need to match this up with our customer table... on Customer Name.
View 9 Replies
View Related
Feb 22, 2015
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?
View 1 Replies
View Related
Mar 10, 2015
I am creating a report on an asp.net webpage to show how many times a coupon has been used from a set list of items.There are two tables I am using for this query.Table 1 has the list of items or coupons. Table 2 has a column for the item id from Table 1 and a value.
Example:
Table 1
id Name
1 Coupon1
2 Coupon2
3 Coupon3
[code]...
My current query will only show coupon1 and coupon2 and the number and value. It will not show coupon2 since it hasn't been used. I would still like to show coupon2 with a number of zero and a value of zero.
Current query is:
SELECT Table1.Name, COUNT(Table2.ID) AS CNum, SUM(Table2.Value) AS CValue FROM Table2 JOIN Table1 ON Table2.ID = Table1.ID GROUP BY Table1.Name
View 5 Replies
View Related
Nov 28, 2014
Query that show me a Products Tables and its models, 1 product could have 2 or more models
Example Table Products
id Name
1 Product 1
2 Product 2
3 Product 3
Example Table Models
idmodel idproduct model
1 1 Model 1
2 1 Model 2
3 1 Model 3
4 2 Model 1
5 2 Model 2
6 3 Model 1
And I want to show:
Product Model
Product 1 Model 1
Model 2
Model 3
Product 2 Model 1
Model 2
Product 3 Model 1
How can I query to show that?
View 5 Replies
View Related
Mar 17, 2015
If I skip those objects that were deleted, is there a query that I can run off Master/MSDB that will show all database objects and when they were last modified, or create date if it has not been modified?
View 7 Replies
View Related
Mar 10, 2015
selecting table data in hierarchical XML .
Here is the sample table DDL and data
Declare @continents Table
(
id int identity (1,1)
,continent_id int
,continent_Name varchar(100)
,continent_surface_area varchar(100)
,country_id int
[code]....
View 8 Replies
View Related
Sep 14, 2007
I explicitly set one column to have text qualifiers in a flat file connection mgr and specified to use double quotes as the qualifier, yet in the output file, the column is not qualified. What did I leave out ?
View 2 Replies
View Related
Nov 6, 2014
in my table i ve the column of item code which contains '1000' ,'2000' ,'3000' series i jus wanna display the output of item codes '1000','2000'series and some of ('3000019','3000020','3000077','3000078').
i tried in my join query
these code left(itemcode,4) in ('1000','2000') or itemcode in ('3000019','3000020','3000077','3000078')
its taking so much of time how t o solve ?
View 1 Replies
View Related
Jun 3, 2015
I have the following table:
SELECT 'A' as Item, '1' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.1' as Version, 1 as Counter, '01-02-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.2' as Version, 2 as Counter, '01-03-2011' as CreatedDate UNION ALL
SELECT 'B' as Item, '1.2' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
[Code] .....
I want to write a script where if a user enters the version number, then the output should show all the upward and downward nodes..e.g. if a user selects '1.2' version then following should be the output
View 3 Replies
View Related
May 1, 2015
I have a T-SQL query that outputs exactly as I wish when run via Management Studio. I now want to automate this query to run monthly via a SQL Agent Job.
I have got this to run but the resulting file is left with a header row which shows "Job 'MyJobName' : Step 1, 'My QUERY' : Began Executing 2015-05-01 10:52:01" and an extra blank row before my data (and column headers).
Is there any way of stopping these extra rows from being created using T-SQL in my query?
View 2 Replies
View Related
Oct 15, 2014
Note: @procName,@strAccount,@intHospital,@patType are passed to this procedure from another procedure
Note: The @procname procedure also takes the above parameters and @outDupCheck as output parameter
DECLARE @sqlStr NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @parmINAccount VARCHAR(30),@parmINHospId int , @ParmINpatType varchar(1)
DECLARE @parmRET1 int
SET @parmINAccount = @strAccount
SET @parmINHospId = @intHospital
SET @ParmINpatType = @patType
SET @sqlStr = N'Exec ' + @procName + ' @strAccount,@intHospital,@patType, @outDupCheck OUTPUT'
SET @ParmDefinition=N'@strAccount varchar(50),@intHospital int,@patType varchar(1), @outDupCheck int OUTPUT';
EXECUTE sp_executesql @sqlStr, @ParmDefinition, @strAccount = @parmINAccount, @intHospital=@parmINHospId,@patType=@ParmINpatType,@outDupCheck = @parmRET1
SELECT @parmRET1
--The parameter @parmRET1 returns NULL instead of 1 .
The @procName returns value 1 correctly if I run it separately ( outside of the dynamic sql)
Not sure what is wrong here...
View 2 Replies
View Related
Mar 27, 2008
I have the following table
FeedBack Type Date
test2 positive 03/15/08
tes3 negative 03/01/08
.. ....
in my page i need to select the number of negative/positive comments within the last
1 month, 6 months, 12 months
How can I accomplish that?
thanks
View 5 Replies
View Related
May 17, 2006
We started out with the trial version of sql2005 and we later purchased the full standard version I belive, I just realized our trial is going to expire which we have data we are using on it. Is it possible to enter our liscense number in the trial to continue using or do I have to detach all of the databases and install the new sql version and then reattach all of the databases and setup any maintence plans again.
View 5 Replies
View Related
Mar 25, 2014
Get output of SQL Procedure and assign it to a variable used in WHERE Clause
Later I want to use this variable in my code in the WHERE Clause
Declare @ProjectNo nvarchar(10)
--Now I want to assign it to output of a storedprocedure which returns only 1 value and use it in the below SELECT query.
SELECT ID from TABLEA where Project in (@ProjectNo)
How to do it. How to assign @ProjectNo to output of storedProcedure called 'GetProjNumber'
View 1 Replies
View Related
Jun 6, 2014
See DDL and sample data below. What would be the easiest way to get the desired output without hard coding the values? Data in both tables may change over time.
DECLARE @num AS TABLE (
Id INT IDENTITY(1, 1)
,Price MONEY
)
DECLARE @range AS TABLE (
Id INT IDENTITY(1, 1)
,Rng MONEY
[code]....
View 7 Replies
View Related
Jul 2, 2007
How to find out number of SQL Server seats purchased?
View 2 Replies
View Related
