Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL QUERY REQUIRED


SQL QUERY REQUIRED

Author
Message
nicklibee
nicklibee
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 19
Dear all,

I have following tables

TABLE : A

ENTERPRISE_MAIN_CODE ENTERPRISE_SUB_CODE ENTERPRISE_DESCRIPTION
1 0 A
2 0 B
3 0 C
4 0 D
5 0 E
6 0 F
7 0 G
8 0 H
9 0 I

TABLE : B

ID_NO ENTERPRISE_MAIN_CODE ENTERPRISE_SUB_CODE
1 1 1
2 1 2
3 7 1
4 4 1
5 4 1
6 6 1
7 6 5


TABLE : C
ID_NO ENTERPRISE_ID_NO ENTERPRISE_DATE ENTERPRISE_AMT
1 1 20130109 1000
1 2 20130315 2000
2 3 20120120 3000
2 4 20120305 4000
3 5 20141111 5000
4 6 20130115 6000
5 7 20130320 7000


user inputs only month and year: for example 201303.

the date format is yyyymmdd

o/p details: it has 4 parts which are explained below

1. User should see the count and the amount of the selected year and month (201303 i.e 2013 march data)

2. the previous year of the selected year and month ( count and the amount ) (201203 i.e 2012 march data)

3. the first month of selected year till the selected year's selected month (count and the amount) ( 20130101 - 20130330 )

4. the first month of previous year till the previosu year's month (count and the amount) ( i.e 20120101 - 20120330 )


The expected op is as follows


ENTERPRISE_DESCRIPTION count amt count amt count amt count amt
A 1 2000 1 4000 2 3000 2 7000

B 0 0 0 0 0 0 0 0

C 0 0 0 0 0 0 0 0

D 1 7000 0 0 2 13000 0 0

E 0 0 0 0 0 0 0 0

F 0 0 0 0 0 0 0 0

G 0 0 0 0 0 0 0 0

H 0 0 0 0 0 0 0 0

I 0 0 0 0 0 0 0 0
Thanks

Nick
Neeraj Prasad Sharma
Neeraj Prasad Sharma
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 Visits: 675
Mate sorry but your question is so confusing :-D
could you explain it
in another fashion.

here is your all three tables




Create Table #A( ENTERPRISE_MAIN_CODE INT , ENTERPRISE_SUB_CODE INT , ENTERPRISE_DESCRIPTION Char )
GO
insert into #a values
(1, 0, 'A'),
(2 ,0, 'B'),
(3 ,0, 'C'),
(4 ,0, 'D'),
(5 ,0, 'E'),
(6 ,0, 'F'),
(7 ,0, 'G'),
(8 ,0, 'H'),
(9 ,0, 'I')
GO



Create TABLE #B ( ID_NO int ,ENTERPRISE_MAIN_CODE int , ENTERPRISE_SUB_CODE int )
GO
insert into #B values
(1, 1 ,1),
(2 ,1 ,2),
(3 ,7, 1),
(4 ,4 ,1),
(5 ,4 ,1 ),
(6 ,6 ,1),
(7 ,6 ,5)
GO



Create TAble #C (ID_NO int, ENTERPRISE_ID_NO int ,ENTERPRISE_DATE bigint , ENTERPRISE_AMT int )
GO
INsert into #C VAlues
(1, 1, 20130109 ,1000),
(1, 2 ,20130315 ,2000 ),
(2 ,3 ,20120120 ,3000),
(2 ,4 ,20120305 ,4000),
(3 ,5 ,20141111 ,5000),
(4 ,6 ,20130115 ,6000),
(5 ,7, 20130320 ,7000)
GO


Select * From #a
Select * From #B
Select * From #C

Neeraj Prasad Sharma
Sql Server Tutorials
nicklibee
nicklibee
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 19
the tables what you created are the tables which is used to generate following op

ENTERPRISE
DESCRIPTION count amt count amt count amt count amt
A 1 2000 1 4000 2 3000 2 7000

B 0 0 0 0 0 0 0 0

c 0 0 0 0 0 0 0 0

D 1 7000 0 0 2 13000 0 0

E 0 0 0 0 0 0 0 0

F 0 0 0 0 0 0 0 0

G 0 0 0 0 0 0 0 0

H 0 0 0 0 0 0 0 0

I 0 0 0 0 0 0 0 0

op is based on the year and month only (for example user provide 201303 in selection criteria)

Hope this is clear to you. pls help me to generate the query

thanks
nick
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16525 Visits: 16991
Simply repeating a vague explanation and a table of unformatted gibberish does not explain what you want. You need to explain what the logic here is. We don't know your project, your data or the requirements. You have a bunch of columns in your output, can you explain the business rules to get those values?

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
nicklibee
nicklibee
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 19
Ok thanks for the comments.

The business rule is explained below

This is basically a statistical report for a small and medium enterprise.

With this report user should be able to judge the growth of the business.

The output has 4 parts.

User only provides month and year as input. Say he provides 201303

There is only one input thats the date and month

Part 1 (this is for selected year - 201303)
System should display the count of ENTERPRISE_MAIN_CODE and the amount associated to the ENTERPRISE_MAIN_CODE
Table c data : 1 2 20130315 2000
the output should be A (enterprise_description) 1(count) 2000(amount)


Part 2(this is for previous year – 201203)
System should display the count of ENTERPRISE_MAIN_CODE and amount associated to the ENTERPRISE_MAIN_CODE
Table c data : 2 4 20120305 4000
the output should be A (enterprise_description) 1(count) 4000(amount)


Part 3 (this is for selected year – from 20130101 - 20130330)
System should display the count of ENTERPRISE_MAIN_CODE and the amount associated to the ENTERPRISE_MAIN_CODE
Table C data 1 1 20130109 1000
1 2 20130315 2000

Start counting from 01/01/2013 till 30/03/2013

the output should be A (enterprise_description) 2(count) 3000(amount)



Part 4 (this is for previous year – from 20120101 - 20120330)
System should display the count of ENTERPRISE_MAIN_CODE and the amount associated to the ENTERPRISE_MAIN_CODE
Table C data 2 3 20120120 3000
2 4 20120305 4000

Start counting from 01/01/2012 till 30/03/2012

the output should be A (enterprise_description) 2(count) 7000(amount)

Hope its clear to you

Thanks
Nick
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16525 Visits: 16991
So given the input of "201303" what should the output look like.

I have to say that I am still pretty unclear about what you are trying to do here.

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
nicklibee
nicklibee
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 19
output should be like this

ENTERPRISE
DESCRIPTION | count | amt | count | amt | count | amt | count | amt |

A 1 2000 1 4000 2 3000 2 7000

B 0 0 0 0 0 0 0 0

c 0 0 0 0 0 0 0 0

D 1 7000 0 0 2 13000 0 0

E 0 0 0 0 0 0 0 0

F 0 0 0 0 0 0 0 0

G 0 0 0 0 0 0 0 0

H 0 0 0 0 0 0 0 0

I 0 0 0 0 0 0 0 0
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16525 Visits: 16991
You aren't big on providing information today. Given the excellent ddl and sample data provided by Neeraj can you tell what the relationship between these tables is?

I think it is something like this, but I don't know what the last one would be.


Select *
From #a a
join #B b on b.ENTERPRISE_MAIN_CODE = a.ENTERPRISE_MAIN_CODE and b.ENTERPRISE_SUB_CODE = a.ENTERPRISE_SUB_CODE
join #C c on ???



Your output continues to be very elusive. The description is very confusing.

As a side note, you really should use datetime datatypes when storing datetime information. Storing them as bigint is a waste of space and makes everything far more difficult to work with.

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
nicklibee
nicklibee
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 19
I just need the op displayed above....

its just the count and the amount too be displayed.

thanks
nick
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16525 Visits: 16991
nicklibee (6/20/2013)
I just need the op displayed above....

its just the count and the amount too be displayed.

thanks
nick


Well I don't understand the rules because it is very vague. I also don't understand how to join the tables. I guess you just want me to figure it out and do the work for you? You have to remember that I can't see you screen, I don't know your project or your data structures. The only information I have is what you have posted and what you have posted is not very clear. You came looking for an answer and I am trying to help you find that answer. The problem is that you have only posted part of the question. I can't help you if you don't provide the details.

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search