Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Dev@nlkss
134 Posts |
 Posted - 2009-05-27 : 06:36:12
|
| in my application auditing is done in 6 stages each with any of 6 visits.i have columns like A1V1notes,A1V1Date,A1V2notes,A1v2Date........A1v6notes,A1V6Date,A2V1notes,A2V1Date,..........................A2V6notes,A2v6Date,...A6v1notes,a6v1date...........................A6v6notes,a6v6dateand coresponding to each visit i have some other columns like Personid,... moreclearly A1V1notes means comments entered in 1st Stage 1st Visit A1v1date means auditDate entered in 1st Stage 1st Visitand so on.my Stored procedure accepts 1 input parameter i.e AuditDate.based on the audit date entered i have to show the records.If A6V6Date contains some date means the auditing is done for that site in 6th stage and 6th visit and all the previous columns from A1V1Date to A6V5Date are contains some date values like D1 to D35. my requirement is if i select D2 then All the sites contains D2 date have to display.i.e if A2V3Date contains D2 date then Display that Notes and PersonName.how to write a queryI think it make sence.Thanks for any help.$atya.Love All Serve All. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-27 : 06:42:10
|
Please post 1. Table structure with correct datatypes2. Proper sample data3. Expected output for a few scenarios
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
Dev@nlkss
134 Posts |
Posted - 2009-05-27 : 07:23:55
|
| sorry, my table contains almost 300 columns,here i am posting sample columns with data,SiteId column of int and remaining all are varchar datatype.SiteID A1V1Notes A1V1Date A1V1PersonID A1V2Notes A1V2Date A1V2PersonID A1V3Notes A1V3Date A1V3PersonID A1V4Notes A1V4Date A1V4PersonID A1V5Notes A1V5Date A1V5PersonID A1V6Notes A1V6Date A1V6PersonID.......A6V1Notes A6V1Date A1V6PersonID...A6V6Notes A6V6Date A6V6PersonID1 abc 21-may-2009 21 abc2 22-may-2009 22 abc3 24-May-2009 25 abc4 25-may-2009 27 abc5 27-May-2009 22 abc6 28-May-2009 23 abc61 05-Jun-2009 29 abc66 08-Jun-2009 302 abc 22-may-2009 29 abc2 24-may-2009 20 abc3 25-May-2009 25 abc4 25-may-2009 27 abc5 27-May-2009 22 abc6 28-May-2009 23 abc61 04-Jun-2009 29 abc66 05-Jun-2009 303 abc 25-may-2009 21 abc2 27-may-2009 22 abc3 28-May-2009 23 abc4 29-may-2009 30 abc5 30-May-2009 32 abc6 01-Jun-2009 21 abc61 10-Jun-2009 55 abc66 18-Jun-2009 50PersonIDs are maped with PersonNames table-->if i select 22-may-2009 as audit date then output should beSiteID PersonName Stage VisitCount Notes 1 (22)Name 1 2 abc2 2 (29)Name 1 1 abc1 -->if i select 05-Jun-2009 as audit date then output should beSiteID PersonName Stage VisitCount Notes 1 (29)Name 6 1 abc61 2 (30)Name 2 6 abc62Assume there are noentries for Site-3 for above dates.plz help me.$atya.Love All Serve All. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-27 : 09:44:30
|
| how will you decide which date field the input value needs to be compared with? do you have another parameter for that? |
|
|
|
Dev@nlkss
134 Posts |
Posted - 2009-05-28 : 00:27:32
|
| no we dont have any other parameter.$atya.Love All Serve All. |
|
|
|
Dev@nlkss
134 Posts |
Posted - 2009-05-28 : 02:18:09
|
| Team,any ideas.Thanks for any help.$atya.Love All Serve All. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
Query help