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 123»»»

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
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:53 PM
Points: 1,318, Visits: 3,770
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: Today @ 9:49 AM
Points: 7,129, Visits: 13,510
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 9:47 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 1,945, Visits: 2,862
>> I have one table that contains members, and a query which contains related records [sic: Rows are not records] of payments made grouped by month and year. I would like to find all member records [sic] having at least one record [sic] for every month of the year for the past 12 months. <<

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

This is minimal polite behavior on SQL forums. Here is my guess:

CREATE TABLE Membership
(member_id CHAR(10) NOT NULL PRIMARY KEY,
..);

CREATE TABLE Payments
(member_id CHAR(10) NOT NULL
REFERENCES Membership(member_id),
payment_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
payment_amt DECIMAL(9,2) NOT NULL,
..);

Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML.

The report period table gives a name to a range of dates that is common to the entire enterprise.

CREATE TABLE Month_Periods
(month_name CHAR(10) NOT NULL PRIMARY KEY
CHECK (month_name LIKE <pattern>),
month_start_date DATE NOT NULL,
month_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (month_start_date <= month_end_date),
etc);

INSERT INTO Month_Periods
VALUES
('2014-01-00', '2014-01-01', '2014-01-31'), ---2014 jan
('2014-02-00', '2014-02-01', '2014-01-28'), ---2014 feb
..
('2014-12-00', '2014-12-01', '2014-12-31'),
('2014-00-00', '2014-01-01', '2014-12-31'), --- year
..;

These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantages are that it will sort with the ISO-8601 data format required by Standard SQL and it is language independent. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[01][0-9]-00'

CREATE VIEW Monthly_Payments(member_id, month_name)
AS
SELECT DISTINCT P.member_id, M.month_name
FROM Payments AS P, Month_Periods AS M
WHERE Payment_date BETWEEN M.month_start_date AND M.month_end_date;

See how this is all data driven and has no procedural code? This is one of many reasons you need to know that rows are not records. Notice all the extra tools? The lack of proprietary code?

SELECT member_id
FROM Monthly_Payments
WHERE month_name BETWEEN '2013-06-00' AND '2014-05-00'
GROUP BY member_id, month_name
HAVING COUNT(*) = 12;


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1570917
Posted Wednesday, May 14, 2014 10:07 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:59 PM
Points: 3,369, Visits: 7,285
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 1,945, Visits: 2,862
Luis Cazares (5/14/2014)
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.


I know about infixed join syntax; I voted for it when I was on the ANSI Database standards committee You ought to Google up all the options! There are two reasons that the infixed syntax exists are:
1) The [LEFT | RIGHT | FULL] OUTER JOIN was a mess of proprietary options and had no standard. The extended equality (*=, += )in Sybase, Oracle, et al looked alike and worked differently. They were all wrong!
2) We needed a rule for linear query execution. The Standards are laced with the term “effectively”, but we had no rules for testing and defining what that meant! The infixed notation is defined as executed from left to right. It also meant that an SQL without an optimizer could be created within the ANSI/ISO framework.

What happened however, was that we had a general model of a simple two table join. Being a committee we never met a feature we did not like So we added CORRESPONDING (from COBOL, that came from me! Mia Kulpa!), NATURAL (that was relational algebra), OUTER UNION (it was easy to write the definition) and I cannot remember any more of them.

You have to use the infixed OUTER JOIN; there is no option because of the relational algebra.

People that use infixed notation for inner joins do not think in sets. Their mindset is locked into sequential linear language with their strict left-to-right execution. People that use the set-oriented will re-arrange the WHERE clause predicates to show a fact table, clusters of related tables, etc.

The example I use is that infixed people would write a summation with plus signs (a + b + c +..); linear notation, infixed, assumes a sequence. But set oriented people use “big sigma” Σ notation; more abstract, non-linear and a completed set. I use the standard freshman math course into lecture on the two notations, then carry it over to RDBMS.

Over the last few decades, I have found this difference in style (Both are ANSI syntax) is a good symptom of weak or bad SQL programming.

The period table is a good option, but it should be tested as well to see if it's the best.


The performance tricks are to pack it tight (why have a fill factor? It is constant), and index it in descending order since most queries are recent. Only keep 20 to 50 years, since that is usually more than enough.



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1570958
Posted Wednesday, May 14, 2014 11:46 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:59 PM
Points: 3,369, Visits: 7,285
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 1,945, Visits: 2,862
>> 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. <<

Have you ever read Donald Knuth's essays on mathematical notations? You might understand something, but a good notation makes it easy to see and to manipulate. The original Σ notation has the index variable and the initial value in the sequence under the sigma, and the upper bound on top, with the old alchemist ∞ infinity symbol. This violate the law of proximity from typography and visual psychology.

Knuth changed this to put the entire set expression under the sigma with a set expression. For example “ i  {i: ..}” can be as complex as you wish. No initial value, no terminal value. Beats the heck out of ∞ for expressive power.

The infixed notation is easy to read with two tables (like + ). But the ON clause associates with the nearest JOIN. This is based on the “dangling else clause” problem from Algol-60. There is only one way to write a two-table join:

T1
INNER JOIN
T2
ON T1.a = T2.a

but three tables is a problem. Let's add parentheses to show the required execution order

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

versus:

((T1
INNER JOIN
(T2
INNER JOIN
T3
ON T1.a = T3.a
AND T2.b = T3.b)
ON T1.a = T2.a));

And you can do the rest of them. As the number of table increases, the options increase drastically. Now consider

((T1
INNER JOIN
(T2
INNER JOIN
T3
ON T1.a = T3.a
AND T2.b = T3.b)
ON T2.a = T1.a));

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);

The X table is materialized first by definition. In this super-simple example, that would be absurd and a good optimizer would catch it. But add more tables and more predicates, the only safe option will be left to right evaluation. SQL has scoping rules!

Now look at the set notation:

SELECT *
FROM T1, T2, T3
WHERE T1.a = T3.a
AND T2.b = T3.b
AND T2.a = T1.a;

Every time I invoke the query, the optimizer will figure out what to do. I cannot circumvent the optimizer like the infixed notation.

Another advantage is visual. With a touch of your text editor, put the set of all join conditions and predicates that involve table T1:

WHERE T1.a = T3.a
AND T1.a = T2.a

AND T2.b = T3.b;

Now I want to look at the set of all the predicates with T2:

WHERE T2.a = T1.a
AND T2.b = T3.b

AND T1.a = T3.a

I can also quickly check for redundant or conflicting predicates.

>> 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. <<

I agree. But there was no “old outer join” syntax. We had dialect I often mixed INNER and OUTER notations with parens when have a mixed query. I also try to use parens to get the inner joins together so they can be optimized.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1571061
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse