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

SQL QUERY REQUIRED Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 2:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 1:34 PM
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
Post #1465520
Posted Thursday, June 20, 2013 3:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 27, 2013 5:37 AM
Points: 306, Visits: 524
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
Post #1465539
Posted Thursday, June 20, 2013 3:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 1:34 PM
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
Post #1465554
Posted Thursday, June 20, 2013 7:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:23 PM
Points: 13,272, Visits: 12,103
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)
Post #1465689
Posted Thursday, June 20, 2013 8:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 1:34 PM
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
Post #1465723
Posted Thursday, June 20, 2013 8:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:23 PM
Points: 13,272, Visits: 12,103
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)
Post #1465729
Posted Thursday, June 20, 2013 8:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 1:34 PM
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

Post #1465735
Posted Thursday, June 20, 2013 8:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:23 PM
Points: 13,272, Visits: 12,103
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)
Post #1465742
Posted Thursday, June 20, 2013 9:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 1:34 PM
Points: 14, Visits: 19
I just need the op displayed above....

its just the count and the amount too be displayed.

thanks
nick
Post #1465772
Posted Thursday, June 20, 2013 9:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:23 PM
Points: 13,272, Visits: 12,103
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)
Post #1465777
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse