May 10, 2015 at 2:05 am
petervdkerk (5/9/2015)
A little explanation:[giftregistry] is the general description of the list, the bridal couple in this is the only owner. They can add a text they want to send to the guests.
[giftregistryitems] contains the items the bridal couple is requesting. Here column amount is the total amount they want of that item, e.g. 6 coffee cups
[giftregistryemails] contains the guests
[giftregistrypurchases] contains which guest has purchased which amount of which item, e.g. guest nr. 3 has purchased 2 coffee cups (of the total 6 requested)
1) The amount column in giftregistrypurchase table, is that the quantity of items?
Yes, the amount of requested items that was purchased
2) In the same table, is the itemid the giftregistryitems.id?
Yes, that's correct. I now noticed in the database diagram that this relationship is not defind...I'll update my original post with the latest DDL.
Made some changes based on this information
😎
High level diagram
+--------------+ +----------------------+ +----------------------+
| giftregistry |-+----------<| giftregistryitems |-+----------<| giftregistrypurchase |
| (the owner) | | (the wish list) | | (the purchase) |
+--------------+ +----------------------+ +----------------------+
One entry for each bridal The wish-list has one entry for Items purchases towards the
couple, the date and time any distinct item, the quantity wish-list, the email of the
of registration, title and desired, the price of the item contributors and the time and
invititation message to and details. date of the purchase registration.
the invitees.
The registryid in the giftregistrypurchases table then becomes redundant
+-----------------+ +--------------------+ +-----------------------+
| giftregistry | | giftregistryitems | | giftregistrypurchases |
+-----------------+ +--------------------+ +-----------------------+
|(pk) id |-(1)--, |(pk) id |-(1)-, |(pk) id |
| listuuid | '--(0,n)-|(fk) registryid | '--(0,n)-|(fk) itemid |
| userid | | title | | emailid |
| title | | ogimg | | amount |
| description | | description | | createdate |
| invitetext | | URL | +-----------------------+
| createdate | | amount |
+-----------------+ | price |
| createdate |
+--------------------+
Now the query for item status on a gift registry becomes very simple
USE Test;
GO
SET NOCOUNT ON;
;WITH ITEM_PURCHASE_STATUS AS
(
SELECT
GI.id
,GI.registryid
,GI.title
,GI.amount AS ASKED_QTY
,GI.price AS ITEM_PRICE
,ISNULL(SUM(GP.amount),0) AS NUMBER_BOUGHT
,COUNT(GP.id) AS NUMBER_OF_PURCHASES
,GI.amount - ISNULL(SUM(GP.amount),0) AS NUMBER_OUTSTANDING
,GI.price * ISNULL(SUM(GP.amount),0) AS PURCHASE_VALUE
,GI.price *
(GI.amount - ISNULL(SUM(GP.amount),0)) AS OUTSTANDING_VALUE
FROM dbo.giftregistryitems GI
LEFT OUTER JOIN dbo.giftregistrypurchases GP
ON GI.id = GP.itemid
GROUP BY GI.id
,GI.registryid
,GI.title
,GI.amount
,GI.price
)
SELECT
GR.id AS registryid
,GR.listuuid
,GR.userid
,GR.title
,GR.description
,GR.invitetext
,GR.createdate
,IPS.id AS itemid
,IPS.title
,IPS.ASKED_QTY
,IPS.ITEM_PRICE
,IPS.NUMBER_BOUGHT
,IPS.NUMBER_OF_PURCHASES
,IPS.NUMBER_OUTSTANDING
,IPS.PURCHASE_VALUE
,IPS.OUTSTANDING_VALUE
FROM dbo.giftregistry GR
INNER JOIN ITEM_PURCHASE_STATUS IPS
ON GR.id = IPS.registryid
Results
registryid listuuid userid title description invitetext createdate itemid title ASKED_QTY ITEM_PRICE NUMBER_BOUGHT NUMBER_OF_PURCHASES NUMBER_OUTSTANDING PURCHASE_VALUE OUTSTANDING_VALUE
----------- -------------------------------------------------- -------------------------------------- -------- ------------ --------------- ----------------------- ----------- ---------------------------------------------------------------------------------- --------- ----------- ------------- ------------------- ------------------ -------------- -----------------
2 83875b62-3cc1-4516-b932-6e60a116cbff 32DD30EB-1691-457B-9FF5-FC41D687E579 my list my descr hey u there?!? 2015-04-29 21:21:53.813 4 coffee cups 12 10 5 2 7 50 70
2 83875b62-3cc1-4516-b932-6e60a116cbff 32DD30EB-1691-457B-9FF5-FC41D687E579 my list my descr hey u there?!? 2015-04-29 21:21:53.813 5 Microsoft Surface Pro 3 - 12" Tablet - 256GB SSD, Intel Core i7 Haswell, 8GB RAM 1 1010 0 0 1 0 1010
May 10, 2015 at 6:44 pm
Thanks!!!
May 10, 2015 at 7:16 pm
Oh, one final question though, I'm trying to add paging to this query, so I now have the following, but I'm unsure where the "WHERE RowNum > 0 AND RowNum <= 10" should go.
I also think I need to add it to the first query as I don't want to retrieve ALL results, but just a smaller subset.
How can I do that?
;WITH ITEM_PURCHASE_STATUS AS
(
SELECT ROW_NUMBER() OVER (ORDER BY registryid asc) as RowNum,
GI.id
,GI.registryid
,GI.title
,GI.amount AS ASKED_QTY
,GI.price AS ITEM_PRICE
,ISNULL(SUM(GP.amount),0) AS NUMBER_BOUGHT
,COUNT(GP.id) AS NUMBER_OF_PURCHASES
,GI.amount - ISNULL(SUM(GP.amount),0) AS NUMBER_OUTSTANDING
,GI.price * ISNULL(SUM(GP.amount),0) AS PURCHASE_VALUE
,GI.price *
(GI.amount - ISNULL(SUM(GP.amount),0)) AS OUTSTANDING_VALUE
FROM dbo.giftregistryitems GI
LEFT OUTER JOIN dbo.giftregistrypurchases GP
ON GI.id = GP.itemid
WHERE RowNum > 0 AND RowNum <= 10
GROUP BY GI.id
,GI.registryid
,GI.title
,GI.amount
,GI.price
)
SELECT
GR.id AS registryid
,GR.listuuid
,GR.userid
,GR.title
,GR.description
,GR.invitetext
,GR.createdate
,IPS.id AS itemid
,IPS.title
,IPS.ASKED_QTY
,IPS.ITEM_PRICE
,IPS.NUMBER_BOUGHT
,IPS.NUMBER_OF_PURCHASES
,IPS.NUMBER_OUTSTANDING
,IPS.PURCHASE_VALUE
,IPS.OUTSTANDING_VALUE
FROM dbo.giftregistry GR
INNER JOIN ITEM_PURCHASE_STATUS IPS
ON GR.id = IPS.registryid
May 10, 2015 at 8:02 pm
Unless something completely escaped me, you can't do paging in T-SQL. You do that in the presentation layer (Reporting Services).
May 10, 2015 at 8:10 pm
Paging is indeed done at the presentation layer, however, I want to only return the data for the current page and not ALL results.
How other of my SQL statements look to support returning only the current page data:
set @SQL='SELECT * FROM (select ROW_NUMBER() OVER (ORDER BY '+@sortExpression+') as RowNum,
* FROM giftregistryitems WHERE registryid=''' + STR(@registryid) + ''') as info
WHERE RowNum > '+STR(@startRowIndex)+' AND RowNum <= ('+STR(@startRowIndex + @maximumRows)+') '
May 10, 2015 at 9:38 pm
Yes, paging can be done in T-SQL. Below is some sample data and two examples of how to do it.
-- Sample Data
DECLARE @sales TABLE
(
sale_id int primary key,
cust_id int NOT NULL,
sale_amt money NOT NULL
);
WITH iTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a)
INSERT @sales
SELECT TOP 1000
N+10000, ABS(CHECKSUM(NewId())) % 5+1, ABS(CHECKSUM(NewId())) % 500
FROM iTally;
DECLARE
@pagenum int = 2,
@pagesize int = 25;
-- Itzik Ben-Gan Solution
WITH C AS
(
SELECT row_num = ROW_NUMBER() OVER (ORDER BY sale_id), *
FROM @sales
)
SELECT *
FROM C
WHERE row_num BETWEEN (@pagenum - 1) * @pagesize +1 AND @pagenum * @pagesize;
-- My alternate solution using NTILE()
WITH pages AS
(
SELECT
row_num = ROW_NUMBER() OVER (ORDER BY sale_id), -- not needed for calculation, used for display purposes,
page_num = NTILE((SELECT COUNT(*) FROM @sales)/@pagesize) OVER (ORDER BY sale_id), *
FROM @sales
)
SELECT *
FROM pages
WHERE page_num = @pagenum;
The Ben-Gan solution is faster (NTILE() is the slowest of the windows "ranking" functions)... But the NTILE solution returns the page number. I included both just to show how a couple ways to do paging using T-SQL.
-- Itzik Ben-Gan 2001
May 12, 2015 at 5:34 am
Thanks everyone!
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply