June 20, 2013 at 2:50 am
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
June 20, 2013 at 3:31 am
Mate sorry but your question is so confusing 😀
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
June 20, 2013 at 3:51 am
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
June 20, 2013 at 7:27 am
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 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/
June 20, 2013 at 8:09 am
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
June 20, 2013 at 8:21 am
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 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/
June 20, 2013 at 8:31 am
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
June 20, 2013 at 8:41 am
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 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/
June 20, 2013 at 9:23 am
I just need the op displayed above....
its just the count and the amount too be displayed.
thanks
nick
June 20, 2013 at 9:26 am
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 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/
June 20, 2013 at 9:37 am
pls let me know what information u need from me.
i think i have explained you very clearly what iam looking for.
pls check the business rules mentioned before.
user inputs 201303
what he wants is just the count of "Enterprise Main Code" and the Enterprise amount" to be displayed in part 1 of the op(i.e 201303, pls check the sample data provided)
the second part is the previous year information i.e 201203
the third part is selected year beginning till the month selected which is 01/01/2013 till 01/03/2013
count and the amount
the fourth part is previous year beginning till the month selected which is 01/01/2012 till 01/03/2012
count and the amount
June 20, 2013 at 9:50 am
Dear Sean,
Hope its clear to you.
thanks
nick
June 20, 2013 at 10:10 am
nicklibee (6/20/2013)
Dear Sean,Hope its clear to you.
thanks
nick
No it is not even close to clear. We are going to to break this apart into pieces. Let's start with the "first part".
Here are the business rules that you posted.
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)
I THINK I understand what you are looking for but...as I have said twice before. I don't know the relationship between these tables. How do you join your tables? What column(s) tell me that a row #c is related to a row in #a?
It seems like you want me to do the work for you. What have you tried?
In the meantime maybe you should take a look at this article. http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
_______________________________________________________________
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/
June 20, 2013 at 10:12 am
Following is the relatioship
Table A is related to Table B BY ENTERPRISE_MAIN_CODE AND ENTERPRISE_SUB_CODE
Table C is related to Table B by ID_NO
Table B is used tp link Table A and Table C to get the Enterprise Description.
Hope its clear to you.
The primary key of Table A is ENTERPRISE_MAIN_CODE
The Primary key of Table B is ID_NO
The primary key of Table C is ENTERPRISE_ID_NO
June 20, 2013 at 10:22 am
nicklibee (6/20/2013)
Following is the relatioshipTable A is related to Table B BY ENTERPRISE_MAIN_CODE AND ENTERPRISE_SUB_CODE
Table C is related to Table B by ID_NO
Table B is used tp link Table A and Table C to get the Enterprise Description.
Hope its clear to you.
The primary key of Table A is ENTERPRISE_MAIN_CODE
The Primary key of Table B is ID_NO
The primary key of Table C is ENTERPRISE_ID_NO
Thank you.
So using the sample data and this query I get no results.
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 c.ID_NO = b.ID_NO
_______________________________________________________________
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/
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply