Query on some specific condition

问题:

I want to create a query, according the some condition.

I have one program and one program has lot of packages. then package show according the condition that which package is assigned to contractor then it will show in one column. packages and amount will calculate in different column those package is assigned to contractor.

condition 1: if those packages is assigned to contractor. then this shows 1 in category column. and Stage of progress column shows Contract awarded. then how many packages is assigned to contractor then will show in the Numbers. if contractor is not award then it goes to second condition.

Condition 2: some package is assigned to Bid Under Price Evaluation then show in one column and amount will also show in one column.

Condition 3: some package is assigned to Bid Under Technical Evaluation then show in one column and amount will also show in one column.

In second condition you did get the date then it goes to third condition and check there is technical date or not and that package will show in 3 category.

There is some formula on conversion rate.

Tables Name

1. tblPrograms (vsSysProgramId)
2. tblProgramsAndPackages (vsSysProgramId, iPackage)
3. tblPackages (iPackageId)
4. tblPkgContractAwardDetails (iPackageId, iCurrency)
5. tblCurrencies (iCurrencyId)
6. tblPkgDPRBidSubmissionDetails (iPackage, dtCEAgreement(date Column)

Example:

1.Stage of progress  2.Category    3.No    4.Packages     5.Amount(INR)   6.Amount(Jpy)   7.Amount(USD)    8.%Progress

   Contracts Awarded    I           2       Pac1,Pac2      100             50                200              Amount/totalamount
   BidsUnderPrice       II          2       Pac3,pac4      50               60                120

Total                               4                      150              110               320

回答1:

It sounds like your problem is a mixture of determining the query or queries to use, and some rather complex report layout and formatting requirements.

Don't try to do both of these in T-SQL queries: you'll wind up with complex, slow queries that will be hard to understand and to debug, and even harder to enhance.

I assume that ultimately your data will be displayed somewhere, whether on a screen or on a report. My advice would be to use the features of your reporting tool or web page to evaluate your various conditions and determine what should be hidden or shown. Then, write queries, as simple as you can manage, to return the data needed for the conditions and for display.

T-SQL is not a report-writing tool, and it is not equipped to handle complex data formatting requirements. Those concerns are best left to tools like ASP.NET and SSRS, which can be as flexible as you need them to be in terms of layout, logic, and data formatting.