Transact SQL :: Order Of XML Nodes In Xpath Query
Nov 20, 2015
I have an XML field with some data that looks about like this:
<steps>
<step userName="abc"></step>
<step userName="abc"></step>
<step userName="saf2"></step>
<step userName="abc"></step>
</steps>
I have a query something like this:
SELECT XmlField.value('(//step/@userName)[1]', 'VARCHAR(100)') AS userName
FROM theTable
I would like to have some kind of indicator in my TSQL results of the number of each <step> node. For instance, saf2 would be a 3. I think this is similar to a ROW_NUMBER() kind of function in normal table data.
Is there a good way to get a 1, 2, 3, and 4 value for this sample data?
View 2 Replies
ADVERTISEMENT
May 28, 2015
I have a column with XML data stored in it. I need to update that column several times with new values for different nodes. I've written a CLR function to update the XML quickly but the update is always based on the initial value of the xmlData column. I was hoping that the subsequent updates would be based on the new data from the prior update (each xmlTable has several newData rows). Do I have to make this a table valued function and use cross apply?
UPDATE xmlTable
SET xmlTable.xmlData = Underwriting.UpdateByDynamicValue(xmlTable.xmlData,newData.NodeID,newData.NewValue)
FROM xmlTable
JOIN newData
ON xmlTable.ID = newData.fkXmlTableID
View 2 Replies
View Related
Aug 8, 2014
I have a situation that can be summarized like this :
DECLARE @XML XML = '<X C="0" I="1"><E D="CODE1" A="0" /><E D="CODE2" A="0.03" /><E D="CODE2" A="0.04" /></X>'
SELECT
E.value('@D','varchar(MAX)') AS Code,
E.value('@A','varchar(MAX)') AS Rate
FROM @XML.nodes('./X/E') AS T(E)
The order of appearance is of capital importance as the rates do apply on the previous ones. Is there a way to generate a sequential ID based on the order of appearance in the XML string ? In this case, I want :
1 CODE1 0
2 CODE2 0.03
3 CODE2 0.04
I though of using a temp table with Identity column... But it's not the best way for my need. I have multiple lines each with a XML String. The ROW_NUMBER() windowed function needs a ORDER BY clause that I can't provide.
View 3 Replies
View Related
Nov 30, 2006
for example,xml like
'<name>test</name><age>10</age>'
select ? from @xml.nodes('/*')
how to get the node tag name like name and age?
thanks
View 4 Replies
View Related
Dec 9, 2014
My xml is in below format:
DECLARE @MyXML XML
SET @MyXML = '<RS>
<R id="6330">
<WF id="71445">
<WFS id="12790"> <fname>John12790</fname> </WFS>
[Code] ....
I need to insert into table with TSQL query:
My output should be similar below:
John12790 12790 71445 6330
Eric 12791 12790 71445 6330
John12793 12793 71446 6331
Eric12794 12794 71446 6331
otherwise i need to write huge c# code to execute such thing..
View 2 Replies
View Related
Jun 9, 2014
I'm trying to go deeper into SQL Server's XML support. When looking at a recent forum post, I got curious to see if I could achieve the same result with XPath and FLOWR. Here's the data I'm working with:
create table #data (myXMLData xml)
insert into #data(myXMLData) values (
'<?xml version="1.0" encoding="utf-8"?>
<order>
<Product Index="e1e2c499-f9...etc...." ProductID="12" >
<Attribute Name="Paper...etc..." />
[Code] ....
Here's the working XPath query and results:
select data.mynodes.value('@Name', 'varchar(50)') Name
from #data
cross apply myXMLData.nodes('/order/Product/Drops/Drop[@Number="1"]/Area') data(mynodes)
Results:
Name
43001PBOX
[Code] .....
Here's my first attempt at a FLWOR expression. Note that doesn't produce the same results.
select myXmlData.query('
for $drop in /order/Product/Drops/Drop
for $Name in $drop/Area/@Name
where $drop/@Number=1
return data($Name)
')
from #data
Results:
43001PBOX 43001R001 43001R002 43023PBOX 43023R001 43023R002 43031PBOX 43031R001
How to build a FLOWR-based query that produces the same result as the pure XPath one?
View 1 Replies
View Related
Oct 22, 2007
Hi,
I'm trying to use the XML Task XPath operation for the first time but having some problems. I'm using the following settings:
Source type: File connection
SaveOperationResult: True (file destination)
SecondOperandType: Direct Input
PutResultInOneNode: False
XPathOperation: Values
The XML file I'm testing has the following stucture:
<?xml version="1.0" standalone="yes"?>
<BackupDataSet xmlns="http://tempuri.org/BackupDataSet.xsd">
<Device>
<DeviceID>6356452a-e7a6-42e1-895a-d4ade62210d5</DeviceID>
<UserID>1533c44f-c263-db11-9db3-000e9bd9f98d</UserID>
</Device>
</BackupDataSet>
When I set the SecondOperand to /* the output is a concatenated text string of DeviceID and UserID values. I'm trying to get just the DeviceID but perhaps my understanding of XPath syntax is wrong. I've tried setting the SecondOperand to the following:
//Device/DeviceID
/BackupDataSet/Device/DeviceID
//DeviceID
All of these return no results. What am I doing wrong?
Regards,
Greg
View 3 Replies
View Related
Jul 14, 2015
I am trying to use a XML task in SSIS to get a session ID value from this XML:
<?xml version="1.0" encoding="utf-16"?>
<AuthenticationResult xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<authenticated xmlns="[link]">true</authenticated>
<sessionId xmlns="[link]">0000-0000000-00000000000-000000</sessionId>
[Code] ....
However I cant get the task to return the Session ID
Task setup:
Operation Type: XPATH
SourceType: File connection
Source: testfile.xml
SaveOperationResult: True
DestinationType: Variable
Destination: User::SessionID
OverwriteDestination: True
SecondOperandType: Direct input
SecondOperand: //sessionId
Namespaces: (Collection)
PutResultsInOneNode: False
XPathOperation: Values
No namespaces are specified.
View 8 Replies
View Related
Oct 9, 2015
I have a table where on all business days per month, for different trades, a price is uploaded, so a simplified extract:
Trade PublishingDate Price
10 2Jan15 100
:
10 31Jan15 150
20 2Jan15 75
:
20 31Jan15 110
:
I'm trying to write a view that will:
a) Give me the average for each trade, per month.
b) Give me the average of the last 7 days, for each trade, per month.
The first one is easy:
SELECT TradeWSRate.Trade,
CASE MONTH(TradeWSRate.PublishedDate) WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
[code]...
Column "TradeWSRate.PublishedDate" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.How can I change the above select to work? Ideally I want to do this all in one view, and not use functions.
View 5 Replies
View Related
Apr 21, 2015
I am using below query i want to order it by month so that data is displayed like
Select (count(I.Colour)) as Litres,
DATEName(MONTH,ShipDate) as [Loaded Date] from InkData I
group by DATEName(MONTH,ShipDate)
order by DATEName(MONTH,ShipDate)
Right now it is not ordering by month, it is ordering alphabetically.I want this to be ordered like
Jan
Feb
March
View 6 Replies
View Related
Sep 2, 2015
I have two tables, one is called (questions), the second one (answers).
questions columns are (ID,questionTitle)
answers columns are (ID,questionID,answer, answerDate)
I use this query to load data: "SELECT q.questionTitle,COUNT (a.ID),a.answerDate FROM questions q LEFT JOIN answers a ON q.ID=a.questionID" the query is easy, but my problem which I can't solve is how can I fetch the data ordered by the column answerDate, I mean I want the first record to be the one which has the most recent answer and so on.
View 12 Replies
View Related
Sep 24, 2015
I have the following query in a user-defined function. It accepts a single string parameter, but for the sake of simplicity, I have substituted actual strings in the query. It basically checks the passed string. If it ends with "Id", it strips off the "Id" and returns the resulting string. If it ends with "Id" followed by a digit, it strips that off and returns the string.
SELECT CASE
WHEN LEN('IncidentViolationId') > 2 AND RIGHT('IncidentViolationId', 2) = 'Id' THEN LEFT('IncidentViolationId', LEN('IncidentViolationId') - 2)
WHEN PATINDEX('%Id[0-9]', 'IncidentViolationId') > 1 THEN LEFT('IncidentViolationId', PATINDEX('%Id[0-9]', 'IncidentViolationId') - 1)
ELSE 'IncidentViolationId' END
This code has worked flawlessly for quite some time and all of a sudden I get "Invalid length parameter passed to left function". I understand why LEFT() would normally fail if I passed it a -1 for the second parameter, but in this scenario, asI understand it, it never should have reached the second WHEN condition since the first one evaluates to true. Why and why all of a sudden?
View 10 Replies
View Related
Sep 18, 2015
This is kind of a follow-up on my previous post "Get 15th and last day trans". Our client has asked for open sales orders dollars for a given date range to include order dollars active as of the 15th of the month and the last day of the month.
Below is what I started and I hard-coded the Jan15Open and JanEndOpen so you could see what I am trying to accomplish. So, for example, if I pass @StartDate of '01-01-2013' and @EndDate of '12-31-2014' I could get back results for 01-15-2013, 01-31-2015, 02-15-2013, 02-28-2013, etc. all the way to 12-31-2014. Note that the results will depend on the TransDate datetime column. Once I get the query correct I plan to create a temp table to hold the results so I can report them back in a web page.
ALTER PROCEDURE [dbo].[afa_selSalesOrdersOpen]
@StartDatedatetime = null,
@EndDatedatetime = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
[Code] ....
View 9 Replies
View Related
Sep 24, 2015
I use Order By Used with Case Statement and may be this is the reason for which i am getting error
Windowed functions can only appear in the SELECT or ORDER BY clauses.
Here is my complete sql where i use Row_Number function for pagination with sorting.
DECLARE @StartIndex INT
DECLARE @EndIndex INT
DECLARE @SortColumn VARCHAR(MAX)
DECLARE @SortDirection VARCHAR(MAX)
SET @StartIndex = 1
[Code] ....
View 7 Replies
View Related
Sep 18, 2015
select
row_number() over (partition by merrickid order by recorddate asc) as rn,
merrickid,
recorddate,
allocestgasvolmcf,
sum(allocestgasvolmcf) over (partition by merrickid order by recorddate asc) as xxxxxxxx
from dbo.completiondailytb
where merrickid=1965
I get the following message after I insert the sum() over function:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'order'.
View 4 Replies
View Related
Nov 17, 2015
I have a table like below
CREATE table #TempTable (ID integer,CTime time(7),CType Varchar(1))
insert into #TempTable VALUES (1001,'16:50:05.0000000','I')
insert into #TempTable VALUES (1001,'13:27:49.0000000','O')
insert into #TempTable VALUES (1001,'20:44:00.0000000','O')
insert into #TempTable VALUES (1001,'21:12:00.0000000','O')
I need result like below screen shot
here 'I' stands for In,and 'O' for Out, in this example first In time not available
View 7 Replies
View Related
Apr 22, 2015
I am using SQL 2012 SE. I have 2 databases say A and B with same structure and relationships. There are 65 tables in each database. A is already replicating data to database C for 35 tables. Now I need to move data from A to B which is greater than getdate()-1 everyday for all the tables and once the move is done I need to delete this data from A. And samething the next day. Since this is for 65 tables its challenging to identify the insert order. Once the insert order is identified the delete order will be the reverse of it.
Is there a tool or any SP that could generate the insert order script? The generate scripts data only is generating the entire data and these databases are almost 400GB. So I thought of generating a schema only script and then create an empty database with it and then generate data only to identify the order of insert. But it wont generate anything since there is no data.
View 10 Replies
View Related
Jun 9, 2015
I am trying to find a solution in order to make a pivot dynamically. One of my department charge every month all the sales figure in one table and I need to pick up the last two months archived in order to make a pivot and to see if something is changed or not. What I am trying to do is to have these last two months dynamically. create table forum (customer varchar (50), nmonth varchar(6), tot int, archived datetime)
insert into forum values ('Pepsi','201503',100,'2015-04-28'),
('Pepsi','201504',200,'2015-04-28'),
('Texaco','201503',600,'2015-04-28'),
('Texaco','201504',300,'2015-04-28'),
[code]...
As you can see I have to change manually the values underlined every months but it's a temporary solution. How can I set up the last two months in a dynamic way?
View 3 Replies
View Related
Jun 25, 2015
Using t-sql, how to list all views in dependency order? I'm trying to script out the creation of views, but need to make sure they are in the correct order when i execute them.
View 2 Replies
View Related
Jun 24, 2015
I'm asking my previous question in this new thread with more specific data and condition.I have below sample data,
DECLARE @DATA TABLE (U_ID INT, U_NM NVARCHAR(20), U_DT DATE, U_ORD INT, P_U_NM NVARCHAR(20) NULL)
INSERT INTO @DATA VALUES (1, 'Design', '06/15/2015', 2, NULL), (1, 'Plan', '07/01/2015', 1, NULL), (1, 'Cust Plan 1', '06/10/2015', 0, 'Plan'), (1, 'Cust Plan 2', '06/05/2015', 0, 'Plan'),
(2, 'Design', '06/25/2015', 2, NULL), (2, 'Plan', '06/20/2015', 1, NULL)
We have 2 different U_ID (1, 2) and I want a SELECT query to display,
1. For U_ID = 1, we have 2 parent U_NM (Design & Plan) and Plan having 2 child (Cust Plan 1 & Cust Plan 2).
2. I want to display parent U_NM ORDER BY U_ORD
3. If any parent having child element, then need to show immediately under that parent and ORDER BY U_DT
4. For U_ID = 2, we don't have any child, hence display ORDER BY U_ORD
View 10 Replies
View Related
Oct 7, 2015
I have a table that has the records as shown below
Id From To
1 Delhi Noida
2 Faridabad Kanpur
3 Noida Delhi
4 Kanpur Faridabad
5 Banglore Mumbai
6 G H
7 I J
8 Mumbai Banglore
I want the results in the following order
5 Banglore Mumbai
8 Mumbai Banglore
1 Delhi Noida
3 Noida Delhi
2 Faridabad Kanpur
4 Kanpur Faridabad
6 G H
7 I J
Suppose as in above records There is a complete roundtrip from Banglore to Mumbai and Mumbai to Banglore.
View 11 Replies
View Related
Apr 20, 2015
I have used Aasim Abdullah's (below link) stored procedure for dynamically generate code for deletion of child tables based on parent with certain filter condition. But I am getting a output which is not proper (Query 1). I would like to have output mentioned in Query 2.
Link: [URL]
--[Patient] is the Parent table, [Case] is child table and [ChartInstanceCase] is grand child
--When I am deleting a grand child table, it should be linked to child table first followed by Parent
--- Query 1
DELETE Top(100000) FROM [dbo].[ChartInstanceCase]
FROM [dbo].[Patient] INNER JOIN [dbo].[Case] ON [Patient].[PatientID] = [Case].[PatientID]
INNER JOIN [dbo].[ChartInstanceCase] ON [Case].[CaseID] = [ChartInstanceCase].[CaseId]
WHERE [Patient].PracticeID = '55';
--Query 2
DELETE Top(100000) [dbo].[ChartInstanceCase]
FROM [dbo].[ChartInstanceCase] INNER JOIN [dbo].[Case] ON [ChartInstanceCase].[CaseId]=[Case].[CaseID]
INNER JOIN
[dbo].[Patient] ON [Patient].[PatientID] = [Case].[PatientID]
WHERE [Patient].PracticeID = '55';
how to modify the SP 'dbo.uspCascadeDelete' to get the output as Query 2
View 15 Replies
View Related
Jul 23, 2015
In y sql server table has millions of records available. I don't want to drop the tables.
My requirement is I want to change the column order of an existing table. some tables I am able to saving on design window Like Below image.
Even I had generate a script and using that script trying to execute on Management Studio but unable to saving the new column orders. I am getting the Timeout expired error after couple of minutes. How can we save the orders without dropping the table !
View 13 Replies
View Related
Jun 23, 2015
Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id.
Problem: When I execute the stored procedure I sp_Test I retrive the list that is from 1 to 8. I don't know how to do it?
Information: I'm using SQL server 2012
create table datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);
insert into datatable values
[Code] .....
View 2 Replies
View Related
Sep 16, 2015
In SQL 2012.A query that joins 2 table, with order by clause doesn't get sorted and the result set is not ordered. This happens when some of the columns in the where criteria are in a unique index which is the index that is used for the join between the 2 tables, and all the columns in the unique index are in the where criteria.In the query plan there is no component for sort.The work around was to drop the unique index, or change it to a non-unique index. Once this was done, the execution plan was changed to add the sort component (even when the index was changed to non-unique and the join was still using this index).
View 4 Replies
View Related
Sep 5, 2015
I am really puzzled by an apparent difference between table index key column order and its statistics order. I was under understanding that index statistics mirror index definition. However, in my db 2470 index ordinal definitions match statistics definition but 66 do not. I also can reproduce such discrepancy in 2008 R2, 2012 and 2014.
As per definition,
stats_column_id
int
1-based ordinal within set of stats columns
This script duplicates this for me.
BEGIN TRAN
GO
use tempdb
GO
CREATE TABLE [dbo].[ItemProperties](
[itmID] [int] NOT NULL,
[cpID] [smallint] NOT NULL,
[ipuID] [tinyint] NOT NULL,
[Code] ....
The result I get is this:
object_id stats_name
stats_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, cpID, ipuID,
and
object_id index_name
index_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, ipuID, cpID,
Also a query I used to discover this in my db is:
WITH stat AS
(
SELECT
s.object_id
,s.name as stats_name
,(
SELECT
c.name + ', ' as [data()]
FROM sys.stats_columns as sc
[Code] .....
View 6 Replies
View Related
Sep 29, 2015
I want to get the list of items present in that order based on the confidentiality code of that product or Item and confidentiality code of the user.
I display the list of orders in first grid, by selecting the order in first grid I display the Items present in that order based on the confidentiality code of that item.
whenever order in 1st grid is selected i want to display the items that the item code should be less than or equal to the confidentiality code of the logged-in user other items should not display.
If the all the items present in the order having confidentiality code greater than Logged-in user at that time the order no# should not display in the first grid.
Table 1:Order
Order_Id Order_No Customer_Id
2401 1234567 23
2402 1246001 24
2403 1246002 25
Table 2 : OrderedItems
OrderItem_Id Order_Id Item_Id Sequence
1567 2401 1001 1
1568 2401 1003 2
1569 2402 1005 1
1570 2402 1007 2
1571 2403 1010 1
Table 3: ItemMaster
Item_Id Item_Name confidentCode
1001 Rice Null
1003 Wheet 7
1005 Badham Null
1007 Oil 6
1010 Pista 8
Out put for 1st grid
**Note :** Logged-in user have confidentiality code 6
Order No Customer
1234567 23
1246001 24
3rd order is not displayed in the grid
After user selects the 1st order in the grid then the items present in that 1st order should be displayed as
1001 Rice
the second item not displayed because that having confidentiality code greater than user.
After user selects the 2nd order in the grid then the items present in that order should displays
1005 Badham
1007 Oil
I need the query to display the order details in 1st grid.
View 3 Replies
View Related
Jun 14, 2007
Hi, I am using below query:
SELECT tbh_Articles.ArticleID, tbh_Articles.AddedDate, tbh_Articles.AddedBy, tbh_Articles.CategoryID, tbh_Articles.Title, tbh_Articles.Abstract, tbh_Articles.Body, tbh_Articles.Country, tbh_Articles.State, tbh_Articles.City, tbh_Articles.ReleaseDate, tbh_Articles.ExpireDate, tbh_Articles.Approved, tbh_Articles.Listed, tbh_Articles.CommentsEnabled, tbh_Articles.OnlyForMembers, tbh_Articles.ViewCount, tbh_Articles.Votes, tbh_Articles.TotalRating, tbh_Articles.ImageURL, tbh_Articles.specialFROM tbh_Lang CROSS JOIN tbh_ArticlesWHERE (tbh_Lang.LangID = @LanguageID) AND (tbh_Articles.ArticleID = tbh_Lang.ArticleMain OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond1 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond2 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond3 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond4 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond5)
Problem is that I want to sort in a manner which the results returns as ArticleMain as the first column, ArticleSecond1 as the second and so on...
Tables structure: tbh_Articles(id, title, body...) ; tbh_Lang(id,ArticleMain,ArticleSecond1 ,ArticleSecond2.... )
Any suggestions?
View 9 Replies
View Related
Sep 29, 2007
I have a Comment Table where a comment can have a reply, if the comment is replied to I want the reply to appear under the comment.
Based on the Fields CommentID and Parent ID the parentID is the Comment and the Comment with a ParentID set too that comment is the answer.
How do I build this Query?
View 7 Replies
View Related
Jun 26, 2015
I have a table with below kind of data,
DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ItemDate DATE, ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ItemDate, ParentItemName, ItemOrder, ReportId)
VALUES ('Plan', '2015-06-01', NULL, 1, 20),('Design', '2015-06-01', NULL, 2, 20),('Test', '2015-06-20', NULL, 3, 20),('Complete', '2015-06-30', NULL, 4, 20),
('Design child A', '2015-06-02', 'Design', 1, 20), ('Design child B', '2015-06-01', 'Design', 2, 20),
('Test child A', '2015-06-10', 'Test', 1, 20), ('Test child B', '2015-06-09', 'Test', 2, 20), ('Test child C', '2015-06-08', 'Test', 3, 20),
('Test grand child A', '2015-06-08', 'Test child B', 1, 20), ('Test grand child B', '2015-06-08', 'Test child B', 2, 20)
select * from @TBL
Here I want,
1. to display all parent with ORDER BY ItemOrder (no need to sort by ItemDate)
2. display all child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
3. display all grand child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
Looking for below output ...
View 3 Replies
View Related
Jul 30, 2015
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
View 13 Replies
View Related
Feb 7, 2008
Hi all,
Been having a good root around the forums and the site here and there's some real smart people on here, i'm hoping one or more of them can help me out. I'm expecting this to be a simple question for some of you, however it's way beyond me at this point!
Table Structure (abridged, relevant columns):
Orders:
Code:
[Orders](
[OrderID] [int] IDENTITY(1,1)
[OrderDateTime] [datetime]
[OrderSiteID] [nvarchar](255)
[OrderOffline] [bit]
[OrderSentToWP] [bit]
[OrderReceivedFromWP] [bit]
[OrderAuthorised] [bit]
[OrderCancelled] [bit]
[OrderApproved] [bit]
[OrderFraud] [bit]
[OrderDispatched] [bit]
OrderItems:
Code:
[OrderItems](
[OrderID] [int]
[ProductID] [nvarchar](255)
[Quantity] [int]
[Price] [real]
[Weight] [real]
Products:
Code:
[Products](
[ProductID] [uniqueidentifier]
[ProductCode] [nvarchar](255)
[ProductTitle] [nvarchar](255)
Product price is captured at time of order, so that reports aren't affected by discounts or promotions, and stored with the productid in orderitems.
I want to get a report between a set of dates and with certain flags set (see below example) and then get a list of unique products, quantity sold and sales values for that products. Results table would have 4 columns; ProductCode, ProductTitle, QuantitySold, Sales Value.
So far I have this:
Code:
SELECT Products.ProductCode, Products.ProductTitle, SUM(OrderItems.Quantity) AS QuantitySold
FROM Orders INNER JOIN
OrderItems ON Orders.OrderID = OrderItems.OrderID INNER JOIN
Products ON OrderItems.ProductID = Products.ProductID
WHERE (Orders.OrderDateTime BETWEEN '2007/01/01' AND '2007/12/31') AND
(Orders.OrderSentToWP = 1) AND (Orders.OrderReceivedFromWP = 1) AND (Orders.OrderAuthorised = 1) AND (Orders.OrderCancelled = 0) AND
(Orders.OrderDispatched = 1) AND (Orders.OrderApproved = 1) AND (Orders.OrderFraud = 0) AND Orders.OrderSiteID= 'someguid'
GROUP BY Products.ProductCode, Products.ProductTitle
Which gets my summed quantities, and I guess I could use ASP to multiply that by the current price, but that defeats the point of setting the database up properly in the first place! I know how to design data, i just don't know how to get it back out again
I could most likely just do the whole thing in ASP and get it to output the correct answer, so if it's impossible/very difficult to do it in pure SQL then I'll go that route. Ideal situation would be a stored proc or saved query that I can pass a start date, an end date and a siteid to and that will get me the answers I want!
Thanks in advance to anyone that looks at this for me.
Also, any recommended books/sites to learn this kind of query?
Richard
View 4 Replies
View Related
Jun 5, 2008
Greatful for any help....
Have the query below which is taking delimited address information from _Venue column. This works well apart from the order it is returned, for example, the output below has the address tittle displayed in a different column for each row
Queen Elizabeth's Hunting Lodge is in Address1
All Saints' Church is in Address2
Audley End House is in Address3
As I need to reference from the query the correct part of the address from the same location each time, is there anyway to get around this?
Thanks in advance
SELECT coalesce (PARSENAME(REPLACE(_Venue,',', '.'),4), '-') address1
,coalesce(PARSENAME(REPLACE(_Venue, ',', '.'),3), '-') address2
,coalesce(PARSENAME(REPLACE(_Venue, ',', '.'),2), '-') address3
,coalesce(PARSENAME(REPLACE(_Venue, ',', '.'),1), '-') address4
FROM table
Address1 Address2Address3Address4
----------------------------------------------------------------------------------------
Queen Elizabeth's Hunting LodgeRangers RoadChingfordLondon E4 7 QH
- All Saints' ChurchShrub End RoadColchester
---Audley End House
View 10 Replies
View Related