Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

query help needed Expand / Collapse
Author
Message
Posted Monday, April 14, 2014 3:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:48 PM
Points: 3, Visits: 4
I have one table that contains members, and a query which contains related records of payments made grouped by month and year. I would like to find all member records having at least one record for every month of the year for the past 12 months. How can I do that? Nothing I do seems to work. Thanks in advance.
Post #1561680
Posted Monday, April 14, 2014 5:51 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:35 AM
Points: 4,039, Visits: 10,415
This should get you started
DECLARE @REF_DATE DATE = '2014-06-01';
DECLARE @MEM_PAY_DATE TABLE
(
PAYMENT_ID INT NOT NULL
,MEMBER_ID INT NOT NULL
,PAYMENT_DATE DATE NOT NULL
,PAYMENT_AMOUNT INT NOT NULL
)
INSERT INTO @MEM_PAY_DATE (PAYMENT_ID,MEMBER_ID,PAYMENT_DATE,PAYMENT_AMOUNT)
SELECT
PAYMENT_ID,MEMBER_ID,CAST(PAYMENT_DATE AS DATE) AS PAYMENT_DATE,PAYMENT_AMOUNT
FROM (VALUES
( 1,2,'2013-01-01',500.00),( 2,2,'2013-02-01',500.00),( 3,2,'2013-03-01',500.00)
,( 4,2,'2013-04-01',500.00),( 5,2,'2013-05-01',500.00),( 6,2,'2013-06-01',500.00)
,( 7,2,'2013-07-01',500.00),( 8,2,'2013-08-01',500.00),( 9,2,'2013-09-01',500.00)
,(10,2,'2013-10-01',500.00),(11,2,'2013-11-01',500.00),(12,2,'2013-12-01',500.00)
,(13,2,'2014-01-01',500.00),(14,2,'2014-02-01',500.00),(15,2,'2014-03-01',500.00)
,(16,2,'2014-04-01',500.00),(17,2,'2014-05-01',500.00),(18,2,'2014-06-01',500.00)
,(19,2,'2014-07-01',500.00),( 1,3,'2013-01-01',500.00),( 2,3,'2013-02-01',500.00)
,( 3,3,'2013-03-01',500.00),( 4,3,'2013-04-01',500.00),( 5,3,'2013-05-01',500.00)
,( 6,3,'2013-06-01',500.00),( 7,3,'2013-07-01',500.00),( 8,3,'2013-08-01',500.00)
,( 9,3,'2013-09-01',500.00),(10,4,'2013-10-01',500.00),(11,4,'2013-11-01',500.00)
,(12,4,'2013-12-01',500.00),(13,4,'2014-01-01',500.00),(14,4,'2014-02-01',500.00)
,(15,4,'2014-03-01',500.00),(16,4,'2014-04-01',500.00),(17,4,'2014-05-01',500.00)
,(18,4,'2014-06-01',500.00),(19,4,'2014-07-01',500.00),( 8,2,'2014-12-01',500.00)
) AS X(PAYMENT_ID,MEMBER_ID,PAYMENT_DATE,PAYMENT_AMOUNT);
;WITH DIST_MONTHS AS
(SELECT * FROM
(
SELECT
MPD.MEMBER_ID
,MPD.PAYMENT_DATE
,ROW_NUMBER() OVER
(
PARTITION BY MEMBER_ID, YEAR(MPD.PAYMENT_DATE), MONTH(MPD.PAYMENT_DATE)
ORDER BY YEAR(MPD.PAYMENT_DATE) ASC, MONTH(MPD.PAYMENT_DATE) ASC
) AS MPD_RID
,(YEAR(MPD.PAYMENT_DATE) * 100) + MONTH(MPD.PAYMENT_DATE) AS MPD_YM
FROM @MEM_PAY_DATE MPD
) AS X WHERE X.MPD_RID = 1)
,FULL_YEAR_PAY AS
(
SELECT
DM.MEMBER_ID
,COUNT(DM.PAYMENT_DATE) OVER
(PARTITION BY DM.MEMBER_ID) AS MP_COUNT
,ROW_NUMBER() OVER
(
PARTITION BY DM.MEMBER_ID
ORDER BY (SELECT NULL)
) AS MP_RID
FROM DIST_MONTHS DM
WHERE DM.MPD_YM BETWEEN ((YEAR(DATEADD(MONTH,-11,@REF_DATE)) * 100) + MONTH(DATEADD(MONTH,-11,@REF_DATE)))
AND ((YEAR(@REF_DATE) * 100) + MONTH(@REF_DATE))
)
SELECT
*
FROM FULL_YEAR_PAY FYP
WHERE FYP.MP_RID = 1
AND FYP.MP_COUNT = 12

Post #1561702
Posted Tuesday, April 15, 2014 3:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 10:12 AM
Points: 7,291, Visits: 15,324
Using Eirikur's data set, here's a different way:
;WITH MyOriginalQuery AS ( -- a query which contains related records of payments made grouped by month and year
SELECT PAYMENT_ID, MEMBER_ID, [Year] = YEAR(PAYMENT_DATE), [Month] = MONTH(PAYMENT_DATE), PAYMENT_AMOUNT
FROM @MEM_PAY_DATE
)
SELECT m.*
FROM (
SELECT
MEMBER_ID,
MonthsPaid = COUNT(*) OVER(PARTITION BY MEMBER_ID)
FROM MyOriginalQuery q
INNER JOIN (
SELECT
[Year] = YEAR(DATEADD(month,-n,GETDATE())),
[Month] = MONTH(DATEADD(month,-n,GETDATE()))
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) d (n)
) ym
ON ym.[Year] = q.[Year] AND ym.[Month] = q.[Month]
) d
INNER JOIN Members m
ON m.MEMBER_ID = d.MEMBER_ID
WHERE d.MonthsPaid = 12



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1561791
Posted Tuesday, May 13, 2014 5:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:48 PM
Points: 3, Visits: 4
Thank you! I'll try it.
Post #1570591
Posted Tuesday, May 13, 2014 5:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:48 PM
Points: 3, Visits: 4
Thank you! I'll try this one too!
Post #1570592
Posted Wednesday, May 14, 2014 10:07 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 4,836, Visits: 11,216
It's funny how Joe Celko can suggest that someone learns so many ISO standards and can't even learn an ANSI standard that has been out for 22 years. Please use SQL-92 JOINS.
The period table is a good option, but it should be tested as well to see if it's the best.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1570925
Posted Wednesday, May 14, 2014 11:46 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 4,836, Visits: 11,216
You're assuming that all people think the same way.
People that think in sets, are very capable to understand commutativity so they can understand that a + b + c = c + a + b. Which can use "big sigma" notation.
The problem would be consistency. As this is a SQL Server 2014 forum, the old join syntax would not be available for outer joins. Even worse than using SQL-86 joins is to mix both standards just because you claim it's the way to think on a set based manner.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1570962
Posted Wednesday, May 14, 2014 5:01 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 4,836, Visits: 11,216
You really love to overcomplicate things.
Even worse, I can force the order of execution with derived tables. I materialize one of the joins, thus

((T1
INNER JOIN
T2
ON T1.a = T2.a)
AS X
T3
ON X.a = T3.a);

You can't be sure about that. In SQL Server (because this is a SQL Server site), there's no guarantee of the order of the execution unless you use a hint to your query.

Another advantage is visual.

If you take your time to format your code correctly, you don't need to move around the predicates to identify the relations of the tables. Using ON clauses, you can differentiate between relationships and conditions. I find a lot easier to read when you write one table per row with its relationships explained, than throwing all of them in a single row and trying to differentiate what's a relationship and what's a condition for the query.

I also try to use parens to get the inner joins together so they can be optimized.

Again, that won't give optimization. If you aren't sure about how a specific engine works, you shouldn't claim something will optimize a query. And no, there's no one-query-fits-all.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1571069
Posted Wednesday, May 14, 2014 9:14 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 3:27 PM
Points: 21,782, Visits: 34,452
< horse riding in ... clop, clop, clop, clop, clop > Everyone else is wrong!! My way is the only way!! < clop, clop, clop, clop, clop ... horse riding out >


(here we go again)



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1571098
Posted Thursday, May 15, 2014 2:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 10:12 AM
Points: 7,291, Visits: 15,324
SQL Server will join tables in whatever order results in the lowest-cost plan. Join order doesn’t matter. The order of ON clauses matters – and can be manipulated to do some funky stuff.

“You have a query which joins three tables, say Customers, Orders and Order Lines. It’s an outer join between Customers and Orders because you want all customers in the output whether or not they’ve ever placed an order. The order can’t be empty, it must actually have some lines, and these should appear in the output.”

Create a customer table with three customers, Peter, Simon and Chris
CREATE TABLE #Customers (CustomerID INT IDENTITY(1,1), CustomerName VARCHAR(20))
INSERT INTO #Customers (CustomerName) VALUES ('Peter'), ('Simon'), ('Chris')

Three orders for Peter, two orders for Simon and none for Chris (boo hoo)
CREATE TABLE #Orders (OrderID INT IDENTITY(1,1), CustomerID INT)
INSERT INTO #Orders (CustomerID) VALUES (1),(1),(1),(2),(2)

Only one of those orders has any items on it – Peter’s first order
CREATE TABLE #Orderlines (OrderlineID INT IDENTITY(1,1), OrderID INT, PartName VARCHAR(20))
INSERT INTO #Orderlines (OrderID, PartName) VALUES (1, 'Peter01'), (1, 'Peter02')

Then you write the obvious query:

-- Query 1
SELECT c.*, o.*, ol.*
FROM #Customers c
LEFT JOIN #Orders o ON o.CustomerID = c.CustomerID
INNER JOIN #Orderlines ol ON ol.OrderID = o.OrderID

The result set isn’t what you might expect – there are only two rows, corresponding to Peter’s two items. The result set looks as if SQL Server has changed the outer join to an inner join and the execution plan confirms this. Most TSQL coders know and will sometimes begin by testing a query where both child tables are outer joined:

-- Query 2
SELECT c.*, o.*, ol.*
FROM #Customers c
LEFT JOIN #Orders o ON o.CustomerID = c.CustomerID
LEFT JOIN #Orderlines ol ON ol.OrderID = o.OrderID

- Which returns too many rows and is tricky to filter to meet the requirements. So they switch to this:

-- Query 3
SELECT c.*, o.*
FROM #Customers c
LEFT JOIN (
SELECT o.*, ol.OrderlineID, ol.PartName
FROM #Orders o
INNER JOIN #Orderlines ol ON ol.OrderID = o.OrderID
) o
ON o.CustomerID = c.CustomerID

- Which generates the correct result set as you would expect, and the execution plan confirms an inner join and an outer join.
You could also bracket your joins, but it’s so counterintuitive and tricky to maintain that I’m not even going to provide an example. If you must have a look, use Google – then forget what you’ve seen because if you use it, you risk defenestration by other TSQL coders. There is another way, and that is to change the order of the ON clauses, so that Orders and Orderlines are inner joined before the product is then joined to the Customer table:

-- Query 4
SELECT c.*, o.*, ol.*
FROM #Customers c
LEFT JOIN #Orders o
INNER JOIN #Orderlines ol
ON ol.OrderID = o.OrderID
ON o.CustomerID = c.CustomerID

This also generates the correct result set, confirmed by the execution plan. It’s easy to see what’s going on and the plan is only trivially different from query 3 – it’s slightly “cheaper”.

There are two points to make here. Firstly, join order doesn’t matter to SQL Server but ON clause order does. Secondly, TSQL coders like any other programmers choose a formatting style for their code which offers to them the fastest “read-to-understand” interval – the length of time it takes to figure out what the code is supposed to be doing. For most of us, that means putting join predicates in ON clauses, putting ON clauses immediately after the second table to which they relate, and putting filters in the WHERE clause for inner joins or in the ON clause for outer joins. There are rules for the first table in the FROM list too. If you take a look at the TSQL posted on ssc by the most prolific TSQL responders, almost all of it adheres to this format. I don’t believe any of them considered ANSI standards when they chose to code in this way. Most don’t give a flying snake. What they care about is their own productivity, and what they gain is exchangeability with other TSQL coders. We can read and understand each other’s code really fast.




“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1571191
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse