Re: should i Make new database after Financial year - Database Discussion Boards

Re: Sql query that sorts data by top quantity per category

Hardz15-Jul-12 15:18

15-Jul-12 15:18

Hi,

Thanks for the reply, but what I want to accomplish is that to determine the highest Qty then prioritized the Category that has the highest Qty, from this sample Cat3 contains the highest Qty(65) then sorted from high to low, next is Cat2(50), and Cat1(40). If I changed for ex. the Qty at cat1(40) to 95 then Cat1 now is the first priority of sorting, then Cat2 and so on, so the sorting may varies from high to low no. of Qty per Category, not by Category per Qty.

Regards,

Hardz

Re: Sql query that sorts data by top quantity per category

Luc Pattyn15-Jul-12 16:27

Luc Pattyn

15-Jul-12 16:27

This works fine for SQL Server:

SELECT Qty, t1.cat FROM Inv1 AS t1
INNER JOIN
(SELECT MAX(Qty) AS mq, cat FROM Inv1 GROUP BY cat) AS t2
ON t1.cat=t2.cat
ORDER BY mq DESC, Qty DESC

However, you didn't fully specify what you want: when two categories have equal maxima, what should happen? The above may intertwine such categories!

This wouldn't:

SELECT Qty, t1.cat FROM Inv1 AS t1
INNER JOIN
(SELECT MAX(Qty) AS mq, cat FROM Inv1 GROUP BY cat) AS t2
ON t1.cat=t2.cat
ORDER BY mq DESC, cat, Qty DESC

Luc Pattyn [My Articles] Nil Volentibus Arduum

Re: Sql query that sorts data by top quantity per category

Hardz15-Jul-12 16:50

Hardz

15-Jul-12 16:50

Hi Luc,

Your 2nd code worked perfect for me, coz you're right for your assumption that when there's an equal maxima between these categories it will automatically sorted by category.

Thank you very much.

Regards,
Hardz

make a databse with ADO.Net

messages13-Jul-12 4:50

messages

13-Jul-12 4:50

SQL

Hi everyone
Is it possible to create a database of "Data Source configuration wizard"?
let me explain you select it from Data->Add New Data Source in the visual studio and then you select Database in "Choose a Data Source Type"  after select "DataSet" you have to make a "Add Connection" this window has an item "Connect a Database->Select or enter a database Name",Is it possible to create a database instead name of a database?

Re: make a databse with ADO.Net

Sandeep Mewara15-Jul-12 0:15

Sandeep Mewara

15-Jul-12 0:15

messages wrote:
Is it possible to create a database instead name of a database?

No. It's a datasource configuration wizard. It's expected that database is pre-defined. Create your database from before and then configure it using the wizard.

Sandeep Mewara
[My last article]: Server side Delimiters in ASP.NET[^]

should i Make new database after Financial year

Varinder Raii11-Jul-12 20:57

Varinder Raii

11-Jul-12 20:57

Hi Friends..

I want to know should i Make new database after Financial year or just create new session in the current database .

Please tell me with reasons.

Re: should i Make new database after Financial year

Richard MacCutchan11-Jul-12 23:33

Richard MacCutchan

11-Jul-12 23:33

How on earth can anyone answer this question without knowing anything about your business?

speaking as ...

Re: should i Make new database after Financial year

Eddy Vluggen11-Jul-12 23:39

Eddy Vluggen

11-Jul-12 23:39

The idea to start a "new" database comes from an accountant, not a technician; a new year means a new book, a new page, a clean slate to them.

In a database, that's just silly. Include a date on the record, and you can select/manipulate the data is if you were working in a database with only data from that particular year (thanks to the WHERE clause)

Advantages include;

Easy maintenance (one database, as opposed to 20 clients with 4 years worth of databases)
Easy backups (since all is in one database)
Easier cross-querying (summing the data over multiple years)
Easier to understand (KISS applies to almost everything, everywhere)

The only disadvantage is that the accountant won't understand why you put the data in the "same books". That's the reason why they're not involved when creating a technical storage-facility, only when describing the data - they may explain what they need, but can hardly explain how it should be stored.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: should i Make new database after Financial year

PIEBALDconsult13-Jul-12 3:51

PIEBALDconsult

13-Jul-12 3:51

No, but you could have tables where you summarize various periods for future comparisons and such.

Generate Excel From SQL

vvashishta11-Jul-12 19:48

vvashishta

11-Jul-12 19:48

I want to generate Excel Worksheet through a SQL Query fired on the table directly export data to an Excel Sheet.

- Happy Coding -
Vishal Vashishta

Re: Generate Excel From SQL

PIEBALDconsult11-Jul-12 19:54

PIEBALDconsult

11-Jul-12 19:54

No, you really don't. Excel is the devil's workshop.

I prefer to generate XML, it's more flexible. It can also be converted to CSV which Excel can read.

Re: Generate Excel From SQL

Eddy Vluggen11-Jul-12 23:43

Eddy Vluggen

11-Jul-12 23:43

vvashishta wrote:
I want to generate Excel Worksheet through a SQL Query fired on the table directly export data to an Excel Sheet.

..and?

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

SQL CE license question...

Jun Du11-Jul-12 16:41

Jun Du

11-Jul-12 16:41

Do I need a license to deploy SQL CE to my CE devices for commercial use? I heard it's free but am not sure about it.

Best,
Jun

Re: SQL CE license question...

Richard MacCutchan11-Jul-12 23:32

Richard MacCutchan

11-Jul-12 23:32

Why not check with Microsoft, it's their product.

speaking as ...

Re: SQL CE license question...

Eddy Vluggen11-Jul-12 23:33

Eddy Vluggen

11-Jul-12 23:33

It is free for commercial use;

MSDN[^] wrote:
SQL Server Compact 4.0 is freely redistributable under a redistribution license agreement and application developers redistributing SQL Server Compact 4.0 can optionally register at the SQL Server Compact redistribution site. Registering will help the developers in getting information about SQL Server Compact critical security patches and hot fixes that can be further applied to the client installations.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

HELP SQL WILDCARDS PROB

EjojAnrodac11-Jul-12 7:31

EjojAnrodac

11-Jul-12 7:31

Display the last name of all employees who have an 'i' and an 'n' in their last name.

IS THIS STILL AN SQL WILDCARDS WITH A COMBINATION OF AN AND? SIGH..PLS. HELP.. Cry | :((

Re: HELP SQL WILDCARDS PROB

Luc Pattyn11-Jul-12 7:38

Luc Pattyn

11-Jul-12 7:38

YES

Luc Pattyn [My Articles] Nil Volentibus Arduum

Message Closed

11-Jul-12 7:41

EjojAnrodac

11-Jul-12 7:41

Message Closed

Re: HELP SQL WILDCARDS PROB

Luc Pattyn11-Jul-12 7:45

Luc Pattyn

11-Jul-12 7:45

NO

However I could help you when you show what you tried and explained how the results differ from what you want. It isn't my homework.

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

Re: HELP SQL WILDCARDS PROB

jschell11-Jul-12 8:57

jschell

11-Jul-12 8:57

EjojAnrodac wrote:
PLS. HELP..

Steps
1. Learn basics of SQL
2. Learn how AND works in WHERE clauses
3. Learn how wildcards work in LIKE expressions.
4. Use 1-3 to determine the answer.

Re: HELP SQL WILDCARDS PROB

Corporal Agarn11-Jul-12 9:26

Corporal Agarn

11-Jul-12 9:26

Which SQL language are you using? SQL, T-SQL, PL-SQL?

Re: HELP SQL WILDCARDS PROB

R. Giskard Reventlov11-Jul-12 9:45

R. Giskard Reventlov

11-Jul-12 9:45

Let me put you put of your misery: SQL Wildcards[^].

Note the article is very general: you will still have to work out how to apply the information therein.

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

Generate Excel From SQL Server

vvashishta11-Jul-12 3:36

vvashishta

11-Jul-12 3:36

I want to get data of a table in to an Excel direct from a SQL Query.

Can anyone help?

- Happy Coding -
Vishal Vashishta

Re: Generate Excel From SQL Server

gonzalezmail1980h11-Jul-12 4:57

gonzalezmail1980h

11-Jul-12 4:57

Hello,

A quickly lookup in google.com, I´ve obtained this information:

SQL

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT Name, Date FROM [Sheet1$]')
SELECT [Name], GETDATE() FROM msdb.dbo.sysjobs
GO

If you need more informtion, you can use this link:
http://www.mssqltips.com/sqlservertip/1202/export-data-from-sql-server-to-excel/[^]

Regards,
David G. Big Grin | :-D

Trigger for execute exe When inserted a receode to table

hiran12310-Jul-12 23:09

hiran123

10-Jul-12 23:09

....

modified 12-Jul-12 4:32am.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.