Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Here's how I implemented this...tell me if my logic is sound:</p> <pre><code>/****Create Sample Data*****/ --&gt;Member table IF exists (SELECT 1 from dbo.sysobjects WHERE name = 'Member') DROP TABLE Member GO CREATE TABLE Member (membername VARCHAR(50), memberkey INT) GO INSERT INTO Member VALUES ('mbr1',702), ('mbr2',1382), ('mbr3',1389), ('mbr4',1390), ('mbr5',1391), ('mbr6',1392), ('mbr7',1393), ('mbr8',1394), ('mbr9',1395), ('mbr10',1396), ('mbr11',1397), ('mbr12',1401), ('mbr13',1402), ('mbr14',1404), ('mbr15',1405), ('mbr16',1406), ('mbr17',1516), ('mbr18',1111)--&gt;Should NOT show up in query GO --&gt;Items table IF exists (SELECT 1 from dbo.sysobjects WHERE name = 'Items') DROP TABLE Items GO CREATE TABLE Items (itemname VARCHAR(50), itemkey INT) GO INSERT INTO Items VALUES ('A',1), ('B',2), ('C',3), ('D',4) GO --&gt;UOM table IF exists (SELECT 1 from dbo.sysobjects WHERE name = 'UOM') DROP TABLE UOM GO CREATE TABLE UOM (uomname VARCHAR(50), uomkey INT) GO INSERT INTO UOM VALUES ('QTY', 1) GO --&gt;Fact table IF exists (SELECT 1 from dbo.sysobjects WHERE name = 'Fact') DROP TABLE Fact GO CREATE TABLE Fact (memberkey INT, itemkey INT, uomkey INT, purchaseamount decimal(18,2), quantity INT, purchasedate date) GO INSERT INTO Fact VALUES (702, 1, 1, 1.11, 2, '1/3/2012'),--&gt;Should show up in query (1382, 1, 1, 1.12, 3, '1/4/2013'),--&gt;Should NOT show up in query (1382, 1, 1, 1.14, 2, '7/5/2013'),--&gt;Should show up in query (1404, 1, 1, 1.21, 2, '1/7/2012'),--&gt;Should show up in query (1401, 2, 1, 3.01, 1, '4/2/2013'),--&gt;Should NOT show up in query (1111, 3, 1, 6.92, 1, '12/12/2012'),--&gt;Should NOT show up in query (702, 3, 1, 5.01, 2, '4/1/2011'),--&gt;Should show up in query (1401, 3, 1, 4.01, 1, '6/5/2012'),--&gt;Should show up in query (1397, 4, 1, 5.45, 1, '7/4/2013'),--&gt;Should NOT show up in query (1397, 4, 1, 5.22, 3, '3/15/2011')--&gt;Should NOT show up in query GO /*****Code to get results*****/ BEGIN --&gt;Members to Filter On DECLARE @MemberKeys AS VARCHAR(max) SET @MemberKeys = '702,1382,1389,1390,1391,1392,1393,1394,1395,1396,1397,1401,1402,1404,1405,1406,1516,1844'; --&gt;Parse out comma delimited VALUES into a table variable DECLARE @Member TABLE ( memberkey INT ) DECLARE @spot SMALLINT, @str VARCHAR(max), @sql VARCHAR(max) WHILE @MemberKeys &lt;&gt; '' BEGIN SET @spot = CHARINDEX(',', @MemberKeys) IF @spot&gt;0 BEGIN SET @str = LEFT(@MemberKeys, @spot-1) SET @MemberKeys = RIGHT(@MemberKeys, LEN(@MemberKeys)-@spot) END ELSE BEGIN SET @str = @MemberKeys SET @MemberKeys = '' END INSERT INTO @Member VALUES(CONVERT(VARCHAR(100),@str)) END END; --&gt;Display Results WITH staged(memberkey, membername, itemname ,itemkey, uomname, uomkey, purchaseamount, quantity, price, purchasedate, noitems ) AS ( SELECT m.memberkey ,m.membername ,i.itemname ,i.itemkey ,u.uomname ,u.uomkey ,f.purchaseamount ,f.quantity ,f.purchaseamount/f.quantity as price ,f.purchasedate ,COUNT(m.memberkey) OVER(PARTITION BY i.itemkey )-COUNT(m.memberkey) OVER(PARTITION BY convert(VARCHAR,m.memberkey)+convert(VARCHAR,i.itemkey) ) as noitems FROM Fact f JOIN Member m ON m.memberkey = f.memberkey JOIN Items i ON i.itemkey = f.itemkey JOIN UOM u ON u.uomkey= f.uomkey WHERE EXISTS(SELECT 1 FROM @Member m2 WHERE m.memberkey=m2.memberkey) ) SELECT memberkey, membername, itemname , itemkey, uomname, uomkey, sum(purchaseamount) as purchaseamount , sum(quantity) as quantity , sum(price) as price, max(purchasedate) as purchasedate FROM staged st WHERE noitems&gt;0 and exists( select memberkey, itemkey , uomkey, max(purchasedate) as maxdate from staged st2 where st.memberkey=st2.memberkey and st.itemkey=st2.itemkey and st.uomkey=st2.uomkey group by memberkey, itemkey , uomkey having st.purchasedate=max(st2.purchasedate) ) GROUP BY memberkey ,membername ,itemname ,uomname , itemkey , uomkey ORDER BY itemname ,memberkey; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POSelecting common items for price comparison across 2 or more members
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USjymbo
    1. USjymbo
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSelecting common items for price comparison across 2 or more members
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COLogic is sound - testing on my real tables. I like the use of the cte. Appears to be working faster than my original solution - thanks! I'll check the io and time stats with larger query but I suspect yours will outperform.
      singulars
      1. PO
      1. USbsivel
    2. CO@user1130761, Took a second look at this and found it summing purchase amount across all records for same item and member. Made a quick change and corrected the logic error. Please see updated code.
      singulars
      1. PO
      1. USjymbo
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload