SQL Server Query question

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I have a table that looks something like this (the table also has a unique ID column):

name    value    month    year
John    0.50      11      2014
Mary    0.60      11      2014
Pete    0.60      11      2014
John    0.60      12      2014
Don     0.50      10      2013

If I pass 11 and 2014 to a stored proc, I want to retrieve one record for each of name, like so:

John    0.50      11      2014
Mary    0.60      11      2014
Pete    0.60      11      2014
Don     0.50      10      2013

If I pass 12 and 2014 to a stored proc, I want to retrieve one record for each of name, like so:

Mary    0.60      11      2014
Pete    0.60      11      2014
John    0.60      12      2014
Don     0.50      10      2013

If I pass 10 and 2014 to a stored proc, I want to retrieve one record for each of name, like so:

Mary    0.0       10      2014
Pete    0.0       10      2014
John    0.0       10      2014
Don     0.50      10      2013

How would I do that in sql server?

Posted 25-Nov-14 5:54am

#realJSOP

Updated 25-Nov-14 6:08am

v4

Add a Solution

Comments

PIEBALDconsult 25-Nov-14 12:17pm

What have you tried? :D

Actually, do you really mean 2013 for Don?

#realJSOP 25-Nov-14 12:21pm

Honestly, I don't know how to approach it. I'm not even sure how to ask the freakin question (makes it real hard to google). My brain is completely failing to engage... :)

Yes on the year for Don. Not all names will have a changed value in a given year, and I wanted to return a valid row for each name regardless of stupid user tricks.
Further, new names might be added in the future which of course won't have past values.

PIEBALDconsult 25-Nov-14 12:39pm

"a changed value"

Ah, so a value stays in effect until a new value is set, that's more complex.

3 solutions

Solution 2

Try this one:

SQL

declare @mytable TABLE (
	[name] [nchar](10) NULL,
	[value] [numeric](10, 2) NULL,
	[month] [numeric](2, 0) NULL,
	[year] [numeric](4, 0) NULL
)

insert into @mytable
SELECT 'John' [name] , 0.50 [value] , 11 [month] , 2014 [year]
UNION ALL
  SELECT 'Mary' 'name' , 0.60 'value' , 11 'month' , 2014 'year'
UNION ALL
  SELECT 'Pete' 'name' , 0.60 'value' , 11 'month' , 2014 'year'
UNION ALL
  SELECT 'John' 'name' , 0.60 'value' , 12 'month' , 2014 'year'
UNION ALL
  SELECT 'Don' 'name' , 0.50 'value' , 10 'month' , 2013 'year'


declare @year numeric(4,0) = 2014
declare @month numeric(2,0) = 11;

WITH myCTE(name, my)
AS
(
	select distinct 
	t1.name,
	(select max(cast(cast(year as varchar) + '-' + cast(month as varchar) + '-01' as DATE))
		from @mytable 
		where cast(cast(@year as varchar) + '-' +cast(@month as varchar) + '-01' as DATE) >= cast(cast(year as varchar) + '-' + cast(month as varchar) + '-01' as DATE)
		and t1.name = name) as my
	from @mytable t1 
)
select myCTE.name, t2.value, DATEPART(MONTH, my) as month, DATEPART(YEAR, my) as year
from myCTE left join @mytable t2 on 
(myCTE.name = t2.name and myCTE.my = cast(cast(year as varchar) + '-' + cast(month as varchar) + '-01' as DATE))

Posted 25-Nov-14 8:27am

Zoltán Zörgő

Updated 25-Nov-14 8:39am

v2

Comments

Zoltán Zörgő 26-Nov-14 12:40pm

I would be interested why I got the downvote, as the solution - even not the best possible - is working as expected. :(

Solution 3

In addition to solution 1 and 2 i'd like to recommend a very good article about joins: Visual Representation of SQL Joins[^]. It might help you to understand how joins work.

Posted 25-Nov-14 8:49am

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PIEBALDconsult · Accepted Answer · 2014-11-25T06:29:00

Solution 1

Maybe this will help. LEFT JOIN the list of DISTINCT names to the data and use ISNULL:

(take two)

SQL

DECLARE @yr INTEGER = 2014
DECLARE @mn INTEGER = 10
DECLARE @ym INTEGER = @yr*100+@mn

;
WITH src AS
(
  SELECT 'John' [name] , 0.50 [value] , 11 [month] , 2014 [year]
UNION ALL
  SELECT 'Mary' 'name' , 0.60 'value' , 11 'month' , 2014 'year'
UNION ALL
  SELECT 'Pete' 'name' , 0.60 'value' , 11 'month' , 2014 'year'
UNION ALL
  SELECT 'John' 'name' , 0.60 'value' , 12 'month' , 2014 'year'
UNION ALL
  SELECT 'Don' 'name' , 0.50 'value' , 10 'month' , 2013 'year'
)
, lst AS
(
  SELECT [name]
  , [value]
  , [month]
  , [year]
  FROM (
    SELECT *
    , ROW_NUMBER() OVER ( PARTITION BY [name] ORDER BY [year] DESC , [month] DESC) RN
    FROM src
    WHERE [year]*100+[month]<=@ym
  ) T
  WHERE RN=1
)
, nam AS
(
  SELECT DISTINCT [name] FROM src
)
SELECT A.Name
, ISNULL(B.value,0) 'value'
, ISNULL(B.[month],@mn) [month]
, ISNULL(B.[year],@yr) [year]
FROM nam A
LEFT OUTER JOIN lst B
ON A.name=B.name

Posted 25-Nov-14 6:29am

PIEBALDconsult

Updated 25-Nov-14 6:50am

v2

Comments

#realJSOP 25-Nov-14 12:45pm

works great for old dates that have no values for some names, but if I use 12 and 2014, if should give me the next earliest value (11 2014).

PIEBALDconsult 25-Nov-14 12:50pm

Yep, updated.

PIEBALDconsult 25-Nov-14 13:03pm

It should (I'm using 2012), are you getting an error?
http://msdn.microsoft.com/en-us/library/ms186734(v=sql.105).aspx

#realJSOP 25-Nov-14 13:04pm

Yeah - it's the typical useless "problem by xyz" error

PIEBALDconsult 25-Nov-14 13:06pm

And you have the semi-colon before the WITH ?

#realJSOP 25-Nov-14 13:09pm

yeah - colons for eveyone :)

Richard Deeming 25-Nov-14 13:12pm

Just override the intellisense, and replace PARTITION_FRAGMENT_ID with PARTITION. :)

#realJSOP 25-Nov-14 13:32pm

i did, and now i'm getting "incorrect syntax near T"

(I wanted to make sure I hadn't missed anything before posting this)

PIEBALDconsult 25-Nov-14 13:43pm

I'd have to see the code.

#realJSOP 25-Nov-14 14:09pm

It's been modified to fit my table/column names, but when I copy paste direct from your answer, it works...

#realJSOP 25-Nov-14 14:33pm

Erp - found it - I had an extra parenthesis

Richard Deeming 25-Nov-14 13:07pm

That one catches me out too! As soon as you start to type PARTITION BY, SQL's auto-complete insists on changing it to PARTITION_FRAGMENT_ID. You have to hammer the Esc key like a madman to stop it.

It seems to be fixed in SSMS 2012.

PIEBALDconsult 25-Nov-14 13:16pm

OMG! I turn off intellisence in SSMS, it causes more trouble than it's worth.

Maciej Los 25-Nov-14 14:49pm

+5!