query

Question

query

nicklibee

SSC Enthusiast

Points: 124
More actions
June 25, 2013 at 1:07 pm

#395038

dear all,
i have follwoing table
REGION date_apprvd sal
a 20130101 1000
a 20130201 2000
a 20130301 3000
b 20140101 4000
b 20140201 5000
b 20140301 6000
c 20130101 7000
c 20130201 8000
c 20130301 9000
d 20130101 1100
d 20130201 2100
d 20130301 3100
e 20140101 4100
e 20140201 5100
e 20140301 6100
pls help me to create a query
input is 20130101
region sal count
a 1000 1
b 0 0
c 7000 1
d 1100 1
e 0 0
thanks
nick

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323506 More actions · Answer 1

well here's a runnable query base don your copy/paste, but i couldn't figure out where the b 0 0 data came from for the desired results;

if you can explain it better, maybe someone can offer some better help.

;With MySampleData([REGION],[date_apprvd],[sal])

AS

(

SELECT 'a','20130101','1000' UNION ALL

SELECT 'a','20130201','2000' UNION ALL

SELECT 'a','20130301','3000' UNION ALL

SELECT 'b','20140101','4000' UNION ALL

SELECT 'b','20140201','5000' UNION ALL

SELECT 'b','20140301','6000' UNION ALL

SELECT 'c','20130101','7000' UNION ALL

SELECT 'c','20130201','8000' UNION ALL

SELECT 'c','20130301','9000' UNION ALL

SELECT 'd','20130101','1100' UNION ALL

SELECT 'd','20130201','2100' UNION ALL

SELECT 'd','20130301','3100' UNION ALL

SELECT 'e','20140101','4100' UNION ALL

SELECT 'e','20140201','5100' UNION ALL

SELECT 'e','20140301','6100'

)

SELECT * FROM MySampleData WHERE [date_apprvd] = '20130101'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323506 More actions · Answer 2

ok if you have a master table of regions, you can join it agaisnt your transacitons? table?

;With Regions(REGION)

AS

(SELECT 'a' UNION ALL

SELECT 'b' UNION ALL

SELECT 'c' UNION ALL

SELECT 'd' UNION ALL

SELECT 'e' UNION ALL

SELECT 'f'

)

,MySampleData([REGION],[date_apprvd],[sal])

AS

(

SELECT 'a','20130101','1000' UNION ALL

SELECT 'a','20130201','2000' UNION ALL

SELECT 'a','20130301','3000' UNION ALL

SELECT 'b','20140101','4000' UNION ALL

SELECT 'b','20140201','5000' UNION ALL

SELECT 'b','20140301','6000' UNION ALL

SELECT 'c','20130101','7000' UNION ALL

SELECT 'c','20130201','8000' UNION ALL

SELECT 'c','20130301','9000' UNION ALL

SELECT 'd','20130101','1100' UNION ALL

SELECT 'd','20130201','2100' UNION ALL

SELECT 'd','20130301','3100' UNION ALL

SELECT 'e','20140101','4100' UNION ALL

SELECT 'e','20140201','5100' UNION ALL

SELECT 'e','20140301','6100'

)

SELECT Regions.REGION,

ISNULL(SUM(CONVERT(int,MySampleData.sal)),0) AS sal,

COUNT(MySampleData.sal) AS count

FROM Regions

LEFT OUTER JOIN MySampleData

ON Regions.REGION = MySampleData.REGION

AND [date_apprvd] = '20130101'

GROUP BY

Regions.REGION

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

nicklibee SSC Enthusiast Points: 124 More actions · Answer 3

my expected op is as follows

region sal count

a 1000 1

b 0 0

c 7000 1

d 1100 1

e 0 0

the ip value is 20130101, if you see my sample, you can find out b and e records doesnot have dates

20130101 , so it should display as 0

nick

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

Without a master table of regions, the query can perform poorly with large data.

CREATE TABLE #MyTable(

REGION char(1), date_apprvd date, sal int)

INSERT #MyTable VALUES

('a', '20130101', 1000),

('a', '20130201', 2000),

('a', '20130301', 3000),

('b', '20140101', 4000),

('b', '20140201', 5000),

('b', '20140301', 6000),

('c', '20130101', 7000),

('c', '20130201', 8000),

('c', '20130301', 9000),

('d', '20130101', 1100),

('d', '20130201', 2100),

('d', '20130301', 3100),

('e', '20140101', 4100),

('e', '20140201', 5100),

('e', '20140301', 6100)

DECLARE @Date date = '20130101'

SELECT REGION,

SUM( CASE WHEN date_apprvd = @Date THEN sal ELSE 0 END) sal,

SUM( CASE WHEN date_apprvd = @Date THEN 1 ELSE 0 END) count

FROM #MyTable

GROUP BY REGION

DROP TABLE #MyTable

Luis C.
General Disclaimer:
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?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286573 More actions · Answer 5

I have a feeling this thread is going like the last one from the poster. http://www.sqlservercentral.com/Forums/Topic1465520-391-1.aspx

Lots of completely vague posts and suddenly, voila! The OP posts a complete random lengthy script with the answer. We are still left wondering what the question was in the first place.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323506 More actions · Answer 6

Sean Lange (6/25/2013)
I have a feeling this thread is going like the last one from the poster. http://www.sqlservercentral.com/Forums/Topic1465520-391-1.aspx
Lots of completely vague posts and suddenly, voila! The OP posts a complete random lengthy script with the answer. We are still left wondering what the question was in the first place.

wow, just reviewed that thread, and it's like a rabbit out of the hat; i'll make sure to encourage this poster to provide real details in the future; it looked like low hanging fruit at first glance, without that context.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

nicklibee SSC Enthusiast Points: 124 More actions · Answer 7

thank you ,, Luis ,,that's the exact answer,,,

neglect these humpty dumpties, they can't even understand what a poster wants..

pitty some fellows here, they can't understand simple things and they will jump on the poster without any reason....

you r too sharp,,

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

nicklibee (6/25/2013)
thank you ,, Luis ,,that's the exact answer,,,
neglect these humpty dumpties, they can't even understand what a person wants..
you r too sharp,,

Lowell posted a complete solution before I did using a master table of regions and you just ignored it.

The other thread mentioned was so confusing that I wasn't even interested on trying to understand it.

Lowell and Sean are great contributors of this forum and they deserve respect as anyone willing to help you for free.

Luis C.
General Disclaimer:
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?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286573 More actions · Answer 9

Nick, I was merely trying to help my fellow posters not waste a lot of time like I did just a day or two ago trying to extract enough information to understand the question to have you suddenly post an answer that was impossible to have achieved based on the information posted. It is too bad that you view me as a "humpty dumpty". I am probably one of the most patient people around here at trying to get posters to provide details. I stick around after everybody else has given up.

Keep in mind that we don't get paid for the stuff we do around here. We post here because we enjoy helping other people. I personally have helped hundreds of people with issues far more complicated than this one. I was able to do that because they provided the details.

I am very glad you found a solution to your issue. Please come visit us again the next time you need help. But I highly suggest you post ddl and sample in a consumable format. If not, you risk being asked for it by all the other ~~posters~~ humpy dumpty's on this site.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 10

nicklibee (6/25/2013)
thank you ,, Luis ,,that's the exact answer,,,
neglect these humpty dumpties, they can't even understand what a poster wants..
pitty some fellows here, they can't understand simple things and they will jump on the poster without any reason....
you r too sharp,,

Niki you cant say in this manner for anyone...

Sean and Lowell are very good contributors and people in this forum and I personally learned a lot from them and they help me many times...

We people are here to help you...

Keep in mind next time whenever you post any queries you must follow this link first:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

These link are for people like you who don't provide proper information and expect answer from incomplete information....;-)

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Lowell SSC Guru Points: 323506 More actions · Answer 11

nicklibee I'm sorry if you took any of the comments here as harsh.

Sean and I,as well as other regular volunteers here have both contributed to tens or hundreds of thousands of posts over time, and based on that experience, we've found that as soon as we can train someone to post their question in a specific manner, they get a working testable answer in literally minutes.

If I can copy and paste some code into SSMS that is representative of the problem,

I can usually create or modify the solution very quickly.

Most people have desktop tunnel vision...they look at their problem with substantially more back story and insight than they usually able to put into a forum post.

Volunteers on the other side of the post, which only have what was pasted, and not the rest of the back story, end up asking for more clarifications.

When we help people overcome that information gap, by getting them to provide DDL and sample data, they have a better experience.

So good in fact, that is one of the main attractions here. Many first time posters spend more time here, and end up contributing and helping others themselves, writing articles, adding blogs, and joining the SQL community in a positive way.

That's all anyone here wants, is to help you become better; it's the only reason I volunteer here; I like helping people.

SQL is kind of unique compared to programming languages, because if you have a problem, you can usually paste twenty or so lines of code and get help;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!